Point In Time Recovery (PITR) in PDB in Oracle Database 12c Release 2

Pluggable Database (CDB) Point In Time Recovery (PITR)

Point In Time Recovery (PITR) of a PDB follows as same as regular database. The PDB is closed, restored and recovered to the required point in time, then opened with the resetlogs option.

In this case, the resetlogs option does nothing with the log files themselves, but creates a new PDB incarnation.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 10 02:04:01 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> !
[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Oct 10 02:05:01 2018

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

connected to target database: DBWR (DBID=1322876759)

Taking backup of PDB5 database backup

RMAN> backup pluggable database pdb5; 
Starting backup at 10-OCT-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf input datafile file number=00023 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf channel ORA_DISK_1: starting piece 1 at 10-OCT-18 channel ORA_DISK_1: finished piece 1 at 10-OCT-18 piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T020514_fvt4837h_.bkp tag=TAG20181010T020514 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 Finished backup at 10-OCT-18 Starting Control File and SPFILE Autobackup at 10-OCT-18 piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_10/o1_mf_s_989114740_fvt48wpf_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 10-OCT-18

Connecting user with the PDB5 database

Create table and inserting records in that table with commit.

Checking the scn after giving commit.

Then delete the records in the table with commit.

SQL> conn c##hari@pdb5;
Enter password: 
Connected.
SQL> create table test (id number(5));

Table created.

SQL> insert into test values(&a);
Enter value for a: 1
old 1: insert into test values(&a)
new 1: insert into test values(1)

1 row created.

SQL> /
Enter value for a: 2
old 1: insert into test values(&a)
new 1: insert into test values(2)

1 row created.

SQL> /
Enter value for a: 3
old 1: insert into test values(&a)
new 1: insert into test values(3)

1 row created.

SQL> /
Enter value for a: 4
old 1: insert into test values(&a)
new 1: insert into test values(4)

1 row created.

SQL> /
Enter value for a: 5
old 1: insert into test values(&a)
new 1: insert into test values(5)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID
----------
1
2
3
4
5

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
2196232

SQL> delete from test;

5 rows deleted.

SQL> commit;

Commit complete.

Checking the table records after delete with commit option

SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist

Connecting the RMAN

inside the run command we need to give as follows

a) particular scn number (which we got before delete records in table)

b) restore the pdb

c) recover the pdb

d) open the pdb with reset logs option

[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Oct 10 02:40:00 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> run 
{
set until scn =2196232;
restore pluggable database pdb5;
recover pluggable database pdb5 auxiliary destination='/u01/app/oracle/oradata/DBWR/';
alter pluggable database pdb5 open resetlogs;
}

executing command: SET until clause

Starting restore at 10-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 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 00021 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
channel ORA_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
channel ORA_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T020514_fvt4837h_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T020514_fvt4837h_.bkp tag=TAG20181010T020514
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 10-OCT-18

Starting recover at 10-OCT-18
current log archived
using channel ORA_DISK_1


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

Finished recover at 10-OCT-18

Statement processed

RMAN> exit


Recovery Manager complete.


After recovery log into particular pluggable database and we can check the table records.

[oracle@ram ~]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 10 02:44:55 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
-----------------
CDB$ROOT

SQL> conn c##hari@pdb5
Enter password: 
Connected.

SQL> select * from test;

ID
----------
1
2
3
4
5


The Deleted records restored successfully in the pluggable database .

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Leave a Reply

Your email address will not be published. Required fields are marked *