Friday , December 2 2022

Optimize Data Guard Configuration

After installing Data Guard, we may need to make some adjustments for it to work effectively.

For example,

Let’s assume that we have a network bottleneck, we can solve this problem with Redo Compression when we cannot overcome this bottleneck with the SDU, Send Buffer Size and Redo Buffer Size parameters we entered in Listener and Tns.

Or, if we have a problem accessing the location where we send the Redos, we would like to determine when the redo transmission will be triggered again.

Or, let’s assume that the number of APPLIER or PREPARER processes is not sufficient in the Logical Standby Database. In this case, we need to increase them.

Here are the optimizations that can be made in this and similar cases.

A.REOPENSECS

It determines how many seconds after the ARCHIVER process will try to send redo’s to that location in failed Redo sends.

In Default for the broker, this value is 300 seconds.

It corresponds to the REOPEN attribute in the LOG_ARCHIVE_DEST_n parameter.

The parameter is changed as follows.

1. We query the current value of the parameter.

2. We change the parameter value.

3. We query the current value of the parameter.

B.NETTIMEOUT

It is the second value that LGWR will wait for the commit on the Primary side so that a transaction coming to the Primary Database is written to the Standby Redo Logs on the Standby side.

The broker’s default value is 30 seconds.

Corresponds to the NET_TIMEOUNT attribute in the LOG_ARCHIVE_DEST_n parameter.

The parameter is changed as follows.

1. We query the current value of the parameter.

2. We change the parameter value.

3. We query the current value of the parameter.

C.MAXCONNECTIONS

It determines how many archiver processes will send a single archive redo log file to the Standby side in parallel at the GAP moment that will occur on the standby side.

Since Redo Transfer Rate will increase with this parameter, Redos are sent to the Standby side faster.

Default value is 1 and Maximum value is 20.

Corresponds to the MAX_CONNECTIONS attribute in the LOG_ARCHIVE_DEST_n parameter.

The value given in the LOG_ARCHIVE_MAX_PROCESSES initial parameter must be greater than and equal to the value given in MAX_CONNECTIONS. Otherwise, Data Guard chooses the most appropriate number of processes.

We change the parameter as follows.

1. We query the current value of the parameter.

2. The value of the parameter is changed.

a. First we query the value of log_archive_max_processes.

b. The value of the parameter is set so that it does not exceed this value.

3. We query the current value of the parameter.

D.REDOCOMPRESSION

For SYNC or ASYNC Redo Transport Services, it specifies that Redo is compressed to the Standby side.

Default value is DISABLE because it requires Advance Compression License.

Corresponds to the COMPRESSION attribute in the LOG_ARCHIVE_DEST_n parameter.

The parameter is changed as follows.

1. We query the current value of the parameter.

2. We change the value of the parameter.

3. The current value of the parameter is queried.

E.DELAYMINS

Due to user or application reasons, it may be requested to apply the data to the Standby side after a certain delay.

Zero data loss is guaranteed even if the DELAY parameter is used, as the Redos are sent SYNC to the Standby side in Maximum Protection and Maximum Availability modes.

Default value is 0.

Corresponds to the DELAY attribute in the LOG_ARCHIVE_DEST_n parameter.

We change the parameter as follows.

1. The current values of DELAY, PROTECTION_MODE, and RECOVERY_MODE parameters are queried.

2. It is seen that the DML made to the Primary with the current values of the parameters is applied instantly on the Standby side.

3. While applying Real-Time Redos, the DELAY attribute is set to a certain value.

4. A table is DROPed in Primary and it is seen whether it goes from Standby instantly.

a. Existing Archive Log Sequence numbers are queried.

b. The table is DROP.

c. It is checked whether the table has been deleted or not.

It appears that the table is not deleted. The reason is that when changing the value of the parameter from the broker, it disables Real-Time Redo Apply and works with Archived Apply. Therefore, it will be applied as the archive is created.

d. An archive is created.

[Standby-1]—–ALERT_LOG

e. It is checked whether the created archive is APPLY on the standby side.

