Oracle Database 12c: Point-in-time recovery of a pluggable database.

Point-in-time recovery of a pluggable database:

In Oracle Database 12c, it is possible to do an incomplete recovery of a pluggable database without affecting other pluggable databases or the container database. The mechanism is similar to a database point-in-time recovery. An auxiliary instance is required to stage the restore and recovery.

The auxiliary instance consists of

The entire set of tablespaces (system, sysaux and others) belonging to the target pdb.
Tablespaces from the container database which are needed to perform point-in-time recovery (at least the system, sysaux, and undo).

After the restore and recovery is complete, the target pluggable database is at its desired recovery point.
The auxiliary instance gets dropped and pluggable database is ready to be opened for use.

Scenario:

Container db name : CDB1
Tenant dbs : salespdb, hrpdb
Target pdb : salespdb
Recovery point : recover to a specific scn, at which a table sales.sales was known to exist in SALESPDB.

Pre-requisites :

The target pdb salespdb must be closed.
Adequate disk space should be provisioned to stage the auxiliary instance.
A backup of the pdb and the container db should have been taken before the recovery point.
Archive logs must exist to do media recovery from the time of the backup until the desired recovery point.

Procedure :
Connect to the root container from rman and execute the run block below.

RMAN> run {
2> set until scn 1522385;
3> restore pluggable database salespdb;
4> recover pluggable database salespdb auxiliary destination '/u01/app/oracle/oradata/tmp';
5> }

Note:
In the run block above, a recovery point scn is specified first. This is the scn to which the named pdb will
be recovered. The recover statement in the run block also specifies an auxiliary destination folder for
staging the auxiliary instance.This run block will restore and recover pluggable database called salespdb to a specific recovery point.

After recovery, we will examines the record count of a dropped table after recovery to make sure its data has been recovered.

RMAN generates and executes a series of memory scripts to achieve the restore/recover.
The scripts and rman output are discussed below.

1) First, RMAN does an in-place restore of the pdb datafiles from an appropriate backupset according to the specified recovery point scn.

RMAN> run {
2> set until scn 1522385;
3> restore pluggable database salespdb;
4> recover pluggable database salespdb auxiliary destination '/u01/app/oracle/oradata/tmp';
5> }

executing command: SET until clause

Starting restore at 20-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 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 00011 to /u01/app/oracle/oradata/cdb1/salespdb/system.ora
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/cdb1/salespdb/users.ora
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/cdb1/salespdb/ts_sales.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/fra/CDB1/F75C25CAA70D1833E0430100007F21F9/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r9wnb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/fra/CDB1/F75C25CAA70D1833E0430100007F21F9/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r9wnb_.bkp tag=TAG20140419T103559
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 20-APR-14

Starting recover at 20-APR-14
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 UNDOTBS

Creating automatic instance, with SID='BvDq'

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=BvDq_pitr_salespdb_CDB1
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/app/oracle/oradata/tmp
log_archive_dest_1='location=/u01/app/oracle/oradata/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance CDB1

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes
Variable Size 281019648 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created

2) Next, RMAN prepares a controlfile for the auxiliary instance from a suitable backup of the CDB.

contents of Memory Script:
{
# set requested point in time
set until scn 1522385;
# 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 20-APR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=75 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/oracle/oradata/fra/CDB1/autobackup/2014_04_19/o1_mf_s_845289482_9o3rdc8b_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/oradata/fra/CDB1/autobackup/2014_04_19/o1_mf_s_845289482_9o3rdc8b_.bkp tag=TAG20140419T103802
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/oradata/tmp/CDB1/controlfile/o1_mf_9o64vdxg_.ctl
Finished restore at 20-APR-14

sql statement: alter database mount clone database

3) RMAN finalizes the datafiles for the auxiliary instance in this step by including any files from the CDB itself which may be needed for the recovery.

The system, undo, sysaux, and users tablespace from the container database are needed to create the auxiliary instance. Suitable backups of those CDB data files are being restored below to the auxiliary destination and assigned OMF names with set newname for ... to new. The auxiliary instance controlfile is updated with the new locations with a series of switch statements to prepare it for the media recovery stage.

contents of Memory Script:
{
# set requested point in time
set until scn 1522385;
# switch to valid datafilecopies
switch clone datafile 11 to datafilecopy
"/u01/app/oracle/oradata/cdb1/salespdb/system.ora";
switch clone datafile 12 to datafilecopy
"/u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora";
switch clone datafile 13 to datafilecopy
"/u01/app/oracle/oradata/cdb1/salespdb/users.ora";
switch clone datafile 14 to datafilecopy
"/u01/app/oracle/oradata/cdb1/salespdb/ts_sales.ora";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 11 switched to datafile copy
input datafile copy RECID=1 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/system.ora

datafile 12 switched to datafile copy
input datafile copy RECID=2 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora

datafile 13 switched to datafile copy
input datafile copy RECID=3 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/users.ora

datafile 14 switched to datafile copy
input datafile copy RECID=4 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/ts_sales.ora

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-APR-14
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 /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_undotbs_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/oradata/fra/CDB1/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r8hgk_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/oradata/fra/CDB1/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r8hgk_.bkp tag=TAG20140419T103559
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 20-APR-14

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_system_9o64w899_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_undotbs_9o64w8n2_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9o64w8nc_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_users_9o64w8qj_.dbf

4) Taking all clone datafiles online to enable media recovery, and performing media recovery, followed by auxiliary instance drop at the end.

contents of Memory Script:
{
# set requested point in time
set until scn 1522385;
# 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 'SALESPDB' "alter database datafile
11 online";
sql clone 'SALESPDB' "alter database datafile
12 online";
sql clone 'SALESPDB' "alter database datafile
13 online";
sql clone 'SALESPDB' "alter database datafile
14 online";
sql clone "alter database datafile 6 online";
# recover pdb
recover clone database tablespace "SYSTEM", "UNDOTBS", "SYSAUX", "USERS" pluggable database
'SALESPDB' 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 => 'SALESPDB');
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 11 online

sql statement: alter database datafile 12 online

sql statement: alter database datafile 13 online

sql statement: alter database datafile 14 online

sql statement: alter database datafile 6 online

Starting recover at 20-APR-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_47_9o3rd8fo_.arc
archived log for thread 1 with sequence 48 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_48_9o3tjwyy_.arc
archived log for thread 1 with sequence 49 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_49_9o60dlml_.arc
archived log for thread 1 with sequence 50 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_50_9o60rgmt_.arc
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_47_9o3rd8fo_.arc thread=1 sequence=47
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_48_9o3tjwyy_.arc thread=1 sequence=48
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_49_9o60dlml_.arc thread=1 sequence=49
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_50_9o60rgmt_.arc thread=1 sequence=50
media recovery complete, elapsed time: 00:00:18
Finished recover at 20-APR-14

sql statement: alter database open read only

Oracle instance shut down

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9o64w8nc_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/controlfile/o1_mf_9o64vdxg_.ctl deleted
Finished recover at 20-APR-14

5) Opening the newly recovered pdb from SqlPlus and checking the sales.sales table.

SQL> alter pluggable database salespdb open resetlogs;

Pluggable database altered.

SQL> select count(*) from sales;

COUNT(*)
----------
89

Conclusion:

The point-in-time restore of a pluggable database is similar to the point-in-time restore of a noncdb.
But the point-in-time recovery of a pluggable database is also a bit different because it requires some datafiles from the container database. Other pluggable databases can continue to operate and be available while the recovery is in progress and after it also.

Oracle Database 12c

Monday, 21 April 2014

Point-in-time recovery of a pluggable database.