Before you implement the Oracle Data Guard best practices that follow, apply the Oracle Database configuration best practices.

The Oracle Data Guard configuration best practices are considered additional to the general Oracle Database configuration best practices, and will help you achieve the services levels you expect of the MAA Gold reference architecture. It is implied that all of the database configuration best practices should be followed in a Data Guard configuration, and that the Data Guard recommendations discussed here supplant the general database recommendation where there are conflicts.

See Oracle Database Configuration Best Practices for more details.

There are several methods you can use to create an Oracle Data Guard standby database, but because of its simplicity, the Oracle MAA recommended approach is to create a physical standby database using the RMAN RESTORE ... FROM SERVICE clause.

For information about this approach see Creating a Physical Standby database using RMAN restore from service (Doc ID 2283978.1).

Use Oracle Data Guard broker to create, manage, and monitor an Oracle Data Guard configuration.

You can perform all Data Guard management operations locally or remotely using the broker interfaces: the Data Guard management pages in Oracle Enterprise Manager, which is the broker's graphical user interface (GUI), and the Data Guard command-line interface, called DGMGRL.

The broker interfaces improve usability and centralize management and monitoring of the Data Guard configuration. Available as a feature of Oracle Database Enterprise Edition and Personal Edition, the broker is also integrated with Oracle Database, Oracle Enterprise Manager, and Oracle Cloud Control Plane.

• Example Broker Installation and Configuration
  The following is an example broker installation and configuration, which is used in all of the broker configuration best practices examples.
• Configure Redo Transport Mode
  Configure the redo transport service on each configuration member by setting the LogXptMode property to one of the following modes.
• Validate the Broker Configuration
  To identify any problems with the overall configuration, validate it using the following steps.
• Configure Fast Start Failover
  Fast-start failover allows the broker to automatically fail over to a previously chosen standby database in the event of loss of the primary database. Enabling fast-start failover is requirement to meet stringent RTO requirements in the case of primary database, cluster, or site failure.
• Fast Start Failover with Multiple Standby Databases
  The FastStartFailoverTarget configuration property specifies the DB_UNIQUE_NAME of one or more standby databases that can act as target databases in a fast-start failover situation when the database on which the property is set is the primary database. These possible target databases are referred to as candidate fast-start failover targets.

For optimal network throughput, the minimum recommended settings for TCP send and receive socket buffer sizes is a value equal to the bandwidth-delay product (BDP) of the network link between the primary and standby systems.

Settings higher than the BDP may also yield incremental improvement. For example, Oracle MAA tests simulating high-latency, high-bandwidth networks continued to realize small, incremental increases in throughput as TCP send and receive socket buffer settings were increased to 3xBDP.

BDP is product of the network bandwidth and latency. Socket buffer sizes are set using the Oracle Net Services parameters RECV_BUF_SIZE and SEND_BUF_SIZE, so that the socket buffer size setting affects only Oracle TCP connections.

The operating system may impose limits on the socket buffer size that must be adjusted so Oracle can use larger values. For example, on Linux, the parameters net.core.rmem_max and net.core.wmem_max limit the socket buffer size and must be set larger than RECV_BUF_SIZE and SEND_BUF_SIZE.

For example, if bandwidth is 622 Mbits and latency is 30 ms, then you would calculate the minimum size for the RECV_BUF_SIZE and SEND_BUF_SIZE parameters as shown here.

Bandwidth Delay Product (BDP) = bandwidth x latency

BDP = 622,000,000 (bandwidth) / 8 x 0.030 (latency) = 2,332,500 bytes.

Given this example the optimal send and receive socket buffer sizes are calculated as follows.

Socket buffer size = 3 x BDP

= 2,332,500 (BDP) x 3

= 6,997,500 bytes

The size of the socket buffers can be set at the operating system level or at the Oracle Net Services level. As socket buffer size requirements can become quite large (depending on network conditions) it is recommended that you set them at the Oracle Net Services level so that normal TCP sessions, such as telnet, do not use additional memory.

