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.