Tuesday, February 10, 2015

12c Pluggable DB Point In Time Recovery


While working with pluggable database in our test environment, we came across situation where we want to to point in time recovery of one of the pluggable database.

Version - 12.1.0.2
Container DB - ORCL1 & ORCL2 (RAC CDB)
Pluggable DB - PDB1


Command used to take backup is as follows -

backup pluggable database pdb1 plus archivelog tag='pdb1 backup';

BEFORE INITIATING RESTORE AND RECOVERY MAKE SURE PDB IS IN MOUNTED STATE -

SQL> select name, open_mode from gv$pdbs

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB1                   MOUNTED
PDB$SEED               READ ONLY
PDB1                   MOUNTED


PDB restore can be done either by directly connecting to PDB or thru CDB. In this case, I will be doing restore and recovery thru CDB, which is preferred way. Following are the steps to analyse and perform restore/recovery of PDB

-- To Identify the latest point to which I need to perform PITR.

RMAN> restore pluggable database pdb1 preview;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
33      Full    607.06M    DISK        00:00:30     10-02-15 21:37:41
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20150210T213722
        Piece Name: +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443
  List of Datafiles in backup set 33
  Container ID: 3, PDB Name: PDB1
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  19      Full 9644899    10-02-15 21:36:26 +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/system.293.871263527
  20      Full 9644899    10-02-15 21:36:26 +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/sysaux.296.871263527
  21      Full 9644899    10-02-15 21:36:26 +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/users.292.871263605

archived logs generated after SCN 9646487 not found in repository
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 9646487
Recovery must be done beyond SCN 9646487 to clear datafile fuzziness
Finished restore at 10-02-15 21:56:09

-- Validate the ability of backup, if re-storable or not.

RMAN> restore pluggable database pdb1 validate;

Starting restore at 10-02-15 21:56:57
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443
channel ORA_DISK_1: piece handle=+DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443 tag=TAG20150210T213722
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
Finished restore at 10-02-15 21:57:05


[oracle@dbrac1 admin]$ rman target /
connected to target database: ORCL (DBID=1388310818)


RMAN> spool log to rman.log
RMAN> run  {
set until time "to_date('2015-10-02 21:36:26','YYYY-DD-MM HH24:MI:SS')";2>
restore pluggable database pdb1;
recover pluggable database pdb1;
}

2> RMAN>

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 instance=orcl1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00019 to +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/system.293.871263527
channel ORA_DISK_1: restoring datafile 00020 to +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/sysaux.296.871263527
channel ORA_DISK_1: restoring datafile 00021 to +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/users.292.871263605
channel ORA_DISK_1: reading from backup piece +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443
channel ORA_DISK_1: piece handle=+DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443 tag=TAG20150210T213722
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 10-02-15 22:00:40


RMAN>
Starting recover at 10-02-15 22:00:42
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 10-02-15 22:00:45


RMAN> alter pluggable database pdb1 open ;Statement processed


SQL> select name, open_mode from gv$pdbs;

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB1                   READ WRITE
PDB$SEED               READ ONLY
PDB1                   MOUNTED


Hope this will help you in pluggable database recovery.