I am trying to test standby database on the same machine. After activating the standby machine, I can not mount the database as it requires the lock file same as the database name (ie, lk<db_name>). Since this file is already used by the primary database, I have to create the control file with a new database name. (say "Create control file set database new_db resetlogs ...da dada")
The problem is with the "Create Controlfile"
I have the following situation. Please let me know how to resolve this.
I created the control file with all the data files, except one. (I purposely did this, as I want to test, wht happens if someone forget to include some of the datafiles)
I open the database (I can open only with the resetlogs)
Now, I select from the table which is part of the omitted datafile.
I get the following error, as expected,
ORA-00376: file 12 cannot be read at this time
ORA-01111: name for data file 12 is unknown - rename to correct file
ORA-01110: data file 12: '/oracle/product/8.1.6/dbs/MISSING00012'
Here is my question:
1, is there anyway to find which file was the original file?
(ie. whats the name of the original file of "/oracle/product/8.1.6/dbs/MISSING00012")
2, Once I have the file name by some means, how do make it part of the database? Is it possible at all, since I did the "resetlogs"?
3, What is the correct way of creating a control file, so that even if I miss the datafile the first time I should be able to add the file later (may be by recreating the controlfile) and should be able to get the data back?.
Is it possble after doing the "resetlogs"?. If not, how to
avoid the "resetlogs" until I make sure all the datafiles are
How do I make sure all the datafiles are in place before
doing the "resetlogs"?
Note: I have done many control file recreation, but haven't tried anything with "RESETLOGS" and "MISSING" filename.
Please advise me of the right way.
Click Here to Expand Forum to Full Width