Tuesday, February 24, 2009

Restore and Recover Inconsistent rman backup

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.

1 comment:

  1. THANK YOU SOOOO MUCH for posting this!

    I 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!

    ReplyDelete