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, transactional) 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 archive log mode.
- You must have all the data file 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@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Thu May 16 01:46:34 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1778381832 bytes
Fixed Size 8897544 bytes
Variable Size 536870912 bytes
Database Buffers 1224736768 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ARC_BKP
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
User creation for testing
SQL> create user hari identified by “oracle” default tablespace users temporary tablespace temp profile default account unlock;
User created.
SQL> grant create session, create table to hari;
Grant succeeded.
SQL> alter user hari quota 500M on users;
User altered.
SQL> conn hari/oracle
Connected.
SQL> create table emp(id number);
Table created.
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 /u01/ARC_BKP
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
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(
---------- ------------- --------
11 2408756 00:57:43
12 2410538 00:59:40
13 2520532 01:47:09
The developer states that the wrong DML statement was made AFTER 00.57 AM.Log sequence 12 was first written to at 00.59.40 AM so we should recover to a log sequence before this – i.e sequence# 11
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 1778381832 bytes
Fixed Size 8897544 bytes
Variable Size 536870912 bytes
Database Buffers 1224736768 bytes
Redo Buffers 7876608 bytes
Database mounted.Step 6:- Specify the log sequence number before wrong DML statement happen we need recover using RMAN backup and archivelogs.
[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 02:09:31 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBWR (DBID=1337475478, not open)
RMAN> run
2> {
3> set until sequence=12;
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 16-MAY-19
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/DBWR/datafile/o1_mf_system_g91ppy2b_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_g91pxgmq_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_g91q0521_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_g91q08bm_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/02u1l80c_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/02u1l80c_1_1 tag=TAG20190516T005748
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 16-MAY-19
Starting recover at 16-MAY-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /u01/ARC_BKP/1_11_1003340442.dbf
archived log file name=/u01/ARC_BKP/1_11_1003340442.dbf thread=1 sequence=11
media recovery complete, elapsed time: 00:00:05
Finished recover at 16-MAY-19Step 7:- After recovery,open the database using resetlogs option
SQL> alter database open resetlogs;
Statement processed
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
