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.