Note that some operating systems have parameters that set the maximum size for all send and receive socket buffers. You must ensure that these values have been adjusted to allow Oracle Net Services to use a larger socket buffer size.

With Oracle Net Services you can set the send and receive socket buffer sizes globally for all connections using the following parameters in the sqlnet.ora file.

RECV_BUF_SIZE=6997500

SEND_BUF_SIZE=6997500

If you only want the larger buffer sizes for the connections associated with Data Guard transport then configure RECV_BUF_SIZE and SEND_BUF_SIZE in the Oracle Net Services alias used for transport as well as in the listener on the standby host.

The following example shows the send and receive socket buffer size set as a description attribute for a particular connect descriptor.

standby =
 (DESCRIPTION=
   (SEND_BUF_SIZE=6997500)
   (RECV_BUF_SIZE=6997500)
     (ADDRESS=(PROTOCOL=tcp)
     (HOST=stby_host)(PORT=1521))
   (CONNECT_DATA=
     (SERVICE_NAME=standby)))

The socket buffer sizes must be configured the same for all databases within a Data Guard configuration. On a standby side or the receiving side you can do this in either the sqlnet.ora or the listener.ora file.

In the listener.ora file, you can either specify the buffer space parameters for a particular protocol address or for a description, as shown here.

LISTENER =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)
    (HOST=stby_host)(PORT=1521)
    (SEND_BUF_SIZE=9375000)
    (RECV_BUF_SIZE=9375000)))

With Oracle Net Services you can control data transfer by adjusting the session data unit (SDU) size. Oracle testing has shown that setting the SDU parameter to its maximum value of 65535 improves performance of synchronous transport.

You can set SDU on a per connection basis using the SDU parameter in the local naming configuration file, tnsnames.ora, and the listener configuration file, listener.ora, or you can set the SDU for all Oracle Net Services connections with the profile parameter DEFAULT_SDU_SIZE in the sqlnet.ora file.

Redo log switching has a significant impact on redo transport and apply performance. Follow these best practices for sizing the online redo logs on the primary and standby databases.

Following these guidelines for online redo logs.

• All online redo log groups should have identically sized logs (to the byte).

• Online redo logs should reside on high performing disks (DATA disk groups).

• Create a minimum of three online redo log groups per thread of redo on Oracle RAC instances.

• Create online redo log groups on shared disks in an Oracle RAC environment.

• Multiplex online redo logs (multiple members per log group) unless they are placed on high redundancy disk groups.

• Size online redo logs to switch no more than 12 times per hour (every ~5 minutes). In most cases a log switch every 15 to 20 minutes is optimal even during peak workloads.

• Sizing Redo Logs
  Size the redo logs based on the peak redo generation rate of the primary database.

Configure the standby redo log groups on all primary and standby databases for improved availability and performance.

For each redo log thread--a thread is associated with an Oracle RAC database instance--the number of standby redo log groups must be greater than or equal to (>=) the number of online redo log groups.

Consider the following additional guidelines when creating standby redo log groups.

• All online redo logs and standby redo log groups should have identically sized logs (to the byte). Standby redo logs are not used if they are not the same size as the online redo logs.

• All standby redo log groups should have identically sized logs (to the byte) on both the primary and standby databases.

• Standby redo logs should reside on high performing disks (DATA disk group).

• Standby redo logs should be multiplexed (multiple members per log group) unless placed on high redundancy disk groups. Multiplexing standby redo logs is optional in all cases because Data Guard can fetch any missing redo.

• In an Oracle RAC environment, create standby redo logs on a shared disk.

• In an Oracle RAC environment, assign a thread to each standby redo log group.

The following example creates three log groups for each redo thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('+DATA') SIZE 4194304000, GROUP 8 ('+DATA') SIZE 4194304000, GROUP 9 ('+DATA') SIZE 4194304000; 

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 10 ('+DATA') SIZE 4194304000, GROUP 11 ('+DATA') SIZE 4194304000, GROUP 12 ('+DATA') SIZE 419430400

To check the thread number and group numbers of the online redo logs, query the V$LOG view.

