--- title: "12c: How to Restore/Recover a Small Table in a Large Database" description: "12c: How to Restore/Recover a Small Table in a Large Database" --- [Blog | Pythian ](https://www.pythian.com/blog) # [12c: How to Restore/Recover a Small Table in a Large Database](https://www.pythian.com/blog/12c-how-to-restore-recover-a-small-table-in-a-large-database) Written by [Howie Jiang](https://www.pythian.com/blog/author/howie-jiang) | Aug 28, 2014 4:00:00 AM ## Introduction As a DBA, you will receive requests from developers or users, indicating that they deleted some data in a small table in a large database a few hours prior. They will probably want you to recover the data as soon as possible, and it will likely be a critical production database. Flashback will not be enabled, and the recycle bin will have been purged. Restoring a full database using RMAN might take you over 10 hours, and you will need a spare server with big storage. Looks like it’s going to be a difficult and time consuming task for you. In Oracle Database 12c, there is a method available which allows us to recover the table more efficiently, and at a lower cost. The method is to create a second database (often called a stub database) using the backup of the first database. In this situation, we restore the SYSTEM, SYSAUX, and UNDO tablespaces and the the individual tablespaces that contain the data that we want to restore. After the restore is complete, we alter any tablespaces that we did not restore offline. We then apply the archived redo logs to the point in time that we want to restore the table to. Having restored the database to the appropriate point in time, we then use Oracle Data Pump to export the objects, and then you import them into the original database, again using Oracle Data Pump. Oracle Database 12c introduces new functionality in RMAN that supports point-in-time restore of individual database tables and individual table partitions. ## Example of when I tested this new feature ### ***1. The database TEST has 9 tablespaces and a schema called Howie. *** I created a table with 19377 records called TEST1 which is in the tablespace DATA_HOWIE. > ``` > SQL> select * from v$instance; > > INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION FAMILY > --------------- ---------------- ---------------------------------------------------------------- ----------------- --------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- ---------- ----------- ------- -------------------------------------------------------------------------------- > 1 TEST 12cServer1 12.1.0.1.0 17-AUG-14 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMALNO 0 REGULAR EE > > SQL> select tablespace_name from dba_tablespaces order by tablespace_name; > > TABLESPACE_NAME > ------------------------------ > DATA_HOWIE > DATA_TB1 > DATA_TB2 > DATA_TB3 > SYSAUX > SYSTEM > TEMP > UNDOTBS1 > USERS > > 9 rows selected. > > SQL> conn howie > Enter password: > Connected. > SQL> create table test1 as select * from dba_objects; > > Table created. > > SQL> select count(*) from test1; > > COUNT(*) > ---------- > 19377 > > SQL> select table_name,tablespace_name from user_tables where table_name='TEST1'; > > TABLE_NAME TABLESPACE_NAME > -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ > TEST1 DATA_HOWIE > ``` ### ***2. The database is in archivelog mode, and I took a full backup of the database.*** > ``` > [oracle@12cServer1 RMAN]$ rman target / > > Recovery Manager: Release 12.1.0.1.0 - Production on Sun Aug 17 20:16:17 2014 > > Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. > > connected to target database: TEST (DBID=2146502230) > > RMAN> run > { > allocate channel d1 type disk format '/u01/app/oracle/RMAN/rmn_%d_t%t_p%p'; > backup > incremental level 0 > tag backup_level0 > filesperset 1 > (database) > plus archivelog ; > release channel d1; > }2> 3> 4> 5> 6> 7> 8> 9> 10> 11> > ``` ### ***3. The data in the table howie.test1 has been deleted.*** > ``` > SQL> select sysdate,current_scn from v$database; > > SYSDATE CURRENT_SCN > ------------------- ----------- > 08/17/2014 21:01:15 435599 > > SQL> delete test1; > > 19377 rows deleted. > > SQL> commit; > > Commit complete. > ``` ### ***4. I ran following scripts to recover the data to an alternative table *** Table: howie.test1_temp to the point in time “08/17/2014 21:01:15” > ``` > [oracle@12cServer1 RMAN]$ rman target / > > Recovery Manager: Release 12.1.0.1.0 - Production on Sun Aug 17 21:01:35 2014 > > Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. > > connected to target database: TEST (DBID=2146502230) > > RMAN> recover table howie.test1 > until time "to_date('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')" > auxiliary destination '/u01/app/oracle/aux' > remap table howie.test1:test1_temp;2> 3> 4> > ``` ### ***5. The scripts above will take care of everything and you will see the data has been restored to howie.test1_temp*** > ``` > SQL> select count(*) from TEST1_TEMP; > > COUNT(*) > ---------- > 19377 > > SQL> select count(*) from TEST1; > > COUNT(*) > ---------- > 0 > ``` ## **Let’s take a look at the log of RMAN recovery and find out how it works. ** ### ***1. Creation of the auxiliary instance*** > ``` > Creating automatic instance, with SID='ktDA' > > initialization parameters used for automatic instance: > db_name=TEST > db_unique_name=ktDA_pitr_TEST > compatible=12.1.0.0.0 > db_block_size=8192 > db_files=200 > sga_target=1G > processes=80 > diagnostic_dest=/u01/app/oracle > db_create_file_dest=/u01/app/oracle/aux > log_archive_dest_1='location=/u01/app/oracle/aux' > #No auxiliary parameter file used > ``` ### ***2. Restore of the control file for the auxiliary instance*** > ``` > contents of Memory Script: > { > # set requested point in time > set until time "to_date('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')"; > # restore the controlfile > restore clone controlfile; > # mount the controlfile > sql clone 'alter database mount clone database'; > # archive current online log > sql 'alter system archive log current'; > } > ``` ### ***3. A list of datafiles that will be restored, followed by their restore and recovery in the auxiliary instance*** > ``` > contents of Memory Script: > { > # set requested point in time > set until time "to_date('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')"; > # online the datafiles restored or switched > sql clone "alter database datafile 1 online"; > sql clone "alter database datafile 3 online"; > sql clone "alter database datafile 2 online"; > # recover and open database read only > recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX"; > sql clone 'alter database open read only'; > } > > contents of Memory Script: > { > # set requested point in time > set until time "to_date('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')"; > # online the datafiles restored or switched > sql clone "alter database datafile 8 online"; > # recover and open resetlogs > recover clone database tablespace "DATA_HOWIE", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog; > alter clone database open resetlogs; > } > ``` ### ***4. Export of tables from the auxiliary instance via Oracle Data Pump*** > ``` > Performing export of tables... > EXPDP> Starting "SYS"."TSPITR_EXP_ktDA_BAkw": > EXPDP> Estimate in progress using BLOCKS method... > EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA > EXPDP> Total estimation using BLOCKS method: 3 MB > EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE > EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS > EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER > EXPDP> . . exported "HOWIE"."TEST1" 1.922 MB 19377 rows > EXPDP> Master table "SYS"."TSPITR_EXP_ktDA_BAkw" successfully loaded/unloaded > EXPDP> ****************************************************************************** > EXPDP> Dump file set for SYS.TSPITR_EXP_ktDA_BAkw is: > EXPDP> /u01/app/oracle/aux/tspitr_ktDA_70244.dmp > EXPDP> Job "SYS"."TSPITR_EXP_ktDA_BAkw" successfully completed at Sun Aug 17 21:03:53 2014 elapsed 0 00:00:14 > Export completed > ``` ### ***5. Import of tables, constraints, indexes, and other dependent objects into the target database from the Data Pump export file*** > ``` > contents of Memory Script: > { > # shutdown clone before import > shutdown clone abort > } > executing Memory Script > > Oracle instance shut down > > Performing import of tables... > IMPDP> Master table "SYS"."TSPITR_IMP_ktDA_lube" successfully loaded/unloaded > IMPDP> Starting "SYS"."TSPITR_IMP_ktDA_lube": > IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE > IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA > IMPDP> . . imported "HOWIE"."TEST1_TEMP" 1.922 MB 19377 rows > IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS > IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER > IMPDP> Job "SYS"."TSPITR_IMP_ktDA_lube" successfully completed at Sun Aug 17 21:04:19 2014 elapsed 0 00:00:19 > Import completed > ``` ### ***6. Clean-up of the auxiliary instance *** > ``` > Removing automatic instanceAutomatic instance removedauxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_temp_9z2yqst6_.tmp deletedauxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_3_9z2yrkqm_.log deletedauxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_2_9z2yrj35_.log deletedauxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_1_9z2yrh2r_.log deletedauxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/datafile/o1_mf_data_how_9z2yrcnq_.dbf deletedauxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_sysaux_9z2yptms_.dbf deletedauxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_undotbs1_9z2yq9of_.dbf deletedauxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_system_9z2yp0mk_.dbf deletedauxiliary instance file /u01/app/oracle/aux/TEST/controlfile/o1_mf_9z2yos1l_.ctl deletedauxiliary instance file tspitr_ktDA_70244.dmp deletedFinished recover at 17-AUG-14 > ``` ## Oracle Database Consulting Services Ready to optimize your Oracle Database for the future? [View full post](https://www.pythian.com/blog/12c-how-to-restore-recover-a-small-table-in-a-large-database) ```json { "@context" : "http://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Howie Jiang" }, "dateModified" : "2026-01-26T19:40:52.587Z", "datePublished" : "2014-08-28T04:00:00Z", "headline" : "12c: How to Restore/Recover a Small Table in a Large Database", "image" : { "@type" : "ImageObject", "height" : 60, "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png", "width" : 60 }, "mainEntityOfPage" : "https://www.pythian.com/blog/12c-how-to-restore-recover-a-small-table-in-a-large-database", "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "height" : 60, "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png", "width" : 60 }, "name" : "Pythian Blog" } } ```