Oracle 12c: Performing PDB Point-In-Time Recovery

July 8th, 2013 | Posted in 12c, Blog, RMAN | 3 Comments

Introduction:

Database point-in-time recovery (DBPITR) restores the database from backups prior to the target time for recovery. DBPITR is sometimes called incomplete recovery because it does not use all of the available redo or completely recover all changes to your database.
The target SCN can be specified using a date and time, in which case the operation is sometimes called time-based recovery.

In this post, I am going to show how to perform a DBPITR of a Pluggable database (PDB).


In this article:

-The database named “ORAWISS” is the container root database also called target database.
-The database named “ORAWISS12C” is the PLUGGABLE database belongs to “ORAWISS” root container database.
-Recovery Manager (RMAN) will be used to perform DBPITR.
-We don’t setup a Fast Recovery Area (FRA).

Procedure:

V$PDBS view displays information about all Pluggable databases (PDB) associated with Container database.
We check the PDB is in open write mode.

sandbox1(orawiss):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 15:40:47 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       READ ONLY
         3 1574282659 ORAWISS12C                     READ WRITE

We access to the PDB in SQL*Plus using ALTER SESSION SET CONTAINER statement ;

SQL> alter session set container=ORAWISS12C;

Session altered.

We check the database is in archive log mode.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

We create a table to test the Point in Time recovery

SQL> create table wissem.test_incomplete_rec(P_DATE DATE) TABLESPACE TBS_REC;

Table created.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> insert into wissem.test_incomplete_rec values (SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from wissem.test_incomplete_rec;

P_DATE
-------------------
2013-07-07 15:43:00

We backup the database: Note we connect to the target database as the SYSBACKUP user.

sandbox1(orawiss):/home/oracle>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jul 7 16:02:49 2013

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

RMAN>  CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> backup database plus archivelog;


Starting backup at 07/07/2013 16:03:06
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=288 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=34 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=277 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=289 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=118 RECID=105 STAMP=820166587
channel ORA_DISK_1: starting piece 1 at 07/07/2013 16:03:09
channel ORA_DISK_1: finished piece 1 at 07/07/2013 16:03:10
piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_annnn_TAG20130707T160309_8xmlcxrj_.bkp tag=TAG20130707T160309 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07/07/2013 16:03:10

Starting backup at 07/07/2013 16:03:10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORAWISS/DATAFILE/sysaux.257.819111131
channel ORA_DISK_1: starting piece 1 at 07/07/2013 16:03:11
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORAWISS/DATAFILE/system.258.819111197
channel ORA_DISK_2: starting piece 1 at 07/07/2013 16:03:11
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
input datafile file number=00010 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
channel ORA_DISK_3: starting piece 1 at 07/07/2013 16:03:11
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.819111307
channel ORA_DISK_4: starting piece 1 at 07/07/2013 16:03:11
channel ORA_DISK_5: starting full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
input datafile file number=00014 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465
channel ORA_DISK_5: starting piece 1 at 07/07/2013 16:03:14
channel ORA_DISK_3: finished piece 1 at 07/07/2013 16:03:29
piece handle=/home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczy0_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:18
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.819111307
channel ORA_DISK_3: starting piece 1 at 07/07/2013 16:03:29
channel ORA_DISK_4: finished piece 1 at 07/07/2013 16:03:29
piece handle=/home/oracle/wissem/ORAWISS/E010D7F0F0EE7CE6E0433514DA0AF47E/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmld00t_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:18
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/ORAWISS/DATAFILE/undotbs1.260.819111263
channel ORA_DISK_4: starting piece 1 at 07/07/2013 16:03:29
channel ORA_DISK_5: finished piece 1 at 07/07/2013 16:03:29
piece handle=/home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmld43t_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_5: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_5: starting full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
input datafile file number=00013 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
channel ORA_DISK_5: starting piece 1 at 07/07/2013 16:03:30
channel ORA_DISK_2: finished piece 1 at 07/07/2013 16:03:33
piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczw1_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:22
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORAWISS/DATAFILE/users.259.819111261
channel ORA_DISK_2: starting piece 1 at 07/07/2013 16:03:34
channel ORA_DISK_4: finished piece 1 at 07/07/2013 16:03:34
piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldllt_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_5: finished piece 1 at 07/07/2013 16:03:34
piece handle=/home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldlod_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_5: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_3: finished piece 1 at 07/07/2013 16:03:34
piece handle=/home/oracle/wissem/ORAWISS/E010D7F0F0EE7CE6E0433514DA0AF47E/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldkv4_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: finished piece 1 at 07/07/2013 16:03:35
piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczw8_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:24
channel ORA_DISK_2: finished piece 1 at 07/07/2013 16:03:35
piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldq8v_.bkp tag=TAG20130707T160310 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
Finished backup at 07/07/2013 16:03:35

Starting backup at 07/07/2013 16:03:35
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=119 RECID=106 STAMP=820166616
channel ORA_DISK_1: starting piece 1 at 07/07/2013 16:03:36
channel ORA_DISK_1: finished piece 1 at 07/07/2013 16:03:37
piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_annnn_TAG20130707T160336_8xmldrvt_.bkp tag=TAG20130707T160336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07/07/2013 16:03:37

Starting Control File and SPFILE Autobackup at 07/07/2013 16:03:38
piece handle=/home/oracle/wissem/ORAWISS/autobackup/2013_07_07/o1_mf_s_820166618_8xmldtp6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07/07/2013 16:03:39

RMAN>

We insert a new record as of the date “2013-07-07 16:04:46″.

sandbox1(orawiss):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 16:04:08 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>  alter session set container=ORAWISS12C;

Session altered.

SQL>  alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> insert into wissem.test_incomplete_rec values (SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from wissem.test_incomplete_rec;

P_DATE
-------------------
2013-07-07 15:43:00
2013-07-07 16:04:46

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
sandbox1(orawiss):/home/oracle>

Close the PDB using the command: alter pluggable database ORAWISS12C close; before performing the point-in time recovery.

sandbox1(orawiss):/home/oracle>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jul 7 16:05:35 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> alter pluggable database ORAWISS12C close;

using target database control file instead of recovery catalog
Statement processed

Run the following RMAN block:

run {
set UNTIL TIME "to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')" ;
restore pluggable database ORAWISS12C;
recover pluggable database ORAWISS12C auxiliary destination='/home/oracle/wissem';
alter pluggable database ORAWISS12C open resetlogs;
}

The RMAN block does the following:

1) Restore the datafiles from Backup location.
2) Create an auxiliary instance with SID=’fcpj’ :