SQL> SELECT * FROM V$LOG;

To check the results of the ALTER DATABASE ADD STANDBY LOGFILE THREAD statements, query the V$STANDBY_LOG view.

SQL> SELECT * FROM V$STANDBY_LOG;

Oracle Database corruption prevention, detection, and repair capabilities are built on internal knowledge of the data and transactions it protects, and on the intelligent integration of its comprehensive high availability solutions.

When data corruption is detected, Oracle Data Guard, block media recovery, and data file media recovery can recover the data. Database-wide logical corruptions caused by human or application errors can be undone with Oracle Flashback Technologies.

Tools are also available for proactive validation of logical data structures. For example, the SQL*Plus ANALYZE TABLE statement detects inter-block corruptions.

Achieve the most comprehensive data corruption prevention and detection with these best practices.

• Use Oracle Data Guard with physical standby databases to prevent widespread block corruption. Oracle Data Guard is the best solution for protecting Oracle data against data loss and corruption, and lost writes.

• Set the Oracle Database block-corruption initialization parameters on the Data Guard primary and standby databases as shown in the following table.

  Table 11-3 Block-Corruption Initialization Parameter Settings

  On the primary database set...	On the standby databases set...
  DB_BLOCK_CHECKSUM=MEDIUM or FULL DB_LOST_WRITE_PROTECT=TYPICAL DB_BLOCK_CHECKING=FALSE*	DB_BLOCK_CHECKSUM=MEDIUM or FULL DB_LOST_WRITE_PROTECT=TYPICAL DB_BLOCK_CHECKING=MEDIUM or FULL

  * DB_BLOCK_CHECKING on the PRIMARY is recommended to be set to MEDIUM or FULL but only after a full performance evaluation with the application.

• Performance overhead is incurred on every block change, therefore performance testing is of particular importance when setting the DB_BLOCK_CHECKING parameter. Oracle highly recommends the minimum setting of DB_BLOCK_CHECKING=MEDIUM (block checks on data blocks but not index blocks) on either the primary or standby database. If the performance overhead of enabling DB_BLOCK_CHECKING to MEDIUM or FULL is unacceptable on your primary database, then set DB_BLOCK_CHECKING to MEDIUM or FULL for your standby databases.

The following recommendations also help to protect against data corruptions.

• Use Oracle Automatic Storage Management (Oracle ASM) to provide disk mirroring to protect against disk failures.

• Use Oracle ASM HIGH REDUNDANCY for optimal corruption repair. Using Oracle ASM redundancy for disk groups provides mirrored extents that can be used by the database if an I/O error or corruption is encountered. For continued protection, Oracle ASM redundancy provides the ability to move an extent to a different area on a disk if an I/O error occurs. The Oracle ASM redundancy mechanism is useful if you have bad sectors returning media errors.

• Enable Flashback Technologies for fast point-in-time recovery from logical corruptions most often caused by human error and for fast reinstatement of a primary database following failover.

• Use RMAN for additional block checks during backup and restore operations. Implement a backup and recovery strategy with Recovery Manager (RMAN) and periodically use the RMAN BACKUP VALIDATE CHECK LOGICAL scan to detect corruptions.

• Use Zero Data Loss Recovery Appliance for backup and recovery validation including corruption checks and repairs, central backup validation, reduced production database impact, and Enterprise Cloud backup and recovery solution.

Enable Flashback Database on both the primary and standby database, so that if the original primary database has not been damaged, you can reinstate the original primary database as a new standby database following a failover.

If there is a failure during the switchover process, then it can easily be reversed when Flashback Database is enabled.

Set DB_FLASHBACK_RETENTION_TARGET to the same value on the standby database as the primary. Set DB_FLASHBACK_RETENTION_TARGET initialization parameter to the largest value prescribed by any of the following conditions that apply.

