thanks a lot. it works great. a few more questions to triuble u. i had a few tablespaces which had 2-3 more datafiles. i can add these tablespaces and datafiles now, right ? second, what diff does it make whether i am in archive or nonarchive mode ? thanks a lot for u r help. i did learn a lot today.
hm what do you mean you have a few more files to add...? You didnt include all datafiles in your create control file statement? Well it is too late now to add because the file headers have been updated with new SCNs. You will get errors if you wanna add.
Archive log allows you to recover until point of failure (as long as you keep the archived logs)
With no archive log you can only recover until last cold backup, if your last cold backup is from 1 week ago then you would lose 1 week work.
i had mentioned all the datafiles but it seems there was a datafile in d:\oracle\ora81\database. but i couldnt fond any datafile there. now in the dba studo, it is showing me that this datafile is missing but i am not getting the name of the file. just the path. whenever i am querying most of my tables, i am getting this error
ORA-00376: file 9 cannot be read at this time
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: 'D:\ORACLE\ORA81\DATABASE\MISSING00009'
what is the way out ? can i delete this datafile ?
Well I think this is a bit trouble. You said most of your tables cant be queried then seems like itīs the main datafile that is missing and you said itīs not anywhere, so it was deleted accidently? If it was deleted then you will have to do a recovery, it can be a few situations, I doubt I can help remotely.
Do you have backups at all?
Your company should hire a DBA, really. Oracle is pretty complex since it offers so many options, features, and depending on situation you do a specific thing.
If the boss doesnt want to hire a DBA I would suggest using SQL Server.
thanks a lot. i guess i will manage from here onwards. actually my co. wants me to be the dba so i gues i woill have to start studying very hard. :D . i did learn a lot as this is the first time my comapny has encountered a serious database problem. earlier, they simply used to reinstall oracle and import the dump. but i guess i will have to put systems in place. for the time being my database working properly(hopefully). i will just have to import a few tables and process them.can u just tell me how to delete that datafile. right now it is offline. thanks once again. bye and have a nice time.
shutdown the database
alter database datafile 'MISSINGwhatever' offline drop;
select * from v$datafile;
check the status column, see if itīs offline
alter database open;
drop tablespace XXXXX including contents;
where XXXX the tablespace that owns that datafile.