Oracle Data Guard architecture is very streamlined and efficient; however, like any application, there are reasonable system and network prerequisites to achieve satisfactory performance. After all of the configuration best practices have been implemented as described in Oracle Data Guard Configuration Best Practices, consider the following recommendations.

Primary Database System

• Sufficient CPU utilization for LGWR to post foregrounds efficiently

• Sufficient I/O bandwidth so local log writes maintain low I/O latency during peak rates

• Network interfaces that can handle peak redo rate volumes combined with any other network activity across the same interface

• Primary AWR, ASH, and OSwatcher data gathered for tuning and troubleshooting

Standby Database System

• Sufficient CPU utilization for RFS (the Data Guard process that receives redo at the standby database) to efficiently write to standby redo logs

• Sufficient I/O bandwidth to enable local log writes to maintain low I/O latency during peak rates

• A network interface that can receive the peak redo rate volumes combined with any other network activity across the same interface

• Standby AWR, ASH, and OSwatcher data should be gathered

Network

• For synchronous redo transport, the round trip network latency (RTT) between the primary database and target standby database or target far sync standby server should typical less than 2 ms to minimize primary database performance impact.

  Oracle is often asked what the maximum latency is that can be supported or whether there is an equation that can be used to project performance impact. Unfortunately every application has a different tolerance to network latency. Differences in application concurrency, number of sessions, the transaction size in bytes, how often sessions commit, and log switch frequency can cause differences application performance impact.

• Sufficient network bandwidth to support peak redo rates (steady state and when resolving gaps) combined with any other network activity that shares the same network.

  Note that your point to point network bandwidth is throttled by the network segment, switch, router, or interface with the lowest network bandwidth. For example, if you have 10gigE for 90% of your network route and your existing switch or network interface only supports 1 GigE, then your maximum network bandwidth is 1 GigE.

• Netstat and/or any network monitoring statistics should be gathered

Monitor system resources on primary and standby hosts and the network using the utilities and statistics recommended here.

Monitoring CPU

• When all of a system's CPU cores are occupied running work for processes, other processes must wait until a CPU core or thread becomes available, or the scheduler switches a CPU to run their code. A bottleneck can be created if too many processes are queued too often.

  You can use the uptime, mpstat, sar, dstat, and top utilities to monitor CPU usage.

• The commands mpstat -P ALL and sar -u -P ALL display CPU usage statistics for each CPU core and an average across all CPU cores.

• The %idle value shows the percentage of time that a CPU is not running system code or process code. If the value of %idle is near 0% most of the time on all CPU cores, the system is CPU-bound for the workload that it is running. The percentage of time spent running system code (%systemor %sys) should not usually exceed 30%, especially if %idle is close to 0%.

• The system load average represents the number of processes that are running on CPU cores, waiting to run, or waiting for disk I/O activity to complete, averaged over a period of time. On a busy system, the load average reported by uptime or sar -q should not exceed two times the number of CPU cores. The system is overloaded if the load average exceeds four times the number of CPU cores for long periods.

• In addition to load averages (ldavg-*), the sar -q command reports the number of processes currently waiting to run (the run-queue size, runq-sz) and the total number of processes (plist_sz). The value of runq-sz also provides an indication of CPU saturation.

• Determine the system's average load under normal loads when users and applications do not experience problems with system responsiveness, and then look for deviations from this benchmark over time. A dramatic rise in the load average can indicate a serious performance problem.

Monitoring Memory Usage

• When a system runs out of real or physical memory and starts using swap space, its performance deteriorates dramatically. If you run out of swap space, your programs or the entire operating system are likely to crash.

  If free or top indicate that little swap space remains available, this is also an indication you are running low on memory.

• The sar -r command reports memory utilization statistics, including %memused, which is the percentage of physical memory in use.

• The sar -B command reports memory paging statistics, including pgscank/s, which is the number of memory pages scanned by the kswapd daemon per second, and pgscand/s, which is the number of memory pages scanned directly per second.

• The sar -W command reports swapping statistics, including pswpin/s and pswpout/s, which are the numbers of pages per second swapped in and out per second.

