Oracle Database Recovery Scenarios

Backup and Recovery

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;