Oracle 12c Table Point-in-time Recovery

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:
db_name=O37114KT
db_unique_name=Yzeg_pitr_O37114KT
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/oracle/recover
_system_trig_enabled=FALSE
sga_target=1024M
processes=200
db_create_file_dest=/u01/oracle/recover
log_archive_dest_1='location=/u01/oracle/recover'
#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> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   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.

SQL>

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

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles