Oracle Database Recovery Scenario and Solution

The purpose of this article  is to get acquianted with some of the recovery scenarios , this topic is useful for the folks who is having less experience and looking for good DBA exposure and preparing for interview .

   I am trying to  jotting down both RMAN and normal database technique :-

 1.Complete Closed database Recovery. System tablespace is missing  In this case complete recovery is performed, only the system tablespace is missing , so the database can be opened without resetting the redologs. 

  1. rman target/
  2. startup nomount;
  3. restore database ;
  4. recover database ;
  5. alter database open;

If the system tablespace is missing or corrupted the database can not be started up so a complete closed database recovery must be performed.

Pre – requisites: A closed or open database backup and archive logs.

a.       Use OS command to restore the missing or corrupted system datafile to its original location , i.e. :

cp –p /fh01/backup/uman/system01.dbf   /fh02/oradata/system01.dbf

b.      startup mount;

c.       recover datafile 1 ;

d.      alter database open ;

2. Complete Open Database Recovery. Non – System tablespace is missing.   If a non system tablespace is missing or corrupted while the database is open, recovery   Can be performed while the database remains open.Pre – requisites: A closed or open database backup and archive logs.a    .       cp –p /fh01/backup/uman/user01.dbf  /fh02/oradata/user01.dbfb.      alter tablespace <tablespace_name> offline immediate ;c.       recover tablespace <tablespace_name> ;

d.      alter tablespace <tablespace_name> online ;

a.       rman target /

b.      sql ‘alter tablespace <tablespace_name> offline immediate ;

c.       restore datafile 3;

d.      recover datafile 3;

e.       sql ‘ alter tablespace <tablespace_name> online’;

3. Complete Open Database Recovery .(when database is initially closed).Non system tablespace is missing . 

A user datafile is missing when trying to startup the database. The datafile can be turned offline and the database started up. Restore and  recover are performed using Rman and without Rman .After recovery is preformed the datafile can be turned online again .

a.       sqlplus “ as/ sysdba”

b.      startup mount

c.       alter database datafile ‘<datafile_name>’ offline ;

d.      alter database open ;

e.       exit ;

f.        rman target /

g.       restore datafile ‘<datafile_name>’;

h.       recover datafile ‘<datafile_name>’;

i.         sql ‘alter tablespace <tablespace_name> online’;

If a non system datafile that was not backed up since last backup is missing, recovery can be perform if all archived logs since the creation of the missing datafile exist. Since database is up you can check the tablespace .

If a non system tablespace is missing or corrupted and the database is crashed ,recovery can be performed after the database is open .

 Pre requisites: – A closed or open database backup and archived logs . a.       startup ; (You ill get Ora – 1157 ora – 1110 and the name of the missing datafile , the database ill remain mounted )

b.      Use OS commands to restore the missing or corrupted datafile to its original location  i.e

cp –p /fh01/backup/uman/user01.dbf  /fh02/oradata/user01.dbf  

c.       Alter database datafile3 offline; (Tablespace cannot be used because database is not open )

d.      Alter database open ;

e.       Recover datafile 3;

f.        Alter tabelspace <tablespace_name> online;

4. Recovery of missing datafile that has no backup (database is open)If a non system datafile that was not backed up since last backup is missing, recovery can be perform if all archived logs since the creation of the missing datafile exist. 

Pre requisites : All relevant archived logs.

a.       Alter tablespace <tablespace_name> offline immediate ;

b.      Alter tablespace create datafile ‘/fh02/oradata/newdata.dbf’ ;

c.       Recover tablespace <tablespace_name> ;

d.      Alter tablespace <tablespace_name> online ;

If the datafile command needs to be executed to place the datafile on a location different than the original use :

  Alter database create datafile ‘/fh01/backup/uman/newdata.dbf’ as ‘‘/fh02/oradata/rajat/newdata.dbf’ ;

1.      sqlplus “/as sysdba “

2.      Alter tablespace <tablespace_name> offline immediate ;

3.      Alter tablespace create datafile ‘/fh01/backup/uman/newdata.dbf’ ;

4.      exit

5.      rman  target /

6.      Recover tablespace <tablespace_name> ;

7.      Alter tablespace <tablespace_name> online ;

If the datafile command needs to be executed to place the datafile on a location different than the original use :

     Alter database create datafile ‘/fh01/backup/uman/newdata.dbf’ as ‘‘/fh02/oradata/rajat/newdata.dbf’ ;

5.Restore and Recovery of a datafile to a different location , Database is up.

If a non – system datafile is missing and its original location is not available , restore can be made to different location  and recovery performed.

Pre Requisites: – All relevant archived logs , complete cold or hot backup.

a.  Use OS commands to restore the missing or corrupted datafile to its original location  i.e

cp –p /fh01/backup/uman/user01.dbf  /fh02/oradata/user01.dbf

b. alter tablespace <tablespace_name> offline immediate ;

