Restore table point in time recovery in Oracle 12c

One of the new exciting features of Oracle 12c is the ability to restore a Table using RMAN which was not possible in previous Oracle releases.

To implement this there are pre-requisites:

  • The database should be in Archive log mode, and read-write mode.
  • You need to have successful RMAN backup of the table existed at the point of time you need to recover to.

Let us proceed in testing:

I will create a new dummy table based on actual table existing in the database:

SQL> create table TESTSCHEMA.DUMMY_TABLE as select * from TESTSCHEMA.EMPLOYEE_INFO;

Now check the current SCN number and use it for the command later on:

SQL> select dbms_flashback.get_system_change_number from dual;

OR use this query:

SQL> select current_scn from v$database;

OR check the SCN generated based on the tablespace backup:

RMAN> list backup;

list backups rman

OR

Use the following command to check the backup of the data file associated with the tablespace:

This query will report the FILE_ID number that can be used with the below RMAN command to get the SCN for the tablespace:

select TABLESPACE_NAME,FILE_ID from dba_data_files;

then,

rman target /

RMAN> LIST BACKUP OF DATAFILE 7;

datafile backup command

***Before Dropping the table make sure you have a valid successful backup:

RMAN> backup database;

Then drop the newly created table:

SQL>Drop table TESTSCHEMA.DUMMY_TABLE;

Command to recover the table:

recover table ‘TESTSCHEMA’.’DUMMY_TABLE’ until scn 161451617 auxiliary destination ‘/oracle/app/’;

You need to wait as it will take time with many messages that is going to be generated….

Important Remark: you will notice messages about AUX Database being shutdown and startup, DON’T PANIC (this is not the database you are working on it, from behind the scene an auxiliary database is being created while table restoration is taking place).

You can safely remove the files generated under the “auxiliary destination” directory.

****The following are another alternatives for the same action:

A. You can also recover the table to be exported as a dump file (where you can import the table later on ) :

recover table ‘ TESTSCHEMA ‘. ‘DUMMY_TABLE’ until scn 161773617 auxiliary destination ‘/oracle/app/’ DATAPUMP DESTINATION ‘/oracle/export/’ DUMP FILE ‘table_recovery_dump.dmp’ notableimport;

table recovery dump

B. You can recover the table to be renamed to a different name while recovered:

recover table ‘TESTSCHEMA’.’DUMMY_TABLE’ until scn 161773617 auxiliary destination ‘/oracle/app/’ remap table TESTSCHEMA.DUMMY_TABLE:DUMMY_TABLE_TEMP;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s