Recovering Tables and Table Partitions from RMAN Backups (UNTIL SCN)

Recovering Tables and Table Partitions from RMAN Backups (UNTIL SCN)

This tutorial ,You can learn how to recover tables and table partitions Specified SCN

Pre-requisites:

The target database must be in read-write mode.

The target database must be in ARCHIVELOG mode.

You must have valid RMAN backups ,Which has contain of the tables or table partitions ,which you want recover  these objects.

You must have enough space on filesystem to accommdate auxillary database and dumpfile

Target database COMPATIBLE initialization parameter must be set  11.1.0 or higher.

When a table or partition recovery is initiated via RMAN, the following action is performed

1) Required backup sets are identified to recover the table/partition

2) Create auxiliary database temporarily in the process of recovery

3) Recover the specified tables or table partitions, until the specified SCN, into this auxiliary instance

4) Creates a Data Pump export dump file which contains the recovered table or table partitions

5) Optionally, you can import the table/partitions in the source database

6) Rename option while recovery

Example:

    We are going to create a test table under the DEMO Schema, then check for the SCN and delete the table. The goal is to recreate the table with RMAN.

Ensure database must be in ARCHIVELOG mode:-

Create test table under Demo user:-

Now take a Backup:-

rman "target / nocatalog"

run {

allocate channel t1 type disk;

allocate channel t2 type disk;

allocate channel t3 type disk;

backup database plus archivelog ;
backup current controlfile ;

release channel t1;

release channel t2;

release channel t3;

}

Check for the current TIMESTAMP and delete the table:-

select dbms_flashback.get_system_change_number from dual;

Recover the table again

Recover the table again

RECOVER TABLE 'DEMO'.'TEST' UNTIL SCN 849720

AUXILIARY DESTINATION '/u01/app/oracle/bkup/rman/ORCL/AUX'

DATAPUMP DESTINATION '/u01/app/oracle/bkup/rman/ORCL/dump'

DUMP FILE 'demo_test.dmp' ;

At this poit RMAN opened auxiliary database:-

At this  point, RMAN starts an export/import with datapump to move the table from the auxiliary database back to the target database

Finally, the auxiliary instance is deleted or cleaned:-

We can check if our table is restored ok :-

Hope it helps. Please stay tuned, add me to your G+ Circles!! Your feedback always helps me and motivates me to blog more, please leave your valuable comment that is always a pleasure to read.

Many Thanks,

Regards

Senthilkumar Devaraj