Point In Time Recovery of PDB database
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.
We 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).
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
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.
$/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;
##################################################
We insert a new record as of the date “2013-07-07 16:04:46”.
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
###########################################
Close the PDB using the command:
alter pluggable database ORAWISS12C close;
before performing the point-in time recovery.
$/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;
}
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.
$/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
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
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_INCAR# PDB_INCAR# STATUS INCARNATION_SCN INCARNATION_TIME BEGIN_RESETLOGS_SCN BEGIN_RESETLOGS_TIME END_RESETLOGS_SCN END_RESETLOGS_TIME 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 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 YES 3
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.