Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted March 28, 2016

WEBINAR: On-demand Event

Replace Oracle with the NoSQL Engagement Database: Why and how leading companies are making the switch REGISTER >

Oracle 12c Table Point-in-time Recovery

By David Fitzjarrell

Recovering dropped tables is an ongoing issue with any database engine, but Oracle has provided a method of restoring a table or a table partition from an RMAN backup that makes such recovery pretty convenient. Yes, there's flashback database and the recycle bin, but what if flashback database isn't configured and the recycle bin isn't enabled? In such cases Oracle's Point-in-time Table recovery can be a lifesaver. Let's look at how it works and limitations where it's not applicable.

RMAN table point-in-time recovery can be a lifesaver if:

    The table or partition was truncated.  In this case Flashback Table won't work as there is no undo available
    The table or partition was dropped with the purge option so no remnant of the table or partition exists in the recycle bin
    The table or partition has logical corruption and no undo is available 
    The table or partition has logical corruption and the structure of the table or partition has been modified

In earlier releases it was possible to perform a database point-in-time recovery, but to get that one table or partition the entire database would be restored to the same point in time. While that's good for the missing table it also puts the rest of the database back to a previous state and the most recent data could (and most likely would) be lost. Another method would be a tablespace point-in-time recovery; this would restore the affected tablespace into an auxiliary database so the table or partition could be exported for import into the target. Fortunately, with 12.1.0.x, RMAN can do all of this on its own. Let's see how this can be accomplished.

For RMAN to perform this miracle it's necessary to have a backup of the database or of the required tablespaces for the time period prior to the drop. RMAN will use this backup to perform the point-in-time recovery of the database or tablespaces, export the desired objects then import them into the target database.

The ubiquitous EMP table will be used to show how RMAN can recover a table after a drop when no other method is available. Table point-in-time recovery can use either the SCN or the timestamp just before the table/partition was dropped. Since it's not likely that the DBA will know the SCN or will be able to get it at the time this recovery is needed, the timestamp will be used.

Backup the database using RMAN:

RMAN> run{
   2  backup database;
   3  backup archivelog all;
   4  }

This provides RMAN with all of the pieces it needs to recover the EMP table. When the backup is complete drop the EMP table and leave no trace in the recycle bin, ensuring that a recent backup of the database exists:

SQL> drop table emp purge;

Table dropped.

RMAN can now be used to recover the dropped table; you must specify the auxiliary destination or the operation will fail. The time of the drop was 15 minutes prior to the recovery, and that time will be used in the 'until time' parameter supplied to RMAN.

RMAN> recover table bing.emp until time 'sysdate - 15/1440' auxiliary destination '/u01/oracle/recover';

Starting recover at 17-MAR-16
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='Yzeg'

initialization parameters used for automatic instance:
#No auxiliary parameter file used

At this point RMAN is duplicating the database to the specified point-in-time so it can export the requested table. The export command isn't shown but let's look at the import command that RMAN executes:

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_Yzeg_jowq" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Yzeg_jowq":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "BING"."EMP"                                5.476 KB       1 rows
   IMPDP> Job "SYS"."TSPITR_IMP_Yzeg_jowq" successfully completed at Mon Sep 7 14:13:05 2015 elapsed 0 00:00:11
Import completed

The import completed successfully and the EMP table has been restored. RMAN now performs a cleanup of the auxiliary database:

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oracle/recover/O37114KT/datafile/o1_mf_temp_bytw2co8_.tmp deleted
auxiliary instance file /u01/oracle/recover/Yzeg_PITR_O37114KT/onlinelog/o1_mf_3_bytw4md4_.log deleted
auxiliary instance file /u01/oracle/recover/Yzeg_PITR_O37114KT/onlinelog/o1_mf_2_bytw4m1t_.log deleted
auxiliary instance file /u01/oracle/recover/Yzeg_PITR_O37114KT/onlinelog/o1_mf_1_bytw4lhq_.log deleted
auxiliary instance file /u01/oracle/recover/Yzeg_PITR_O37114KT/datafile/o1_mf_users_bytw4gos_.dbf deleted
auxiliary instance file /u01/oracle/recover/O37114KT/datafile/o1_mf_sysaux_bytvy598_.dbf deleted
auxiliary instance file /u01/oracle/recover/O37114KT/datafile/o1_mf_undotbs1_bytvy590_.dbf deleted
auxiliary instance file /u01/oracle/recover/O37114KT/datafile/o1_mf_system_bytvy59h_.dbf deleted
auxiliary instance file /u01/oracle/recover/O37114KT/controlfile/o1_mf_bytvxw4w_.ctl deleted
auxiliary instance file tspitr_Yzeg_10996.dmp deleted
Finished recover at 17-MAR-16

The EMP table is now available in the source instance with the data that is relevant to the selected time period (in my case to SCN 25427549):

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7939 DUKE       CEO                  17-NOV-81       5000                    10
      7949 PRINCE     CFO                  17-NOV-81       5000                    10
      7959 QUEEN      CIO                  17-NOV-81       5000                    10
      7869 JACK       PRESIDENT            17-NOV-81       5000                    10

18 rows selected.


The online documentation has several examples of RMAN Table Point-in-time recovery illustrating various options for the process. Options available include specifying a different location for the dumpfile, remapping the table to a new tablespace (using the REMAP_TABLESPACE clause to impdp), importing the table to a new name in the existing tablespace (using REMAP_TABLE) and you can have RMAN export the table but not import it with the NOTABLEIMPORT clause, allowing you to import the table at a later time.

Using RMAN table point-in-time recovery can be a real time saver for a busy DBA when table recovery is required and no other options are available. Obviously there is some 'cost' to this process as an auxiliary database needs to be created, consuming memory and disk space, but that may be an acceptable trade-off to restore a table when no other methods can be used.

See all articles by David Fitzjarrell

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM