Log Transport Services

Log Transport Services enables and controls the automated transfer of redo data within a Data Guard configuration from the primary site to each of its standby sites.

Log transport services also controls the level of data protection for your database. The DBA will configure log transport services to balance data protection and availability against database performance. Log transport services will also coordinate with Log Apply Services and Role Transition Services for switchover and failover operations.

Maximum Protection Mode

Maximum Protection mode offers the ultimate in data protection. It guarantees no data loss will occur in the event the primary database fails. In order to provide this level of protection, the redo data needed to recover each transaction must be written to both the local (online) redo log and to a standby redo log on at least one standby database before the transaction can be committed. In order to guarantee no loss of data can occur, the primary database will shut down if a fault prevents it from writing its redo data to at least one remote standby redo log.

In a multiple-instance RAC database environment, Data Guard will shut down the primary database if it is unable to write the redo data to at least one properly configured database instance (see minimum requirements below).

In order to participate in Maximum Protection mode:

• At least one standby instance has to be configured with standby redo logs.
• When configuring the standby destination service in the LOG_ARCHIVE_DEST_n initialization parameter on the primary database, you must use the LGWR, SYNC, and AFFIRM attributes.

The minimum requirements are described in the following table:

Minimum Requirements for Maximum Protection Mode

Redo Archival Process	LGWR
Network Transmission Mode	SYNC
Disk Write Option	AFFIRM
Standby Redo Logs?	Yes
Standby Database Type	Physical Only

For example:

log_archive_dest_2='service=testdb_standby LGWR SYNC AFFIRM'

The SYNC attribute with the LGWR process specifies that network I/O is to be performed synchronously for the destination. This means that once the I/O (redo data) is initiated, the archiving process waits for the I/O to complete before continuing. When specifying the SYNC attribute, all network I/O operations are performed synchronously, in conjunction with each write operation to the online redo log. The transaction is not committed on the primary database until the redo data necessary to recover that transaction is received by the destination.

Maximum Availability Mode

Maximum Availability mode provides the highest level of data protection that is possible without affecting the availability of the primary database. This protection mode is very similar to Maximum Protection where a transaction will not commit until the redo data needed to recover that transaction is written to both the local (online) redo log and to at least one remote standby redo log. Unlike Maximum Protection mode; however, the primary database will not shut down if a fault prevents it from writing its redo data to a remote standby redo log. Instead, the primary database will operate in Maximum Performance mode until the fault is corrected and all log gaps have been resolved. After all log gaps have been resolved, the primary database automatically resumes operating in Maximum Availability mode.

Just like Maximum Protection mode, Maximum Availability requires:

• At least one standby instance has to be configured with standby redo logs.
• When configuring the standby destination service in the LOG_ARCHIVE_DEST_n initialization parameter on the primary database, you must use the LGWR, SYNC, and AFFIRM attributes.

The minimum requirements are described in the following table:

Minimum Requirements for Maximum Availability Mode

Redo Archival Process	LGWR
Network Transmission Mode	SYNC
Disk Write Option	AFFIRM
Standby Redo Logs?	Required for physical standby databases only. Standby redo logs are not supported for logical standby databases.
Standby Database Type	Physical or Logical

For example:

log_archive_dest_2='service=testdb_standby LGWR SYNC AFFIRM'

Maximum Performance Mode

Maximum Performance mode is the default protection mode and provides the highest level of data protection that is possible without affecting the performance or availability of the primary database. With this protection mode, a transaction is committed as soon as the redo data needed to recover the transaction is written to the local (online) redo log.

When configuring the standby destination service in the LOG_ARCHIVE_DEST_n initialization parameter on the primary database, log transport services can be set to use either LGWR / ASYNC or ARCH. In order to reduce the amount of data loss on the standby destination if the primary database were to fail, set the LGWR and ASYNC attribute. Using this configuration, the primary database writes its redo stream to the standby redo logs on the standby database asynchronously with respect to the commitment of the transactions that create the redo data. When the nodes are connected with sufficient bandwidth, this mode provides a level of data protection that approaches that of Maximum Availability mode with minimal impact on primary database performance.

Note that the use of standby redo logs while operating in Maximum Performance mode is only necessary when configuring log transport services to use LGWR. When log transport services is configured to use ARCH, standby redo logs are not required.

The following table describes the attributes that should be defined for the LOG_ARCHIVE_DEST_n initialization parameter for the standby database destination to participate in Maximum Performance mode:

Minimum Requirements for Maximum Performance Mode

Redo Archival Process	LGWR or ARCH
Network Transmission Mode	ASYNC when using LGWR process. Not applicable when using the ARCH process.
Disk Write Option	NOAFFIRM
Standby Redo Logs?	Required for physical standby databases using the LGWR process.
Standby Database Type	Physical or Logical

For example:

log_archive_dest_2='service=testdb_standby ARCH NOAFFIRM' or log_archive_dest_2='service=testdb_standby LGWR ASYNC NOAFFIRM'

LGWR / ASYNC vs. ARCH

When configuring log transport services to use LGWR and remotely archive in ASYNC mode, the LGWR process does not wait for each network I/O to complete before proceeding. This behavior is made possible by the use of an intermediate process, known as a Log Writer Network Server Process (LNS), which performs the actual network I/O and waits for each network I/O to complete. Each LNS has a user configurable buffer that is used to accept outbound redo data from the LGWR process. This is configured by specifying the size (in 512 byte blocks) on the ASYNC attribute in the archivelog parameter for the standby destination service. For example, ASYNC=2048 indicates a 1MB buffer. As long as the LNS process is able to empty this buffer faster than the LGWR can fill it, the LGWR process will never stall. If the LNS cannot keep up, then the buffer will become full and the LGWR process will stall until either sufficient buffer space is freed up by a successful network transmission or a timeout occurs.

When configuring log transport services to remotely archive using the ARCH attribute, redo logs are transmitted to the destination only during an archival operation. This means that the standby database does not receive any redo data until the primary database fills its current online redo and archives it. The data received and applied to the standby database is only as current as the last archived redo log sent from the primary database. The background archiver processes (ARCn) or a foreground archival process operation serves as the redo log transport service. Using ARCH to remotely archive redo data does not impact the primary database throughput as long as enough redo log groups exist so that the most recently used group can be archived before it must be reopened.

Setting the Data Protection Mode of a Data Guard Configuration

In this section, I will provide the steps necessary to change the protection mode of an existing physical Data Guard configuration. The protection mode will be changed from its default of Maximum Performance to Maximum Protection.

First though, let's review the available protection modes discussed in the previous sections. The protection modes run in the order from highest (most data protection) to the lowest (least data protection):

Each of the Data Guard data protection modes require that at least one standby database in the configuration meet the minimum set of requirements listed in the table below.

Minimum Requirements for Data Protection Modes

	Maximum Protection	Maximum Availability	Maximum Performance
Redo Archival Process	LGWR	LGWR	LGWR or ARCH
Network Transmission Mode	SYNC	SYNC	ASYNC when using LGWR process. Not applicable when using ARCH process.
Disk Write Option	AFFIRM	AFFIRM	NOAFFIRM
Standby Redo Logs Required?	Yes	Required for physical standby databases only. Standby redo logs are not supported for logical standby databases.	Required for physical standby databases using the LGWR process.
Database Type	Physical only	Physical and Logical	Physical and Logical

Take note of the following important points to understand when configuring the standby database:

• The standby database that is used to satisfy the minimum requirements for a given protection mode must be enabled and ready to receive redo data from the primary database before you can switch to that mode.