Creating automatic instance, with SID='fcpj'

initialization parameters used for automatic instance:
db_name=ORAWISS
db_unique_name=fcpj_pitr_ORAWISS12C_ORAWISS
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/opt/app/oracle
db_create_file_dest=/home/oracle/wissem
log_archive_dest_1='location=/home/oracle/wissem'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

3) Set the until time (or SCN) as specified.

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')";
...

4) Restore the control file to the auxiliary destination=’/home/oracle/wissem’
“output file name=/home/oracle/wissem/ORAWISS/controlfile/o1_mf_8xmln03o_.ctl”

5) Mount the cloned instance:
“sql statement: alter database mount clone database”
6) Recover the PDB until time (or SCN) as specified.
“Finished recover at 07/07/2013 16:08:19″
7) Open the PDB with Reset the logs.
“Statement processed”

NOTE: When specifying “AUXILIARY DESTINATION” We need to make sure we have enough space to hold the cloned database.
We are using the clause “AUXILIARY DESTINATION” because we didn’t setup a Fast Recovery Area (FRA). In the case an FRA is setup we can use the following RMAN block.

run {
set UNTIL TIME "to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')" ;
restore pluggable database ORAWISS12C;
recover pluggable database ORAWISS12C;
alter pluggable database ORAWISS12C open resetlogs;
}

Find below full output of the RMAN block:

RMAN> run {
set UNTIL TIME "to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')" ;
restore pluggable database ORAWISS12C;
recover pluggable database ORAWISS12C auxiliary destination='/home/oracle/wissem';
alter pluggable database ORAWISS12C open resetlogs;
}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 07/07/2013 16:06:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=288 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=282 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=285 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=60 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 00008 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
channel ORA_DISK_1: restoring datafile 00014 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465
channel ORA_DISK_1: reading from backup piece /home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmld43t_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00009 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
channel ORA_DISK_2: restoring datafile 00010 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
channel ORA_DISK_2: reading from backup piece /home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczy0_.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00012 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
channel ORA_DISK_3: restoring datafile 00013 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
channel ORA_DISK_3: reading from backup piece /home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldlod_.bkp
channel ORA_DISK_3: piece handle=/home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldlod_.bkp tag=TAG20130707T160310
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: piece handle=/home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmld43t_.bkp tag=TAG20130707T160310
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: piece handle=/home/oracle/wissem/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczy0_.bkp tag=TAG20130707T160310
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
Finished restore at 07/07/2013 16:07:08

Starting recover at 07/07/2013 16:07:08
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
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='fcpj'

initialization parameters used for automatic instance:
db_name=ORAWISS
db_unique_name=fcpj_pitr_ORAWISS12C_ORAWISS
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/opt/app/oracle
db_create_file_dest=/home/oracle/wissem
log_archive_dest_1='location=/home/oracle/wissem'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance ORAWISS

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

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')";
# 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 07/07/2013 16:07:25
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=82 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=12 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=84 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=13 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=85 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 /home/oracle/wissem/ORAWISS/autobackup/2013_07_07/o1_mf_s_820166618_8xmldtp6_.bkp
channel ORA_AUX_DISK_1: piece handle=/home/oracle/wissem/ORAWISS/autobackup/2013_07_07/o1_mf_s_820166618_8xmldtp6_.bkp tag=TAG20130707T160338
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/home/oracle/wissem/ORAWISS/controlfile/o1_mf_8xmln03o_.ctl
Finished restore at 07/07/2013 16:07:29

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')";
# switch to valid datafilecopies
switch clone datafile  8 to datafilecopy
 "+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037";
switch clone datafile  9 to datafilecopy
 "+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037";
switch clone datafile  10 to datafilecopy
 "+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065";
switch clone datafile  12 to datafilecopy
 "+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929";
switch clone datafile  13 to datafilecopy
 "+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381";
switch clone datafile  14 to datafilecopy
 "+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465";
# 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;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=820166860 file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037

datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=820166860 file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037

datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=820166860 file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065

datafile 12 switched to datafile copy
input datafile copy RECID=12 STAMP=820166860 file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929

datafile 13 switched to datafile copy
input datafile copy RECID=13 STAMP=820166860 file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381

datafile 14 switched to datafile copy
input datafile copy RECID=14 STAMP=820166860 file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 07/07/2013 16:07:41
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5

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 /home/oracle/wissem/ORAWISS/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczw1_.bkp
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 00004 to /home/oracle/wissem/ORAWISS/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldllt_.bkp
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /home/oracle/wissem/ORAWISS/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczw8_.bkp
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00006 to /home/oracle/wissem/ORAWISS/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldq8v_.bkp
channel ORA_AUX_DISK_2: piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldllt_.bkp tag=TAG20130707T160310
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_4: piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmldq8v_.bkp tag=TAG20130707T160310
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczw1_.bkp tag=TAG20130707T160310
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_3: piece handle=/home/oracle/wissem/ORAWISS/backupset/2013_07_07/o1_mf_nnndf_TAG20130707T160310_8xmlczw8_.bkp tag=TAG20130707T160310
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:25
Finished restore at 07/07/2013 16:08:07

datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=820166887 file name=/home/oracle/wissem/ORAWISS/datafile/o1_mf_system_8xmlng43_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=20 STAMP=820166887 file name=/home/oracle/wissem/ORAWISS/datafile/o1_mf_undotbs1_8xmlng3h_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=820166887 file name=/home/oracle/wissem/ORAWISS/datafile/o1_mf_sysaux_8xmlng7j_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=22 STAMP=820166887 file name=/home/oracle/wissem/ORAWISS/datafile/o1_mf_users_8xmlng46_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')";
# 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 'ORAWISS12C' "alter database datafile
 8 online";
sql clone 'ORAWISS12C' "alter database datafile
 9 online";
sql clone 'ORAWISS12C' "alter database datafile
 10 online";
sql clone 'ORAWISS12C' "alter database datafile
 12 online";
sql clone 'ORAWISS12C' "alter database datafile
 13 online";
sql clone 'ORAWISS12C' "alter database datafile
 14 online";
sql clone "alter database datafile  6 online";
# recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
 'ORAWISS12C'   delete archivelog;
