LATEST TOPICS

OTN Appreciation Day: Restore and Recovery of database table in Oracle 12c

This post is my contribution to the OTN Appreciation Day, a idea coined by Tim Hall. I am going to talk about the new restore and recovery feature introduced in Oracle 12c. Until 12c, we didn’t have an option to restore and recover a table using RMAN as a single step.

In order to restore or recover a table to a specific point in time, we had to perform a series of steps which involved creating a auxiliary instance (restored and recovered to the specific point in time) and then performing export and import of the table in concern.

The following diagram represents the complete framework of restoring and recovering a table to a specific point in time.

recover_table_process_12c

As we could see, there are a number of steps involved in the restore and recovery process and a lot of manual tasks needs to be performed.

Fortunately, in 12c; Oracle has automated this entire process of restoring and recovering a table to a specific point in time and wrapped it under a single set of RMAN command. We can now use RMAN to restore and recover a table with the following set of RMAN command.

---//
---// RMAN command syntax for recovering table //---
---//
RMAN> run
2> {
3> RECOVER TABLE {schema.table_name}[,schema.table_name][,schema.table_name,...] -----// List of tables to be restored/recovered
4> UNTIL TIME {point_in_time_timestamp} -----// Time until which the table needs to be recovered
5> AUXILIARY DESTINATION {file_system_path} -----// Datafile locations for the temporary (stage) database
6> DATAPUMP DESTINATION {file_system_path} -----// DTATPUMP directory for export/import process
7> DUMP FILE {dump_file_name}; -----// Export Dump file name to be used by the DataPump
8> }

Here is an example to restore and recover a table to a point time using the new RMAN command. In the following example, I am recovering the table tab_rec.emp till the point in time of 24-AUG-2014 02:09:32 using the new RMAN table recovery feature.

---//
---// example of recovering table using RMAN //---
---//
RMAN> run
2> {
3> RECOVER TABLE tab_rec.emp -----// recovering table tab_rec.emp
4> UNTIL TIME "to_date('24-AUG-2014 02:09:32','DD-MON-YYYY HH24:MI:SS')" -----// recovering to the point in time of '24-AUG-2014 02:09:32'
5> AUXILIARY DESTINATION '/app/oracle/tabrec/' -----// Auxiliary DB will '/app/oracle/tabrec/' for it's datafile location 
6> DATAPUMP DESTINATION '/backup/EXP' -----// Dump file will be stored in '/backup/EXP' location
7> DUMP FILE 'tab_rec_demo.dmp'; -----// Export Dump file name is 'tab_rec_demo.dmp'
8> }

Once we execute the RMAN command, it will perform all the steps from creating an auxiliary instance to performing export and import automatically. This new feature is a nice addition to the vast pool of features that Oracle provides and will definitely make the life of DBAs easy.

I have written a detailed post about this new feature and you can refer it here.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed