Description:-
In this article we are going to see point in time recovery using RMAN.
Point in time recovery:-
- RMAN database point-in-time recovery (DBPITR) restores the database from RMAN backups.
- RMAN will be consider all ( required ) backups (full, incremental, transectional) to restore or roll forward to the desire time.
- Point in time recovery may be incomplete recovery because it does not use all the available archive logs files or completely recover all changes to your database.
- If you want to recover your database to the exact date/time in the past, use RMAN point in time recovery.
Prerequisites :-
- Database must be running in archivelog mode.
- You must have all the datafile backups available prior to target time to recover.
Let’s start the Demo:-
Steps to perform point in time recovery using RMAN :-
Step 1:- Table data for the recovery purpose
[oracle@18c ~]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Fri Aug 17 02:20:18 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> conn hari/oracle; Connected. SQL> select count(*) from emp; COUNT(*) ---------- 100000
Step 2:- Note the current log sequence number
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
Step 3:- Simulate a failure
SQL> conn hari/oracle; Connected. SQL> delete emp; 100000 rows deleted. SQL> commit; Commit complete.
Step 4:- Check the log sequence with specified time state by developer
SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3; SEQUENCE# FIRST_CHANGE# TO_CHAR( ---------- ------------- -------- 2 1697955 02:29:37 3 1697962 02:29:47 4 1702379 02:34:10
The developer states that the wrong DML statement was made AFTER 2.33 AM.Log sequence 3 was first written to at 2.29.47 AM so we should recover to a log sequence before this – i.e sequence# 2
Step 5:-Shutdown and mount the database
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1660943808 bytes Fixed Size 8658368 bytes Variable Size 1056964608 bytes Database Buffers 587202560 bytes Redo Buffers 8118272 bytes Database mounted.
Step 6:- Specify the log sequence number before wrong DML statement happen we need recover using RMAN backup and archivelogs.
[oracle@18c ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 02:41:02 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD18C (DBID=3984767297, not open)
RMAN> run
2> {
3> set until sequence=3;
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 17-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 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 00001 to /u01/app/oracle/oradata/PROD18C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T012117_fqcofot
d_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T012117_fqcofotd_.bkp tag=TA
G20180817T012117
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 17-AUG-18
Starting recover at 17-AUG-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqco
h2gd_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcs
fstl_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcs
g3b5_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcoh2gd_.arc thread=1 sequence=2
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcsfstl_.arc thread=1 sequence=1
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_2_fqcsg3b5_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-AUG-18
Step 7:- After recovery,open the database using resetlogs option
SQL> alter database open resetlogs; Database altered.
Step 8:- Check the table data now that the point in time recovery has worked.
SQL> select count(*) from hari.emp; COUNT(*) -------- 100000
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
