Description:-
In this article we will see how to perform a point-in-time recovery for a pluggable database (PDB) using until time.
Situation
- A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA erroneously deletes or updates the contents of one or more tables, drops database objects that are still needed during an update to an application, or runs a large batch update that fails midway.
- A database upgrade fails or an upgrade script goes wrong.
- A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.
Demo:-
Step:-1 Prepare the oradbwrpdb1
[oracle@oracle21c ~]$ sqlplus scott/tiger@oradbwrpdb1
SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 21:39:52 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Tue Sep 20 2022 15:58:01 +05:30
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
SQL> create table pitr as select * from emp;
Table created.
SQL> select count(*) from pitr;
COUNT(*)
———-
14
SQL> select systimestamp from dual;
SYSTIMESTAMP
————————————-
20-SEP-22 09.42.03.945315 PM +05:30
Step:-2 Backing Up the CDB
[oracle@oracle21c backup]$ mkdir -p /u01/backup
export NLS_DATE_FORMAT=’DD-MM-YYYY HH:MI:SS’
rman target /
run
{
configure channel device type disk format ‘/u01/backup/%U’;
configure controlfile autobackup on;
backup database plus archivelog;
}
[oracle@oracle21c ~]$ echo $ORACLE_SID
oradbwr
[oracle@oracle21c ~]$ export NLS_DATE_FORMAT=’DD-MM-YYYY HH:MI:SS’
[oracle@oracle21c ~]$ rman target /
Recovery Manager: Release 21.0.0.0.0 – Production on Tue Sep 20 21:47:28 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (DBID=1541321054)
RMAN> run
{
configure channel device type disk format ‘/u01/backup/%U’;
configure controlfile autobackup on;
backup database plus archivelog;
}2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/u01/backup/%U’;
new RMAN configuration parameters are successfully stored
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
Starting backup at 20-09-2022 09:47:43
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 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=2 RECID=1 STAMP=1114431541
input archived log thread=1 sequence=3 RECID=2 STAMP=1114471460
input archived log thread=1 sequence=4 RECID=3 STAMP=1114549076
input archived log thread=1 sequence=5 RECID=4 STAMP=1114552433
input archived log thread=1 sequence=6 RECID=5 STAMP=1114552434
input archived log thread=1 sequence=7 RECID=6 STAMP=1114632952
input archived log thread=1 sequence=8 RECID=7 STAMP=1114979344
input archived log thread=1 sequence=9 RECID=9 STAMP=1115146514
input archived log thread=1 sequence=10 RECID=11 STAMP=1115151513
input archived log thread=1 sequence=11 RECID=13 STAMP=1115499824
input archived log thread=1 sequence=12 RECID=15 STAMP=1115501070
input archived log thread=1 sequence=13 RECID=17 STAMP=1115847414
input archived log thread=1 sequence=14 RECID=19 STAMP=1115851066
input archived log thread=1 sequence=15 RECID=21 STAMP=1115922697
input archived log thread=1 sequence=16 RECID=23 STAMP=1115934464
channel ORA_DISK_1: starting piece 1 at 20-09-2022 09:47:45
channel ORA_DISK_1: finished piece 1 at 20-09-2022 09:48:20
piece handle=/u01/backup/09187jo1_9_1_1 tag=TAG20220920T214744 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 20-09-2022 09:48:20
Starting backup at 20-09-2022 09:48:21
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/ORADBWR/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORADBWR/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORADBWR/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORADBWR/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-09-2022 09:48:21
channel ORA_DISK_1: finished piece 1 at 20-09-2022 09:49:16
piece handle=/u01/backup/0a187jp5_10_1_1 tag=TAG20220920T214821 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/undotbs01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-09-2022 09:49:17
channel ORA_DISK_1: finished piece 1 at 20-09-2022 09:49:42
piece handle=/u01/backup/0b187jqt_11_1_1 tag=TAG20220920T214821 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/ORADBWR/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORADBWR/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ORADBWR/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 20-09-2022 09:49:42
channel ORA_DISK_1: finished piece 1 at 20-09-2022 09:50:07
piece handle=/u01/backup/0c187jrm_12_1_1 tag=TAG20220920T214821 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 20-09-2022 09:50:07
Starting backup at 20-09-2022 09:50:07
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=17 RECID=26 STAMP=1115934607
channel ORA_DISK_1: starting piece 1 at 20-09-2022 09:50:08
channel ORA_DISK_1: finished piece 1 at 20-09-2022 09:50:09
piece handle=/u01/backup/0d187jsg_13_1_1 tag=TAG20220920T215008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-09-2022 09:50:09
Starting Control File and SPFILE Autobackup at 20-09-2022 09:50:09
piece handle=/u01/app/oracle/fast_recovery_area/ORADBWR/autobackup/2022_09_20/o1_mf_s_1115934609_klmt1sjz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-09-2022 09:50:10
Step:3 Droping a table for testing
[oracle@oracle21c ~]$ sqlplus scott/tiger@oradbwrpdb1
SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 21:52:53 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Tue Sep 20 2022 21:39:52 +05:30
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
SQL> select systimestamp from dual;
SYSTIMESTAMP
—————————————————————————
20-SEP-22 09.52.55.147380 PM +05:30
SQL> drop table pitr;
Table dropped.
Step:-4 Recovering the PDB to a Certain Point in Time
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
SQL> alter pluggable database ORADBWRPDB1 close immediate;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 MOUNTED
[oracle@oracle21c ~]$ mkdir -p /u01/arch/pitr
run
{
SET UNTIL TIME “TO_DATE(’20-SEP-2022 21:51:00‘,’DD-MON-YYYY HH24:MI:SS’)”;
RESTORE pluggable database oradbwrpdb1;
RECOVER pluggable database oradbwrpdb1 auxiliary destination=’/u01/arch/pitr’;
ALTER PLUGGABLE DATABASE oradbwrpdb1 OPEN RESETLOGS;
}
[oracle@oracle21c ~]$ echo $ORACLE_SID
oradbwr
[oracle@oracle21c ~]$ export NLS_DATE_FORMAT=’DD-MM-YYYY HH:MI:SS’
[oracle@oracle21c ~]$ rman target /
Recovery Manager: Release 21.0.0.0.0 – Production on Tue Sep 20 22:01:23 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (DBID=1541321054)
RMAN> run
{
SET UNTIL TIME “TO_DATE(’20-SEP-2022 21:51:00′,’DD-MON-YYYY HH24:MI:SS’)”;
RESTORE pluggable database oradbwrpdb1;
RECOVER pluggable database oradbwrpdb1 auxiliary destination=’/u01/arch/pitr’;
ALTER PLUGGABLE DATABASE oradbwrpdb1 OPEN RESETLOGS;
}
executing command: SET until clause
Starting restore at 20-09-2022 10:02:58
using channel ORA_DISK_1
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 00009 to /u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORADBWR/oradbwrpdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/0b187jqt_11_1_1
channel ORA_DISK_1: piece handle=/u01/backup/0b187jqt_11_1_1 tag=TAG20220920T214821
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 20-09-2022 10:03:14
Starting recover at 20-09-2022 10:03:14
current log archived
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 20-09-2022 10:03:17
Statement processed
Step:-5 Verifying Success of the Point-In-Time Recovery
[oracle@oracle21c ~]$ sql
SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 22:07:21 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORADBWRPDB1 READ WRITE NO
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
[oracle@oracle21c ~]$ sqlplus scott/tiger@oradbwrpdb1
SQL*Plus: Release 21.0.0.0.0 – Production on Tue Sep 20 22:07:32 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Tue Sep 20 2022 21:39:52 +05:30
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
SQL> select count(*) from pitr;
COUNT(*)
———-
14
Connect with me on:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
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