c. alter tablespace <tablespace_name> rename datafile /fh01/oradata/user01.dbf’ to ‘ /fh02/oradata/rajat/user01.dbf’

d. rman target /

e. recover tablespace <tablespace_name>

f. sql ‘ alter tablespace <tablespace_name> online ‘ ;

If a non – system datafile is missing and its original location is not available , restore can be made to different location  and recovery performed.

Pre Requisites: – All relevant archived logs , complete cold or hot backup.

a.  Use OS commands to restore the missing or corrupted datafile to its original location  i.e

cp –p /fh01/backup/uman/user01.dbf  /fh02/oradata/user01.dbf

b. alter tablespace <tablespace_name> offline immediate ;

c. alter tablespace <tablespace_name> rename datafile  /fh01/oradata/user01.dbf’ to ‘ /fh02/oradata/rajat/user01.dbf’

d. recover tablespace <tablespace_name>

e. alter tablespace <tablespace_name> online

6. Control file recovery  

 Always multiplex your control files. Control files are missing , database crash.

Pre Requisites: A backup of your controlfile , and all relevant archived log files .

a.       startup;(You get ora – 205 , missing control file, instance start but database is not mounted )

b.      Use OS command to copy the missing controlfile to its original location

cp –p /fh01/backup/uman/control101.dbf  /fh02/oradata/control101.dbf

c.       alter database mount ;

d.      recover automatic database using backup controlfile ;

e.       alter database open resetlogs ;

f.        make a new complete backup , as the database is open in a new incarnation and previous archived log are not relevant.

Always multiplex your control files. Control files are missing , database crash.

Pre Requisites: A backup of your controlfile , and all relevant archived log files.When

Using Rman always set configuration parameter.

Autobackup of controlfile to ON . You will need the dbid to restore the controlfile.

Get it from the name of the backup controlfile.

It is the number following the ‘c-‘ at the start of the same.

a.       rman target/

b.      set dbid <dbid>

c.       startup nomount ;

d.      restore controlfile from autobackup ;

e.       alter database mount ;

f.        recover database;

g.       alter database open resetlogs ;

h.       make a new complete backup , as the database is open in a new incarnation and previous archived log are not relevant

 7.      Incomplete Recovery, Until Time/Sequence/Cancel   

    Incomplete recovery may be necessary when the database crash and needs to be recovered, and in recovery process you find that an archived log is missing .In this case recovery can only be made until the sequence before the one that is missing.

Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it.

 In this case recovery needs to be performed until before the object was dropped.

Pre requisites: A full closed or open database backup and archived logs , the time or sequence that the ‘until’ recovery needs to be performed .

  a.       If the database is open , shutdown it to perform full restore.

b.      Rman target \

c.       Startup mount ;

d.      Restore database ;

e.       Recover database until sequence 8 thread 1 ;(You must pass the thread , if a single instance ill be always be 1 )

a.       If the database is open , shutdown abort

b.      Use OS commands to restore all the datafiles to its original locations :

Cp –p /user/backup/*.dbf  /u01/oradata/

c.       startup mount ;

d.      recover automatic database until time ‘2007-01-01:10:10:10’ ;

e.       alter database open resetlogs ;

f.        make a new complete backup , as the database is open in a new incarnation and previous archived log are not relevant

Alternatively you may use instead of until time , until sequence or until cancel ;

Recover automatic database until sequence 120 thread 1 ;or

Recover database until cancel ;

   

Share This Post with Your Friends over Social Media!

About the Author Rajat

I am Rajat Dey and I have more than decade of Oracle experience from all major regions throughout the globe. I am well versed in most facets of Oracle, supporting database, fusion middleware and the eBusiness suite across many operating system platforms.Currently I am located in Sydney  Oracle Financials 11i and Release 12,12.2  Oracle RDBMS 7x,8i , 9i,10g and 11g  Amazon cloud .  Oracle RAC 10g and 11g  Oracle Applications Server  Oracle Web logic Server  OBIEE  Hyperion  SSO  Oracle Portal  PL/SQL  UNIX  SQL  MS-SQL 2008.  OEM 12c .  Data Guard .  Essbase .  Database backup and recovery.  Performance Tunning.  TimeSten

Leave a Comment:

3 comments
SundarK says April 13, 2010

Hi,

Can you pls explain, the following scenarios?
Note: You are in EBS R12.1 Environment.

Scenario 1:
What happen, if you need to do an incomplete recovery, due to missing archive log file ? (In that case, I doubt the application file system will be inconsistent state). Need ur views.

Scenario 2:
What happen, if you lost part of application file system ? On which situations, the application file-system backup will go unusable?

Kindly explain.
Sundar K

Reply
obed khan says December 8, 2010

If the part of application is missing then user the missing part of same application from other same evn server.

In case server migration this will happend.

Reply
raju says January 20, 2014

Can you give explanation about the controlefile backup

Reply
Add Your Reply

Not found