sql clone 'alter database open read only';
plsql <<>>;
plsql <<>>;
# shutdown clone before import
shutdown clone abort
plsql <<  'ORAWISS12C');
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  13 online

sql statement: alter database datafile  14 online

sql statement: alter database datafile  6 online

Starting recover at 07/07/2013 16:08:09
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5

starting media recovery

archived log for thread 1 with sequence 119 is already on disk as file /home/oracle/wissem/ORAWISS/archivelog/2013_07_07/o1_mf_1_119_8xmldrhg_.arc
archived log for thread 1 with sequence 120 is already on disk as file /home/oracle/wissem/ORAWISS/archivelog/2013_07_07/o1_mf_1_120_8xmlmf2l_.arc
archived log file name=/home/oracle/wissem/ORAWISS/archivelog/2013_07_07/o1_mf_1_119_8xmldrhg_.arc thread=1 sequence=119
archived log file name=/home/oracle/wissem/ORAWISS/archivelog/2013_07_07/o1_mf_1_120_8xmlmf2l_.arc thread=1 sequence=120
media recovery complete, elapsed time: 00:00:01
Finished recover at 07/07/2013 16:08:12

sql statement: alter database open read only



Oracle instance shut down


Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/wissem/ORAWISS/datafile/o1_mf_sysaux_8xmlng7j_.dbf deleted
auxiliary instance file /home/oracle/wissem/ORAWISS/controlfile/o1_mf_8xmln03o_.ctl deleted
Finished recover at 07/07/2013 16:08:19

Statement processed

RMAN> exit


Recovery Manager complete.

We Check the table: Only one record must be present in the table, as we have recovered the PDB to a time prior the insert of the second record.

sandbox1(orawiss):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 16:09:17 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter session set container=ORAWISS12C;

Session altered.

SQL>  select * from wissem.test_incomplete_rec;

P_DATE
-------------------
07/07/2013 15:43:00

SQL>

We can also use “UNTIL SCN” clause, in this case, we can have the SCN number using timestamp_to_scn function:

SQL> select timestamp_to_scn(to_date('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS')) from v$database;

TIMESTAMP_TO_SCN(TO_DATE('2013-07-07:16:04:45','YYYY-MM-DD:HH24:MI:SS'))
------------------------------------------------------------------------
                                                                 2769644

SQL>

In this case, the RMAN block ;

run {
set until SCN = 2769644 ;
restore pluggable database ORAWISS12C;
recover pluggable database ORAWISS12C auxiliary destination='/home/oracle/wissem';
alter pluggable database ORAWISS12C open resetlogs;
}

Because the PDB was opened with reset logs, Oracle created a new incarnation.
The information about the new incarnation of the PDB is shown in V$PDB_INCARNATION view:

SQL> select * from V$PDB_INCARNATION;
 
DB_INCARNATION# PDB_INCARNATION# STATUS  INCARNATION_SCN INCARNATION_TIME BEGIN_RESETLOGS_SCN BEGIN_RESETLOGS_TIME END_RESETLOGS_SCN END_RESETLOGS_TIME PRIOR_DB_INCARNATION# PRIOR_PDB_INCARNATION#                   FLASHBACK_DATABASE_ALLOWED     CON_ID
--------------- ---------------- ------- --------------- ---------------- ------------------- -------------------- ----------------- ------------------ --------------------- ---------------------------------------- -------------------------- ----------
              2                1 CURRENT         2769647 07/07/2013 16:04             2770155 07/07/2013 16:08:20            2770170 07/07/2013 16:08:2                     2 0                                        YES                                 3
              2                0 PARENT          1720082 06/26/2013 10:54             1720082 06/26/2013 10:54:52            1720082 06/26/2013 10:54:5                     0                                          YES                                 3
 
SQL>

Note: During a PDB Point-In-Time Recovery, all the other PDBs in the Container database (CDB) are not affected and they can still in open write mode.

Cheers,
Wissem

References ; Doc 12cR1



3 Comments to “Oracle 12c: Performing PDB Point-In-Time Recovery”

  1. Tim Hall says:

    Awesome. I was having a conversation about this issue this morning. :)

    Cheers

    Tim…


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs
  • ron: unable to connect to Cluster Synchronization Service
  • KevinB: Please post or attach the response file
  • nnarimanov: I’d like to clarify the reason why rman restores all datafiles into +DATA. It’s because of...
  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!