• The system has a memory shortage if %memused is near 100% and the scan rate is continuously over 200 pages per second.

• The output from the dmesg command might include notification of any problems with physical memory that were detected at boot time.

Monitoring I/O

• Use iostat, sar, and iotop utilities to monitor I/O.

• The iostat command monitors the loading of block I/O devices by observing the time that the devices are active relative to the average data transfer rates. You can use this information to adjust the system configuration to balance the I/O loading across disks and host adapters.

  iostat -x reports extended statistics about block I/O activity at one second intervals, including %util, which is the percentage of CPU time spent handling I/O requests to a device, and avgqu-sz, which is the average queue length of I/O requests that were issued to that device.

  If %util approaches 100% or avgqu-sz is greater than 1, device saturation is occurring and the storage I/O bandwidth needs to be augmented by adding disks or storage.

• You can also use the sar -d command to report on block I/O activity, including values for %util and avgqu-sz.

• The iotop utility can help you identify which processes are responsible for excessive disk I/O. The iotop user interface is similar to top.

  In its upper section, iotop displays the total disk input and output usage in bytes per second. In its lower section, iotop displays I/O information for each process, including disk input-output usage in bytes per second, the percentage of time spent swapping in pages from disk or waiting on I/O, and the command name.

  Use the left and right arrow keys to change the sort field, and press A to toggle the I/O units between bytes per second and total number of bytes, or O to toggle between displaying all processes or only those processes that are performing I/O.

Monitoring the Network

• Use ip, ss, and sar to monitor the network.

• The ip -s link command displays network statistics and errors for all network devices, including the numbers of bytes transmitted (TX) and received (RX). The dropped and overrun fields provide an indicator of network interface saturation.

• The ss -s command displays summary statistics for each protocol.

• Use the sar –n DEV command to monitor the current rate transmitted using an interface.

Once you verify that the system or network resources are not bottlenecked, you can assess database wait events for performance flags related to your Oracle Data Guard configuration.

On the primary database, assess database wait events with AWR reports, and on the standby database you can use standby AWR for Oracle Database 18c and later, or statspack reports for older Oracle Database releases.

Wait events specific to Oracle Data Guard with Oracle Database 11.2 are described in the table below.

See Installing and Using Standby Statspack (Doc ID 454848.1) for information about Standby Statspack.

The following topics describe how to assess redo transport, specifically for synchronous redo transport.

For standby databases on symmetric hardware and configuration, the apply lag should less than 10 seconds.

If you observe high redo transport lag, tune the network transport first and address any insufficient network bandwidth or any overhead resulting from redo encryption or compression.

To monitor apply lag on the standby database query the V$DATAGUARD_STATS view.

SQL> SELECT name,value,time_computed,datum_time FROM v$dataguard_stats WHERE name=’%lag’;

The DATUM_TIME column is the local time on the standby database when the datum used to compute the metric was received. The lag metrics are computed based on data that is periodically received from the primary database. An unchanging value in this column across multiple queries indicates that the standby database is not receiving data from the primary database. The potential data loss in this scenario would be from the last datum time from V$DATAGUARD_STATS to the current time on the standby.

To obtain a histogram that shows the history of apply lag values since the standby instance was last started, query the V$STANDBY_EVENT_HISTOGRAM view.

SQL> SELECT * FROM v$standby_event_histogram WHERE name like '%lag' and count >0;

To evaluate the apply lag over a period of time, take a snapshot of V$STANDBY_EVENT_HISTOGRAM at the beginning of the time period and compare that snapshot with one taken at the end of the time period.

SQL> col NAME format a10
SQL> SELECT NAME,TIME,UNIT,COUNT,LAST_TIME_UPDATED FROM V$STANDBY_EVENT_HISTOGRAM WHERE name like '%lag' and count >0 ORDER BY LAST_TIME_UPDATED;

