DBA Tips Archive for Oracle
Log Gap Detection and Resolution
by Jeff Hunter, Sr. Database Administrator
Archive gaps are simply a range of archived redo logs that were created at a time when the standby database was not available to receive them as they were being generated by the primary database. These archive gaps occur most often during network unavailability between the primary and standby database. During network outages like this, the standby database fails to receive any redo data from the primary database. When the network failure has been resolved, automatic transmission of redo data from the primary to the standby database is resumed. The missing archived redo logs signify the gap.
In general, it is the primary database that first discovers there is a gap. Every one minute, the primary database polls all of its standby databases to determine if there is a gap in the sequence of archived redo logs. This polling between the primary and standby database is often referred to as the heartbeat. This heartbeat is performed by the primary database serially. The DBA can always determine if there is a gap in the archived redo logs by querying the V$ARCHIVE_GAP view (from the physical standby database) as described in the next section.
As mentioned in the previous section, the DBA can determine if there is a there is a gap in the archived redo logs by querying the V$ARCHIVE_GAP view from the physical standby database. Let's walk through a quick example of how to manually check and resolve a gap in the archived redo logs.
We start with the following assumptions:
With the above assumptions, perform a query of the V$ARCHIVE_GAP view from the physical standby database:
From the output of the above query, we can conclude that there are no gaps in the archived redo logs on the physical standby database!
Now, let's simulate a network failure by shutting down the Oracle TNS Listener process and physical standby database on vmlinux4.idevelopment.info:
At this point, the physical standby database is completely down and unavailable to the primary database. One of the first things to notice is a series of RFS errors in the alert.log for the primary database.
Errors in alert.log from the Primary Database
We can then further investigate the trace file (testdb_arc1_3495.trc) written to the alert.log above.
Errors in testdb_arc1_3495.trc from the Primary Database
Now let's perform a few log switches from the primary database:
Next, bring up the Oracle TNS Listener process and mount the physical standby database:
After a minute has passed, the primary database (the ARCH1 process) will wake up and attempt to ping the physical standby database. Once it is determined that the physical standby database is mounted (and the TNS Listener process is up), it will start to push any archived redo logs to the standby database. This can be see in the alert.log from the primary database:
RFS Process Recovery in alert.log from the Primary Database
Finally, let's query the V$ARCHIVE_GAP view from the physical standby database to determine if there are any gaps:
As we can see, there are no gaps in the archive redo logs being reported!
As mentioned throughout this article, we can see that Data Guard provides gap detection and resolution automatically by functionality provided in the primary database. The primary database polls its standby databases using a heartbeat every one minute. No extra configuration settings are required and no intervention is needed by the DBA to detect and resolve these gaps. In addition to this built-in functionality; however, it is also possible to configure log apply services to take part in automatically resolving archive gaps as they occur on the physical standby database side.
FAL_CLIENT and FAL_SERVER are initialization parameters used to configure log gap detection and resolution at the standby database side of a physical database configuration. This functionality is provided by log apply services and is used by the physical standby database to manage the detection and resolution of archived redo logs.
Specifies the TNS network services name for the standby database (which is sent to the FAL server process by log apply services) that should be used by the FAL server process to connect to the standby database. The syntax would be:
Specifies the TNS network service name that the standby database should use to connect to the FAL server process. The syntax would be:
Note that the value provided for FAL_SERVER is not limited to defining only one FAL server; it can define multiple FAL servers (possible a FAL server that is running on another standby database) that are separated by commas as in the following example:
FAL server is a background process that is generally located on the primary database server (however it can also be found on another standby database). FAL server is responsible for servicing incoming requests from the FAL client.
Additional information on Oracle Data Guard can be found in the Data Guard Concepts and Administration guide which is available from the Oracle Documentation Library:
Data Guard Concepts and Administration - (Oracle9i Release 2)
Data Guard Concepts and Administration - (Oracle10g Release 2)
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.
Copyright (c) 1998-2013 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Wednesday, 28-Dec-2011 14:00:47 EST
Page Count: 34606