RMAN PITR recover table Oracle 12c

At one client’s site, I had to restore a table someone had partially deleted one week before. Before Oracle 12c, we had to duplicate the target database to another server, and then to export and import data to the target database. But depending on the database size, it could cost a lot of time, and as nobody knew when the delete action happened, it was more practical to use the rman recover table command in order to have multiple versions of the table content.

At first for security, we save the application table:

SQL> create table appuser.employe_save as select * from appuser.employe;

Table created.

My backups are configured on sbt_tape with ddboost, so I thought I only have to run such a command :

run {
ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576, 
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so, 
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda_proddb.cfg)' 
FORMAT '%d_%U' ;
recover table appuser.employe
until time "to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')"
auxiliary destination '/tmp/proddb/aux';
}

But I got this error message:

RMAN-03002: failure of recover command at 08/23/2018 10:50:04
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06101: no channel to restore a backup or copy of the control file

The problem is documented with bug 17089942:

The table recovery fails when channels are allocated manually within a run block. The solution consists in defining the channel device type in the rman configuration:

rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 23 13:52:39 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODDB (DBID=271333692)
connected to recovery catalog database

RMAN> configure channel device type sbt_tape parms 'BLKSIZE=1048576, 
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so, 
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda_proddb.cfg)';

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'BLKSIZE=1048576, 
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so, 
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda_proddb.cfg)';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Then connected with rman we can run the following recover command in order to restore the employe table with a new name employe_16082018:

RMAN> run {
 recover table appuser.employe
until time "to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')"
auxiliary destination '/tmp/proddb/aux'
remap table appuser.employe:employe_16082018;
}

What happens ? Oracle will create a pseudo database under /tmp/proddb/aux with SYSTEM SYSAUX TEMP UNDO and data tablespaces, then it restores the appuser.employe table at the specified date and renames it with the specified new name. Finally Oracle deletes the pseudo database.

RMAN> run {
2> recover table appuser.employe
3> until time "to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')"
4> auxiliary destination '/tmp/PRODDB/aux'
5> remap table appuser.employe:employe_16082018;
6> }

Starting recover at 23-AUG-2018 14:03:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=765 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=2562 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: database app agent Oracle v4.5.0.0

Creating automatic instance, with SID='ecvh'

initialization parameters used for automatic instance:
db_name=PRODDB
db_unique_name=ecvh_pitr_PRODDB
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u00/app/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/tmp/PRODDB/aux
log_archive_dest_1='location=/tmp/PRODDB/aux'
#No auxiliary parameter file used

…..

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_ecvh_cesy" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_ecvh_cesy":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "APPUSER"."EMPLOYE_16082018"           7.137 MB   16173 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_ecvh_cesy" successfully completed at Thu Aug 23 14:10:28 2018 elapsed 0 00:00:10
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_temp_fqx8w89p_.tmp deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/onlinelog/o1_mf_3_fqx90jyn_.log deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/onlinelog/o1_mf_2_fqx90hyd_.log deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/onlinelog/o1_mf_1_fqx90gwo_.log deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/datafile/o1_mf_affac_1_fqx8xybx_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_sysaux_fqx8p1p7_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_undotbs1_fqx8nskn_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_system_fqx8olyx_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/controlfile/o1_mf_fqx8nb57_.ctl deleted
auxiliary instance file tspitr_ecvh_63884.dmp deleted
Finished recover at 23-AUG-2018 14:10:29

The recover was quite fast, so I had the possibility to run multiple recover at different times allowing me to understand at which time the delete command happened:

SQL> select table_name from all_tables where owner = 'APPUSER' and table_name like 'EMPLOYE%'

TABLE_NAME
--------------------------------------------------------------------------------
EMPLOYE
EMPLOYE_16082018
EMPLOYE_22072018
EMPLOYE_SAVE

SQL> select count(*) from appuser.employe_22072018;

  COUNT(*)
----------
     16141

SQL> r
  1* select count(*) from appuser.employe_16082018

  COUNT(*)
----------
     16173

SQL> select count(*) from appuser.employe;

  COUNT(*)
----------
     16226

I already tested this recover feature on my own virtual machine on a test database. Running this recover command on a production database allowed me to discover the Oracle bug when your backups are on tape. Finally using ddboost with rman is so fast that you do not have to hesitate to restore tables with Oracle 12c even with a huge volumetry.