Last updated on March 30th, 2019 at 04:31 am
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
- 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
Oracle Recovery Manager :RMAN cheatsheet