Monday, February 5, 2018

Point in Time Recovery of a Pluggable Database

In this article I will explain how we can perform a point in time recovery for a single pluggable database. Point in time recovery of a single pluggable database would not have any effect on other pluggable databases in the same container database, or container database itself. In the following, a real time scenario of a pluggable database point in time recovery is explained. Following are the points to consider this scenario.

  • I have a pluggable database “pdb1” in my CDB.
  • Current time is 12:15 PM on 26th May 2016.
  • I want to perform a time/scn based recovery of my pdb1 until time 10:35 AM on 26th May 2016.
  • I have a backup taken yesterday (25th May 2016), and have all archived logs available until current time.

Steps to perform point in time recovery

1)
I can perform either time based recovery or SCN based recovery. For SCN based recovery, I would need to have SCN noted already, or I need to extract it from the database using TIMESTAMP_TO_SCN function. Execute following statement to find out SCN of the time until which I will be doing recovery
SQL> select timestamp_to_scn(to_date('26-may-16 10:35:00','DD-MON-YY HH24:MI:SS')) as scn from dual;

       SCN
----------
   6547615

My example used SCN to perform the recovery as you will see later, but alternatively you can also use UNTIL TIME to perform the recovery; following is the example of command to specify the time for recovery if time is to be used.
RMAN> recover pluggable database pdb1 until time "to_date('26-may-16 10:55:00','DD-MON-YY HH24:MI:SS')";

2)
In RMAN recovery command, we can use clause AUXILIARY DESTINATION to specify destination for temporarily storing auxiliary files that are created during the recovery. If we don’t specify this clause, recovery process will use fast recovery area location by default. 4 steps are required for recovery.
  • Connect to CDB using RMAN.
  • Close pluggable database.
  • Perform recovery .
  • Open pluggable database using RESETLOGS.
In the following you can see how these steps performed are being performed and what is expected output of each step.


C:\>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 26 12:15:57 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SALMAN12 (DBID=3945497372)

RMAN> alter pluggable database pdb1 close;

using target database control file instead of recovery catalog
Statement processed

RMAN> run{
2> set until scn 6547615;
3> restore pluggable database pdb1;
4> recover pluggable database pdb1;
5> }

executing command: SET until clause

Starting restore at 26-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 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 00014 to C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00015 to C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00016 to C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\PDB1_USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\BACK_0RR6G6VD_1_1.BAK
channel ORA_DISK_1: piece handle=C:\BACK_0RR6G6VD_1_1.BAK tag=TAG20160525T172832
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 26-MAY-16

Starting recover at 26-MAY-16
using channel ORA_DISK_1
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='biys'

initialization parameters used for automatic instance:
db_name=SALMAN12
db_unique_name=biys_pitr_pdb1_SALMAN12
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=C:\APP\SALMQURE
_system_trig_enabled=FALSE
db_domain=sg.oracle.com
sga_target=2048M
processes=200
#No auxiliary destination in use
enable_pluggable_database=true
_clone_one_pdb_recovery=true
control_files=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\CONTROLFILE\O1_MF_CNDSKKPK_.CTL
#No auxiliary parameter file used


starting up automatic instance SALMAN12

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3047720 bytes
Variable Size                520097496 bytes
Database Buffers            1610612736 bytes
Redo Buffers                  13725696 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 6547615;
# 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 26-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=87 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 C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\AUTOBACKUP\2016_05_25\O1_MF_S_912792665_CNBW6TYF_.BKP
channel ORA_AUX_DISK_1: piece handle=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\AUTOBACKUP\2016_05_25\O1_MF_S_912792665_CNBW6TYF_.BKP tag=TAG20160525T173105
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\CONTROLFILE\O1_MF_CNDSKKPK_.CTL
Finished restore at 26-MAY-16

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 6547615;
# switch to valid datafilecopies
switch clone datafile  14 to datafilecopy
 "C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSTEM01.DBF";
switch clone datafile  15 to datafilecopy
 "C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSAUX01.DBF";
