RMAN Data Recovery Methods

Hi,

In this article, I will write about RMAN data recovery methods step by step

1-  If you lost all data files ;

SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

2- If you lost a tablespace;

 SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;

if you can not offline tablespace;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;

3- if you lost a datafile;

SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;

if you cannot offline datafile;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;

4-  if you lost your controlfiles;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;

you will receive an error ORA-01589 when you open database

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Bu durumda aşağıdaki işlemler yapılmalıdır.

SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;

if you open database with resetlogs, SCN number will be zero. In this situation
all previous backups will be invalid. You must full backup.

5- May be a special situation. You need to incomplete recovery

A. Time-Based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;

B. SCN-Based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;

C. Archive log sequence based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;

6-  if you need some archive logs in your backup

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';

OR

RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';

7- if your data block is corrupted you will receive an error below.

Error:

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

ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’

for recover data block;

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;

For Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)

To recover, we can give a specific backup set;

# recovery from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# recovery from image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
      FROM DATAFILECOPY;
# recovery from backupset which have "FULL" tag
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
       FROM TAG = FULL;

During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views.  If you run the following command,  RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.

RMAN>BLOCKRECOVER CORRUPTION LIST
  RESTORE UNTIL TIME 'SYSDATE-10';

8- if you have a image copy backup and your datafile number 2 has problems then you
can switch datafile number2 to image copy.

RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;

Talip Hakan Ozturk

Advertisements
  1. Elizabeth Fredette
    June 23, 2017 at 9:48 pm

    Thank you Talip. Your ‘RMAN Data Recovery Methods’ is a very nice concise, helpful, well-organized, and accurate article. Thank you for your time and effort.

  1. April 17, 2015 at 9:53 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: