Performing point in time recovery using RMAN

Description:-

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.

STEP 1:Check the count of the table and the archive lg list for the current log sequence number.

SQL> select * from remo.salary;

ID NAME

———- ———-

11 inba

13 siva

12 saga

14 shanku

16 rahul

15 biju

6 rows selected.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 6

Next log sequence to archive 8

Current log sequence 8

STEP 2: Delete all the records in the table and check the sequence and first change time in the v$log

SQL> delete salary;

6 rows deleted.

SQL> conn / as sysdba

Connected.

SQL> select sequence#,first_change#, to_char(first_time,’HH24:MI:SS’) from v$log order by 3;

SEQUENCE# FIRST_CHANGE# TO_CHAR(

———- ————- ——–

7 1725641 01:24:27

8 1725677 01:25:27

6 1718465 01:33:58

Here you can find the time of log sequence 8 01:25:27 that is the time the data would be available in the database.so we need to go back that time and recover the database using the RMAN backups.

STEP 3: Startup the database at mount stage and connect the rman to recover the database using the log sequence number 8.

[oracle@trichy ~]$ . ./ora12c.env

[oracle@trichy ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Nov 20 01:51:52 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAJ (DBID=2999824006, not open)

RMAN> run

{

set until sequence=8;

restore database;

recover database;

}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 20-NOV-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 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/RAJ/datafile/o1_mf_system_hqhll41p_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/RAJ/datafile/o1_mf_sysaux_hqhlmklv_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/RAJ/datafile/o1_mf_undotbs1_hqhlnbqf_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/RAJ/datafile/o1_mf_users_hqhlnctp_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/raj/RAJ/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T012430_hvfmcql1_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/raj/RAJ/backupset/2020_11_20/o1_mf_nnndf_TAG20201120T012430_hvfmcql1_.bkp tag=TAG20201120T012430

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 20-NOV-20

Starting recover at 20-NOV-20

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/raj/RAJ/archivelog/2020_11_20/o1_mf_1_7_hvfmfhrf_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/raj/RAJ/archivelog/2020_11_20/o1_mf_1_7_hvfmfhrf_.arc thread=1 sequence=7

media recovery complete, elapsed time: 00:00:00

Finished recover at 20-NOV-20

RMAN>

STEP 4: Once the recovery has completed,open the database with resetlogs optionand check the data in the table.

Database mounted.

SQL> alter database open resetlogs;

Database altered.