How to Optimize a Data Guard Configuration

In this post, we will see how to monitor the configuration performance of data guard along with optimization of SQL apply and redo transport for best performances.

Monitoring Configuration Performance by Using Enterprise Manager Cloud Control

Graphical charts on the Performance Overview page:

• Redo Generation Rate: Shows the redo generation rate (in KB per second) on the primary database.
• Apply Rate: Shows the apply rate (in KB per second) on the standby database. The “Apply Rate When Active” statistic indicates the actual apply rate averaged over the last three log files.
• Lag Time: Shows the transport lag and apply lag. Transport lag is the approximate amount of redo (in seconds) that is not yet available on the standby database. Apply lag is the approximate number of seconds by which the standby database is behind the primary database

On the Performance Overview page, you can invoke a test application to generate a workload on the primary database. This provides a way to view performance metrics when the primary database is operating under a load.

Optimizing Redo Transport Services

Optimize redo transport with the following techniques:

• Optimizing asynchronous redo transmission by using multiple archiver processes
• Compressing redo data

Information about these techniques is provided in the following sections.

Setting the ReopenSecs Database Property

When you specify a value for the ReopenSecs database property, it is propagated to the REOPEN attribute of the LOG_ARCHIVE_DEST_n initialization parameter of the sending instance. The sending instance can be a primary database instance or a Far Sync instance.

The REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter specifies the minimum number of seconds before the process that is shipping the redo should try again to access a previously failed destination.

REOPEN applies to all errors and not only connection failures. These errors include (but are not limited to) network failures, disk errors, and quota exceptions.

Summary

• This property specifies the minimum number of seconds before the archiver process tries to access a previously failed destination.
• Broker default: 300
• Set for the standby database or Far Sync instance.
• The setting is propagated to the REOPEN attribute of the LOG_ARCHIVE_DEST_n initialization parameter of the
  sending instance.

DGMGRL> EDIT DATABASE 'london' SET PROPERTY 'ReopenSecs'=600;

Setting the NetTimeout Database Property

When you specify a value for the NetTimeout database property, it is propagated to the NET_TIMEOUT attribute of the LOG_ARCHIVE_DEST_n initialization parameter for your primary database or Far Sync. The NET_TIMEOUT attribute enables you to bypass the default network timeout interval that was established for the system on which the primary database resides. Without the NET_TIMEOUT attribute, the primary database can potentially stall for the default network timeout period. By specifying a smaller, nonzero value for NET_TIMEOUT, you can enable the primary database to mark a destination as “failed” after the user-specified timeout interval expires.

Summary

• This property specifies the number of seconds that the log writer process (LGWR) waits for Oracle Net Services to respond to a request.
• Broker default: 30
• The setting is propagated to the NET_TIMEOUT attribute of the LOG_ARCHIVE_DEST_n initialization parameter of the sending instance.

DGMGRL> EDIT DATABASE 'london' SET PROPERTY 'NetTimeout'=20;

Optimizing Redo Transmission by Setting MaxConnections

The redo transport mechanism uses all available bandwidth by allowing a single large redo log file to be transferred in parallel by multiple archiver processes. This behavior is controlled by the MaxConnections database property.

This architecture increases the redo transfer rate and enables faster redo transmission to standby databases for bulk batch updates on the primary database. As a result of the improvement in transfer rates, there is an increased availability of data at the standby database site.

Setting the MaxConnections Database Property

When you specify a value for the MaxConnections database property, it is propagated to the MAX_CONNECTIONS attribute of the LOG_ARCHIVE_DEST_n initialization parameter for your primary database or Far Sync instance. The MAX_CONNECTIONS attribute of LOG_ARCHIVE_DEST_n is used to set the number of parallel connections that are used for transmitting archived redo log files to a remote destination. The MAX_CONNECTIONS attribute defaults to 1, indicating that a single connection is established for the communication and transfer of data. The maximum value for MAX_CONNECTIONS is 20.

DGMGRL> EDIT DATABASE 'london' SET PROPERTY 'MaxConnections'= 15;

Compressing Redo Data by Setting the RedoCompression Property

When the communication network to remote databases is a high-latency, low-bandwidth WAN link and the redo data that is transferred to standby databases is substantial, you want to make the most effective use of network bandwidth. Redo transport compression can be enabled on any remote destination for all redo transport methods to reduce network bandwidth usage.

Redo compression can be enabled or disabled by setting the Oracle Data Guard broker’s RedoCompression property. The setting is propagated to the COMPRESSION attribute of the LOG_ARCHIVE_DEST_n initialization parameter. Redo compression is disabled by default.

When you add a database to the Data Guard configuration, the Data Guard broker automatically detects whether network compression is enabled or disabled for the standby database being added. The property is set accordingly.

Summary

