Container Database (CDB) Point In Time Recovery (PITR)
Point In Time Recovery (PITR) of a CDB is the same as that of non-CDB instances. Remember that , you are performing a PITR on the CDB and all the PDBs at once.
Log into the Database with up and running
[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.
Log in to the CDB user and create the table t1 in CBD.
SQL> conn c##ram/ram; Connected. SQL>create table t1 (id number(5)); Table created
1.insert the table records in t1 table
2.commit the records.
3.select the table records
4.check the timestamp/scn values
SQL> select * from t1; no rows selected SQL> insert into t1 values(&a); Enter value for a: 1 old 1: insert into t1 values(&a) new 1: insert into t1 values(1) 1 row created. SQL> / Enter value for a: 2 old 1: insert into t1 values(&a) new 1: insert into t1 values(2) 1 row created. SQL> / Enter value for a: 3 old 1: insert into t1 values(&a) new 1: insert into t1 values(3) 1 row created. SQL> / Enter value for a: 4 old 1: insert into t1 values(&a) new 1: insert into t1 values(4) 1 row created. SQL> / Enter value for a: 5 old 1: insert into t1 values(&a) new 1: insert into t1 values(5) 1 row created. SQL> commit; Commit complete. SQL> select * from t1; ID ---------- 1 2 3 4 5 SQL> select timestamp_to_scn(sysdate) from v$database; TIMESTAMP_TO_SCN(SYSDATE) ------------------------- 2234767
4.connect RMAN and take backup
5.connect to the user and drop the table.
6.select the table after drop it will show error because it got dropped.
SQL> ! [oracle@ram ~]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Wed Oct 10 19:33:20 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: DBWR (DBID=1322876759) RMAN> backup database plus archivelog; Starting backup at 10-OCT-18 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=87 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=1 RECID=6 STAMP=989114653 input archived log thread=1 sequence=2 RECID=7 STAMP=989117084 input archived log thread=1 sequence=3 RECID=8 STAMP=989177615 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/backupset/2018_10_10/o1_mf_annnn_TAG20181010T193336_fvw1or7q_.bkp tag=TAG20181010T193336 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 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=1 RECID=5 STAMP=989108146 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/backupset/2018_10_10/o1_mf_annnn_TAG20181010T193336_fvw1ozm6_.bkp tag=TAG20181010T193336 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 10-OCT-18 Starting backup at 10-OCT-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.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/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T193347_fvw1p49g_.bkp tag=TAG20181010T193347 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 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_TAG20181010T193347_fvw1q6xl_.bkp tag=TAG20181010T193347 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:18 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00014 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf input datafile file number=00015 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf input datafile file number=00016 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.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/777D95E41A572D82E053DD971D40E224/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T193347_fvw1qs5d_.bkp tag=TAG20181010T193347 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgr1m0f_.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/777BDA91F244116BE053DD971D400082/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T193347_fvw1rlf3_.bkp tag=TAG20181010T193347 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26 Finished backup at 10-OCT-18 Starting backup at 10-OCT-18 current log archived using channel ORA_DISK_1 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=4 RECID=9 STAMP=989177732 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/backupset/2018_10_10/o1_mf_annnn_TAG20181010T193533_fvw1sf47_.bkp tag=TAG20181010T193533 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 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_989177734_fvw1sh6l_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 10-OCT-18 RMAN> exit Recovery Manager complete. [oracle@ram ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 10 19:36:58 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> conn c##ram/ram; Connected. SQL> select * from t1; ID ---------- 1 2 3 4 5 SQL> show user USER is "C##RAM" SQL> drop table t1; Table dropped. SQL> commit; Commit complete. SQL> select * from t1; select * from t1 * ERROR at line 1: ORA-00942: table or view does not exist
7.connect to RMAN and bring Database to mount stage
8.Bring to that time or scn level to the database then restore and recover database with open reset logs.
SQL> !
[oracle@ram ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Oct 10 19:39:09 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBWR (DBID=1322876759)
RMAN> run{
shutdown immediate;
startup mount;
set until scn=2234767;
restore database;
recover database;
alter database open resetlogs;
}
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
executing command: SET until clause
Starting restore at 10-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
skipping datafile 5; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgr1m0f_.dbf
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 00001 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T003121_fvsyr1nq_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T003121_fvsyr1nq_.bkp tag=TAG20181010T003121
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
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 00013 to /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T003121_fvsysygz_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T003121_fvsysygz_.bkp tag=TAG20181010T003121
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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
using channel ORA_DISK_1
applied offline range to datafile 00021
offline range RECID=116 STAMP=989117086
applied offline range to datafile 00022
offline range RECID=115 STAMP=989117086
applied offline range to datafile 00023
offline range RECID=114 STAMP=989117086
applied offline range to datafile 00024
offline range RECID=113 STAMP=989117086
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/dbwr/DBWR/archivelog/2018_10_10/o1_mf_1_1_fvt4656l_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/dbwr/DBWR/archivelog/2018_10_10/o1_mf_1_2_fvt6l4od_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/fast_recovery_area/dbwr/DBWR/archivelog/2018_10_10/o1_mf_1_3_fvw1oo39_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/archivelog/2018_10_10/o1_mf_1_1_fvt4656l_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/archivelog/2018_10_10/o1_mf_1_2_fvt6l4od_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:17
Finished recover at 10-OCT-18
Statement processed
RMAN> exit
Recovery Manager complete.
9.Log in to that user and check the table.
10.Now we got the table with records successfully.
[oracle@ram ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 10 19:43:29 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> sho con_name CON_NAME ----------- CDB$ROOT SQL> conn c##ram/ram Connected. SQL> select * from t1; ID ---------- 1 2 3 4 5
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
