Performing point in time recovery using RMAN in 19c

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 archive log list for 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             2
Next log sequence to archive          4
Current log sequence                       4

Step 2: Create user for testing  and table data for the recovery purpose.

SQL> create user inba identified by inba default tablespace users temporary tablespace temp profile default account unlock;

User created.

SQL> grant connect,resource to inba;

Grant succeeded.
SQL> grant all privileges to inba;

Grant succeeded.
SQL> conn inba/inba
Connected.

SQL> create table product(pro_id number,pro_name varchar(10));

Table created.

SQL> insert into product values(11,’laptop’);

1 row created.

SQL> select * from product;

PRO_ID PRO_NAME

11 laptop
12 tv
13 monitor
14 cpu
15 mouse

SQL> select count(*) from product;

COUNT(*)

5

STEP 3 : Delete all the records in the table and Check the log sequence with specified time in the v$log.

SQL> archive log list
Database log mode                           Archive Mode
Automatic archival                            Enabled
Archive destination                           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence             2
Next log sequence to archive          4
Current log sequence                       4

SQL> conn inba/inba
Connected.
SQL> delete product;

5 rows deleted.

SQL> commit;

Commit complete.

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(

2 2341033 19:19:21
3 2341053 19:19:55
4 2341065 19:20:14

Here you can find the time of log sequence 3 19:19:55 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 4 : Startup the database at mount stage and connect the rman  to recover  the database using the log sequence number 3.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL>

[oracle@localhost scripts]$ . ora19c.env 
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 28 19:28:33 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966, not open)

RMAN> run
{
set until sequence=3;
restore database;
recover database;
}2> 3> 4> 5> 6>

executing command: SET until clause
Starting restore at 28-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T143632_j14znrcx_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T143632_j14znrcx_.bkp tag=TAG20210128T143632
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 28-JAN-21
Starting recover at 28-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_9_j15g2fl2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15j71yb_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15j83h9_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_9_j15g2fl2_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15gx9vj_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15j71yb_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15j83h9_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:04
Finished recover at 28-JAN-21

RMAN>

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

SQL> alter database open resetlogs;

Database altered.

SQL>

Step 5:- Check the table data now that the point in time recovery has worked.

SQL> select * from inba.product;

PRO_ID PRO_NAME

11 laptop
12 tv
13 monitor
14 cpu
15 mouse

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

One thought on “Performing point in time recovery using RMAN in 19c”

  1. Pingback: RMAN | ORACLEAGENT BLOG