RMAN Database Restore and Recovery scenarios

RMAN  performs backup and recovery tasks on the databases and automates administration of the backup strategies.
Case 1: ALL DATA FILES ARE LOST
Case 2: FEW BLOCKS IN DATAFILE GOT CORRUPTED
Case 3: ONLY ONE DATA FILE IS LOST/CORRUPTED
Case 6:  ALL DATAFILES OF A TABLESPACE GOT CORUPTED/LOST
Case 5: SPFILE/PFILE LOST
CASE 1 : ALL DATA FILES ARE LOST
Assumption :
you have good backups of your database
you have access to all redo archive files generated after the backup was taken.
you have all the redo required to recover the database datafiles.
You can perform a complete database-level recovery in this situation with either the current control file or a backup control file.
i) Using current control file
In this situation, we simply start up the database in mount mode, issue the restore and recover commands, and then open the database:
$ rman
RMAN> connect target /
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Remember that RMAN will look in older backups if it can’t find a backup piece or if corruption is detected.
RMAN will search through backup history until it locates a good backup or until it exhausts all possibilities. This feature is called restore failover.
What about Read only and temporary tablespaces?
Read only tablespaces:
NOTE: By default, the restore command skips datafiles associated with read-only tablespaces. If you want read-only tablespaces restored, then you must use the check readonly command.
RMAN> restore database check readonly;
Temporary Tablespaces:
You don’t have to restore or re-create missing locally managed temporary tablespace tempfiles. When you open your database for use, Oracle automatically detects and re-creates locally managed temporary tablespace tempfiles.
If you wanna restore form some specific backups which you tagged:
RMAN> restore database from tag WEEKLY_BACK
How to use until clause with RMAN restore command
You can also tell RMAN to restore datafiles from a point in the past using the until clause of the restore command in one of the following ways:
A) Until SCN
If you know the SCN in a backup piece that you want to restore from
RMAN> restore database until SCN 5636176;
B) Until sequence
If you know the log sequence number that you want to restore up to
RMAN> restore database until sequence 17;
C) Until restore point
If you’ve created restore points, then you can also use the restore point name
RMAN> restore database until restore point WEEKLY_BACK;
D) Until time
You can also specify a point in time from which you want RMAN to restore an older backup.
RMAN> restore database until time ‘sysdate – 5′;
RMAN> restore database until time “to_date(’08-jan-2016 13:00:00′, dd-mon-rrrr hh26:mi:ss’)”;
ii) Using backup control file
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
You are required to open your database with the open resetlogs command anytime you use a backup control file during a recovery operation.
CASE 2: FEW BLOCKS IN DATAFILE ARE CORRUPT
There can be a corrupt block in a large datafile in your database which RMAN can easily report. Now it would take significant effort and time to perform the traditional restore and recover of a large datafile. So instead we will just recover the corrupt block and not the entire datafile.
For Oracle Database 10g or Oracle9i Database, use the blockrecover command to perform block media recovery.
As for Oracle Database 11g or newer, we will use the recover datafile … block command as shown below:
ONLY FOR TESTING FIRST WE CREATED SOME DB BLOCK CORRUTION
SELECT header_block FROM dba_segments WHERE segment_name=’apps';
HEADER_BLOCK
————
33
$ dd of=/u01/oracle/DB11G/oradata/apdba/users01.dbf bs=8192 conv=notrunc seek=15 << EOF
> corruption test
> EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 0.000159796 s, 100 kB/s
 ALTER SYSTEM FLUSH BUFFER_CACHE;
select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 15)
ORA-01110: data file 6: ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf’
A) CHECK CORRUPTION USING RMAN
RMAN will automatically detect corruption in blocks whenever a backup or backup validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view.
RMAN> backup validate database archivelog all;
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
6 FAILED 0 18 667 1086086
File Name: /u01/oracle/DB11G/oradata/apdba/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 90
Index 0 39
Other 1 693
In Alert Log:
Wed Feb 26 13:53:28 2016
Hex dump of (file 6, block 15) in trace file /u01/oracle/DB11G/diag/rdbms/apdba/apdba/trace/apdba_ora_6736.trc
Corrupt block relative dba: 0x01000093 (file 6, block 15)
Bad header found during validation
Data in bad block:
type: 99 format: 7 rdba: 0x69767075
last change scn: 0x7365.76206e6f seq: 0x76 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb863
block checksum disabled
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
In V$DATABASE_BLOCK_CORRUPTION view:
RMAN backup populates V$DATABASE_BLOCK_CORRUPTION.
 select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE#              CORRUPTIO