– This can be enabled for all redo transport methods (ASYNC and SYNC).
– The setting is propagated to the COMPRESSION attribute of the LOG ARCHIVE DEST _ __n initialization parameter.
– Determine whether redo compression is enabled by querying the COMPRESSION column of the V$ARCHIVE_DEST view.
– Enable by using DGMGRL:

DGMGRL> EDIT DATABASE 'london' SET PROPERTY 'RedoCompression'='ENABLE';

– Enable by using SQL:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = 'SERVICE=london SYNC COMPRESSION=ENABLE';

Delaying the Application of Redo

You can delay the application of changes to standby databases, thereby providing protection from user errors and corruptions. You can protect against the application of corrupted or erroneous data to the standby database. The apply process also revalidates the log records to prevent application of log corruptions.

For example, if a critical table is accidentally dropped from the primary database, you can prevent this action from affecting the standby database by delaying the application of the change in the standby database.

When operating in maximum protection or maximum availability mode, Data Guard ensures zero data loss even with the delayed apply in effect. If you define a delay for a destination that has real-time apply enabled, the delay is ignored.

Setting the DelayMins Database Property to Delay the Application of Redo

Use the DelayMins configurable database property to specify the number of minutes that log apply services must wait before applying redo data to the standby database. This setting is propagated to the DELAY attribute of the LOG_ARCHIVE_DEST_n initialization parameter.

DGMGRL> EDIT DATABASE 'london' SET PROPERTY 'DelayMins'=5;

Broker default: 0 (meaning that apply services applies redo data as soon as possible)

Using Enterprise Manager to Delay the Application of Redo

1. On the Data Guard page, select your standby database and click Edit.
2. On the Edit Standby Database Properties page, click Standby Role Properties.
3. In the Apply Delay field enter the delay value (in minutes)
4. Click Apply.

Optimizing SQL Apply

The MAX_SERVERS, APPLY_SERVERS, and PREPARE_SERVERS parameters can be modified to control the number of processes that are allocated to SQL Apply. Because SQL Apply allocates one process for the READER, BUILDER, and ANALYZER roles, the following relationship between the three parameters is required:

APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3

Here,

• APPLY_SERVERS: The number of APPLIER processes that are used to apply changes
• MAX_SERVERS: The number of processes that SQL Apply uses to read and apply redo
• PREPARE_SERVERS: The number of PREPARER processes that are used to prepare changes

Use the DBMS_LOGSTDBY.APPLY_SET procedure to change the APPLY_SERVERS, MAX_SERVERS, and PREPARE_SERVERS parameters. Query DBA_LOGSTDBY_PARAMETERS to view the SQL Apply parameter settings.

Adjusting the Number of APPLIER Processes

Before changing the number of APPLIER processes, perform the following steps to determine whether adjusting the number of APPLIER processes will help you achieve greater throughput:

1. Determine whether APPLIER processes are busy by issuing the following query:

SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'APPLIER' and status_code = 16166;

2. After ensuring that there are no idle APPLIER processes, determine whether there is enough work available for additional APPLIER processes by issuing the following query:

SELECT NAME, $ VALUE FROM V$LOGSTDBY STATS
WHERE NAME LIKE 'transactions%';

The second query returns two statistics that provide a cumulative total: the number of transactions that are ready to be applied by the APPLIER processes and the number of transactions that have already been applied. If the difference between “transactions mined” and “transactions applied” is higher than twice the number of available APPLIER processes, an improvement in throughput is possible if you increase the number of APPLIER processes.

Before you increase the number of APPLIER processes, consider the following requirement:

APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3

Adjusting the Number of PREPARER Processes

On rare occasions, you may need to adjust the number of PREPARER processes. Before increasing the number of PREPARER processes, verify that the following conditions are true:

• All PREPARER processes are busy.
• The number of transactions ready to be applied is less than the number of available APPLIER processes.
• There are idle APPLIER processes.

Perform the following queries to verify the preceding conditions:

1. Determine whether all PREPARER processes are busy:

SELECT COUNT(*) A SELECT COUNT(*) AS IDLE_PREPARER
FROM V$LOGSTDBY_PROCESS
WHERE TYPE = 'PREPARER' and status_code = 16166;

2. Determine whether the number of transactions ready to be applied is less than the number of APPLIER processes:

SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE 'transactions%';
SELECT COUNT(*) AS APPLIER_COUNT
FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';

3. Determine whether there are idle APPLIER processes:

SELECT COUNT(*) AS IDLE_APPLIER
FROM V$LOGSTDBY PROCESS
WHERE TYPE = 'APPLIER' and status_code = 16166;

Before you increase the number of PREPARER processes, consider the requirement:

APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3

You may need to increase the value of MAX_SERVERS before increasing the value of PREPARE_SERVERS. Use the DBMS_LOGSTDBY.APPLY_SET procedure to increase the values of MAX_SERVERS and PREPARE_SERVERS, as shown in the following example:

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 26);
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 3);