Recovering a Dropped Table from a Full Database Backup using manual backup and recovery procedures (Doc ID 96197.1)
You cannot follow the note exactly as this note is for a valid full backup where you have all logs needed for point in time recovery – the changes you need to make are in the summary steps above.
Once you have done the recovery we can attempt a force open (you will have to do the restore/recovery again for sys, sysaux and undo because you have done an open resetlogs on the restored database):
YOU ARE ADVISED TO TAKE A FULL COLD OS BACKUP OF THE DATABASE BEFORE PROCEEDING WITH THE FORCE OPEN.
To force open a database:
1. if you are using AUM you need to identify ALL rollback segments listed in your dictionary (system dbfs):
On unix, you can use the unix strings command to extract the rollback segments:
$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU
where system01.dbf is the name of the datafile for the SYSTEM tablespace – if you have > 1 system dbf then you need to do this for each file, writing to a different listSMUn file each time.
2. Mount the database and create a pfile (if you dont already have one)
SQL>create pfile=’init<sid>.ora’ from spfile;
Edit the pfile – add :
Where <list> is the list of rollback segment names extracted as above with ‘$’ appended to each name eg
3. Remount the instance using the amended pfile and then do the following:
SQL>recover database using backup controlfile until cancel;
SQL>alter database open resetlogs;
If this works then you can EXPORT the database contents – the database will have to be completely rebuilt and the data re-imported.
IF this fails then I need to see the failure (ORA-1555, ORA-600, ORA-600, Ora-600 are common errors raised during force open), along with the alert log extract showing the start up after setting these hidden parameters. Please remember to REMOVE the hidden parameters after you have exported – the should never be used without advice from Oracle Support.