About Oracle Data Guard Log Transport Services

on October 1, 2013

Log transport services are at the heart of Oracle Data Guard. The basis of Oracle Data Guard is the fact that we are effectively able to ship changes generated on the primary database to a secondary site. Once those changes exist on the secondary site, we are protected from the dreaded disaster. But log transport services does more than simply ship redo from one site to another site. It also lets us determine the level of data protection for our database. Log transport services configuration can be performed to balance data protection and availability against any performance impact. We will examine how to configure log transport services and discuss items that should be considered when designing your disaster recovery solution.

Defining log transport services destinations

We define where and how log transport services will transmit redo using the log_archive_dest_n initialization parameter. Using this parameter in Oracle 11g, we can define up to 30 destinations, each of which can send redo to distinct destinations. When defining the destination for redo data, we use the LOCATION attribute to store the redo locally on disk and the SERVICE attribute to enable shipment to a remote location via Oracle Net.

The following example will ship redo via Oracle Net to the specified netservice name using the default attributes:

   SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=cosp' scope=both sid='*';

Once the parameter is set, log transport services will redo data to the specified Oracle netservice name. If we want to disable the shipment of redo data to this destination, we would simply modify the log_archive_dest_state_n parameter from enabled to defer as follows:

   SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=defer scope=both sid='*';

Although the parameter in even its simplest form is very effective, numerous attributes allow fine-grained control over how and when data is sent. Because covering all attributes is not practical for this chapter, we will describe some of the more frequently used attributes.

NOTE

If you are familiar with log transport attributes available prior to Oracle 11g, you will notice that we are not covering the ARC or LGWR attributes. These attributes were deprecated in favor of always using the more efficient LNS process to perform redo shipment.

ASYNC (Default) and SYNC attributes

When the default mode of ASYNC is used, the log network server (LNS) process does not wait for each network I/O to complete before proceeding to minimize the overhead of Oracle Data Guard on the primary database. To ensure minimal overhead of Oracle Data Guard when operating in ASYNC mode, LNS will always attempt to read redo from the log buffer to avoid disk I/O; should the log buffer have been flushed, LNS will then read from online redo and subsequently archived redo. Obviously, in this case it is more efficient for LNS to read from the log buffer (instead of performing physical I/O), so we should ensure that LNS is reading from the log buffer as often as possible. To check how often LNS is reading from the log buffer, we can query x$logbuf_readhist.

The SYNC attribute specifies that network I/O is to be performed synchronously for the destination, which means that once the I/O is initiated, the archiving process waits for the I/O to complete before continuing. That being said, a transaction is not committed on the primary database until the redo data necessary to recover that transaction is received by all destinations in which the SYNC attribute has been specified. With Oracle Database 11g Release 2, the local write and remote write(s) will occur at the same time to minimize the performance impact of SYNC on the primary database. The SYNC attribute is not available with log_archive_dest_11 through log_archive_dest_31.

AFFIRM attribute

The AFFIRM attribute is valid only when using ASYNC log transport in 11g Release 2 (defaulted when using SYNC). When configured, this setting ensures that all disk I/O at the standby site are performed synchronously and that they have completed successfully prior to returning control to the user on the primary.

NET_TIMEOUT attribute

During normal Oracle Data Guard operations, the LNS process establishes an Oracle Net service connection to the standby. If for some reason the network between the primary and standby hosts is lost, the network connection will go through normal TCP processing before determining that the connection no longer exists. Depending on the log transport configuration, this can negatively impact processing on the primary database. Oracle developed the NET_TIMEOUT attribute so that the user could specify the amount of time in seconds for Oracle Net services to respond to a LGWR request. The NET_TIMEOUT attribute has a default value of 30 seconds and is applicable only when the SYNC attribute has been specified. Oracle recommends that this value not be set lower than 10 seconds to avoid false disconnects.

REOPEN attribute

If an archive destination receives an error, the destination will close and will not be retried until the period of time (in seconds) as specified by the REOPEN attribute. Once REOPEN has expired, the destination is once again valid and will be attempted at the next log switch. This attribute has a default value of 300 seconds.

MAX_FAILURE attribute

The MAX_FAILURE attribute defines the number of times we will reopen a destination that has been closed due to a failure.

COMPRESSION attribute

Log transport compression is a feature within the Advanced Compression database option that allows for compression of redo data prior to transporting that redo data to the specified destination (not only in Fetch Archive Log [FAL] as in previous releases). With 11g Release 2, compression is available for use when shipping redo in both ASYNC and SYNC modes. This is definitely a feature worthy of consideration if you have enough CPU capacity to perform the compression but are tight on network bandwidth between the primary and standby databases. That being said, if you do have sufficient bandwidth between the primary and standby databases, it is recommended that you not incur the overhead of compression, because the increased need for CPU resources may result in unnecessary latency in redo transport and/or apply.

VALID_FOR attribute

Both the primary and standby initialization parameters should be configured to support either the primary or standby role so that role reversals via switchover are seamless. To prevent the DBA from having to enable or defer archive destination depending on when that destination should be used, Oracle developed the VALID_FOR attribute. The VALID_FOR attribute is used to specify exactly when an archive destination is to be used and what types of redo logs are to be archived.

The VALID_FOR attribute comprises two keywords: archival source and database role. The archival source keywords are as follows:

  • ONLINE_LOGFILE Archive online redo logs only for this destination.
  • STANDBY_LOGFILE Archive standby redo logs only for this destination.
  • ALL_LOGFILES Archive both online and standby redo logs for this destination.

The database role keywords are as follows:

  • PRIMARY_ROLE Archive to this destination only when in the primary role.
  • STANDBY_ROLE Archive to this destination only when in the standby role.
  • ALL_ROLES Archive to this destination when in either primary or standby role.

Let’s consider the following example from a physical standby to see how the VALID_FOR attribute works. First, the PITT database:

   LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
       VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PITT' scope=both
   sid='*'
   LOG_ARCHIVE_DEST_2='SERVICE=cosp
       VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
       DB_UNIQUE_NAME=COSP' scope=both sid='*';

Then the COSP database:

   LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
        VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=COSP' scope=both
   sid='*'
   LOG_ARCHIVE_DEST_2='service=pitt
        VALID_FOR=( ONLINE_LOGFILES,PRIMARY_ROLE)
        DB_UNIQUE_NAME=PITT' scope=both sid='*';

In this example, when the PITT database is in the primary role, we will archive the online redo logs only to destination 1, because the standby redo logs are not active in the primary role. We will also archive the online redo logs to destination 2 while we are in the primary role. In the COSP database, we will archive the standby redo logs to destination 1 because the online redo logs are not active in the standby role. We will not archive to destination number 2 because the COSP database is not in the primary role. When a role reversal occurs, no change to the parameters is necessary to achieve the desired effect. The VALID_FOR attribute makes enabling or deferring destinations during role reversals unnecessary.

Related Posts

Leave a Reply