Home » Oracle » How to Restore and Recover database using RMAN

How to Restore and Recover database using RMAN

Recover database is an important part of Oracle database administration. Here in this post, I will try to cover scenarios on How to restore and recover a database using RMAN

Case 1: How to Restore and recover database using RMAN from 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.

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, 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';
}

Case 4: Loss of a system or undo datafile

(a) First Shutdown the database

See also  How to change the Web Port in EBS 12.2

(b) restore and recover

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

Case 5: How to restore and recover database using RMAN when Complete Database lost

run {
set DBID=<DBID>;
startup nomount 
allocate channel t1 type 'SBT_TAPE';
RESTORE CONTROLFILE FROM "<backup piece name>";
sql 'alter database mount';
restore database; 
recover database until SCN <SCN>; 
sql 'alter database open resetlogs'; 
}

I hope this post on How to restore and recover a database using RMAN helps.

Related Articles

How to create RMAN recovery catalog and register database in catalog: Learn how to create an RMAN recovery catalog, how to register a database in the catalog, how to catalog archived log files on the filesystem,
RMAN Backup commands: Check out the RMAN Backup commands in this post. This is going to be very helpful for the person who is involved in backup and recovery
RMAN List backup commands: RMAN List backup commands are used to list the backup taken using RMAN, Date and Time and many other details are included
Oracle RMAN cheatsheet: Download the Very Useful and helpful Oracle RMAN cheatsheet for the various commands on backup and recovery.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top