rman recovery scenariosCreate PFILE/SPFILE from memory (Oracle 11g) SQL> create spfile='/oracle/spfiletest.ora' from memory; Restore SPFILE Using Recovery Catalog : Start database instance in NOMOUNT mode without a parameter file RMAN> restore spfile; Restoring SPFILE From Autobackup: RMAN> startup nomount; RMAN> set dbid 1112323; ---your dbid can be found on v$database RMAN> restore spfile from autobackup; Restore SPFILE from FRA (FlashRecoveryArea): RMAN> startup nomount; RMAN> restore spfile from autobackup db_recovery_file_dest='....' db_name='DB10G'; Restoring Control File From Autobackup: – All Control Files are lost – Autobackup is configured • Solution: – Start database instance in NOMOUNT mode - Set DBID in RMAN – Restore control file from autobackup RMAN> restore controlfile from autobackup; – MOUNT the database – Recover database – Open the database with RESETLOGS option Restore Control File From Recovery Catalog: – All Control Files are lost – Recover Catalog is configured • Solution(s): – Start database instance in NOMOUNT mode - Setting DBID is not required as recover catalog is configured – Restore control file ( RMAN> restore controlfile ; ) – MOUNT the database – Recover database – Open the database with RESETLOGS option In case you have FRA activated, you can restore also CF : RMAN> restore controlfile from autobackup db_recovery_file_dest='....' db_name='DB10G'; Loosing INACTIVE Redo Log Files: - LGWR terminates the instance with ORA-00321 error as shown: - Start database - Oracle performs crash recovery behind the scenes - The V$LOG.STATUS is updated to NULL - Identify whether redo log was archived or not by querying V$LOG SQL> select group#, status, archived from v$log; - If the redo log file is archived then use CLEAR ARCHIVED command SQL> alter database clear logfile group 4;-- suppose gr4 is with problem - If the redo log file is not archived then use CLEAR UNARCHIVEDcommand SQL> alter database clear unarchived logfile group 4 ; - Open database Alternate solution: Drop and Re-create the redo log SQL> alter database drop logfile member 'xxxx.log'; SQL> alter database add logfile member '/oracle...' to group 4; Then, open the database Loosing CURRENT Redo Log Files: – All the member of an CURRENT redo log group are lost – Valid database backup exist • Solution: – Startup database in MOUNT mode – Identify the last good SCN ( first_change# ) select group#, status, archived, first_change# from v$log; -- Restore database until last good SCN RMAN> restore database until scn ....; – Recover database until last good SCN RMAN> recover database until scn ....; – Re-create the redo log group to a different location SQL> alter database rename file '/oracle/badlog.ora' to '/oracle/goodlog'; – Open database with RESETLOGS option Loosing ACTIVE Redo Log Files: – All the member of an ACTIVE redo log group are lost – Database is Up • Solution: – Issue a Checkpoint SQL> alter system checkpoint; – Check redo log status ( from v$log ) – If Checkpoint is SUCCESS then CLEAR redo log group. SQL> alter database clear unarchived logfile member 4; – If Checkpoint FAILS to complete then perform incomplete recovery by identifying the last good SCN ( see previous case ) Recovering Temporary Tablespaces: As tempfiles aren't checkpointed, we don't need to back them up. We can recreate them at any point after the database has been restored, recovered and opened. Temporary datafiles that belong to locally managed temporary tablespaces are automatically recreated during database recovery. This eliminates the need to manually create temporary tablespaces after recovery Flashback Database: -- Flashback database SQL> flashback database to scn xxx ; – Open database with RESETLOGS option SQL> alter database open resetlogs; Recovering Datafiles Not Backed Up: Problem: – New datafile is added to a tablespace (MOMEN_TS) – Datafile was lost before it could be backed up – Valid database backup exists – All Archive logs exist – Database is UP • Solution: – List datafiles that need recovery (file number 9 ); SQL> select * from v$recover_file ; – Restore datafile RMAN> restore datafile 9 ; -- it will say that this file is new. it will restore a dummy datafile – Recover tablespace RMAN> recover datafile 9; --> will apply archive logs - Bring the tablespace online; Recovering through RESETLOGS: Problem: – Incomplete database recovery was performed (RESETLOGS) – Soon after restore completed, you suffered from another media failure – Backup was not performed after opening database with RESETLOGS option – All the generated archive logs exist • Solution: – Start database in NOMOUNT mode and list incarnations; RMAN> list incarnation; – Restore Control File RMAN> restore controlfile from autobackup; – MOUNT database – Restore database RMAN> restore database; – Recover database RMAN> recover database; – Open database with RESETLOGS option Recovering to a Restore Point : - you have create a restore point SQL> create restore point xxx guarantee flashback database; - you want to restore the database to the restore point created - valid database backups exists Solution: - list restore points SQL> select * from v$restore_point; - shutdown and start the database in mount - restore database until restore point; RMAN> restore database until restore point xxx; - recover database until restore point; RMAN> recover database until restore point xxx; - open with RESETLOGS the database; Recovering to a previous incarnation: SOLUTIONS: - RMAN> list incarnation; - restore controlfile RMAN> restore controlfile from '.<FRA>.' until time "to_date('...','...')"; - mount database; - reset database incarnation : RMAN> reset database to incarnation '....'; - restore database until time; - recover database until time; - open database with resetlogs; http://oradbastuff.blogspot.com/2010/11/rman-recovery-scenarios.html http://momendba.blogspot.com/2010/09/rman-backup-recovery-most-essential-but.html