• To leverage flashback database to reinstate your failed primary database after Data Guard failover, for most cases set DB_FLASHBACK_RETENTION_TARGET to a minimum of 120 (minutes) to enable reinstatement of a failed primary.
• If using Flashback Database for fast point in time recovery from user error or logical corruptions, set DB_FLASHBACK_RETENTION_TARGET to a value equal to the farthest time in the past to which the database should be recovered. If you can detect and repair from logical corruptions in less than 24 hours, then set DB_FLASHBACK_RETENTION_TARGET to a minimum of 1440 (minutes).

When the primary database is in FORCE LOGGING mode, all database data changes are logged. FORCE LOGGING mode ensures that the standby database remains consistent with the primary database.

If it is not possible to use this mode because you require the load performance with NOLOGGING operations, then see Enable an Appropriate Logging Mode for other options.

You can enable force logging immediately by issuing an ALTER DATABASE FORCE LOGGING statement. If you specify FORCE LOGGING, then Oracle waits for all ongoing non-logged operations to finish.

The Oracle Data Guard broker automates management and operation tasks across multiple databases in an Oracle Data Guard configuration. The broker also monitors all of the systems in a single Oracle Data Guard configuration.

In a multi-member Data Guard configuration the following redo transport destinations are supported:

• Oracle Data Guard standby databases
• Far sync instances (See Using Far Sync Instances for more information)
• Oracle Streams downstream capture databases
• Zero Data Loss Recovery Appliance (Recovery Appliance)

You can use the Oracle Data Guard broker RedoRoutes property to override the default behavior by which a primary database sends the redo that it generates to every other redo transport destination in the configuration.

An example redo transport topology that differs from the default would be one in which a physical standby, or a far sync instance, forwards redo received from the primary database to one or more destinations, or one in which the redo transport mode used for a given destination is dependent on which database is in the primary role.

Consider a configuration that has a primary database (North_Sales) and two physical standby databases (Local_Sales and Remote_Sales). The Local_Sales database is located in the same data center as the primary for high availability purposes and for simpler application and database failover. The Remote_Sales database is located in a remote data center for disaster recovery purposes.

Rather than have North_Sales ship its redo to both databases, you can use the RedoRoutes broker property to configure real-time cascading, in which the local physical standby database forwards to Remote_Sales the redo it receives from North_Sales. To accomplish this, the RedoRoutes property is set on North_Sales and Local_Sales as follows:

• On the North_Sales database, the RedoRoutes property specifies that if North_Sales is in the primary role, then it should ship redo to the Local_Sales database using synchronous transport mode. This rule prevents the primary from shipping redo data directly to the Remote_Sales database.
• On the Local_Sales database, the RedoRoutes property must specify that if North_Sales is in the primary role, then Local_Sales should forward redo it receives from North_Sales on to Remote_Sales.

To see the runtime RedoRoutes configuration, use the SHOW CONFIGURATION command. For example:

DGMGRL> SHOW CONFIGURATION;

Configuration - Sales_Configuration

  Protection Mode: MaxAvailability
  Members:
 North_Sales  - Primary database
    Local_Sales  - Physical standby database
      Remote_Sales  - Physical standby database (receiving current redo)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Note that the asynchronous redo transport attribute was explicitly specified in the redo route rule for the Remote_Sales destination to enable real-time cascading of redo to that destination. (Real-time cascading requires a license for the Oracle Active Data Guard option.)

DGMGRL> EDIT DATABASE 'Local_Sales' SET PROPERTY 'RedoRoutes' = '(North_Sales : Remote_Sales)';

See RedoRoutes for more information.

• Using the RedoRoutes Property for Remote Alternate Destinations
  The RedoRoutes property can be used to set up a remote alternate destination, so that a terminal member can still receive redo data even if the member from which it was receiving the redo data fails.

The Oracle Data Guard FastStartFailoverTarget broker configuration property specifies the DB_UNIQUE_NAME of one or more standby databases that can act as target databases in a fast-start failover scenario when the database on which the property is set is the primary database.

These possible target databases are referred to as candidate fast-start failover targets. The FastStartFailoverTarget property can only be set to the name of physical standbys. It cannot be set to the name of a snapshot standby database, far sync instance, or Zero Data Loss Recovery Appliance.