f. After a certain time, we check whether the table has been dropped or not.

g. A few more Log Switch operations are done.

h. When it is seen that archive number 185, which still contains the DROP operation, is not processed, it is followed from the logs and after a certain period of time, it is seen that the apply operation is performed.

[Standby-1]—–ALERT_LOG
5. The property is set back to its old value.
[Standby-1]—–>ALERT_LOG

F. Optimizing SQL APPLY

SQL APPLY operation in Logical Standby Database is performed by a series of processes. In busy transactional systems, the number of these processes should be set correctly.

If no adjustment is made in Default, SQL APPLY processes running in Logical Standby Database are as follows.

Whichever Apply Instance is in the RAC structure, the following query should be run on that instance.

In addition, all the details about the Logical Standby Database can be seen in the figure below.

Whichever Apply Instance is in the RAC structure, the following query should be run on that instance.

The following algorithm should not be ignored when the number of processes will be changed.

APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3

For example, as can be seen in the v$logstdby_stats view above, we have 1 Preparer and 5 Applier processes. Our Max Process number is 9, so it fits the formula.

The DBMS_LOGSTDBY.APPLY_SET package is used when making changes.
When you want to change the number of APPLIER process: APPLY_SERVERS,

When you want to change the number of PREPARER process: PREPARE_SERVERS,

when you want to change the total number of processes in SQL APPLY process: MAX_SERVERS attribute is changed.

1. Changing APPLIER Process Number

a. It is questioned whether the existing APPLIER Processes are running.

b. If there are no IDLE processes, it is checked whether there are enough jobs to increase the number of APPLIER processes.

This query did not return any results for us, but in a busy transactional system, 2 values are returned as a result of this query.

Transactions Mined: Number of transactions ready to be implemented by APPLIER processes.

Transactions Applied: The number of transactions that have been applied.

If the difference between these values is more than 2 times the number of APPLIER processes, then it will be beneficial to increase the number of APPLIER processes.

While increasing, it should be increased according to the formula below.

2. Changing the Number of PREPARER Processes

a. Ensure that all PREPARER processes are running.

b. The number of transactions ready to be implemented (Transactions Mined) must be less than the number of APPLIER processes.

This query did not return any results for us, but in a busy transactional system, 2 values are returned as a result of this query.

Transactions Mined: Number of transactions ready to be implemented by APPLIER processes.

Transactions Applied: The number of transactions that have been applied.

c. If these conditions are met, the number of PREPARER processes is increased by considering the following formula.

d. We provide the controls.

G. Optimizing LCR Cache Size

Redo records read by the Reader Process are converted to Logical Change Records (LCRs) by the Preparer Process and kept in the LCR Cache area within the SGA area. Therefore, the number of this field should be set correctly in a system with intensive transaction processing.

In Default, this value is 30 MB.

If LCR Cache is not enough for you, this information is stored in SPILL tables in SYSAUX, which is very costly.

We can query whether our system uses the tables in SYSAUX as follows.

0 means that SYSAUX is not used and LCR Cache Size is sufficient.

What percentage of LCR Cache Size is used can also be found in the figure below.

If we see the PCT_UTILIZATION value here as 100% or very high, then we understand that the LCR Cache Size is not enough and should be increased.

LCR Cache Size is increased if desired.

Status of Processes in Logical Standby as a result of IMPORT operation

After 74 tables are imported to the Primary, the situation in the Logical Standby Database is as follows.

1. The sequence numbers of the archives are checked in the Primary and Standby Databases.

As can be seen from here, APPLY work of approximately 67 archives is still in progress.

2. We check the status of SQL APPLY Processes running in Logical Standby Database.

3. Check if there are any APPLIER processes in IDLE.

4. How much of the LCR Size is used is checked.

5. Let’s see if there are any of the PREPARER Processes in IDLE.

In the light of all this information, the following conclusion is reached.

The number of APPLIER processes is sufficient.

The number of preparer processes is not sufficient.

LCR Cache Size is not enough.

 

Onur ARDAHANLI
Author: Onur ARDAHANLI

About Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *