Recovering Tables and Table Partitions using RMAN Backups

Oracle 12c offers new feature to recover tables and table partitions at a point-in-time using RMAN without affecting the other objects in the database. Use RECOVER TABLE command to recover tables or table partitions from an RMAN backup.

This feature is useful in the following scenarios:

• Object has Logical corruption or dropped
• There is no sufficient undo to perform Flashback table
• DDL operation modified the structure and you want to recover the data (Flashback cannot rewind the structural changes)
• Recover a small number of tables to a point-in-time

User should verify the pre-requisites before you prepare to recover the tables and table partitions. RMAN enables you to specify the point-in-time either using SCN, Time or sequence number.

The conditions must be met to perform table/ table Partition recovery:

Before you prepare to recover the tables and table partitions,

• Database must be in ARCHIVELOG mode and read-write mode
• At least one full backup is available along with archived logs
• Enough disk space is available on the database server for auxiliary instance
• If present, any dependent objects to include in recovery
• At least 1 Gigabyte extra in memory for the auxiliary database
• Tables and table partitions on standby databases cannot be recovered.
• Tables with named NOT NULL constraints cannot be recovered with the REMAP option. · COMPATIBLE parameter must be set to 11.1.0 or higher to recover table partition

RMAN enables recovery of selected tables without affecting remaining database objects. During the recovery process RMAN creates an auxiliary database, which is used to recover the tables or table partitions to a specified point-in-time. User need to specify the auxiliary database location using AUXILIARY DESTINATION clause in the RECOVERY command or SET NEWNAME command.

Please find the steps performed by RMAN during the recovery process:

1. Determine the backup which has the tables or table partitions that needs to recover to specified point-in-time
2. Create auxiliary database and recovery the tables or table partitions until specified point-in-time
3. Take a data dump export with recovered tables or table partitions
4. Import the dump into target database
5. Rename the recovered tables or table partitions in the target database

Please find an example to recovery TABLE1 table.

RECOVER TABLE TESTUSER.TABLE1
UNTIL SCN 384840289
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
DATAPUMP DESTINATION '/TEMP/ORCLDB/dumpfiles'
DUMP FILE 'testdump.dat';

If source table exists then user can specify NOTABLEIMPORT or REMAP TABLE. Also user can use UNTIL TIME or UNTIL SEQUENCE clause to specify point-in-time recovery.

Please find an example to recovery TABLE1 table as TABLE1_REC to the state that it was 2 days before the current date.

RECOVER TABLE TESTUSER.TABLE1
UNTIL TIME ‘SYSDATE-2’
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
REMAP TABLE 'TESTUSER'.'TABLE1':'TABLE1_REC';

There are some limitations recovering tables and table partitions:

• We cannot recover table and table partitions belongs of SYS schema
• We cannot recover table or table partitions from SYSAUX,SYSTEM tablespace Tables
• We cannot recover tables with named NOT NULL constraint using REMAP option.
• We cannot recover Table/Table partitions ON STANDBY database
• We cannot recovery table partitions if version is prior Oracle Database 11g R1

Note that there are other methods available to recover tables to a point-in-time such as Oracle Flashback and Tablespace Point-in-Time Recovery.

Refer below link for

Flashback and Database Point-in-Time Recovery

http://docs.oracle.com/database/121/BRADV/rcmflash.htm#g1016666
RMAN Tablespace point-in-time Recovery (TSPITR)
http://docs.oracle.com/database/121/BRADV/rcmtspit.htm#CIHGGAFH

Thanks,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com