• When archiving to a physical standby destination using the LGWR process, changes (transactions) being made and committed on the primary database are not instantly written to the actual database files on the standby database. In Oracle9i Release 2 and higher, when log transport services is configured for Maximum Availability mode or Maximum Protection mode, the LGWR process on the primary database will send redo data to the standby redo logs (located on the standby database) at the same time it is writing redo data to the local (online) redo logs. Keep in mind that the LGWR process is actually communicating with a Remote File Server (RFS) process on the standby database server. This RFS process on the standby database is responsible for capturing and writing the redo data it obtains from the primary database to the standby redo logs (LGWR) or the standby archived redo logs (ARCn).

  The Remote File Server process runs on the standby database and can receive redo data over the network from both LGWR and ARCn. The RFS process will write the redo data it receives to either a standby redo log or to a standby archived redo log.

  When a log switch occurs on the primary database, a log switch is also triggered on the standby database where the ARCH process then archives the standby redo logs to the archive destination specified on the standby database. After the archival process has completed on the standby database, the Managed Recovery Process (MRP) then writes the changes to the actual database files from the archived redo log files.

  Why is this important to point out? It illustrates the fact that the actual changes (transactions) being made and committed on the primary database do indeed make it over to the standby database, but get applied to the standby redo logs. In Oracle9i, these changes are only made to the actual database files on the standby database when a log switch occurs on the primary. In Oracle Database 10g, a new feature called real-time apply can be enabled which tells log apply services to apply redo data to the database files on the standby database as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.

  To enable the real-time apply feature on the standby database, use the following ALTER DATABASE statement:

  [FOR PHYSICAL STANDBY DATABASES] SQL> alter database recover managed standby database using current logfile; [FOR LOGICAL STANDBY DATABASES] SQL> alter database start logical standby apply immediate;

  Standby redo log files are required to use real-time apply.

• When using standby redo logs, you must ensure the archiver process (ARCn) is enable on the standby database. Prior to Oracle Database 10g, it was required to set the log_archive_start initialization parameter to TRUE in order to enable automatic archiving. This is no longer necessary as automatic archiving is enabled by default when the database is placed into archivelog mode with Oracle Database 10g or higher.

Upgrading the Protection Mode

Use the following steps to upgrade the protection mode from the default of Maximum Performance.

1. Identify the current configuration:

   • Primary Database (TNS Service Name):    TESTDB_VMLINUX3.IDEVELOPMENT.INFO
   • Standby Database (TNS Service Name):    TESTDB_VMLINUX4.IDEVELOPMENT.INFO
   • Current Protection Mode:    Maximum Performance mode using ARCH

2. Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. If standby redo logs do not exist for the standby database, create them now.

   [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba [Verify if using oracle managed files] SQL> select value from v$parameter where name = 'db_create_online_log_dest_1'; VALUE ------------------- /u02/oradata/TESTDB [Do any standby redo logs exist on the standby database?] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE [Need to first cancel Managed Recovery mode if it is running] SQL> alter database recover managed standby database cancel; [Create appropriate number standby redo log files on the standby database] SQL> alter database add standby logfile group 4 size 100m; SQL> alter database add standby logfile group 5 size 100m; SQL> alter database add standby logfile group 6 size 100m; SQL> alter database add standby logfile group 7 size 100m; [Put standby database back into Managed Recovery mode] SQL> alter database recover managed standby database disconnect from session; [Verify new standby redo logs on standby database] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE 4 104857600 STANDBY 5 104857600 STANDBY 6 104857600 STANDBY 7 104857600 STANDBY

3. Ensure that the attributes for LOG_ARCHIVE_DEST_n are configured on the primary instance to support the desired protection mode. Also note that the destination standby database should be enabled to support the target protection mode as documented in this article.

   For the mode of Maximum Protection, the standby database must be up and mounted!

   Set the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to support the required protection mode:

   [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Configure log transport services to support the desired protection mode] SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info LGWR SYNC AFFIRM'; SQL> alter system set log_archive_dest_state_2=enable;

4. The primary database will need to be closed and then placed in the MOUNT stage.

   SQL> shutdown immediate SQL> startup mount

5. On the primary database, change the protection mode and open the database:

   For Maximum Protection mode:

   SQL> alter database set standby database to maximize protection; SQL> alter database open;

   For Maximum Availability mode:

   SQL> alter database set standby database to maximize availability; SQL> alter database open;

6. Query the data dictionary on the primary database to verify the new protection mode:

   SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE; PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE ------------------- ------------------- -------------- MAXIMUM PROTECTION MAXIMUM PROTECTION PRIMARY

7. (Optional) Although the steps in this section are optional, there are highly recommended so that the primary database can easily and quickly switchover to a standby role without the need for DBA intervention. For example, standby redo logs are only used on the physical standby database; however, creating and having standby redo logs ready to go on the physical primary database makes role transition much easier if the primary would every have to become the standby:

   [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Do any standby redo logs exist on the primary database?] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE [Create appropriate number standby redo log files on the primary database] SQL> alter database add standby logfile group 4 size 100m; SQL> alter database add standby logfile group 5 size 100m; SQL> alter database add standby logfile group 6 size 100m; SQL> alter database add standby logfile group 7 size 100m; [Verify new standby redo logs on primary database] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE 4 104857600 STANDBY 5 104857600 STANDBY 6 104857600 STANDBY 7 104857600 STANDBY

   Also, on the standby database(s), configure the LOG_ARCHIVE_DEST_n parameter attributes so the Data Guard configuration can continue to operate in the new protection mode after a switchover:

   [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba SQL> alter system set log_archive_dest_2='service=testdb_vmlinux3.idevelopment.info LGWR SYNC AFFIRM'; SQL> alter system set log_archive_dest_state_2=defer;

Downgrading the Protection Mode

Use the following steps to downgrade the protection mode from the current higher protection mode.

1. Identify the current configuration:

   • Primary Database (TNS Service Name):    TESTDB_VMLINUX3.IDEVELOPMENT.INFO
   • Standby Database (TNS Service Name):    TESTDB_VMLINUX4.IDEVELOPMENT.INFO
   • Current Protection Mode:    Maximum protection using LGWR SYNC AFFIRM

2. Configure the attributes for LOG_ARCHIVE_DEST_n on the primary instance to support the new desired protection mode:

   [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Configure log transport services to support the desired protection mode] SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info ARCH'; SQL> alter system set log_archive_dest_state_2=enable;

3. Shutdown and mount the primary and standby database:

   [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba SQL> shutdown immediate SQL> startup mount [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba SQL> alter database recover managed standby database cancel; SQL> shutdown immediate SQL> startup nomount SQL> alter database mount standby database;

4. Downgrade the primary database to the desired protection mode:

   [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [For Maximum Performance mode] SQL> alter database set standby database to maximize performance; [For Maximum Availability mode] SQL> alter database set standby database to maximize availability;

5. Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. It you were to downgrade from Maximum Protection mode to Maximum Availability mode, you would need to keep the standby redo logs that exist for the standby database. If downgrading to Maximum Performance mode, you can drop the standby redo logs from the standby unless you are going to be configuring log transport services to use LGWR. For this example, I am going to be downgrading to maximum performance mode and using ARCH for log transport services and can therefore drop any standby redo logs from the standby database (and the primary if they exist):

   [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba [Drop any standby redo logs from the standby database] SQL> alter database drop standby logfile group 4; SQL> alter database drop standby logfile group 5; SQL> alter database drop standby logfile group 6; SQL> alter database drop standby logfile group 7;

   If standby redo logs exist on the primary and you are downgrading to Maximum Performance mode, they can be removed:

   [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Drop any standby redo logs from the primary database] SQL> alter database drop standby logfile group 4; SQL> alter database drop standby logfile group 5; SQL> alter database drop standby logfile group 6; SQL> alter database drop standby logfile group 7;

6. Put the standby database in managed recovery mode and open the primary database with the new protection mode enabled:

   [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba SQL> alter database recover managed standby database disconnect from session; [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba SQL> alter database open;

7. Query the data dictionary on the primary database to verify the new protection mode:

   SQL> select protection_mode, protection_level, database_role from v$database; PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE -------------------- -------------------- -------------- MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

Further Reading

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: