Point in Time recovery of an PDB.

Point in Time recovery of an PDB.

================================

PITR recovery in PDB

1) Only the PDB which to be recovered is affected, CDB and all the remaining PDB      is not affected.

2) Once PITR recover is done on an PDB and opened with reset log option a new        incarnation of PDB is created, it will not create the new incarnation / reset    the logs sequence of the CDB.

3) In CDB there is only redo log files in the CDB level, there is no redo logs in    the PDB level. Each redo data will have a PDB_ID which denotes the redo data      belong to which PDB and recorded in redo header.

   This is the way how the recovery knows which redo belongs to which PDB and        applied accordingly.

4) After PITR still the old backups remain valid. The incarnation can                be viewed in    V$PDB_INCARNATION view.

     

Eg:-

-----

SQL> create table emp (emp_id number, ename varchar2(30));

Table created.

SQL> begin

  2     for i in 1..3000 loop

  3             insert into emp values (i,dbms_random.string('U',30));

  4     end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

  COUNT(*)

----------

      3000

SQL> select current_scn from V$database;

CURRENT_SCN

-----------

    3737410

SQL> update emp set ename = 'Update all' ;

3000 rows updated.

SQL> commit;

Commit complete.

-- Oops!!! we have updated all the records wrongly... so i need to go back to scn 3737410 in hrdepts pdb database.

(This is to test PITR, if the undo is still available then you can explore flashback options).

SQL> alter pluggable database hrdepts close;

Pluggable database altered.

RMAN> run

{

 set until scn 3737410;

 restore pluggable database hrdepts;

 recover pluggable database hrdepts auxiliary destination='/oracle/auxiliary_dest';

 }

2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 25-AUG-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=66 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=44 device type=DISK

skipping datafile 8; already restored to file /oracle/product/oradata/sec_cdb/hrdept/system01.dbf

skipping datafile 10; already restored to file /oracle/product/oradata/sec_cdb/hrdept/users01.dbf

skipping datafile 9; already restored to file /oracle/product/oradata/sec_cdb/hrdept/sysaux01.dbf

skipping datafile 12; already restored to file /oracle/product/oradata/sec_cdb/hrdept/manzoor01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 25-AUG-15

Starting recover at 25-AUG-15

using channel ORA_DISK_1

using channel ORA_DISK_2

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='gwda'

initialization parameters used for automatic instance:

db_name=SEC_CDB

db_unique_name=gwda_pitr_hrdepts_SEC_CDB

compatible=12.1.0.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oraInventory

_system_trig_enabled=FALSE

sga_target=628M

processes=200

db_create_file_dest=/oracle/auxiliary_dest

log_archive_dest_1='location=/oracle/auxiliary_dest'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used

starting up automatic instance SEC_CDB

Oracle instance started

Total System Global Area     658505728 bytes

Fixed Size                     2927864 bytes

Variable Size                188744456 bytes

Database Buffers             461373440 bytes

Redo Buffers                   5459968 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until  scn 3737410;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 25-AUG-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=22 device type=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: SID=23 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oraInventory/fast_recovery_area/SEC_CDB/autobackup/2015_08_25/o1_mf_s_888662165_bxqo4pbm_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/autobackup/2015_08_25/o1_mf_s_888662165_bxqo4pbm_.bkp tag=TAG20150825T103605

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/oracle/auxiliary_dest/SEC_CDB/controlfile/o1_mf_bxqq6lom_.ctl

Finished restore at 25-AUG-15

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until  scn 3737410;

# switch to valid datafilecopies

switch clone datafile  8 to datafilecopy

 "/oracle/product/oradata/sec_cdb/hrdept/system01.dbf";

switch clone datafile  9 to datafilecopy

 "/oracle/product/oradata/sec_cdb/hrdept/sysaux01.dbf";

switch clone datafile  10 to datafilecopy

 "/oracle/product/oradata/sec_cdb/hrdept/users01.dbf";

switch clone datafile  12 to datafilecopy

 "/oracle/product/oradata/sec_cdb/hrdept/manzoor01.dbf";

# set destinations for recovery set and auxiliary set datafiles

set 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  6 to new;

set newname for clone datafile  11 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3, 6, 11;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

datafile 8 switched to datafile copy

input datafile copy RECID=3 STAMP=888664283 file name=/oracle/product/oradata/sec_cdb/hrdept/system01.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=4 STAMP=888664283 file name=/oracle/product/oradata/sec_cdb/hrdept/sysaux01.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=5 STAMP=888664283 file name=/oracle/product/oradata/sec_cdb/hrdept/users01.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=6 STAMP=888664283 file name=/oracle/product/oradata/sec_cdb/hrdept/manzoor01.dbf

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-AUG-15

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00011 to /oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_manzoor_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /oracle/bkp_seccdb/bkp_cold_1hqfe1gp_1_1_20150824

channel ORA_AUX_DISK_2: starting datafile backup set restore

channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_2: restoring datafile 00001 to /oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_2: restoring datafile 00004 to /oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_2: reading from backup piece /oracle/bkp_seccdb/bkp_cold_1gqfe1go_1_1_20150824

channel ORA_AUX_DISK_1: piece handle=/oracle/bkp_seccdb/bkp_cold_1hqfe1gp_1_1_20150824 tag=TAG20150824T182943

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:36

channel ORA_AUX_DISK_2: piece handle=/oracle/bkp_seccdb/bkp_cold_1gqfe1go_1_1_20150824 tag=TAG20150824T182943

channel ORA_AUX_DISK_2: restored backup piece 1

channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:03:36

Finished restore at 25-AUG-15