NAME             TIME UNIT                  COUNT LAST_TIME_UPDATED
---------- ---------- ---------------- ---------- --------------------
apply lag          41 seconds                   3 04/05/2018 16:30:59
apply lag          44 seconds                   1 04/05/2018 16:31:02
apply lag          45 seconds                   2 04/05/2018 16:31:03
apply lag          46 seconds                   2 04/05/2018 16:31:04

In an Oracle Data Guard physical standby environment, it is important to determine if the standby database can recover redo as fast as, or faster than, the primary database can generate redo.

If the apply lag is above expectations, then evaluate redo apply performance by querying the V$RECOVERY_PROGRESS view. This view contains the columns described in the following table.

The most useful statistic is the Active Apply rate because the Average Apply Rate includes idle time spent waiting for redo to arrive making it less indicative of apply performance.

The simplest way to determine application throughput in terms of redo volume is to collect Automatic Workload Repository (AWR) reports on the primary database during normal and peak workloads, and determine the number of bytes per second of redo data the production database is producing. You can then compare the speed at which redo is being generated with the Active Apply Rate columns in the V$RECOVERY_PROGRESS view to determine if the standby database is able to maintain the pace.

Once you have verified that you are not bottlenecked on any system or network resources you are ready to assess database wait events. On the primary database this is done using AWR reports while on the standby database you will use standby AWR.

Before assessing database wait events, it is important that you understand the process flow involved in recovery. In general there are three distinct phases to standby recovery; the log read phase, the apply phase, and the checkpoint phase.

1. Redo is received on the standby by the RFS (Remote File Server) process.

   The RFS process writes newly received redo for each thread into the current standby redo log for that thread. The RFS write operation is tracked by the rfs random I/O wait event.

2. Once redo has been written the recovery coordinator process (pr00) will read the redo from the standby redo logs for each thread.

   This read I/O is tracked by the log file sequential read operation. The recovery coordinator then merges redo from all threads together and place the redo into memory buffers for the recovery slaves. The wait events for writing and reading into recovery memory buffers is tracked by the parallel recovery read buffer free and parallel recovery change buffer free wait events.

3. The recovery processes retrieve redo or change vectors from the memory buffers and begin the process applying the changes to data blocks.

   First the recovery slaves determine which data blocks need to be recovered and reads those into the buffer cache if it’s not already present. This read I/O by the recovery slaves is tracked by the recovery read wait event.

4. When a log is switched on the primary for any thread the standby will coordinate a switch of the standby redo log for that thread at the same time.

   A log switch on a standby will force a full checkpoint which will result in flushing all dirty buffers from the buffer cache out to the data files on the standby. A checkpoint on the standby is currently more expensive than on a primary. Multiple DB writer processes (DBWR) will write the data file blocks down to the data files with its write time tracked by the db file parallel write wait event. The total time for the checkpoint to complete is covered by the checkpoint complete wait event.

During the apply phase it is normal to observe that the recovery coordinator process (pr00) has high utilization on a single CPU, while during the checkpoint phase you normally see an increase in the write I/O to the data files.

The following table provides a description as well as tuning advice for wait events involved in the recovery process.

See How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1) for more information about generating AWRs on the standby database.

Under extremely heavy workloads, a single instance redo apply is not sufficient.

If there are insufficient CPU resources on the standby redo apply database server, or if the Recovery Coordinator process (pr00) is CPU bound (by checking OS utilities), then multi-instance redo apply will allow you to scale across Oracle RAC instances.

Multi-instance redo apply greatly improves the scalability of redo apply for Oracle RAC databases. Rather than merging all threads of redo into a single apply process, multiple apply instances divide the threads of redo between them. For example, when two apply nodes are used to apply four threads of redo from the primary, each apply instance will apply two threads.

When the workload is well balanced between all threads of the primary, the scalability of multi-instance redo apply is predictable (2x, 4x and so on). Unbalanced workloads, where one instance does more work than another, get mixed scaling results compared to single instance redo apply.

The following is an example which applies the redo apply tuning discussed in this document.

The first step to assess performance and determine steps to improve performance is to take standby statspack snaps that capture statistics on the standby database.

