Automatic Gap Detection and Resolution

Archive Gap: It means set of Archive Logs are not transmitted from Primary Database to Standby Database for some reason. The main reason could be your Network Connectivity. When network connectivity resumes data guard resumes redo data transmission from the Primary to Standby site.

Causes of Archive Gaps :

  1. Network Disconnection.
  2. Standby database outage.
  3. I/O issues at the standby database.
  4. Insufficient Bandwith in the Network between the Primary and Standby Site

Oracle Data Guard has two mechanisms for Gap Detection and  Resolution.

  1. Automatic Gap Resolution
  2. FAL configuration

Automatic Gap Resolution :

We don’t need any extra configuration for this. Let us understand this with an example.

To simulate this situation, I have stopped listener at the standby database.

Step 1 : Stop listener at standby database.

Step 2: Check archive log sequence on both sides.

Primary :

SQL> select name,open_mode from v$database;

NAME	  OPEN_MODE
--------- --------------------
TESTDB	  READ WRITE

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /u01/arc/testdb/stdby/
Oldest online log sequence     46
Next log sequence to archive   48
Current log sequence	       48

Standby :

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    46

Step 3: Generate archive logs using “ALTER SYSTEM SWITCH LOGFILE” command.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Step 4: We can check error at the alert log that listener is not up at standby side so, there is trouble creating archive logs at the standby site.

***********************************************************************

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testdb)(UR=A)(CID=(PROGRAM=oracle)(HOST=test1.localdomain)(USER=oracle))))

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 23-APR-2018 16:33:05
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12541
    
TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    
TNS-00511: No listener
    nt secondary err code: 111
    nt OS err code: 0
Error 12541 received logging on to the standby
Check whether the listener is up and running.
PING[ARC2]: Heartbeat failed to connect to standby 'std_testdb'. Error is 12541.
Mon Apr 23 16:33:40 2018
Thread 1 cannot allocate new log, sequence 51
Checkpoint not complete
  Current log# 2 seq# 50 mem# 0: /u01/oracle/oradata/testdb/redo02.log
Thread 1 advanced to log sequence 51 (LGWR switch)
  Current log# 3 seq# 51 mem# 0: /u01/oracle/oradata/testdb/redo03.log
Mon Apr 23 16:33:43 2018
Archived Log entry 66 added for thread 1 sequence 50 ID 0xa4536939 dest 1:
Mon Apr 23 16:34:05 2018


***********************************************************************

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testdb)(UR=A)(CID=(PROGRAM=oracle)(HOST=test1.localdomain)(USER=oracle))))

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 23-APR-2018 16:34:05
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12541
    
TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    
TNS-00511: No listener
    nt secondary err code: 111


***********************************************************************

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testdb)(UR=A)(CID=(PROGRAM=oracle)(HOST=test1.localdomain)(USER=oracle))))

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 23-APR-2018 16:34:05
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12541
    
TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    
TNS-00511: No listener
    nt secondary err code: 111
    nt OS err code: 0


***********************************************************************

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testdb)(UR=A)(CID=(PROGRAM=oracle)(HOST=test1.localdomain)(USER=oracle))))

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 23-APR-2018 16:34:05
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12541
    
TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    
TNS-00511: No listener
    nt secondary err code: 111
    nt OS err code: 0
Error 12541 received logging on to the standby
Check whether the listener is up and running.
PING[ARC2]: Heartbeat failed to connect to standby 'std_testdb'. Error is 12541.

We can check error: “Heartbeat failed to connect to standby ‘std_testdb”.

Step 5: Start listener at Standby :

Step 6: Check alert log of standby database  :

RFS[1]: Assigned to RFS process 6622
RFS[1]: Opened log for thread 1 sequence 48 dbid -1538101191 branch 973537980
Mon Apr 23 16:35:07 2018
RFS[2]: Assigned to RFS process 6624
RFS[2]: Opened log for thread 1 sequence 49 dbid -1538101191 branch 973537980
Mon Apr 23 16:35:07 2018
RFS[3]: Assigned to RFS process 6626
RFS[3]: Selected log 4 for thread 1 sequence 47 dbid -1538101191 branch 973537980
Mon Apr 23 16:35:07 2018
RFS[4]: Assigned to RFS process 6620
RFS[4]: Opened log for thread 1 sequence 50 dbid -1538101191 branch 973537980
Archived Log entry 18 added for thread 1 sequence 48 rlc 973537980 ID 0xa4536939 dest 2:
Archived Log entry 19 added for thread 1 sequence 50 rlc 973537980 ID 0xa4536939 dest 2:
Archived Log entry 20 added for thread 1 sequence 49 rlc 973537980 ID 0xa4536939 dest 2:
Mon Apr 23 16:35:10 2018
Archived Log entry 21 added for thread 1 sequence 47 ID 0xa4536939 dest 4:
Mon Apr 23 16:35:10 2018
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process 6628
RFS[5]: Selected log 4 for thread 1 sequence 52 dbid -1538101191 branch 973537980
Mon Apr 23 16:35:10 2018
RFS[6]: Assigned to RFS process 6632
RFS[6]: Selected log 5 for thread 1 sequence 51 dbid -1538101191 branch 973537980
Mon Apr 23 16:35:10 2018
Archived Log entry 22 added for thread 1 sequence 51 ID 0xa4536939 dest 4:

We can see here Old archive logs are automatically transmitted to Standby Database.

FAL Configuration :

Step 1: Configure FAL_SERVER:

SQL> show parameter FAL

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
fal_client			     string	 std_testdb
fal_server			     string	 TESTDB
SQL>

Note: FAL_SERVER accepts the name that is associated in tnsnames.ora entry.

Step 2: Generate archive logs in primary using alter system switch logfile.

Step 3: Check maximum applied log at the standby database.

SQL> select max(sequence#) from v$archived_log  where applied='YES';

MAX(SEQUENCE#)
--------------
	    73

SQL> recover managed standby database cancel;
Media recovery complete. 
SQL>

Step 4: To simulate this situation manually delete archive logs from standby.

[oracle@localhost stdby]$ rm -rfv 1_76_973537980.arc 
removed `1_76_973537980.arc'
[oracle@localhost stdby]$ rm -rfv 1_77_973537980.arc 
removed `1_77_973537980.arc'
[oracle@localhost stdby]$

Step 5: Start recovery

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL>

Step 6: Check alert log file.

MRP0: Background Managed Standby Recovery process started (testdb)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/arc/testdb/stdby/1_74_973537980.arc
Media Recovery Log /u01/arc/testdb/stdby/1_75_973537980.arc
Media Recovery Log /u01/arc/testdb/stdby/1_76_973537980.arc
Error opening /u01/arc/testdb/stdby/1_76_973537980.arc
Attempting refetch
Media Recovery Waiting for thread 1 sequence 76
Fetching gap sequence in thread 1, gap sequence 76-76
Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session  
Mon Apr 23 22:25:48 2018
RFS[5]: Assigned to RFS process 4299
RFS[5]: Allowing overwrite of partial archivelog for thread 1 sequence 76
RFS[5]: Opened log for thread 1 sequence 76 dbid -1538101191 branch 973537980
Archived Log entry 57 added for thread 1 sequence 76 rlc 973537980 ID 0xa4536939 dest 2:
Mon Apr 23 22:25:57 2018
Media Recovery Log /u01/arc/testdb/stdby/1_76_973537980.arc
Media Recovery Log /u01/arc/testdb/stdby/1_77_973537980.arc
Error opening /u01/arc/testdb/stdby/1_77_973537980.arc
Attempting refetch
Media Recovery Waiting for thread 1 sequence 77
Fetching gap sequence in thread 1, gap sequence 77-77
Mon Apr 23 22:25:57 2018
RFS[6]: Assigned to RFS process 4301
RFS[6]: Allowing overwrite of partial archivelog for thread 1 sequence 77
RFS[6]: Opened log for thread 1 sequence 77 dbid -1538101191 branch 973537980
Archived Log entry 58 added for thread 1 sequence 77 rlc 973537980 ID 0xa4536939 dest 2:
Mon Apr 23 22:26:07 2018
Media Recovery Log /u01/arc/testdb/stdby/1_77_973537980.arc
Media Recovery Log /u01/arc/testdb/stdby/1_78_973537980.arc
Media Recovery Log /u01/arc/testdb/stdby/1_79_973537980.arc
Media Recovery Log /u01/arc/testdb/stdby/1_80_973537980.arc

We can see here Error of deleted file: Error opening /u01/arc/testdb/stdby/1_76_973537980.arc and then it is fetched by FAL server – Fetching gap sequence in thread 1, gap sequence 76-76. So it is automatically detected and resolved by FAL.

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:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

I am 11g OCP and OCE(RAC and GRID Infrastructure). Currently working as Oracle Database Administrator at _VOIS and Blogger (Freelancer) at Oracle-help . Previously I worked with Acute Informatics pvt ltd. and Mobile Tornado where I handled Banking Data Migration , created DR using log shipping , Golden Gate and Data Guard. I have experience of handling planned and unplanned DB role switchover and failovers and monitoring and troubleshooting Database Performance. Key skills : RMAN , GG , DG , RAC , Virtualization in OVMM.I have experience in Banking and Telecom domain. Reach me at : https://www.linkedin.com/in/jagruti-jasleniya

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.