switch clone datafile  16 to datafilecopy
 "C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\PDB1_USERS01.DBF";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  1 to
 "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSTEM_CNDSMHYN_.DBF";
set newname for datafile  5 to
 "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_UNDOTBS1_CNDSMJ1C_.DBF";
set newname for datafile  3 to
 "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF";
set newname for datafile  6 to
 "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_USERS_CNDSMRO6_.DBF";
set newname for datafile  10 to
 "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_TEST_CNDSMROT_.DBF";
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 5, 3, 6, 10;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 14 switched to datafile copy
input datafile copy RECID=3 STAMP=912855545 file name=C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSTEM01.DBF

datafile 15 switched to datafile copy
input datafile copy RECID=4 STAMP=912855545 file name=C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSAUX01.DBF

datafile 16 switched to datafile copy
input datafile copy RECID=5 STAMP=912855545 file name=C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\PDB1_USERS01.DBF

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-MAY-16
using channel ORA_AUX_DISK_1

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 00001 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSTEM_CNDSMHYN_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_UNDOTBS1_CNDSMJ1C_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_USERS_CNDSMRO6_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00010 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_TEST_CNDSMROT_.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\BACK_0QR6G6U0_1_1.BAK
channel ORA_AUX_DISK_1: piece handle=C:\BACK_0QR6G6U0_1_1.BAK tag=TAG20160525T172832
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 26-MAY-16

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSTEM_CNDSMHYN_.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_UNDOTBS1_CNDSMJ1C_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_USERS_CNDSMRO6_.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=912855654 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_TEST_CNDSMROT_.DBF

contents of Memory Script:
{
# set requested point in time
set until  scn 6547615;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  5 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB1' "alter database datafile
 14 online";
sql clone 'PDB1' "alter database datafile
 15 online";
sql clone 'PDB1' "alter database datafile
 16 online";
sql clone "alter database datafile  6 online";
sql clone "alter database datafile  10 online";
# recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS", "TEST" pluggable database
 'PDB1'   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 =>  'PDB1');
end; >>>;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  14 online

sql statement: alter database datafile  15 online

sql statement: alter database datafile  16 online

sql statement: alter database datafile  6 online

sql statement: alter database datafile  10 online

Starting recover at 26-MAY-16
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 588 is already on disk as file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_25\O1_MF_1_588_CNBW6QP9_.ARC
archived log for thread 1 with sequence 589 is already on disk as file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_26\O1_MF_1_589_CNDRBP94_.ARC
archived log file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_25\O1_MF_1_588_CNBW6QP9_.ARC thread=1 sequence=588
archived log file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_26\O1_MF_1_589_CNDRBP94_.ARC thread=1 sequence=589
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-MAY-16

sql statement: alter database open read only



Oracle instance shut down


Removing automatic instance
Automatic instance removed
auxiliary instance file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF deleted
auxiliary instance file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\CONTROLFILE\O1_MF_CNDSKKPK_.CTL deleted
Finished recover at 26-MAY-16

RMAN> alter pluggable database pdb1 open resetlogs;

Statement processed

Since the purpose of point in time recovery is to recover from user error (user mistakenly performed some database change, or dropped an object that is not required), you need to check your pluggable database if you have achieved your required goal, and have recovered from the user error.
If you are doing this in 12.1.0.2, the common users/roles present in the CDB, are not synchronized with the PDB, after a point in time recovery, and PDB lacks these common users/roles. Following is the way to synchronize the PDB with the CDB.

Pluggable Database not in sync with Container Database after recovery

If your pluggable database (PDB) is not in sync with the container database (CDB) – for example a common user/role exists in CDB, but not in CDB, it means PDB is not in sync with the PDB. There could be a reason for this, a bug, for example. Execute following steps to synchronize the PDB with the CDB.
SQL> alter session set container=pdb1;

Session altered.

SQL> exec DBMS_PDB.SYNC_PDB;

PL/SQL procedure successfully completed.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts - All Times