RMAN Recovery scenarios

Pre-requisite for  RMAN

RMAN recovery scenarios require that the database is in archive log mode, and that backups of datafiles, control files and archived redolog files are made using Rman. Incremental Rman backups may be used also. Rman can be used with the repository installed on the archivelog, or with a recovery catalog that may be installed in the same or other database. Configuration and operation recommendations: Set the parameter controlfile autobackup to ON to have with each backup a controlfile backup also:

configure controlfile autobackup on;

Set the parameter retention policy to the recovery window you want to have, ie redundancy 2 will keep the last two backups

available, after executing delete obsolete commands:

configure retention policy to redundancy 2;

Execute your full backups with the option 'plus archivelogs' to include your archivelogs with every backup:

backup database plus archivelog;

Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:

crosscheck backup;

crosscheck archivelog all;

delete noprompt obsolete backup;

To work with Rman and a database based catalog follow these steps:

1. sqlplus /

2. create tablespace repcat;

3. create user rcuser identified by rcuser default tablespace repcat temporary tablespace temp;

4. grant connect, resource, recovery_catalog_owner to rcuser

5. exit

6. rman catalog rcuser/rcuser # connect to rman catalog as the rcuser

7. create catalog # create the catalog

8. connect target / #

RMAN Recovery Scenarios

1.     Block Recovery with RMAN

2.      Complete Closed Database Recovery. System tablespace is missing

3.     Complete Open Database Recovery. Non system tablespace is missing

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

5.     Loss Of All Files – Disaster Recovery

6.      Recovery of a Datafile that has no backups.

7.     Restore and Recovery of a Datafile to a different location.

8.     Control File Recovery

9.     Loss of all online redo log files

10.   Incomplete Recovery (until point in time)

11.   Incomplete Recovery (until log Incomplete log sequence)

12.   Restoring the Server Parameter File

13.   RMAN duplicate database on the same host

14.   RMAN Restore on another machine with different file system- same database name

1) Block Recovery with RMAN:-

In this scenario, you identify the blocks that require recovery and then use any available backup to perform the restore and recovery of these blocks.

To recover data blocks by using all available backups:

1.        Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)

ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'

ORA-01578: ORACLE data block corrupted (file # 2, block # 19)

ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

2.        Assuming that you have pre-allocated automatic channels, run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks as in the following example:

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19;

2)    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 reseting the redologs.

1. rman target /

2. startup mount;

3. restore database;

4. recover database;

5. alter database open;

3)  Complete Open Database Recovery. Non system tablespace is missing

1. rman target /

2. sql 'alter tablespace offline immediate';

3. restore datafile 3;

4. recover datafile 3;

5. sql 'alter tablespace online';

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

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

1. sqlplus /nolog

2. connect / as sysdba

3. startup mount

4. alter database datafile '' offline;

5. alter database open;

6. exit;

7. rman target /

8. restore datafile '';

9. recover datafile '';

10. sql 'alter tablespace online';

5)  Loss Of All Files including spfile – Disaster Recovery

1.        RECOVERY OF SPFILE

a. Create spfile.rcv as:

set dbid= 1499754868

run

{startup nomount force  ;

 };

b. Now restore the spfile

set dbid=1499754868

run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

restore spfile ;

release channel ch1 ;

}

2.        RESTORE OF CONTROLFILES

     Same Steps as spfile with the restore command changed. So the new script is

set dbid=1499754868

run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

restore controlfile ;

release channel ch1 ;

}

3.        RESTORE OF DATABASE

Since you have the control files now mount the database, open the database in mount stage

SQL> alter database mount;

Now get the log sequence number of the database from the catalog database:

select sequence# from rc_backup_redolog where db_name=’’;

RMAN> run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

 restore database ;

recover database until logseq=6;                       

release channel ch1 ;

}

4.        alter database open resetlogs;

6)  Recovery of a Datafile that has no backups (database is up).

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

name and put it offline. The option offline immediate is used to avoid that the update of the datafile header. Pre requisites: All relevant archived logs.

1. sqlplus '/ as sysdba'

2. alter tablespace offline immediate;

3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;

4. exit

5. rman target /

6. recover tablespace ;

7. sql 'alter tablespace online';

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

alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as '/user/oradata/u02/dbtst/newdata01.dbf'

7)  Restore and Recovery of a Datafile to a different location. Database is up:-

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed. Pre requisites: All relevant archived logs, complete cold or hot backup.

1. Use OS commands to restore the missing or corrupted datafile to the new location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2. alter tablespace offline immediate;

3. alter tablespace rename datafile '/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';

4. rman target /

5. recover tablespace ;

6. sql 'alter tablespace online';

8)  Control File Recovery :-

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash. Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON. You will need the dbid to restore the controlfile, get it from the name of the backed up controlfile. It is the number following the 'c-' at the start of the name.

