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
Leave a Reply