Single-table recovery of new 12c features

Start with a full backup of the database

rman> list backup; List of Backup sets===================bs Key type LV Size Device Type Elapsed Time completion time--------------              -----------------------------------------------1 full 1.11G DISK 00:01:41 23-jun-16 BP key:1 status:available compressed:no tag:tag20160623t202948 Piece Name:/u02/app/oracle/fra/orcl2/bac    KUPSET/2016_06_23/O1_MF_NNNDF_TAG20160623T202948_CPQOKWWN_.BKP List of Datafiles in Backup set 1 File LV Type CKP SCN CKP time Name---------------------------------1 full 2158832 23-jun-16/u02/app/oracle/orcl2/orcl2/dat AFILE/O1_MF_SYSTEM_CPO3TVHX_.DBF 3 Full 2158832 23-JUN-16/U02/APP/ORACLE/ORCL2/ORCL2/DATAFILE/O1_MF_SYSAUX_CPO3 RT7B_.DBF 4 Full 2158832 23-jun-16/u02/app/oracle/orcl2/orcl2/datafile/o1_mf_undotbs1_cpo3wy5w_.dbf 6 Fu ll 2158832 23-jun-16/u02/app/oracle/orcl2/orcl2/datafile/o1_mf_users_cpo3wwsy_.dbfbs Key Type LV Size Device Ty PeElapsed Time Completion time-------------------------------------------------------------2 full 596.79M        DISK 00:00:35 23-jun-16 BP key:2 status:available compressed:no tag:tag20160623t202948 Piece Name:/u02/app/oracle/fra/orcl2/35decf1e89340d51e053c8c4a8c0f458/backupset/2016_06_23/o1_mf_nnndf_  TAG20160623T202948_CPQOOJB6_.BKP List of Datafiles in Backup set 2 Container id:3, PDB name:orcl2pdb File LV Type CKP SCN CKP Time Name---------------------------------8 full 2158067 23-jun-16/u02/app/oracle/orcl2/or CL2/35DECF1E89340D51E053C8C4A8C0F458/DATAFILE/O1_MF_SYSTEM_CPO4636Q_.DBF 9 Full 2158067 23-jun-16/u02/app/oracl E/ORCL2/ORCL2/35DECF1E89340D51E053C8C4A8C0F458/DATAFILE/O1_MF_SYSAUX_CPO46379_.DBF 2158067 23-jun-16/u02 /app/oracle/orcl2/orcl2/35decf1e89340d51e053c8c4a8c0f458/datafile/o1_mf_users_cpo472hm_.dbfbs Key Type LV Size Devi Ce Type Elapsed Time completion time-------------------------------------------------------------3 full 596.74M DISK 00:00:23 23-ju N-16 BP key:3 status:available compressed:no tag:tag20160623t202948 Piece Name:/u02/app/oracl  E/fra/orcl2/35df89c6d1b814bae053c8c4a8c09e79/backupset/2016_06_23/o1_mf_nnndf_tag20160623t202948_cpqopxm4_.bkp List of Datafiles in Backup set 3 Container id:4, PDB name:salespdb File LV Type Ckp SCN Ckp time Name-------- -------------------------full 1949411 23-jun-16/u02/app/oracle/orcl2/orcl2/35df89c6d1b814bae053c8c4a8c09e 79/DATAFILE/O1_MF_SYSTEM_CPO77Y1Q_.DBF 1949411 23-jun-16/u02/app/oracle/orcl2/orcl2/35df89c6d1b814bae053 C8C4A8C09E79/DATAFILE/O1_MF_SYSAUX_CPO77Y22_.DBF Full 1949411 23-jun-16/u02/app/oracle/orcl2/orcl2/35df89c6d1 B814bae053c8c4a8c09e79/datafile/o1_mf_users_cpo77y24_.dbfbs Key type LV Size Device Type Elapsed Time Completion Ti Me-------------------------------------------------------------4 full 594.30M DISK 00:00:28 23-jun-16 BP key:4 Status:available compressed:no tag:tag20160623t202948 Piece Name:/u02/app/oracle/fra/orcl2/35dec3f899680a82e0  53C8C4A8C0792A/BACKUPSET/2016_06_23/O1_MF_NNNDF_TAG20160623T202948_CPQOR18L_.BKP List of Datafiles in Backup set 4       Container id:2, PDB name:pdb$seed File LV Type Ckp SCN Ckp time Name---------------------------------5 Full 1602940 22-jun-16/u02/app/oracle/orcl2/orcl2/datafile/o1_mf_system_cpo3yld2_.dbf 7 full 1602940 22-J Un-16/u02/app/oracle/orcl2/orcl2/datafile/o1_mf_sysaux_cpo3ylcs_.dbfbs Key type LV Size Device Type Elapsed time C Ompletion time-------------------------------------------------------------5 full 17.20M DISK 00 : 00:01 23-jun-16 BP key:5 status:available compressed:no tag:tag20160623t203340 Piece Name :/u02/app/oracle/fra/orcl2/aUTOBACKUP/2016_06_23/O1_MF_S_915309220_CPQOS5JO_.BKP SPFILE included:modification time:23-jun-16 SPFILE db_unique_ Name:orcl2 Control File included:ckp scn:2158935 CKP time:23-jun-16