1. rman target /

2. set dbid

3. startup nomount;

4. restore controlfile from autobackup;

5. alter database mount;

6. recover database;

7. alter database open resetlogs;

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

9)  Loss of all online redo log files:-

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file we can see the following error message:

Tue Jan 30 00:47:19 2011

ARC1: Failed to archive thread 1 sequence 93 (0)

Tue Jan 30 00:47:24 2011

Errors in file /opt/oracle/admin/opsdba/bdump/opsdba_arc0_32722.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u02/ORACLE/opsdba/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

The file is missing at the operating system level.

Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo log file. From the error message in the log file we can get the last archived file in our case it is sequence 92 as the error shows that it fails to archive the log file sequence 93.

SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

         1          1         95   52428800          1 NO  CURRENT                3203078 30-JAN-11

         2          1         93   52428800          1 NO  INACTIVE               3202983 30-JAN-11

         3          1         94   52428800          1 NO  INACTIVE               3203074 30-JAN-11

a.        Shutdown the database

b.        Mount the database

c.        Using RMAN connect to the target database:

RMAN> run {

set until sequence 93;

restore database;

recover database;

  alter database open resetlogs;

}

d.        The recovery process creates the online redo logfiles at the operating system level also.

Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.

10)              Incomplete Recovery (until point in time):-

Alert log file will give the timing when tablespace was dropped.

Information got from Alert log file.

Sun Feb  4 10:59:43 2011

drop tablespace test including contents and datafiles

Sun Feb 4 10:59:47 2011

Completed: drop tablespace test including contents and datafiles

1)       shutdown abort

2)       Using RMAN connect to the target database

RMAN> run

{

startup nomount

set until time "to_date ('04-02-11 10:58:00', 'DD-MM-YY HH24:MI:SS')";

restore controlfile;

alter database mount;

restore database;

recover database;

alter database open resetlogs;

}

3)       Check the dropped tablespace.

select name from v$tablespace;

11)              Incomplete Recovery (until log sequence)

     The developer states that the wrong DML statement was made AFTER 8.15 AM and is positive about the same. We need to determine the log sequence we need to recover until

select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3

SQL>  SEQUENCE# FIRST_CHANGE# TO_CHAR(

---------- ------------- --------

        13       2760463 07:49:36

        14       2761178 08:12:47

        15       2766622 08:18:49

Log sequence 14 was first written to at 8:12 AM so we should recover to a log sequence before this – i.e sequence# 13

1)       Shutdown the database

2)       Mount the database

3)       Using RMAN connect to the target database

run {

set until sequence=14;  >>> add one to the sequence number we have to recover until

restore database;

recover database;

}

4)       Open the database in resetlog mode.

RMAN> sql 'alter database open resetlogs';

12)   Restoring the Server Parameter File

a)        Connect to the target database and, optionally, the recovery catalog database. For example, run:

% rman TARGET / CATALOG rman/cat@catdb

b)       If you are connected to a catalog, and if the target database DB_NAME of the target database is unique in the catalog, then skip this step. Otherwise, set the DBID of the target database. For example:

SET DBID 676549873;

c)        Shut down the instance and restart it without mounting. Because the server parameter file is lost, RMAN starts the instance with a dummy parameter file. For example:

STARTUP FORCE NOMOUNT;

d)       Restore the server parameter file. If restoring to the default location, then simply run:

RESTORE SPFILE; # if you are using a catalog

RESTORE SPFILE FROM AUTOBACKUP; # if in NOCATALOG mode

If restoring to a nondefault location, then you could run commands as in the following example:

RESTORE SPFILE TO '/tmp/spfileTEMP.ora'; # if you are using a catalog

RESTORE SPFILE TO '/tmp/spfileTEMP.ora' FROM AUTOBACKUP; # if in NOCATALOG mode

You can restore the server parameter file as a client-side initialization parameter file with the TO PFILE 'filename' clause as in the following example:

RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';

e)        Restart the instance with the restored file. If restarting with a server parameter file in a nondefault location, then create a new client-side initialization parameter file with the single line SPFILE=new_location, where new_location is the path name of the restored server parameter file. Then, restart the instance with the client-side initialization parameter file. For example:

HOST 'echo "SPFILE=/tmp/spfileTEMP.ora" > /tmp/init.ora';

STARTUP FORCE PFILE=/tmp/init.ora; # starts instance with /tmp/spfileTEMP.ora

If you restored the server parameter file as a client-side initialization parameter file, then simply specify the path name of this restored file. For example:

STARTUP FORCE PFILE=/tmp/pfileTEMP.ora; # starts instance with /tmp/pfileTEMP.ora


Please visit:-
http://sysdbaonline.com/?p=60
http://blogs.oracle.com/AlejandroVargas/gems/BackupAndRecoveryChecklist.pdf