Scenario Preview: Resolving gap between primary and physical standby database resolved by taking incremental backup and perform recovery using rman incremental backup.
- Query to check current SCN status of standby database. Run on standby DB.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
339341431
SQL> select min(fhscn) from x$kcvfh;
MIN(FHSCN)
----------------
339341432
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY';
MIN(F.FHSCN)
----------------
339341432
- SCN based rman incremental backup command below for reference.
run
{
allocate channel d0 type disk FORMAT 'backupscn_based%d_%s_%t.bak';
allocate channel d1 type disk FORMAT 'backupscn_based%d_%s_%t.bak';
allocate channel d2 type disk FORMAT 'backupscn_based%d_%s_%t.bak';
allocate channel d3 type disk FORMAT 'backupscn_based%d_%s_%t.bak';
backup as compressed backupset incremental from scn 339341432 database;
copy current controlfile to 'backupscn_basedcontrol01full_%d_%t_%U.bak';
backup current controlfile for standby format 'backupscn_basedstandbycontrol_%d_%T_%U.ctl';
RELEASE CHANNEL d0;
RELEASE CHANNEL d1;
RELEASE CHANNEL d2;
RELEASE CHANNEL d3;
}
- Open database in nomount stage and restore control file
[oracle@srv4 backup]$ . oraenv
ORACLE_SID = [ZABBIXDR] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srv4 backup]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 27 18:37:36 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1795162112 bytes
Fixed Size 2925456 bytes
Variable Size 620760176 bytes
Database Buffers 1157627904 bytes
Redo Buffers 13848576 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@srv4 backup]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 27 18:40:44 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ZABBIX (not mounted)
RMAN> restore standby controlfile from '/u01/backup/standbycontrol_ZABBIX_1d051c76_1_1.ctl';
Starting restore at 27-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ZABBIXDR/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ZABBIXDR/control02.ctl
Finished restore at 27-JUL-21
- Open database in mount stage and restore standby database
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/backup';
Starting implicit crosscheck backup at 27-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 27-JUL-21
Starting implicit crosscheck copy at 27-JUL-21
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 27-JUL-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/autobackup/2021_07_25/o1_mf_s_1078857835_jhts676p_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/autobackup/2021_07_23/o1_mf_s_1078691869_jhopt5fk_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/autobackup/2021_07_19/o1_mf_s_1078341966_jhc13pjr_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_17/o1_mf_1_51_jh5txgn3_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_17/o1_mf_1_52_jh5w956q_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_33_jhz922g1_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_34_jhz923jb_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_32_jhz87wwc_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_35_jhzcszvk_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_27/o1_mf_1_31_jhz87yd4_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_29_jhxdgqjh_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_28_jhx8b6c8_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_26_jhx752c2_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_27_jhx754fs_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_25_jhx750kr_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_24_jhwmbsk6_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_26/o1_mf_1_30_jhxfy2os_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_21_jhtrwp8q_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_10_jhtr7176_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_11_jhtr72bt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_12_jhtr73o7_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_23_jhts9kwh_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_9_jhtqv96b_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_15_jhtrmltg_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_17_jhtrnorq_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_19_jhtrwhvo_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_8_jhtqv8w7_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_16_jhtrmmh5_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_18_jhtrpgbr_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_13_jhtrmj20_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_20_jhtrwmnk_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_6_jhtpbq66_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_14_jhtrmlrt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_22_jhts9jpq_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_25/o1_mf_1_7_jhtqjvr0_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_08/o1_mf_1_49_jgg446oc_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_2_jhokqtln_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_3_jhop0w97_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_4_jhop0wt0_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_23/o1_mf_1_5_jhopsvv9_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_54_jhbqjq1l_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_64_jhc0njy1_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_58_jhbw0yf3_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_61_jhbzy9yf_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_57_jhbw02oc_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_63_jhbzyc4y_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_53_jhbqjqf9_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_56_jhbvzxjs_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_60_jhbzy6vz_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_55_jhbr7mg0_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_62_jhbzy9xr_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_59_jhbw495f_.arc
File Name: /u01/app/oracle/fast_recovery_area/ZABBIXDR/archivelog/2021_07_19/o1_mf_1_1_jhc0tnhq_.arc
searching for all files that match the pattern /u01/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/standbycontrol_ZABBIX_1d051c76_1_1.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/standbycontrol_ZABBIX_1d051c76_1_1.ctl
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 20 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 20 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
RMAN> RECOVER DATABASE NOREDO;
RMAN> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
- Apply Media recovery process on standby database
RMAN> alter database recover managed standby database disconnect from session;
Statement processed
RMAN> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 7