If only one physical standby database exists, then the broker selects that database as the default value for FastStartFailoverTarget on the primary database when fast-start failover is enabled. If more than one physical standby database exists, then the broker selects a single standby based on the order in which they are specified in the property definition. The targets are verified when fast-start failover is enabled.

See also, FastStartFailoverTarget.

• Setting FastStartFailoverTarget
  If you have two or more standby databases, set up the FastStartFailoverTarget configuration property on the primary database to indicate the desired fast-start failover target standby database.
• Switchover with FastStartFailoverTarget Set
  If fast-start failover is enabled with FastStartFailoverTarget set you can still perform a switchover or a manual failover, as long the role change is directed to the same standby database that was specified for the FastStartFailoverTarget database property on the primary database.
• Fast-Start Failover Outage Handling
  If the primary database's fast-start failover target standby database becomes unavailable, perhaps because the standby database or instance is down or there's an issue with transporting redo, then the primary's fast-start failover target is automatically switched to the next target configured in the FastStartFailoverTarget property.

Far Sync is an Oracle Active Data Guard feature that provides increased flexibility in the location of a disaster recovery site for those who wish to implement zero data loss protection.

Even users who have already deployed Oracle Data Guard synchronous transport can benefit from configuring a far sync instance closer to the primary than their current standby to reduce the performance impact on the production database.

Synchronous redo transport over WAN distances or on an under-performing network often has too large an impact on primary database performance to support zero data loss protection. Oracle Active Data Guard Far Sync provides the ability to perform a zero data loss failover to a remote standby database without requiring a second standby database or complex operation.

Far Sync enables this by deploying a far sync instance (a lightweight Oracle instance) at a distance that is within an acceptable range of the primary for synchronous redo transport. A far sync instance receives redo from the primary using synchronous transport and forwards the redo to up to 29 remote standby databases using asynchronous transport.

Far sync instances are part of the Oracle Active Data Guard Far Sync feature, which requires an Oracle Active Data Guard license.

• Offloading to a Far Sync Instance
  A far sync instance offloads from the primary any overhead of resolving gaps in redo received by the remote standby database (for example, following network or standby database outages) and can conserve WAN bandwidth by performing redo transport compression without impacting primary database performance.

Oracle Active Data Guard Far Sync provides the ability to perform a zero data loss failover to a remote standby database without requiring a second standby database or complex operation.

Data Guard enables this by deploying a far sync instance (a lightweight Oracle instance that has only a control file, SPFILE, password file and standby log files; there are no database files or online redo logs) at a distance that is within an acceptable range of the primary for synchronous transport. A far sync instance receives redo from the primary through synchronous transport and immediately forwards the redo to up to 29 remote standby databases using asynchronous transport. A far sync instance can also forward redo to the new Oracle Database Backup, Logging, and Recovery Appliance.

Figure 11-1 Far Sync Architecture Overview

The following use cases illustrate the benefits of various architecture choices you can implement with far sync instances.

• Case 1: Zero Data Loss Protection Following Role Transitions
  This is the most basic example in which a primary database uses high availability far sync instances to extend zero data loss failover to a remote standby database.
• Case 2: Reader Farm Support
  Far Sync can support up to 30 remote destinations, making it a very useful tool for supporting a reader farm – an Active Data Guard configuration having multiple active standby databases to easily scale read performance.
• Case 3: Cloud Deployment With Far Sync Hub
  Far Sync is a very lightweight process; a single physical server can support multiple far sync instances, each providing zero data loss failover to a remote destination.
• Far Sync High Availability Topologies
  To keep far sync instances highly available, consider the following deployment topologies.
• Choosing a Far Sync Deployment Topology
  All configurations for far sync instance high availability perform equally with regard to receiving and sending redo. The choice of configuration should be based on application tolerance to the maximum data loss (RPO) and application brownout period of the different failure scenarios.

The following are far sync configuration best practices that are necessary in addition to those best practices that apply to any synchronous redo transport destination.