After generating a standby statspack report based off of those snaps the next step is to examine the load profile section.

Load Profile                   Total         Per Second
~~~~~~~~~~~~       ------------------  -----------------
      DB time(s):                9.2                0.0
       DB CPU(s):                0.7                0.0
 Redo MB applied:           14,497.8                9.7
   Logical reads:              471.0                0.3
  Physical reads:        6,869,213.0            4,570.3
 Physical writes:        9,722,929.0            6,469.0
      User calls:              510.0                0.3
          Parses:            1,192.0                0.8
     Hard parses:                3.0                0.0
W/A MB processed:               35.9                0.0
          Logons:               45.0                0.0
        Executes:            1,410.0                0.9
       Rollbacks:                0.0                0.0

From the load profile shown above, you can see the amount of redo recovered during the report period and the amount of redo recovered on a per second basis. In addition you can see the number of physical reads and writes being performed.

Compare the number of reads and writes to the baseline reports that showed acceptable performance. Increased reads could be coming from read-only queries that might not have been seen previously, while increased writes could indicate a change in the type of workload being recovered.

The top five wait events section is the most important section to review. This section details where the majority of the time is spent waiting, as shown below.

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
checkpoint completed                             4,261       8,210   1927   42.1
db file parallel write                          20,982       2,658    127   13.6
lreg timer                                         501       1,502   2998    7.7
free buffer waits                              119,108       1,278     11    6.5
parallel recovery read buffer free               8,046       1,101    137    5.6

In the output above the 42% call time is spent waiting for checkpoint completed, with the second wait event db file parallel write being associated with the checkpoint completed wait as it relates to DBWR performing writes from the buffer cache. The free buffer waits wait event indicates that the buffer cache is full and recovery slaves reading buffers into the buffer cache are stalled.

This wait event profile indicates that you should increase the number of DBWR processes to help increase the rate that buffers can be flushed from the buffer cache.

Before making any changes, consult the statspack report to make note of the recovery active apply rate and the time being spent in the apply phase versus the checkpoint phase. After you adjust the number of DBWR processes, compare the subsequent standby statspack reports to these numbers to see improvement.

Recovery Start Time Item                       Sofar Units   Redo Timestamp
------------------- ----------------- -------------- ------- ------------------
21-Oct-15 08:03:56  Log Files                      6 Files
21-Oct-15 08:03:56  Active Apply Rate         10,809 KB/sec
21-Oct-15 08:03:56  Average Apply Rat         10,708 KB/sec
21-Oct-15 08:03:56  Maximum Apply Rat         80,592 KB/sec
21-Oct-15 08:03:56  Redo Applied              15,111 Megabyt
21-Oct-15 08:03:56  Last Applied Redo              0 SCN+Tim 20-Oct-15 12:48:25
21-Oct-15 08:03:56  Active Time                1,408 Seconds
21-Oct-15 08:03:56  Apply Time per Lo            121 Seconds
21-Oct-15 08:03:56  Checkpoint Time p             14 Seconds
21-Oct-15 08:03:56  Elapsed Time               1,445 Seconds
21-Oct-15 08:03:56  Standby Apply Lag         70,785 Seconds

It’s important that you make only one change at a time, and obtain new standby statspack reports to assess any improvement based on that change. For each change follow the same methodology of assessing the physical reads and writes, the top wait events, and the time spent in the different recovery phases.

The Oracle Data Guard broker VALIDATE DATABASE command gathers information related to switchover and failover readiness.

The validation verifies that the standby and primary database are reachable and the apply lag is less than ApplyLagThreshold for the target database. If these data points are favorable, the command output displays Ready for Failover: Yes as shown below. In addition, if redo transport is running, the command output displays Ready for Switchover: Yes.

DGMGRL> validate database [verbose] database_name

Database Role: Physical standby database
 Primary Database: standby db_unique_name

Ready for Switchover: Yes
 Ready for Failover: Yes (Primary Running)

VALIDATE DATABASE checks additional information that can impact switchover time and database performance, such as whether the online redo logs have been cleared, number of temporary tablespaces, parameter mismatches between primary and standby, and the status of flashback databases.

