Oracle19c-Using RMAN Incremental Backups to Refresh Standby Database

Description:

A standby database is a transactionally-consistent copy of the production database. It enables production Oracle database to survive disasters and data corruption. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch a standby database to the production role, minimizing the downtime associated with the outage. Moreover, performance of production database can be improved by offloading resource-intensive backup and reporting operations to standby systems. As you can see, it’s always desirable to have standby database synchronized with the primary database.

A standby database might lag behind the primary for various reasons like:

Unavailability of or insufficient network bandwidth between primary and standby database
Unavailability of Standby database
Corruption / Accidental deletion of Archive Redo Data on primary

Synchronizing the standby and primary databases can be done by copying and applying the archived logs from the primary database but this process is quite time consuming as it will first apply both the COMMITED and the NON COMMITED transactions followed by rolling back uncommitted transactions. Employing incremental backups of the primary database containing changes since the standby database was last refreshed is a faster alternative which will recover the standby database much faster as it will apply only the COMMITED transactions on the standby database. Moreover, incremental backups are also useful in cases when there are missing archived logs on Primary which have not been applied to the standby database.

Overview :

CHECK THE SEQUENCE IN BOTH NODES :
STOP THE MRP PROCESS
CHECK THE SCN IN STANDBY
SHUTDOWN THE STANDBY DATABASE
TAKE THE RMAN INCREMENTAL BACKUP FROM THE SCN IS NOTED IN STANDBY DATABASE
CREATE A STANDBY CONTROL FILE TO RESTORE IT IN THE STANDBY DATABASE
TRANSFER BACKUP FILES TO STANDBY SERVER
MANUALLY RESTORE THE CONTROL FILE BY REPLACING NEW CONTROL FILE TO ALL LOCATIONS
RESTORE THE CONTROL FILE IN RMAN
RECOVER THE DATABASE
START THE MRP PROCESS
CHECK THE SEQUENCE IN BOTH NODES

REFRESH STANDBY USING INCREMENTAL SCN BASED BACKUP :
——————————————————————————————-

CHECK THE SEQUENCE IN BOTH NODES :
———————————————————-

PRIMARY :
—————
SQL> select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq Generated
———- ————————–
1 68

STANDBY :
—————

SQL> select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Standby Seq Generated
———- ————————–
1 62

STANDBY :
————–

STOP THE MRP PROCESS :
————————————-

SQL> alter database recover managed standby database finish;

Database altered.

CHECK THE SCN IN STANDBY :
——————————————-

SQL> select current_scn from v$database;

CURRENT_SCN
——————-
2173711

SHUTDOWN THE STANDBY DATABASE :
——————————————————-

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

PRIMARY :
—————

TAKE THE RMAN INCREMENTAL BACKUP FROM THE SCN IS NOTED IN STANDBY DATABASE :
————————————————————————————————————————————–

SQL> !rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Sat Jul 22 14:13:07 2019
Version 19.3.0.0.0

connected to target database: ORCL (DBID=1539378116)

RMAN> run {
allocate channel c1 type disk format ‘/u01/backup/archive%U.bkp’;
backup incremental from scn 2173711 database;
}

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=138 device type=DISK

Starting backup at 22-JUL-19
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel c1: starting piece 1 at 22-JUL-19
channel c1: finished piece 1 at 22-JUL-19
piece handle=/u01/backup/archive2pu4o8oo_1_1.bkp tag=TAG20190622T141800 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 22-JUL-19
channel c1: finished piece 1 at 22-JUL-19
piece handle=/u01/backup/archive2qu4o8pi_1_1.bkp tag=TAG20190622T141800 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-JUL-19
released channel: c1

RMAN> exit

CREATE A STANDBY CONTROL FILE TO RESTORE IT IN THE STANDBY DATABASE :
——————————————————————————————————–

SQL> alter database create standby controlfile as ‘/u01/backup/standby.ctl’;

Database altered.

SEND THE BACKUP FILES TO STANDBY SERVER :
————————————————————–

[oracle@ram ORCL]$ cd /u01/backup/
[oracle@ram backup]$ ls -lrth
total 20M
-rw-r—– 1 oracle oinstall 9.1M Jul 22 14:18 archive2pu4o8oo_1_1.bkp
-rw-r—– 1 oracle oinstall 11M Jul 22 14:18 archive2qu4o8pi_1_1.bkp
[oracle@ram backup]$ scp -r archive2* oracle@192.168.1.44:/u01/backup
oracle@192.168.1.44’s password:
archive2pu4o8oo_1_1.bkp 100% 9288KB 30.7MB/s 00:00
archive2qu4o8pi_1_1.bkp 100% 10MB 47.2MB/s 00:00

