How to recover database using RMAN

Recover database is an 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 data file because of a hard disk crash and we get to restore the data file to correct 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 the 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 the 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