• The network between the primary database and the far sync instance must:

  • Have round trip latency low enough so that the impact to response time and throughput of the primary database does not exceed business requirements. The degree of impact is very application specific and will require testing to validate. In general, experience shows that there is a higher likelihood of success if the round-trip latency is less than 5ms, though there are successful deployments at higher latencies.

  • Provide enough bandwidth between the primary database and the far sync instance to accommodate peak redo volumes, in addition to any other traffic sharing the network. Redo transport compression can be used to reduce network bandwidth requirements.

  • Ideally have redundant network links that also tolerate network component failure.

• Standard Oracle Data Guard network best practices, such as setting appropriate TCP send and receive buffer sizes equal to three times the bandwidth delay product. See Configure Online Redo Logs Appropriately.

• Standby redo logs for a far sync instance should be placed on storage with sufficient IOPS (writes per second) capacity to exceed the I/O of the LGWR process on the primary database during peak activity, in addition to any IOPS from other activities. This is an important consideration. For example:

  • If the far sync instance has lower performing disks than the primary database, it will not be able to forward redo to remote destinations as fast as it is received, and an archive log gap may form.

  • In redo gap resolution scenarios, due to planned maintenance on the standby or network outages, for example, there will be additional I/O requests for gap resolution on top of peak redo coming in.

  • Lower performing disks at the far sync instance will delay acknowledgment to the primary database, increasing the total round-trip time between primary and standby databases and impacting application response time. This impact can be eliminated by using Fast Sync between the primary database and the far sync instance.

• The far sync instance should follow the same standby redo log best practices as the standby database. See Configure Online Redo Logs Appropriately.

• The standby redo logs of an alternate far sync instance should be manually cleared before use to achieve the fastest return to synchronous transport when the alternate far sync is activated. For example:

  ALTER DATABASE CLEAR LOGFILE GROUP 4, GROUP 5, GROUP 6;

• Oracle MAA performance testing shows that a small far sync instance SGA does not impact the performance of the far sync instance or the primary database. To conserve system resources, you can configure the minimum SGA required for Far Sync to function.

  • Set CPU_COUNT=4. Values of 1 or 2 are possible when neither compression nor encryption are not being used.

  • Reducing the CPU_COUNT during testing has no effect on the performance of the Far sync instance.

• Configure far sync instances for both the primary and standby databases to maintain zero data loss protection following role transitions. The second far sync instance configured in proximity to the standby database is idle until the standby becomes the primary database, enabling synchronous redo transport in the reverse direction.

  Note that in a Data Guard Broker configuration, a switchover (planned role transition) cannot occur while in Maximum Availability mode unless the protection mode can be enforced from the target standby site. If the standby database does not have its own far sync instance it will have to be configured to ship asynchronous redo to the original primary database after the roles are reversed. This prevents a switchover from occurring unless the protection mode for the primary database is first dropped from Maximum Availability to Maximum Performance.

• Fast Sync yields a 4% to 12% primary database performance improvement compared to synchronous transport, depending on the network latency and the I/O speed of the far sync instance hardware.

• Provided CPU, I/O, and network requirements are met.

  • Placing the far sync instance on a virtual machine produces no reduction in performance over physical hardware configurations.

  • Multiple far sync instances servicing multiple Data Guard configurations can share the same physical server, cluster, or virtual machine.

• Note that archives may need to be managed on the far sync server.

The following topics walk you through an example of configuring an Active Data Guard Far Sync architecture.

• Configuring the Far Sync Instances
  The following examples show you how to add far sync instances to an Oracle Data Guard broker configuration.
• Setting Up HA Far Sync Instances
  Alternate HA far sync instances are set up to provide high availability for the far sync instances you created for the primary and remote standby databases.
• Configuring Far Sync Instances with Oracle RAC or Oracle Clusterware
  If a far sync instance is deployed on a server or cluster with Oracle Clusterware (for example, in an Oracle Restart, Oracle Real Application Clusters (Oracle RAC), or Oracle RAC One Node installation), then use the SRVCTL utility to specify a default open mode of mount.