In most failover cases the primary database has crashed or become unavailable. The Ready for Failover output indicates if the primary database is running when VALIDATE DATABASE was issued. This state does not prevent a failover, but it is recommended that you stop the primary database before issuing a failover to avoid a split-brain scenario where the configuration has two primary databases. The broker only guarantees split-brain avoidance on failover when Fast-Start Failover is used.

You can also run VALIDATE DATABASE periodically as a configuration monitoring tool.

Use the Oracle Data Guard broker SWITCHOVER command to initiate switchover, and the FAILOVER command to initiate failover.

As part of a switchover or failover operation the broker does the following.

• Configures redo transport from the new primary database
• Starts redo apply on the new standby database
• Ensures that other standby databases in the broker configuration are viable and receiving redo from the new primary
• Integrates Oracle Clusterware and Global Data Services to ensure the correct services are started after role change

To configure broker to initiate switchover, run

DGMGRL> SWITCHOVER TO database_name;

To configure broker to initiate failover, run

DGMGRL> FAILOVER TO database_name [IMMEDIATE];

By default FAILOVER applies all redo that was received before failing over. The IMMEDIATE clause skips the pending redo and fails over immediately.

The SWITCHOVER and FAILOVER commands are idempotent and can be re-issued in the unlikely event of a failed transition.

Implement the following best practices to optimize failover processing.

• Enable Flashback Database to reinstate the failed primary databases after a failover operation has completed. Flashback Database facilitates fast point-in-time recovery from failures caused by user error or logical corruption.

• Enable real-time apply, which allows apply services to apply the redo on the standby databases as soon as it is received.

• Consider configuring multiple standby databases to maintain data protection following a failover.

• Use Oracle Managed Files to pre-clear and pre-create online redo logs on the standby database.

  As part of a failover, the standby database must clear its online redo logs before opening as the primary database. The time needed to complete this I/O can add significantly to the overall failover time. With Oracle Managed Files, the standby pre-creates the online redo logs the first time the managed redo process (MRP) is started.

  • Alternatively set the LOG_FILE_NAME_CONVERT parameter. You can also pre-create empty online redo logs by issuing the SQL*Plus ALTER DATABASE CLEAR LOGFILE statement on the standby database.

• Use fast-start failover. If possible, to optimize switchover processing, ensure that the databases are synchronized before the switchover operation. Real-time apply ensures that redo is applied as received and ensures the fastest switchover.

Fast-start failover automatically, quickly, and reliably fails over to a designated standby database if the primary database fails, without requiring manual intervention to execute the failover. You can use fast-start failover only in an Oracle Data Guard configuration that is managed by Oracle Data Guard broker.

The Data Guard configuration can be running in either the maximum availability or maximum performance mode with fast-start failover. When fast-start failover is enabled, the broker ensures fast-start failover is possible only when the configured data loss guarantee can be upheld. Maximum availability mode provides an automatic failover environment guaranteed to lose no data. Maximum performance mode provides an automatic failover environment guaranteed to lose no more than the amount of data (in seconds) specified by the FastStartFailoverLagLimit configuration property.

Adopt fast-start failover best practices discussed in Configure Fast Start Failover.

The Time Management Interface (TMI) event is a low overhead event which adds a line to the alert log whenever certain calls are executed in Oracle.

These entries in the alert log, or tags, delineate the beginning and end of a call. The tables in the topics below depict the delineation of key switchover and failover operations. This method is the most accurate for determining where time is being spent.

Set the database level event 16453 trace name context forever, level 15 on all databases. There are two methods of enabling this trace, either using the EVENT database parameter or setting the EVENTS at the system level. The difference is that the EVENT parameter is not dynamic but is persistent across restarts. set EVENTS is dynamic but NOT persistent across database restarts. See the following examples.

ALTER SYSTEM SET EVENT=‘16453 trace name contextforever, level 15’ scope=spfile sid=’*’

ALTER SYSTEM SET EVENTS ‘16453 trace name context forever, level 15’;