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;

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;

***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;

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;