datafile 1 switched to datafile copy

input datafile copy RECID=12 STAMP=888664501 file name=/oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_system_bxqq6xov_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=13 STAMP=888664501 file name=/oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_undotbs1_bxqq6xsf_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=14 STAMP=888664501 file name=/oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_sysaux_bxqq6xp8_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=15 STAMP=888664501 file name=/oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_users_bxqq6y1o_.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=16 STAMP=888664501 file name=/oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_manzoor_bxqq6xxh_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 3737410;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

sql clone 'HRDEPTS' "alter database datafile

 8 online";

sql clone 'HRDEPTS' "alter database datafile

 9 online";

sql clone 'HRDEPTS' "alter database datafile

 10 online";

sql clone 'HRDEPTS' "alter database datafile

 12 online";

sql clone "alter database datafile  6 online";

sql clone "alter database datafile  11 online";

# recover pdb

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS", "MANZOOR" pluggable database

 'HRDEPTS'   delete archivelog;

sql clone 'alter database open read only';

plsql <<<begin

   add_dropped_ts;

end; >>>;

plsql <<<begin

   save_pdb_clean_scn;

end; >>>;

# shutdown clone before import

shutdown clone abort

plsql <<<begin

   pdbpitr_inspect(pdbname =>  'HRDEPTS');

end; >>>;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  10 online

sql statement: alter database datafile  12 online

sql statement: alter database datafile  6 online

sql statement: alter database datafile  11 online

Starting recover at 25-AUG-15

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting incremental datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00008: /oracle/product/oradata/sec_cdb/hrdept/system01.dbf

destination for restore of datafile 00010: /oracle/product/oradata/sec_cdb/hrdept/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /oracle/bkp_seccdb/pdb_inc_0_1rqffq3r_1_1_20150825.bkp

channel ORA_AUX_DISK_2: starting incremental datafile backup set restore

channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set

destination for restore of datafile 00009: /oracle/product/oradata/sec_cdb/hrdept/sysaux01.dbf

channel ORA_AUX_DISK_2: reading from backup piece /oracle/bkp_seccdb/pdb_inc_0_1qqffq3r_1_1_20150825.bkp

channel ORA_AUX_DISK_1: piece handle=/oracle/bkp_seccdb/pdb_inc_0_1rqffq3r_1_1_20150825.bkp tag=TAG20150825T103538

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_2: piece handle=/oracle/bkp_seccdb/pdb_inc_0_1qqffq3r_1_1_20150825.bkp tag=TAG20150825T103538

channel ORA_AUX_DISK_2: restored backup piece 1

channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 56 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_24/o1_mf_1_56_bxoyczw7_.arc

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_1_bxqd78ff_.arc

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_2_bxqdc248_.arc

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_3_bxqdc2z5_.arc

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_4_bxqdc694_.arc

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_1_bxqkcj1m_.arc

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_2_bxqlfhnt_.arc

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_3_bxqoz2wm_.arc

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_4_bxqpl34g_.arc

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_1_bxqd78ff_.arc thread=1 sequence=1

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_2_bxqdc248_.arc thread=1 sequence=2

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_3_bxqdc2z5_.arc thread=1 sequence=3

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_4_bxqdc694_.arc thread=1 sequence=4

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_1_bxqkcj1m_.arc thread=1 sequence=1

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_2_bxqlfhnt_.arc thread=1 sequence=2

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_3_bxqoz2wm_.arc thread=1 sequence=3

archived log file name=/u01/app/oraInventory/fast_recovery_area/SEC_CDB/archivelog/2015_08_25/o1_mf_1_4_bxqpl34g_.arc thread=1 sequence=4

media recovery complete, elapsed time: 00:00:14

Finished recover at 25-AUG-15

sql statement: alter database open read only

Oracle instance shut down

Removing automatic instance

Automatic instance removed

auxiliary instance file /oracle/auxiliary_dest/SEC_CDB/datafile/o1_mf_sysaux_bxqq6xp8_.dbf deleted

auxiliary instance file /oracle/auxiliary_dest/SEC_CDB/controlfile/o1_mf_bxqq6lom_.ctl deleted

Finished recover at 25-AUG-15

RMAN> ALTER PLUGGABLE DATABASE HRDEPTS OPEN RESETLOGS;

sql> connect / as sysdba

Connected

SQL> alter session set container=hrdepts;

Session altered.

SQL> select count(*) from manzoor.emp;

                                COUNT(*)

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

                                    3000

SQL> select * from manzoor.emp where rownum < 10;

                                  EMP_ID ENAME

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

                                     728 XDEMUXNQFEDBILPVYGQFHRPUJMYEWH

                                     729 TDWALMTPMCXPLUUEZYVFKLAVTHQCQQ

                                     730 EGTCEONCBCYVWBZCWOTESJPWCNKNCC

                                     731 CYVGECEZXVHXURHKNWKMLILGMHTEPH

                                     732 QQAWJDXAAXXDMOWZTCSMZWVOIPGPKU

                                     733 JUPYSQASSIHKZWBIHFIFUTAKWYFHMT

                                     734 XHVQVQTMGOHBNSIBEDUQUAIKEFTUFF

                                     735 KXAOYDBTIHXPHVYDUFNQPAIVEBNPKA

                                     736 VDKHDFEOMRIBSBMSEDPSWJUNAOZODJ

9 rows selected.

From the above we could see that

1) Creating an auxliary CDB instance / Resotring controlfile and data files of CDB in auxiliary location.

2) Restoring the PDB and recovering it until PIT.

3) Removing  the auxiliary instance and removing the aux files which are restored.