TABLE OR PARTITION RECOVERY IN RMAN

TABLE OR PARTITION RECOVERY IN RMAN 
Oracle database backups are mainly categorized into two types: logical and physical. Each backup type has its own pros and cons. In previous editions, it was not feasible to restore a table or partition using existing physical backups. In order to restore a particular object, you must have logical backup. With 12c R1, you can recover a particular table or partition to a point-in-time or SCN from RMAN backups in the event of a table drop or truncate.

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. An auxiliary database will be configured to a point-in-time temporarily in the process of recovering the table/partition
3. Required table/partitions will be then exported to a dumpfile using the data pumps
4. Optionally, you can import the table/partitions in the source database
5. Rename option while recovery

An example of a table point-in-time recovery via RMAN (ensure you already have a full database backup from earlier):

RMAN> connect target "username/password as SYSBACKUP";

RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'

AUXILIARY DESTINATION '/u01/tablerecovery'

LE 'tablename.dmp'

  NOTABLEIMPORT

DATAPUMP DESTINATION '/u01/dpump'

  DUMP F

I   -- this option avoids importing the table automatically.

    -- can rename table with this option.

REMAP TABLE 'username.tablename': 'username.new_table_name';

1. Ensure sufficient free space available under /u01 filesystem for auxiliary database and also to keep the data pump file
2. A full database backup must be exists, or at least the SYSTEM related tablespaces

The following limitations/restrictions are applied on table/partition recovery in RMAN:

1. SYS user table/partition can’t be recovered
2. Tables/partitions stored under SYSAUX and SYSTEM tablespaces can’t be recovered
3. Recovery of a table is not possible when REMAP option used to recovery a table that contains NOT NULL constraints