———- ———- ———- —————— ———
6         15    1             0                         CORRUPT
Using DBV:
dbv file=/u01/oracle/DB11G/oradata/apdba/users01.dbf blocksize=8192
B) CORRECT DATA BLOCK CORRUPTION
Database can be in mounted or open when you are performing block-level recovery. Also you do not have to take the datafile being recovered offline. Block-level media recovery allows you to keep your database available hence reducing the mean time to recovery since only the corrupt blocks are offline during the recovery.
RMAN CAN NOT perform block-level recovery on block 1 (datafile header) of the datafile.
RMAN> recover datafile 6 block 15;
C) VERIFY
select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
CASE 3: ONLY ONE DATA FILE IS LOST
You have one datafile that has experienced media failure. You don’t want to restore and recover the entire database or all datafiles associated with the tablespace. You just want to restore and recover the datafile that experienced media failure.
In this example we lost /u01/oracle/DB11G/oradata/apdba/users01.dbf
SCENARIO 1: DATABASE IS NOT OPEN
Here you restore/recover datafile from backup
A) CHECK IF YOU HAVE BACKUP PRESENT
RMAN> list backup;
B) MOUNT DATABASE
$ rman
RMAN> connect target /
RMAN> startup mount;
C) RESTORE DATAFILE
RMAN> restore datafile 6;
D) RECOVER DATAFILE
RMAN> recover datafile 6;
E) OPEN DATABASE
RMAN> alter database open;
SCENARIO 2 : DATABASE IS OPEN
Scenario 2 CASE 1: Datafile image copy from FRA
Here we will use datafile image copy from Flash Recovery Area (FRA), if it is enabled. This is FASTER WAY to recover a datafile.
A) check present files of the database:
RMAN> report schema;
B) Check image copies of the damaged datafile:
In our scenario datafile 6 is damaged.
RMAN> list copy of datafile 6;
C) Take ‘damaged’ datafile ‘offline’
RMAN> sql ‘alter database datafile 6 offline';
D) Switch to the copy of the file in the FRA
RMAN> switch datafile 6 to copy;
E) Recover the datafile copy and make it online
RMAN> recover datafile 6;
RMAN> sql ‘alter database datafile 6 online';
sql statement: alter database datafile 6 online
F) Check the datafiles once again:
RMAN> report schema;
AT THIS STAGE DATAFILE IS ONLINE AND DATABASE CAN BE USED NORMALLY.
When you bring the datafile back online, the tablespace will be brought online as well. The tablespace is now operational. But we don’t want to leave the database using a file in the flash recovery area, though, especially not for the long term.
BELOW STEPS ARE ADVISED TO BE DONE CAN BE DONE LATER ON (say weekend or off-business hours)
In below steps we will move back the data file to its original location.
G) Remove the ‘original damaged’ file at the OS level from the original location, if present
$ /u01/oracle/DB11G/oradata/apdba
$ mv users01.dbf users01.dbf.bkp
H) Create an image copy of the file & place it in the file’s original location
RMAN> backup as copy datafile 6 format ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
I) Take the datafile offline:
RMAN> sql ‘alter database datafile 6 offline';
sql statement: alter database datafile 6 offline
J) Switch to the copy of the file
RMAN> switch datafile 6 to copy;
datafile 6 switched to datafile copy “/u01/oracle/DB11G/oradata/apdba/users01.dbf”
K) Recover the datafile copy and make it online
RMAN> recover datafile 6;
RMAN> sql ‘alter database datafile 6 online’;
L) Check the datafiles once again:
RMAN> report schema;
RMAN> backup as copy datafile 6;
$ RMAN
RMAN> sql “alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” offline”;
sql statement: alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” offline
RMAN> restore datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
RMAN> recover datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
RMAN> sql “alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” online”;
NOTE: When using the RMAN sql command, if there are single quote marks within the SQL statement, then you are required to use double quotes to enclose the entire SQL statement and then also use two single quote marks where you would ordinarily just use one quote mark.
If you want to put the restored datafile to new location, use below RMAN commands instead
RMAN>run
{
sql “alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” offline”;
set newname for datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf’ to ‘/backups/users01.dbf';
restore datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
switch datafile all;
recover datafile ‘/backups/users01.dbf';
sql “alter database datafile ”/backups/users01.dbf” online”;
}
Remember that set command needs to be used inside a run block
 select file_name from dba_data_files;

FILE_NAME
——————————————————–
/u01/oracle/DB11G/oradata/apdba/users02.dbf
/u01/oracle/DB11G/oradata/apdba/system02.dbf
/u01/oracle/DB11G/oradata/apdba/undotbs02.dbf
CASE 6: ALL DATAFILES OF A TABLESPACE GOT CORUPTED/LOST
One of the tablespace is giving errors. the tablespace has multiple datafiles and all got corrupted.
If only one datafiles had error, we would have gone for datafile level recovery but since all datafiles associated with have corruption so it is better to go for tablespace level recovery .
Scenario 1: Database NOT Open
This solution works for any tablespace in your database (including system and undo tablesapce).
In this example, we are restoring the users tablespace:
There are two data files in this tablespace and both got corrupted somehow.
$ RMAN
RMAN> startup mount
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
Scenario 2: Database is Open
Sometimes it is not possible to take the database down. You can take a tablespace offline, restore, and recover it while your database is open. This works for any tablespace except the system and undo tablespaces.
This example takes users offline and then restores and recovers before bringing it back online:
Here also we got the same error in users tablesace as showin in scenario 1 forcing us to go for restore/recover of users tabespace.
$ RMAN
RMAN> SQL ‘ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
RMAN> RESTORE TABLESPACE USERS;
RMAN> RECOVER TABLESPACE USERS;
RMAN> sql ‘alter tablespace users online';
CASE 5: SPFILE/PFILE LOST
Sometimes spfile gets accidentally deleted or you are not able to open database using current spfile
Scenario 1: Using a Recovery Catalog
We will start Oracle instance without parameter file for retrieval of spfile
$ RMAN
RMAN> startup nomount
RMAN> restore spfile;
RMAN> startup force;
Scenario 2: Not Using a Recovery Catalog
Here you must tell the DBID of database to RMAN process.
$ rman
RMAN> connect target /
RMAN> set dbid 8473653926;
RMAN> startup force nomount;
RMAN> restore spfile from ‘/backups/apdba/controlfile_apdba_c-8473653926-20160226-06′;
RMAN> startup force;
NOTE: If our spfile autobackup was in default location, we could have used below command:
RMAN> restore spfile from autobackup;