How to recover database using RMAN

Recover database is a important part of Oracle database administration. Here in this post,I will try to cover scenario’s on How to recover database using RMAN

Case 1: Loss of a datafile
We often get the scenario where we lost the datafile because of hard disk crash and we get to restore the datafile to correct the things.

Here in this post, I would be explaining to perform that

Let us take an example to explain it

We have a table emp_master in the database

1. Count the rows

SQL> SELECT COUNT(*) FROM emp_master; -- 100 rows

2) Remove the datafile from the filesystem

3) Try to take the count again

SELECT COUNT(*) FROM emp_master;
SELECT COUNT(*) FROM emp_master;
SELECT COUNT(*) FROM emp_master
*
ERROR at line 1:
ORA-01116: error in opening database file 50
ORA-01110: data file 50: '/oradata/TEST/test_tools1.dbf'
ORA-27041: unable to open file

4) To make the recovery, make the datafile offline and then restore it and recover and again make it online

$
run {
allocate channel t1 type 'SBT_TAPE'
sql 'alter database datafile 50 offline';
restore datafile 50;
recover datafile 50;
sql 'alter database datafile 50 online';
}
SQL> SELECT COUNT(*) FROM emp_master;

Case 2: Loss of a tablespace
Again if we have lost whole TOOLS tablespace, then the RMAN statement would be different

run {
allocate channel t1 type 'SBT_TAPE'
sql 'alter tablespace TOOLS offline';
restore tablespace TOOLS;
recover tablespace TOOLS;
sql 'alter tablespace TOOLS online';
}

Case 3: Loss of all the datafile except control file and redo logs

run {
startup mount
allocate channel t1 type 'SBT_TAPE'
restore database;
recover database;
sql 'alter database open';
}

Related Articles

How to create RMAN recovery catalog and register database in catalog

RMAN Backup commands

RMAN List backup commands

Oracle Recovery Manager :RMAN cheatsheet