RMAN RECOVER TABLE- A New 12c Feature

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Sep 12, 2016

RMAN RECOVER TABLE- A New 12c Feature


The new feature in Oracle 12c has the capability to restore a single table or a single partition of a partitioned table from a RMAN backup via the RECOVER TABLE command. Prior to 12c restoring a table, fixing this was a long drawn out and a difficult task.


Introduction:
The new feature in Oracle 12c has the capability to restore a single table or a single partition of a partitioned table from a RMAN backup via the RECOVER TABLE command. Prior to 12c restoring a table, fixing this was a long drawn out and a difficult task.

Oracle Database Support

Image Source: - https://www.google.co.in/search?q=RMAN+RECOVER+TABLE-+A+New+12c+Feature&noj=1&source=lnms&tbm=isch&sa=X&ved=0ahUKEwiEy6DEr_XOAhXFLI8KHQrlAKEQ_AUICCgB&biw=1242&bih=606#imgrc=KxuBaKx2mnWdcM%3A

When can we use this feature?

We can use this feature when TSPITR (Tablespace point-in-time recovery) is not possible or we want to restore a single table or subset of tables when the tablespace has a large number of tables.

To restore the table, the RMAN RECOVER TABLE command can be run at the backend. Let’s see how the command works: RMAN RECOVER TABLE creates an auxiliary database or instance which is used to recover the tables to a specific point in time. This database will contain a few system related data files like SYSTEM, SYSAUX, UNDO and data files belonging to the tablespace containing the tables we are looking to restore.

From there a Data Pump export file is created which contains the recovered table or partitions of the tables. It will then import the data into the target database using Data Pump Import. Finally it will remove the temporary auxiliary instance.

Constraints:

  • Prior to recovering a table or table partition, you must perform a full backup of undo, SYSTEM, SYSAUX, and the tablespace that contains the table or table partition.
  • To recover tables in a PDB, you need a backup of the root’s undo, SYSTEM, and SYSAUX tablespaces along with a backup of the PDB’s SYSTEM and SYSAUX tablespaces.
  • If the indexes or partitions for a table in tablespace tbs1 are contained in tablespace tbs2, then you can recover the table only if tablespace tbs2 is also included in the recovery set. To recover a table, all partitions that contain the dependent objects of the table must be included in the recovery set.

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 the 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 and table partitions from an RMAN backup, you need to provide the following information:

  • Names of the tables or table partitions that need to 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.

Syntax:

RECOVER TABLE schema name.tablename OF PLUGGABLE DATABASE pdb_name

UNTIL TIME ‘Timeframe’ (sysdate-1 etc)

AUXILIARY DESTINATION 'location for auxiliary database’

REMAP TABLE 'schema_name'.table_name':'new_table_name; 

Conclusion:

By using this feature a DBA can save lot of time and effort which put up in restoring a table in conventional path. In Oracle 12c, Oracle has simplified this task by automating all the steps of table restore from RMAN backup. For any questions on the topic click below:
Ask Amit