Querying for failure points in time

Sql> alter session set nls_date_format= ' Yyyymmdd hh24:mi:ss ';

Session altered.

Sql> select Sysdate from dual; Sysdate-----------------20160623 20:43:02sql> drop table T1; Table dropped. Sql> Select TIMESTAMP_TO_SCN (To_timestamp (' 20160623 20:43:02 ', ' yyyymmdd hh24:mi:ss ')) from dual; TIMESTAMP_TO_SCN (To_timestamp (' 2016062320:43:02 ', ' YYYYMMDDHH24:MI:SS '))--------------------------------------- --------------------------------2159581

Analog Database error Operation:

sql> drop table T1; Table dropped.

Database recovery process, a command is done, the process is probably to create a database auxiliary instance, restore the database backup to the secondary instance, use the data pump to the SCN for Point-in-time export tables, and then use the IMPDP tool to import into the product library.

rman> Recover table Wxc.t1 of pluggable database orcl2pdb until SCN 2159581 auxiliary destination '/tmp/' DataPump dest Ination '/tmp '; Starting recover at 23-jun-16using channel Ora_disk_1rman-05026:warning:presuming following set of tablespaces applies T o specified point-in-timelist of tablespaces expected to has UNDO segmentstablespace systemtablespace undotbs1creating au Tomatic instance, with sid= ' edud ' initialization parameters used for automatic instance:db_name=orcl2db_unique_name= Edud_pitr_orcl2pdb_orcl2compatible=12.1.0.2.0db_block_size=8192db_files=200diagnostic_dest=/u01/app/oracle_ System_trig_enabled=falsesga_target=1552mprocesses=200db_create_file_dest=/tmp/log_archive_dest_1= ' location=/ tmp/' enable_pluggable_database=true_clone_one_pdb_recovery=true#no auxiliary parameter file usedstarting up                     Automatic instance Orcl2oracle instance startedtotal System Global area 1627389952 bytesfixed Size 2924976 bytesvariable Size 419434064 bytesdAtabase buffers 1191182336 Bytesredo buffers 13848576 bytesautomatic instance createdcontents of Memory script:{# set requested point in Timeset until SCN 2159581;# restore the Controlfilerestore clone controlfile; # mount the Controlfilesql clone ' ALTER DATABASE mount clone database '; # Archive Current online log SQL ' alter system archive log current ';} Executing Memory scriptexecuting command:set until clausestarting restore at 23-jun-16allocated channel:ora_aux_disk_1c Hannel ora_aux_disk_1:sid=6 device Type=diskchannel ora_aux_disk_1:starting datafile backup set Restorechannel ORA_AUX_ Disk_1:restoring control FileChannel ora_aux_disk_1:reading from backup piece/u02/app/oracle/fra/orcl2/autobackup/ 2016_06_23/o1_mf_s_915309220_cpqos5jo_.bkpchannel ora_aux_disk_1:piece handle=/u02/app/oracle/fra/orcl2/ AUTOBACKUP/2016_06_23/O1_MF_S_915309220_CPQOS5JO_.BKP Tag=tag20160623t203340channel ora_aux_disk_1:restored Backup Piece 1channel ora_aux_disk_1:restOre complete, elapsed time:00:00:01output file name=/tmp/orcl2/controlfile/o1_mf_cpqq5qwd_.ctlfinished restore at 23- Jun-16sql Statement:alter Database Mount clone databasesql statement:alter System archive log currentcontents of Memory script:{# set requested-Timeset until SCN 2159581;# set destinations for recovery set and auxiliary set datafile Sset newname for clone datafile 1 to New;set newname for clone datafile 4 to New;set newname for clone datafile 3 to NE W;set newname for clone datafile 8 to New;set newname for clone datafile 9 to New;set newname for clone Tempfile 1 to N Ew;set newname for clone tempfile 3 to new;# switch all Tempfilesswitch clone tempfile all;# Restore the tablespaces in T He recovery set and the auxiliary Setrestore clone DataFile 1, 4, 3, 8, 9; Switch clone datafile all;} Executing Memory scriptexecuting command:set until clauseexecuting command:set newnameexecuting command:set NEWNAMEexec Uting Command:set newnameexecuting Command:seT newnameexecuting command:set newnameexecuting command:set newnameexecuting command:set NEWNAMErenamed tempfile 1 to/ Tmp/orcl2/datafile/o1_mf_temp_%u_.tmp in Control filerenamed tempfile 3 to/tmp/orcl2/datafile/o1_mf_temp_%u_.tmp in  Control filestarting Restore at 23-jun-16using Channel Ora_aux_disk_1channel ora_aux_disk_1:starting datafile backup set Restorechannel ora_aux_disk_1:specifying datafile (s) to restore from backup Setchannel ora_aux_disk_1:restoring datafil E 00001 to/tmp/orcl2/datafile/o1_mf_system_%u_.dbfchannel ora_aux_disk_1:restoring datafile 00004 to/tmp/orcl2/ Datafile/o1_mf_undotbs1_%u_.dbfchannel ora_aux_disk_1:restoring datafile 00003 to/tmp/orcl2/datafile/o1_mf_sysaux_ %u_.dbfchannel ora_aux_disk_1:reading from backup piece/u02/app/oracle/fra/orcl2/backupset/2016_06_23/o1_mf_nnndf_ Tag20160623t202948_cpqokwwn_.bkpchannel ora_aux_disk_1:piece handle=/u02/app/oracle/fra/orcl2/backupset/2016_06_ 23/O1_MF_NNNDF_TAG20160623T202948_CPQOKWWN_.BKP tag=tag20160623t202948channel ora_aux_disk_1:restored backup piece 1channel ora_aux_disk_1:restore complete, elapsed time:00:00:4 6channel ora_aux_disk_1:starting datafile Backup set Restorechannel ora_aux_disk_1:specifying datafile (s) to restore fro M backup Setchannel ora_aux_disk_1:restoring datafile 00008 to/tmp/orcl2/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX _disk_1:restoring datafile 00009 To/tmp/orcl2/datafile/o1_mf_sysaux_%u_.dbfchannel ora_aux_disk_1:reading from Backup Piece/u02/app/oracle/fra/orcl2/35decf1e89340d51e053c8c4a8c0f458/backupset/2016_06_23/o1_mf_nnndf_ Tag20160623t202948_cpqoojb6_.bkpchannel ora_aux_disk_1:piece handle=/u02/app/oracle/fra/orcl2/ 35DECF1E89340D51E053C8C4A8C0F458/BACKUPSET/2016_06_23/O1_MF_NNNDF_TAG20160623T202948_CPQOOJB6_.BKP tag= Tag20160623t202948channel ora_aux_disk_1:restored backup piece 1channel ora_aux_disk_1:restore complete, elapsed time: 00:00:25finished Restore at 23-jun-16datafile 1 switched to datafile copyinput datafile copy recid=8 stamp=915310724 file Name=/tmp/orcl2/datafile/o1_mf_system_cpqq5xym_.dbfdatafile 4 switched to datafile Copyinput DataFile copy recid=9 stamp=915310724 file Name=/tmp/orcl2/datafile/o1_mf_undotbs1_cpqq5xz1_.dbfdatafile 3 switched to datafile copyinput datafile Copy recid=10 stamp=915310724 file name=/tmp/orcl2/datafile/o1_mf_sysaux_cpqq5xyw_. Dbfdatafile 8 switched to datafile copyinput datafile copy recid=11 stamp=915310724 file name=/tmp/orcl2/datafile/o1_mf_s Ystem_cpqq7cgq_.dbfdatafile 9 switched to datafile copyinput datafile copy recid=12 stamp=915310724 file name=/tmp/ORCL2/  Datafile/o1_mf_sysaux_cpqq7cg6_.dbfcontents of Memory script:{# set requested point in Timeset until SCN 2159581;# online The datafiles restored or Switchedsql clone "ALTER DATABASE datafile 1 online"; SQL clone "ALTER DATABASE DataFile 4 onl INE "; SQL clone" ALTER DATABASE datafile 3 online "; SQL clone ' orcl2pdb '" ALTER DATABASE datafile 8 online "; SQL Clone ' ORC L2pdb ' "ALTER DATABASE datafile 9 online"; # RECover and open database read Onlyrecover clone database Tablespace "System", "UNDOTBS1", "Sysaux", "orcl2pdb": "System", " Orcl2pdb ":" Sysaux "; SQL clone ' ALTER DATABASE open read Only ';} Executing Memory scriptexecuting command:set until clausesql statement:alter database datafile 1 onlinesql statement:a Lter database datafile 4 onlinesql statement:alter database datafile 3 onlinesql statement:alter database datafile 8 Onlinesql Statement:alter Database datafile 9 onlinestarting recover at 23-jun-16using channel ora_aux_disk_1starting me Dia recoveryarchived Log for thread 1 with sequence are already on disk as file/u02/app/oracle/fra/orcl2/archivelog/201 6_06_23/o1_mf_1_14_cpqq0t9z_.arcarchived log File Name=/u02/app/oracle/fra/orcl2/archivelog/2016_06_23/o1_mf_1_14 _cpqq0t9z_.arc thread=1 sequence=14media Recovery complete, elapsed time:00:00:01finished recover at 23-jun-16sql statem Ent:alter database Open Read onlycontents of Memory Script:{sql clone ' alter pluggable DatabaSe orcl2pdb Open Read Only ';} Executing memory scriptsql statement:alter pluggable database orcl2pdb Open read onlycontents of memory script:{SQL C   Lone "Create SPFile from memory";   Shutdown clone Immediate;   Startup clone Nomount; SQL Clone "alter system set Control_files = '/tmp/orcl2/controlfile/o1_mf_cpqq5qwd_.ctl '" comment= "RMAN set" scope=   SPFile ";   Shutdown clone Immediate; Startup clone nomount;# Mount Databasesql clone ' ALTER DATABASE mount clone database ';} Executing Memory scriptsql statement:create spfile from Memorydatabase closeddatabase dismountedoracle instance shut down                      Connected to auxiliary database (not started) Oracle instance startedtotal System Global area 1627389952 bytesfixed Size 2924976 bytesvariable Size 436211280 bytesdatabase buffers 1174405120 bytes Redo buffers 13848576 bytessql statement:alter system Set Control_files = '/tmp/orcl2/controlfile/o1 _mf_cpqq5qwd_.ctl ' comMent= ' RMAN set ' scope=spfileoracle instance shut downconnected to auxiliary database (not started) Oracle instance start Edtotal System Global Area 1627389952 bytesfixed size 2924976 bytesvariable size 436 211280 bytesdatabase buffers 1174405120 bytesredo buffers 13848576 bytessql statement:alter D Atabase Mount clone databasecontents of Memory script:{# set requested point in Timeset until SCN 2159581;# set Destinati ONS for recovery set and auxiliary set Datafilesset newname for datafile Ten to new;# restore the tablespaces in the Recov Ery set and the auxiliary Setrestore clone datafile 10; Switch clone datafile all;} Executing Memory scriptexecuting command:set until clauseexecuting command:set newnamestarting restore at 23-JUN-16alloc ated channel:ora_aux_disk_1channel ora_aux_disk_1:sid=6 device Type=diskchannel ora_aux_disk_1:starting datafile Backup set Restorechannel ora_aux_disk_1:specifying datafile (s) toRestore from backup Setchannel ora_aux_disk_1:restoring datafile 00010 TO/TMP/EDUD_PITR_ORCL2PDB_ORCL2/DATAFILE/O1_MF _users_%u_.dbfchannel ora_aux_disk_1:reading from backup piece/u02/app/oracle/fra/orcl2/ 35decf1e89340d51e053c8c4a8c0f458/backupset/2016_06_23/o1_mf_nnndf_tag20160623t202948_cpqoojb6_.bkpchannel ORA_ Aux_disk_1:piece handle=/u02/app/oracle/fra/orcl2/35decf1e89340d51e053c8c4a8c0f458/backupset/2016_06_23/o1_mf_ NNNDF_TAG20160623T202948_CPQOOJB6_.BKP Tag=tag20160623t202948channel ora_aux_disk_1:restored backup piece 1channel Ora_aux_disk_1:restore complete, elapsed time:00:00:01finished restore @ 23-jun-16datafile switched to datafile copy Input datafile copy recid=14 stamp=915310782 file name=/tmp/edud_pitr_orcl2pdb_orcl2/datafile/o1_mf_users_cpqq9xdn_. Dbfcontents of Memory script:{# set requested point in Timeset until SCN 2159581;# online the datafiles restored or SWITC Hedsql clone ' orcl2pdb ' "ALTER DATABASE datafile online"; # Recover and open ResetlogsrecovER clone database Tablespace "orcl2pdb": "USERS", "System", "UNDOTBS1", "Sysaux", "orcl2pdb": "SYSTEM", "orcl2pdb": " Sysaux "Delete archivelog;alter clone database open resetlogs;} Executing Memory scriptexecuting command:set until clausesql statement:alter database datafile onlinestarting recove R at 23-jun-16using Channel ora_aux_disk_1starting media recoveryarchived log for thread 1 with sequence + are already on Disk as file/u02/app/oracle/fra/orcl2/archivelog/2016_06_23/o1_mf_1_14_cpqq0t9z_.arcarchived log file Name=/u02/app /oracle/fra/orcl2/archivelog/2016_06_23/o1_mf_1_14_cpqq0t9z_.arc thread=1 sequence=14media Recovery Complete, Elapsed time:00:00:00finished recover at 23-jun-16database openedcontents of Memory script:{sql clone ' alter pluggable DA Tabase orcl2pdb open ';}  Executing memory scriptsql statement:alter pluggable database orcl2pdb opencontents of memory script:{# Create directory For DataPump importsql ' orcl2pdb ' "Create or replace directory Tspitr_dirobj_dpdirAs '/tmp ' "; # Create directory for DataPump exportsql clone ' orcl2pdb '" Create or replace directory Tspitr_dirobj_dpdir a S '/tmp ' ";} Executing Memory scriptsql statement:create or replace directory Tspitr_dirobj_dpdir as '/tmp ' SQL Statement:create or   Replace directory Tspitr_dirobj_dpdir as '/tmp ' performing export of tables ... Expdp> starting "SYS". "   TSPITR_EXP_EDUD_BAMW ": expdp> Estimate in progress using BLOCKS method ... Expdp> Processing Object Type Table_export/table/table_data expdp> total estimation using BLOCKS method:64 KB E Xpdp> Processing Object Type table_export/table/table expdp> processing object Type Table_export/table/statistics /table_statistics expdp> Processing Object type Table_export/table/statistics/marker expdp>. . Exported "WXC". " T1 "5.046 KB 1 rows expdp> Master table" SYS "." TSPITR_EXP_EDUD_BAMW "Successfully loaded/unloaded expdp> *********************************expdp> Dump file set for SYS. TSPITR_EXP_EDUD_BAMW is:expdp>/tmp/tspitr_edud_26521.dmp expdp> Job "SYS". " TSPITR_EXP_EDUD_BAMW "successfully completed at Thu June 21:00:28 elapsed 0 00:00:18export completedcontents of Mem Ory script:{# shutdown clone before Importshutdown clone abort}executing Memory scriptoracle instance shut downperforming   Import of tables ... impdp> Master table "SYS". " Tspitr_imp_edud_nbec "successfully loaded/unloaded impdp> starting" SYS "." Tspitr_imp_edud_nbec ": impdp> Processing Object type table_export/table/table impdp> processing object Type TA Ble_export/table/table_data impdp>. . Imported "WXC". " T1 "5.046 KB 1 Rows impdp> processing object type Table_export/table/statistic S/table_statistics impdp> Processing Object type Table_export/table/statistics/marker impdp> Job "SYS". " Tspitr_imp_edud_nbec "Successfully completed at Thu June 21:00:42 elapsed 0 00:00:05import completedremoving automatic instanceautomatic instance removed Auxiliary instance file/tmp/orcl2/datafile/o1_mf_temp_cpqq8f81_.tmp deletedauxiliary instance file/tmp/orcl2/ Datafile/o1_mf_temp_cpqq87tr_.tmp Deletedauxiliary Instance file/tmp/edud_pitr_orcl2pdb_orcl2/onlinelog/o1_mf_3_ Cpqqb1o4_.log Deletedauxiliary Instance File/tmp/edud_pitr_orcl2pdb_orcl2/onlinelog/o1_mf_2_cpqqb0hh_.log Deletedauxiliary instance File/tmp/edud_pitr_orcl2pdb_orcl2/onlinelog/o1_mf_1_cpqq9zfj_.log deletedauxiliary Instance file/tmp/edud_pitr_orcl2pdb_orcl2/datafile/o1_mf_users_cpqq9xdn_.dbf deletedauxiliary Instance file/tmp/ ORCL2/DATAFILE/O1_MF_SYSAUX_CPQQ7CG6_.DBF Deletedauxiliary Instance File/tmp/orcl2/datafile/o1_mf_system_cpqq7cgq _.DBF Deletedauxiliary Instance file/tmp/orcl2/datafile/o1_mf_sysaux_cpqq5xyw_.dbf deletedauxiliary instance File/ TMP/ORCL2/DATAFILE/O1_MF_UNDOTBS1_CPQQ5XZ1_.DBF Deletedauxiliary Instance File/tmp/orcl2/daTAFILE/O1_MF_SYSTEM_CPQQ5XYM_.DBF Deletedauxiliary Instance File/tmp/orcl2/controlfile/o1_mf_cpqq5qwd_.ctl Deletedauxiliary instance file tspitr_edud_26521.dmp deletedfinished recover at 23-jun-16

At this point, the entire recovery process is over, similar to the table space-time recovery, but more flexible and convenient.

Check the recovered table

Sql> Select COUNT (*) from T1;  COUNT (*)----------1

　　

Single-table recovery of new 12c features