RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. You can use previously-created RMAN backups to recover tables and table partitions to a specified point in time.

Recovering tables and table partitions from RMAN backups is useful in the following scenarios:

• You need to recover a very small number of tables to a particular point in time. In this situation, TSPITR is not the most effective solution because it moves all the objects in the tablespace to a specified point in time.

• You need to recover tables that have been logically corrupted or have been dropped and purged.

• Flashback Table is not possible because the desired point-in-time is older than available undo.

• You want to recover data that is lost after a DDL operation modified the structure of tables. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. Flashback Table cannot rewind tables through structural changes such as a truncate table operation.

To recover tables or table partitions, you need a full backup of undo, SYSTEM, SYSAUX, and the tablespace that contains the tables or table partitions.

To recover a table, all partitions that contain the dependent objects of the table must be included in the recovery set. If the indexes or partitions for a table in tablespace tbs1 are contained in tablespace tbs2, then you can recover the table only if tablepsace tbs2 is also included in the recovery set.

To recover tables in a PDB, you need backups of the following:

• SYSTEM, SYSAUX, and undo tablespace of the root, CDB seed, and the PDB containing the tables

• Tablespace containing the tables or partitions

RMAN uses the RECOVER command to recover tables or table partitions to a specified point in time.

To recover tables and table partitions from an RMAN backup, you need to provide the following information:

• Names of tables or table partitions that must be recovered

• Point in time to which the tables or table partitions must be recovered

• Whether the recovered tables or table partitions must be imported into the target database

RMAN uses this information to automate the process of recovering the specified tables or table partitions. As part of the recovery process, RMAN creates an auxiliary database that is used to recover tables or table partitions to the specified point in time.

If the recovered tables or table partitions need to be renamed, mapped to a new tablespace, or mapped to a new schema, then you must specify the new names for the tables, tablespaces, or schemas.

Recovering tables and table partitions from RMAN backups by using the RECOVER command is subject to certain limitations.

The limitations include the following:

• Tables and table partitions belonging to SYS schema cannot be recovered.

• Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.

• Tables and table partitions on standby databases cannot be recovered.

• Tables with named NOT NULL constraints cannot be recovered with the REMAP option.

Certain prerequisites must be met before you recover tables or table partitions from RMAN backups.

They include the following:

• The target database must be in read-write mode.

• The target database must be in ARCHIVELOG mode.

• You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.

• To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher.

It is important to determine the exact point in time to which you want to recover the tables or table partitions. There are multiple ways to specify the point in time to which objects must be recovered.

Use one of the following methods:

• SCN

  Recovers tables or table partitions to the state that they were at the time specified by the SCN.

• Time

  Recovers tables or table partitions to the state they were in at the specified time. Use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables. You can also use data constants such as SYSDATE to specify the time, for example SYSDATE-30.

• Sequence number

  Recovers tables or table partitions to the state they were at the time specified by the log sequence number and thread number.

This example recovers multiple tables to a specified point in time that is represented using SYSDATE.

1. Perform the planning tasks described in "Preparing to Recover Tables and Table Partitions".

   In this example, you need to recover tables to a point in time specified by an expression that uses SYSDATE. However, the recovered tables must not be imported in to the target database.

2. Start an RMAN session and connect as TARGET to the target database as described in "Making Database Connections with RMAN".
3. Recover the tables EMP and DEPT using the following clauses in the RECOVER command: DATAPUMP DESTINATION, DUMP FILE, REMAP TABLE, and NOTABLEIMPORT.

   The following RECOVER command recovers the EMP and DEPT tables.

   RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
       UNTIL TIME 'SYSDATE-1'
       AUXILIARY DESTINATION '/tmp/oracle/recover'
       DATAPUMP DESTINATION '/tmp/recover/dumpfiles'
       DUMP FILE 'emp_dept_exp_dump.dat'
       NOTABLEIMPORT;
   

This example uses RMAN backups to recover multiple table partitions.

1. Perform the planning tasks described in "Preparing to Recover Tables and Table Partitions".

   In this example, you need to recover two table partitions to a specified log sequence number and then import these partitions into the target database.

2. Start an RMAN session and connect as TARGET to the target database as described in "Making Database Connections with RMAN".
3. Recover partitions using the following RECOVER command with the REMAP TABLE and REMAP TABLESPACE clauses.

   RECOVER TABLE SH.SALES:SALES_1998, SH.SALES:SALES_1999
       UNTIL SEQUENCE 354
       AUXILIARY DESTINATION '/tmp/oracle/recover'
       REMAP TABLE 'SH'.'SALES':'SALES_1998':'HISTORIC_SALES_1998',
                 'SH'.'SALES':'SALES_1999':'HISTORIC_SALES_1999' 
       REMAP TABLESPACE 'SALES_TS':'SALES_PRE_2000_TS';
    

   In this case, the specified table partitions are imported as separate tables, called historic_sales_1998 and historic_sales_1999, into the sales_pre_2000_ts tablespace of the target database. The REMAP TABLE clause specifies the names used for the imported tables. The auxiliary destination used during the recovery process is /tmp/oracle/recover.

   If you omit the REMAP TABLE clause, RMAN uses default names for the imported tables. The name is a combination of the original table name and the partition name.