Monday, December 20, 2010

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;
























4 comments:

  1. hi could u also post on incomplete recovery until time ,scn and log sequence and change

    great article and gud website but still requires more posts in order to get students for teaching

    so work hard rather than advertisment if u really take critcism then pls approve and post it

    ReplyDelete
  2. Excellent article, it helped me basically in two scenario of datafile receovery in case of COLD and HOT Backup

    ReplyDelete
  3. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete
  4. To convert the file from one format to the other is not a difficult thing now. You have to use the proper tool and the proper method for this task. Also with the community of www.essayuniverse.net you can learn different things about the audio conversations.

    ReplyDelete