Data Guard Redo Log Tranport/copy Tips
Oracle Database Tips by Donald BurlesonDecember 9, 2015
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:
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
Network Characteristics Influencing
Data Guard Redo
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
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
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
A log switch occurs on primary
Buffer to keep redo data is full
and LGWR needs to clear it to write new redo data.
The primary or Oracle instance
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
TIP - For a transaction intensive
database, ASYNC is recommended over SYNC
Acknowledging Redo Disk I/O Operation on the
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.