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
(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.