Have you ever had to restore an oracle database when you did not have all the archived redo logs that occurred during the backup? Well I did and it was an interesting day that required an escalated SR to oracle support. Here is how I recovered.
I restored the tape backup using rman and a backup of the snapshot control file found in my $ORACLE_HOME/dbs directory.
cp $ORACLE_HOME/dbs/snapcf_mydb.f /ora01/mydb.ctl
I mounted the controlfile
startup mount
I connected to rman
rman target /
I restored my inconsistent backup
restore database;
I tried to recover my database but could not since I am missing archived redo logs during the backup.
recover database;
I tried opening the database with alter database resetlogs but could not since system datafile was inconsistent.
I changed init.ora parameter to add the following parameters per Oracle Support recommendation and then restarted the database in mount mode.
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_minimum_giga_scn=6884
_allow_error_simulation=true
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$, _SYSSMU3$,_SYSSMU4$, _SYSSMU5$,_SYSSMU6$,_SYSSMU7$, _SYSSMU8$,_SYSSMU9$, _SYSSMU10$,SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$, _SYSSMU16$,_SYSSMU17$, _SYSSMU18$,_SYSSMU19$,_SYSSMU20$,_SYSSMU21$, SYSSMU22$,_SYSSMU23$,_SYSSMU24$, _SYSSMU25$, _SYSSMU26$,_SYSSMU27$,_SYSSMU28$,_SYSSMU29$, _SYSSMU30$,_SYSSMU31$)
undo_management=manual
In sqlplus I could then do
alter database open resetlogs;
I then created a shell database using dbca
I then exported the full database using
exp system/manager full=y file=full.dmp log=full.log
Next I imported my full export into my new shell database
imp system/manager file=full.dmp log=impfull.log commit=y ingore=yes destroy=no
I then put the new database in archivelog mode so I do not have this problem again.
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
I then created a full backup and archive log backups through grid control.
THANK YOU SOOOO MUCH for posting this!
ReplyDeleteI have followed thread, after thread in trying to recover from a very bad RAID 5 failure, and this was the last piece of the puzzle that allowed me to start my failed instance.
You have truly provided a service to a fellow DevOps!