Data Guard Redo Log Tranport/copy Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Log Transport Service Transmission Attributes

In addition to the attributes specific to log management policies, the log_archive_dest_n parameter has other characteristics that influence the data protection mode. In fact, the text in this section builds the foundation of standby database protection modes, which will be covered in the next section.  A COPY

You can write manual scripts to detect when your source database has comleted writing a redo log and then invoke a copy toll tool (ftp, rsh, ssh) to transfer the file to the standby server.  for working examples, see the book Oracle Shell Scripting.

This section covers information on the transmission and reception of redo records from the primary to the Oracle instance and attributes of the log_archive_dest_n parameter that controls the data protection modes. The following can be specified using log_archive_dest_n parameter:

• Process to transfer redo data.

• Network characteristics to be used for redo transmission.

• Acknowledging disk I/O operations on the Oracle instance during redo data transfer.

The following sections include information on each of these characteristics in detail.

Process to Transfer Redo Data in Data Guard

Log writer (LGWR) or archiver (ARCH) can be configured on the primary database site to transfer the redo data to the standby database. When LGWR is specified as the process responsible for transferring redo records, the Oracle instance receives redo data as it is being generated on the primary database. In case of ARCH, the archived redo logs are transferred when a log switch occurs on the primary database.

For a no-data-loss environment, the LGWR should be used to transfer redo to the standby site even though the default process for the log transfer service is ARCH. The following example shows sample settings for LGWR and ARCH in init.ora file:

LOG_ARCHIVE_DEST_3='SERVICE=appsstdby1 LGWR'
LOG_ARCHIVE_DEST_4='SERVICE=appsdtby2 ARCH'

It is possible to switch between ARCH and LGWR for the log transfer service using the ALTER SYSTEM statement. The change will not take effect until a log switch occurs on the primary database, so for an immediate change, the current log file should be archived.

Network Characteristics Influencing Data Guard Redo Transmission

In addition to the process for the log transfer service, the network attribute for the data transfer over Oracle Net layer can be specified. This option is only available when log writer is used to transfer redo to the standby site. The DBA can choose between the SYNC and ASYNC attributes of the log_archive_dest_n parameter.

SYNC is the synchronized transfers of redo data from the primary to the standby site. When synchronous transfer is in use, the LGWR does not write any redo entry in the online log file of the primary site until the initiated log transfer has passed the network layer successfully. The LGWR will not wait for acknowledgment receipts of the disk I/O operation of redo data on the standby site.

If more than one standby site is in use and LGWR is serving all of these for log transfer, a synchronous transfer mode will cause the LGWR to wait until all of the archival destinations receive data over the network layer. This may cause a severe performance issue on the primary database.

To ease the performance problem, Oracle has provided the option of setting the PARALLEL or NOPARALLEL synchronous data transfer to multiple standby sites. The recommended option is to use the PARALLEL transfer mode, which will initiate synchronous but parallel data transfer. 

For example, if there are three standby sites and LGWR is sending redo data to these three standby sites in SYNC mode, setting the SYNC=PARALLEL will start three parallel slave processes to these three standby sites. LGWR on the primary site will resume writing to the online log file when the last parallel slave completes the log transfer. NOPARALLEL, as the name suggests, will initiate serial transfer of redo data to multiple standby databases.

ASYNC mode provides better performance of the primary database at an expense of small memory within the primary database. The ASYNC attribute is specified with a memory size in the System Global Area of the primary database that is used to buffer the redo data before sending to the standby site. Log writer sends the buffered redo to the standby site in one of the following events:

• A log switch occurs on primary database.
   

• Buffer to keep redo data is full and LGWR needs to clear it to write new redo data.
   

• The primary or Oracle instance is shutdown normally.

In this mode, the log writer does not wait for the network I/O operation to complete before generating new redo on the primary site. This buffer is written at the same time as the log buffer of the primary database and not the online redo log files. In case of abnormal shutdown, data from this buffer as well as the log buffer will be discarded and a no-data-loss environment will be maintained. The value of ASYNC attribute is specified in terms of db blocks. The default value is 2048 blocks and the maximum can be 20480 blocks. The default setting for network I/O attribute of the log_archive_dest_n parameter is SYNC.

The following examples show settings of SYNC and ASYNC:

LOG_ARCHIVE_DEST_3='SERVICE=appsstdby1 LGWR SYNC=PARALLEL'
LOG_ARCHIVE_DEST_4='SERVICE=appsstdby2 LGWR ASYNC=4096'

TIP - For a transaction intensive database, ASYNC is recommended over SYNC

Acknowledging Redo Disk I/O Operation on the Standby Database

A log transfer service can be built that waits for redo data to be written completely and successfully on the standby site before it will allow the primary database to make any further modifications. This guarantees that the data on the primary database is available on the standby site. The AFFIRM attribute of the log_archive_dest_n parameter can be used with LGWR for this purpose, but it will affect the performance of the primary database. This attribute is compliant with both LGWR and ARCH process for log transfer.