RMANTablePointInTimeRecovery (PITR) inPDBs

1. First, use RMAN for a backup (which must be backed up once) rmantargetbackupdatabase; orbackupdatabaseroot; backuppluggabledatabasepdb; 2. Create a test user in pdb and authorize the user; createusertestidentifiedbyr

1. first, use RMAN for a backup (which must be backed up once) rman target/backup database; or backup database root; backup pluggable database pdb; 2. create a test user in pdb and authorize sqlplus/as sysdba alter session set container = pdb; create user test identified by r

1. Use RMAN for a backup (one backup is required)

Rman target/

Backup database;

Or

Backup database root;

Backup pluggable database pdb;

2. Create and authorize the test user in pdb

Sqlplus/as sysdba

Alter session set container = pdb;

Create user test identified by redhat quota unlimited on users;

Grant connect, create table to test;

3. Create a test table with the test user and query the current scn

Create table t1 (id int );

Insert into t1 values (1 );

Commit;

SQL> select * from t1;

ID

----------

1

Conn/as sysdba

SQL> SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1970004

4. Insert a data record to table t1.

Sqlplus test/redhat @ pdb

SQL> insert into t1 values (2 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

ID

----------

1

2

5. Restore with RMAN

Rman target/

Recover table 'test'. 't1' of pluggable database pdb

Until scn 1970004

Auxiliary destination '/u01/aux'

Remap table 'test'. 't1': 'T1 _ old ';

Or

Recover table 'test'. 't1' of pluggable database pdb

Until scn 1970004

Auxiliary destination '/u01/aux'

Datapump destination '/u01/export'

Dump file 'test _ t1_old.dmp '-- expdp the previous data

NOTABLEIMPORT; -- do not import the previous data

6. query the restored table t1_old to verify the restored content.

Sqlplus test/redhat @ pdb

SQL> select * from t1_old;

ID

----------

1

SQL> select * from t1;

ID

----------

1

2

7. Summary

This function is similar to flashback table.

Alter table t1 enable row movement;

Back up t1 first

Create table t1_bak as select * from t1;

Flashback table t1 to scn 1970004; -- flash back Based on SCN

Flashback table t1 to timestamp to_timestamp ('2017-06-07 14:41:19 ','Yyyy-mm-dd hh24: mi: ss'); -- Flash back Based on Timestamp

8. below are logs

[oracle@source admin]$ rman target /Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jun 7 14:42:27 2014Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.connected to target database: CDB1 (DBID=823327692)RMAN> RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb  UNTIL SCN 1970004  AUXILIARY DESTINATION '/u01/aux'    REMAP TABLE 'TEST'.'T1':'T1_OLD';2> 3> 4> Starting recover at 07-JUN-14using target database control file instead of recovery catalogcurrent log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=269 device type=DISKRMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-TimeList of tablespaces expected to have UNDO segmentsTablespace SYSTEMTablespace UNDOTBS1Creating automatic instance, with SID='peCr'initialization parameters used for automatic instance:db_name=CDB1db_unique_name=peCr_pitr_pdb_CDB1compatible=12.1.0.0.0db_block_size=8192db_files=200sga_target=1Gprocesses=80diagnostic_dest=/u01/app/oracledb_create_file_dest=/u01/auxlog_archive_dest_1='location=/u01/aux'enable_pluggable_database=true_clone_one_pdb_recovery=true#No auxiliary parameter file usedstarting up automatic instance CDB1Oracle instance startedTotal System Global Area    1068937216 bytesFixed Size                     2296576 bytesVariable Size                281019648 bytesDatabase Buffers             780140544 bytesRedo Buffers                   5480448 bytesAutomatic instance createdcontents of Memory Script:{# set requested point in timeset until  scn 1970004;# 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 07-JUN-14allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=75 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 /u01/app/oracle/recovery_area/CDB1/autobackup/2014_06_07/o1_mf_s_849623963_9s5dwvr3_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/recovery_area/CDB1/autobackup/2014_06_07/o1_mf_s_849623963_9s5dwvr3_.bkp tag=TAG20140607T143923channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/aux/CDB1/controlfile/o1_mf_9s5f3180_.ctlFinished restore at 07-JUN-14sql statement: alter database mount clone databasesql statement: alter system archive log currentcontents of Memory Script:{# set requested point in timeset until  scn 1970004;# set destinations for recovery set and auxiliary set datafilesset newname for clone datafile  1 to new;set newname for clone datafile  4 to new;set newname for clone datafile  3 to new;set newname for clone datafile  8 to new;set newname for clone datafile  9 to new;set newname for clone tempfile  1 to new;set newname for clone tempfile  3 to new;# switch all tempfilesswitch clone tempfile all;# restore the tablespaces in the 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 NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/aux/CDB1/datafile/o1_mf_temp_%u_.tmp in control filerenamed tempfile 3 to /u01/aux/CDB1/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 07-JUN-14using 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 datafile 00001 to /u01/aux/CDB1/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/CDB1/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/CDB1/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/CDB1/backupset/2014_06_07/o1_mf_nnndf_TAG20140607T143730_9s5dsblv_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/recovery_area/CDB1/backupset/2014_06_07/o1_mf_nnndf_TAG20140607T143730_9s5dsblv_.bkp tag=TAG20140607T143730channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55channel 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 datafile 00008 to /u01/aux/CDB1/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/aux/CDB1/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/CDB1/FB373ED0C69817BBE0436405A8C0E168/backupset/2014_06_07/o1_mf_nnndf_TAG20140607T143848_9s5dvrc5_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/recovery_area/CDB1/FB373ED0C69817BBE0436405A8C0E168/backupset/2014_06_07/o1_mf_nnndf_TAG20140607T143848_9s5dvrc5_.bkp tag=TAG20140607T143848channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36Finished restore at 07-JUN-14datafile 1 switched to datafile copyinput datafile copy RECID=8 STAMP=849624258 file name=/u01/aux/CDB1/datafile/o1_mf_system_9s5f37pd_.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=9 STAMP=849624258 file name=/u01/aux/CDB1/datafile/o1_mf_undotbs1_9s5f37py_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=10 STAMP=849624258 file name=/u01/aux/CDB1/datafile/o1_mf_sysaux_9s5f37pt_.dbfdatafile 8 switched to datafile copyinput datafile copy RECID=11 STAMP=849624258 file name=/u01/aux/CDB1/datafile/o1_mf_system_9s5f4zb5_.dbfdatafile 9 switched to datafile copyinput datafile copy RECID=12 STAMP=849624258 file name=/u01/aux/CDB1/datafile/o1_mf_sysaux_9s5f4z79_.dbfcontents of Memory Script:{# set requested point in timeset until  scn 1970004;# online the datafiles restored or switchedsql clone "alter database datafile  1 online";sql clone "alter database datafile  4 online";sql clone "alter database datafile  3 online";sql clone 'PDB' "alter database datafile  8 online";sql clone 'PDB' "alter database datafile  9 online";# recover and open database read onlyrecover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "PDB":"SYSTEM", "PDB":"SYSAUX";sql clone 'alter database open read only';}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile  1 onlinesql statement: alter database datafile  4 onlinesql statement: alter database datafile  3 onlinesql statement: alter database datafile  8 onlinesql statement: alter database datafile  9 onlineStarting recover at 07-JUN-14using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/recovery_area/CDB1/archivelog/2014_06_07/o1_mf_1_27_9s5f2pgz_.arcarchived log file name=/u01/app/oracle/recovery_area/CDB1/archivelog/2014_06_07/o1_mf_1_27_9s5f2pgz_.arc thread=1 sequence=27media recovery complete, elapsed time: 00:00:01Finished recover at 07-JUN-14sql statement: alter database open read onlycontents of Memory Script:{sql clone 'alter pluggable database  PDB open read only';}executing Memory Scriptsql statement: alter pluggable database  PDB open read onlycontents of Memory Script:{   sql clone "create spfile from memory";   shutdown clone immediate;   startup clone nomount;   sql clone "alter system set  control_files =   ''/u01/aux/CDB1/controlfile/o1_mf_9s5f3180_.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 downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area    1068937216 bytesFixed Size                     2296576 bytesVariable Size                285213952 bytesDatabase Buffers             775946240 bytesRedo Buffers                   5480448 bytessql statement: alter system set  control_files =   ''/u01/aux/CDB1/controlfile/o1_mf_9s5f3180_.ctl'' comment= ''RMAN set'' scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area    1068937216 bytesFixed Size                     2296576 bytesVariable Size                285213952 bytesDatabase Buffers             775946240 bytesRedo Buffers                   5480448 bytessql statement: alter database mount clone databasecontents of Memory Script:{# set requested point in timeset until  scn 1970004;# set destinations for recovery set and auxiliary set datafilesset newname for datafile  10 to new;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile  10;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEStarting restore at 07-JUN-14allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=11 device type=DISKchannel 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 datafile 00010 to /u01/aux/PECR_PITR_PDB_CDB1/datafile/o1_mf_users_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/CDB1/FB373ED0C69817BBE0436405A8C0E168/backupset/2014_06_07/o1_mf_nnndf_TAG20140607T143848_9s5dvrc5_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/recovery_area/CDB1/FB373ED0C69817BBE0436405A8C0E168/backupset/2014_06_07/o1_mf_nnndf_TAG20140607T143848_9s5dvrc5_.bkp tag=TAG20140607T143848channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 07-JUN-14datafile 10 switched to datafile copyinput datafile copy RECID=14 STAMP=849624303 file name=/u01/aux/PECR_PITR_PDB_CDB1/datafile/o1_mf_users_9s5f7gol_.dbfcontents of Memory Script:{# set requested point in timeset until  scn 1970004;# online the datafiles restored or switchedsql clone 'PDB' "alter database datafile  10 online";# recover and open resetlogsrecover clone database tablespace  "PDB":"USERS", "SYSTEM", "UNDOTBS1", "SYSAUX", "PDB":"SYSTEM", "PDB":"SYSAUX" delete archivelog;alter clone database open resetlogs;}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile  10 onlineStarting recover at 07-JUN-14using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/recovery_area/CDB1/archivelog/2014_06_07/o1_mf_1_27_9s5f2pgz_.arcarchived log file name=/u01/app/oracle/recovery_area/CDB1/archivelog/2014_06_07/o1_mf_1_27_9s5f2pgz_.arc thread=1 sequence=27media recovery complete, elapsed time: 00:00:00Finished recover at 07-JUN-14database openedcontents of Memory Script:{sql clone 'alter pluggable database  PDB open';}executing Memory Scriptsql statement: alter pluggable database  PDB opencontents of Memory Script:{# create directory for datapump importsql 'PDB' "create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''";# create directory for datapump exportsql clone 'PDB' "create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''";}executing Memory Scriptsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''Performing export of tables...   EXPDP> Starting "SYS"."TSPITR_EXP_peCr_crua":     EXPDP> Estimate in progress using BLOCKS method...   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA   EXPDP> Total estimation using BLOCKS method: 64 KB   EXPDP> 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 "TEST"."T1"                                 5.039 KB       1 rows   EXPDP> Master table "SYS"."TSPITR_EXP_peCr_crua" successfully loaded/unloaded   EXPDP> ******************************************************************************   EXPDP> Dump file set for SYS.TSPITR_EXP_peCr_crua is:   EXPDP>   /u01/aux/tspitr_peCr_89262.dmp   EXPDP> Job "SYS"."TSPITR_EXP_peCr_crua" successfully completed at Sat Jun 7 14:46:09 2014 elapsed 0 00:00:29Export completedcontents of Memory Script:{# shutdown clone before importshutdown clone abort}executing Memory ScriptOracle instance shut downPerforming import of tables...   IMPDP> Master table "SYS"."TSPITR_IMP_peCr_xgeA" successfully loaded/unloaded   IMPDP> Starting "SYS"."TSPITR_IMP_peCr_xgeA":     IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA   IMPDP> . . imported "TEST"."T1_OLD"                             5.039 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_peCr_xgeA" successfully completed at Sat Jun 7 14:46:15 2014 elapsed 0 00:00:02Import completedRemoving automatic instanceAutomatic instance removedauxiliary instance file /u01/aux/CDB1/datafile/o1_mf_temp_9s5f6jdq_.tmp deletedauxiliary instance file /u01/aux/CDB1/datafile/o1_mf_temp_9s5f66jr_.tmp deletedauxiliary instance file /u01/aux/PECR_PITR_PDB_CDB1/onlinelog/o1_mf_3_9s5f7m7c_.log deletedauxiliary instance file /u01/aux/PECR_PITR_PDB_CDB1/onlinelog/o1_mf_2_9s5f7l9n_.log deletedauxiliary instance file /u01/aux/PECR_PITR_PDB_CDB1/onlinelog/o1_mf_1_9s5f7jps_.log deletedauxiliary instance file /u01/aux/PECR_PITR_PDB_CDB1/datafile/o1_mf_users_9s5f7gol_.dbf deletedauxiliary instance file /u01/aux/CDB1/datafile/o1_mf_sysaux_9s5f4z79_.dbf deletedauxiliary instance file /u01/aux/CDB1/datafile/o1_mf_system_9s5f4zb5_.dbf deletedauxiliary instance file /u01/aux/CDB1/datafile/o1_mf_sysaux_9s5f37pt_.dbf deletedauxiliary instance file /u01/aux/CDB1/datafile/o1_mf_undotbs1_9s5f37py_.dbf deletedauxiliary instance file /u01/aux/CDB1/datafile/o1_mf_system_9s5f37pd_.dbf deletedauxiliary instance file /u01/aux/CDB1/controlfile/o1_mf_9s5f3180_.ctl deletedauxiliary instance file tspitr_peCr_89262.dmp deletedFinished recover at 07-JUN-14