[oracle@ram backup]$ scp -r standby.ctl oracle@192.168.1.44:/u01/backup
oracle@192.168.1.44’s password:
standby.ctl 100% 10MB 47.4MB/s 00:00

REPLACE AND RESTORE THE CONTROL FILE BY NEW CONTROL FILE TO ALL CONTROL FILE LOCATIONS :
—————————————————————————————————————————————

[oracle@ram backup]$ cp standby.ctl /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
[oracle@ram backup]$ cp standby.ctl /u01/app/oracle/oradata/ORCL/control01.ctl

WE CAN RESTORE THE CONTROL FILE IN RMAN :
————————————————————–

SQL> startup mount
ORACLE instance started.

Total System Global Area 1224736224 bytes
Fixed Size 8895968 bytes
Variable Size 889192448 bytes
Database Buffers 318767104 bytes
Redo Buffers 7880704 bytes
Database mounted.

RECOVER THE DATABASE :
————————————-

SQL> !rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Sat Jul 22 14:24:12 2019
Version 19.3.0.0.0

connected to target database: ORCL (DBID=1539378116, not open)

RMAN> catalog start with ‘/u01/backup’;

Starting implicit crosscheck backup at 22-JUL-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 22-JUL-19

Starting implicit crosscheck copy at 22-JUL-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-JUL-19

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619327_gjvqw94c_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvr5d5p_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvr5y0o_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvr61r0_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvr65to_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvrftyz_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvrfxfo_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvrfzwj_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STANDBY/autobackup/2019_06_22/o1_mf_s_1011619672_gjvrg29c_.bkp

searching for all files that match the pattern /u01/backup

List of Files Unknown to the Database
=====================================
File Name: /u01/backup/full01u4o0mi_1_1
File Name: /u01/backup/cont03u4o0r5_1_1
File Name: /u01/backup/sp05u4o0rt_1_1
File Name: /u01/backup/arch09u4o23c_1_1
File Name: /u01/backup/standby.ctl

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backup/standby.ctl

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/backup/full01u4o0mi_1_1
RMAN-07518: Reason: Foreign database file DBID: 3576090905 Database Name: DBWR
File Name: /u01/backup/cont03u4o0r5_1_1
RMAN-07518: Reason: Foreign database file DBID: 3576090905 Database Name: DBWR
File Name: /u01/backup/sp05u4o0rt_1_1
RMAN-07518: Reason: Foreign database file DBID: 3576090905 Database Name: DBWR
File Name: /u01/backup/arch09u4o23c_1_1
RMAN-07518: Reason: Foreign database file DBID: 3576090905 Database Name: DBWR

RMAN> recover database;

Starting recover at 22-JUL-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCL/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCL/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCL/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/archive2pu4o8oo_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/backup/archive2pu4o8oo_1_1.bkp tag=TAG20190622T141800
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

media recovery complete, elapsed time: 00:00:00
Finished recover at 22-JUL-19

RMAN> exit

Recovery Manager complete.

START THE MRP PROCESS :
—————————————

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

CHECK THE SEQUENCE IN BOTH NODES :
———————————————————–

PRIMARY :
————–
LOGS GENERATED IN STANDBY :
———————————————

SQL> select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq Generated
———- ————————–
1 71

STANDBY :
————-
LOGS RECEIVED IN STANDBY :
————————————–

SQL> select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Standby Seq Generated
———- ————————–
1 71

LOG APPLIED IN STANDBY :
————————————–

SQL> select thread#, max(sequence#) “Last Standby Seq Applied” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in (‘YES’,’IN-MEMORY’) group by thread# order by 1;

THREAD# Last Standby Seq Applied
———- ————————
1 71

Catch Me On:- Hariprasath Rajaram

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

Recent Posts

Get updated

Oracle19c-Using RMAN Incremental Backups to Refresh Standby Database

You may also like

Previous Post

Oracle19c-Export Data From Physical Standby Database Using Datapump

View Post

Next Post

Oracle19c-Restore using Rman To Create Oracle 19c Database on scratch server

View Post

Recent Posts

Get updated