The below outlines few of the Oracle Database recovery scenarios in different cases.
Scenario 1 - Loss of C/R/D files
-------------------------------------
sqlplus '/as sysdba'
SQL>startup;
SQL>archive log list; - DB must be running in archive log mode
SQL>conn user1/user1
SQL>select * from emp;
SQL>insert into emp select * from emp;
/
/
/
/
SQL> commit;
SQL>alter system switch logfile;
SQL>exit;
$cd /prod/rk/oradata
$rm -f *
$sqlplus '/as sysdba'
SQL>select name from v$database;
-- You will see Error Here
SQL>shut abort;
SQL>exit;
$cd /prod/rk/backup/cold
$cp * /prod/rk/oradata
$sqlplus '/as sysdba'
SQL>startup mount;
SQL>alter database recover automatic using backup controlfile until cancel;
--
--
--
--
SQL>recover cancel;
SQL>alter database open resetlogs; -- The arch log seq number will be set to 1.
SQL>select name from v$Database;
Scenario 2 - Loss of Non-SYSTEM datafile
----------------------------------------
sqlplus '/as sysdba'
SQL>startup;
SQL>conn user1/user1
SQL>insert into emp as select * from emp;
/
/
/
SQL>commit;
SQL>select USERNAME,DEFAULT_TABLESPACE from dba_users;
SQL> select file_name from dba_data_files where tablespace_name='TS1';
SQL>exit;
$cd /u03/praveen/oradata
$ rm ts1a.dbf
SQL>sqlplus user1/user1
SQL>select * from emp;
-- You will see Error Here
SQL>alter database datafile '/u03/praveen/oradata/ts1a.dbf' offline;
SQL>exit;
$cp /u03/praveen/backup/cold/ts1a.dbf /u03/praveen/oradata
sqlplus '/as sysdba'
SQL>recover datafile '/u03/praveen/oradata/ts1a.dbf';
SQL>alter database datafile '/u03/praveen/oradata/ts1a.dbf' online;
SQL>conn user1/user1
SQL>select * from emp;
Scenario 3 - Loss of SYSTEM datafile
------------------------------------
sqlplus '/as sysdba'
SQL>startup;
SQL>conn user1/user1
SQL>insert into emp as select * from emp;
/
/
/
SQL>commit;
$cd /u03/praveen/oradata
$ rm system01.dbf
SQL>sqlplus user1/user1
SQL>select * from emp;
-- You will see Error Here
SQL>shu abort;
$cp /u03/praveen/backup/cold/system01.dbf /u03/praveen/oradata
sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database;
SQL>alter database open;
SQL>select name from v$database;
SQL>select * from emp;
Scenario 4 - Point in Time Recovery
-------------------------------------
SQL>conn user1/user1
SQL>select count(*) from emp;
SQL>select count(*) from salgrade;
SQL>insert into emp select * from emp;
SQL>/
SQL>commit;
SQL>set time on
SQL>select count(*) from emp; - Note down the records
SQL>drop table emp purge;
SQL>insert into salgrade select * from salgrade;
SQL>/
SQL>/
SQL>/
SQL>commit;
SQL>conn /as sysdba
SQL>alter system switch logfile;
SQL>/
SQL>shu immediate;
$cd /prod/lab/oradata
$rm -rf *
$cp /prod/lab/backup/cold/* /prod/lab/oradata
$sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database using backup controlfile until time '2010/09/16/16:18:49';
-- AUTO --
Scenario 5 - Loss of Log files
-------------------------------------
conn user1/user1
SQL>insert into emp select * from emp;
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/ordaata/
$rm redo1a.log redo2a.log
sqlplus user1/user1
SQL>insert into emp select * from emp;
SQL>/
/
/
/
-- DB will Hang Here -- Open alert log and check
From other session
sqlplus '/as sysdba'
SQL>shu abort;
$cd /prod/lab/backup/cold
cp *.ctl *.dbf /prod/lab/oradata
sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database using backup controlfile until cancel;
SQL>alter database open resetlogs;
Scenario 6: Loss of Undo Datafile
-------------------------------------
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
rm labundo1.dbf
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
-- Error -- Undo TBS missing
SQL>conn /as sysdba
SQL>shu abort
SQL>exit;
$cd /prod/lab/backup/cold
cp labundo1.dbf /prod/lab/oradata
sqlplus '/as sysdba'
SQL>startup mount;
SQL>select * from v$recover_file;
SQL>recover datafile '/prod/lab/oradata/labundo1.dbf'
SQL>alter database open;
SQL>select * from v$recover_file;
Scenario 7: Loss of 1 control File when Control Files Mutiplexed
--------------------------------------------------------------------------
sqlplus '/as sysdba'
SQL>show parameter control
-- Ensure u have 2 control files --
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
$rm cntrl01.ctl
sqlplus user1/user1
SQL>alter database datafile 4 offline;
-- Error Control File Missing --
SQL>shu abort
SQL>exit;
$cd /prod/lab/oradata
$cp cntrl02.ctl cntrl01.ctl
sqlplus '/as sysdba'
SQL>startup;
SQL>select * from user1.emp;
Scenario 8: Loss of all control Files
-------------------------------------
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
$rm cntrl01.ctl
sqlplus '/as sysdba'
SQL>alter database datafile 4 offline;
-- Error Control File Missing --
SQL>shu abort
SQL>exit;
$cd /prod/lab/oradata
$cp /prod/lab/backup/cold/cntrl01.ctl .
sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database using backup controlfile until cancel;
SQL>alter database open resetlogs;
SQL>select * from v$recover_file;
Scenario 9: Loss of control Files with no Backup
--------------------------------------------------------------------------
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
Sqlplus '/as sysdba'
SQL>alter database backup controlfile to trace as '/prod/lab/crt_ctl.sql';
SQL>exit;
$cd /prod/lab/oradata
$rm cntrl01.ctl
sqlplus '/as sysdba'
SQL>alter database datafile 4 offline;
-- Error Control File Missing --
SQL>shu abort
SQL>exit;
$ vi /prod/lab/crt_ctl.sql
-- Modify the control - Use first section
:wq!
sqlplus '/as sysdba'
SQL>@/prod/lab/crt_ctl.sql
SQL>alter database open;
-- If u face any error ---
-- SQL>recover database;
-- SQL>alter database open;
SQL>select count(*) from user1.emp;
SQL>alter database open resetlogs;
SQL>select * from v$recover_file;
Scenario 10: Loss of unbackup datafile
-------------------------------------
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>conn /as sysdba
SQL>alter tablespace userdata add datafile '/prod/lab/oradata/userdata02.dbf' size 500M;
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
$rm userdata02.dbf
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
/
-- Error - Datafile Missing --
SQL>conn /as sysdba
SQL>shu abort;
sqlplus '/as sysdba'
SQL>startup mount;
SQL>alter database create datafile '/prod/lab/oradata/userdata02.dbf';
SQL>recover datafile '/prod/lab/oradata/userdata02.dbf';
SQL>alter database open;
Scenario 11: How to recover from Hot Backup
------------------------------------------------------
sqlplus '/as sysdba'
SQL>select * from v$backup;
conn user1/user1
SQL>insert into salgrade select * from salgrade;
SQL>commit;
SQL>conn /as sysdba
SQL>alter tablespace userdata begin backup; - Taking hot backup of TS USERDATA
SQL>conn user1/user1
SQL>insert into salgrade select * from salgrade;
SQL>commit;
SQL>select count(*) from salgrade;
SQL>exit;
$cd /prod/lab/backup
$mkdir hot
$cd /prod/lab/oradata
$cp userdata01.dbf userdata02.dbf /prod/lab/backup/hot
$sqlplus '/as sysdba'
SQL>select * from v$backup;
SQL>alter tablespace userdata end backup;
SQL>alter system switch logfile;
/
/
SQL>conn user1/user1
SQL>insert into salgrade select * from salgrade;
/
/
SQL>commit;
SQL>select count(*) from salgrade;
SQL>exit;
$cd /prod/lab/oradata
$rm userdata01.dbf
sqlplus user1/user1
SQL>insert into salgrade select * from salgrade;
/
/
/
-- Error userdata datafile missing --
SQL>conn /as sysdba
SQL>alter database datafile '/prod/lab/oradata/userdata01.dbf' offline;
SQL>exit;
$cp ../backup/hot/userdata01.dbf .
sqlplus '/as sysdba'
SQL>recover datafile '/prod/lab/oradata/userdata01.dbf';
- AUTO -
SQL>alter database datafile '/prod/lab/oradata/userdata01.dbf' online;
SQL>select * from v$recover_file;
SQL>select count(*) from user1.salgrade;
SQL>exit;