RMAN Restore and Recovery Scenarios
Full Database Restore
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Tablespace Restore (online)
$rman target / nocatalog
RMAN> sql ‘alter tablespace users offline';
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql ‘alter tablespace users online';
Tablespace Restore (offline)
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
database opened
Restoring a Specific Datafile
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile ‘/oradata/DB1/dbf/users01.dbf';
RMAN> recover datafile ‘/oradata/DB1/dbf/users01.dbf';
RMAN> alter database open;
database opened
Control File Restoration
Prerequisite: In your rman backup directory determine the latest control file backup.
Default Format: c-nnnnnnnnnn-nnnnnnnn-nn
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup nomount;
RMAN> set dbid = 1184749195
RMAN> restore controlfile from ‘/oradata/DB1/rman/c-1184749195-20060626-02′
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened
Database Point-In-Time-Recovery (PITR)
Also known as time-based incomplete recovery.
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> recover database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> alter database open resetlogs;
database opened
* Make sure you perform a full backup after this operation!
Restore to Another System
Prerequisites
Ideally ensure destination system configured exactly like source.
Same OS version and patch level.
Same drives (C:, D:, S: etc.).
CPU and RAM same or better.
The same version of Oracle is installed on the target system as the source.
Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.
Ensure the listener is running.
Copy RMAN backupset files to the destination system rman directory.
Procedure
Restore SPFILE and Control File
$rman target / nocatalog
RMAN> set dbid 161080442
RMAN> startup nomount;
Creates the file: %ORACLE_HOME%\database\hc_db1.dat
RMAN> restore spfile from ‘R:\rman\C-161080442-20080313-00′;
Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA
RMAN> startup force nomount
RMAN> restore controlfile from ‘R:\rman\C-161080442-20080313-00′;
RMAN> shutdown immediate
RMAN> exit
Restore and Recover the Data
$rman target / nocatalog
RMAN> startup mount;
RMAN> restore database;
For a large database this step may take some time.
RMAN> recover database;
If you do not have\need the very last log(s) you can disregard any error messages.
ORA-00310: archived log contains sequence 100; sequence 101 required…
RMAN> alter database open resetlogs;
database opened
Full Database Restore
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Tablespace Restore (online)
$rman target / nocatalog
RMAN> sql ‘alter tablespace users offline';
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql ‘alter tablespace users online';
Tablespace Restore (offline)
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
database opened
Restoring a Specific Datafile
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile ‘/oradata/DB1/dbf/users01.dbf';
RMAN> recover datafile ‘/oradata/DB1/dbf/users01.dbf';
RMAN> alter database open;
database opened
Control File Restoration
Prerequisite: In your rman backup directory determine the latest control file backup.
Default Format: c-nnnnnnnnnn-nnnnnnnn-nn
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup nomount;
RMAN> set dbid = 1184749195
RMAN> restore controlfile from ‘/oradata/DB1/rman/c-1184749195-20060626-02′
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened
Database Point-In-Time-Recovery (PITR)
Also known as time-based incomplete recovery.
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> recover database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> alter database open resetlogs;
database opened
* Make sure you perform a full backup after this operation!
Restore to Another System
Prerequisites
Ideally ensure destination system configured exactly like source.
Same OS version and patch level.
Same drives (C:, D:, S: etc.).
CPU and RAM same or better.
The same version of Oracle is installed on the target system as the source.
Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.
Ensure the listener is running.
Copy RMAN backupset files to the destination system rman directory.
Procedure
Restore SPFILE and Control File
$rman target / nocatalog
RMAN> set dbid 161080442
RMAN> startup nomount;
Creates the file: %ORACLE_HOME%\database\hc_db1.dat
RMAN> restore spfile from ‘R:\rman\C-161080442-20080313-00′;
Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA
RMAN> startup force nomount
RMAN> restore controlfile from ‘R:\rman\C-161080442-20080313-00′;
RMAN> shutdown immediate
RMAN> exit
Restore and Recover the Data
$rman target / nocatalog
RMAN> startup mount;
RMAN> restore database;
For a large database this step may take some time.
RMAN> recover database;
If you do not have\need the very last log(s) you can disregard any error messages.
ORA-00310: archived log contains sequence 100; sequence 101 required…
RMAN> alter database open resetlogs;
database opened
Scenario Basis of Recovery
1- If you lost all data files
SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;
2- If you lost a tablespace
SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;
if you can not offline tablespace;
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;
3- if you lost a datafile
SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;
if you cannot offline datafile;
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;
4- if you lost your controlfiles
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;
you will receive an error ORA-01589 when you open database
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;
if you open database with resetlogs, SCN number will be zero. In this situation
all previous backups will be invalid. You must full backup.
5- May be a special situation. You need to incomplete recovery
A. Time-Based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;
B. SCN-Based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;
C. Archive log sequence based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;
6- if you need some archive logs in your backup
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
OR
RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';
7- if your data block is corrupted you will receive an error below.
Error:
ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’
for recover data block;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;
For Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)
To recover, we can give a specific backup set;
# recovery from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# recovery from image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
FROM DATAFILECOPY;
# recovery from backupset which have "FULL" tag
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
FROM TAG = FULL;
During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views. If you run the following command, RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.
RMAN>BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-10';
8- if you have a image copy backup and your datafile number 2 has problems then you
can switch datafile number2 to image copy.
RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;
SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;
2- If you lost a tablespace
SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;
if you can not offline tablespace;
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;
3- if you lost a datafile
SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;
if you cannot offline datafile;
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;
4- if you lost your controlfiles
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;
you will receive an error ORA-01589 when you open database
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;
if you open database with resetlogs, SCN number will be zero. In this situation
all previous backups will be invalid. You must full backup.
5- May be a special situation. You need to incomplete recovery
A. Time-Based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;
B. SCN-Based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;
C. Archive log sequence based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;
6- if you need some archive logs in your backup
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
OR
RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';
7- if your data block is corrupted you will receive an error below.
Error:
ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’
for recover data block;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;
For Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)
To recover, we can give a specific backup set;
# recovery from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# recovery from image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
FROM DATAFILECOPY;
# recovery from backupset which have "FULL" tag
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
FROM TAG = FULL;
During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views. If you run the following command, RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.
RMAN>BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-10';
8- if you have a image copy backup and your datafile number 2 has problems then you
can switch datafile number2 to image copy.
RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;