Oracle Database Recovery Scenarios

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;