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
