9 Configuring Oracle Data Guard

9.2.1 Use Redo Transport Services Best Practices

At a high level, the Redo Transport best practices for planning and implementing redo transport services for Oracle Data Guard are as follows:

• Use the SYNC redo transport mode for a high degree of synchronization between the primary and standby databases. Use SYNC redo transport for zero data loss protection where performance service levels can tolerate the impact caused by network latency.

• Use the ASYNC redo transport mode for minimal impact on the primary database, but with a lower degree of synchronization. Use ASYNC redo transport when zero data loss protection is not required or when the performance impact caused by network latency makes it impractical to use SYNC.

• Optimize network throughput following the best practices described in Section 9.2.2, "Assess Performance with Proposed Network Configuration".

9.2.2 Assess Performance with Proposed Network Configuration

Oracle recommends that you conduct a performance assessment with your proposed network configuration and current, or anticipated, peak redo rate. The network effect between the primary and standby databases, and the effect on the primary database throughput must be understood. Because the network between the primary and standby databases is essential for the two databases to remain synchronized, the infrastructure must have the following characteristics:

• Sufficient bandwidth to accommodate the maximum redo generation rate

• If using the SYNC transport, then minimal latency is necessary to reduce the performance impact on the primary database

• Multiple network paths for network redundancy

In configurations that use a dedicated network connection the required bandwidth is determined by the maximum redo rate of the primary database and the efficiency of the network. Depending on the data protection mode, there are other recommended practices and performance considerations. Maximum protection mode and maximum availability mode require SYNC transport.

The maximum performance protection mode use ASYNC redo transport. Use ASYNC redo transport when data loss can be tolerated or when the performance impact caused by network latency makes it impractical to use SYNC (use SYNC redo transport for zero data loss protection).

Unlike the ASYNC transport mode, the SYNC transport mode can affect the primary database performance due to the incurred network latency. Distance and network configuration directly influence latency, while high latency can slow the potential transaction throughput and quicken response time. The network configuration, number of repeaters, the overhead of protocol conversions, and the number of routers also affect the overall network latency and transaction response time.

9.3.1 Use Oracle Data Guard Broker with Oracle Data Guard

Use Oracle Data Guard broker to create, manage, and monitor a Data Guard configuration. You can perform all Data Guard management operations locally or remotely through the Oracle Data Guard broker's easy-to-use 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's interfaces improve usability and centralize management and monitoring of the Data Guard configuration. Available as a feature of the Enterprise Edition and Personal Edition of the Oracle database, the broker is also integrated with the Oracle database and Oracle Enterprise Manager.

The benefits of using Oracle Data Guard broker include:

• Enhanced disaster protection.

• Higher availability and scalability with Oracle Real Application Clusters (Oracle RAC) Databases.

• Automated creation of a Data Guard configuration.

• Easy configuration of additional standby databases.

• Simplified, centralized, and extended management.

• Simplified switchover and failover operations.

• Fast Application Notification (FAN) after failovers.

• Built-in monitoring and alert and control mechanisms.

• Transparent to application.

9.3.2 Use Recovery Manager to Create Standby Databases

Oracle recommends that you use the Recovery Manager (RMAN) utility to simplify the process of creating a physical standby database.

You can either create a standby database from backups of your primary database, or create a standby database over the network:

• Use the RMAN DUPLICATE TARGET DATABASE FOR STANDBY command to create a standby database from backups of your primary database.

  You can use any backup copy of the primary database to create the physical standby database if the necessary archived redo log files to completely recover the database are accessible by the server session on the standby host. RMAN restores the most recent data files unless you execute the SET UNTIL command.

• Use the RMAN FROM ACTIVE DATABASE option to create the standby database over the network if a preexisting database backup is not accessible to the standby system.

  RMAN copies the data files directly from the primary database to the standby database. The primary database must be mounted or open.

You must choose between active and backup-based duplication. If you do not specify the FROM ACTIVE DATABASE option, then RMAN performs backup-based duplication. Creating a standby database over the network is advantageous because:

• You can transfer redo data directly to the remote host over the network without first having to go through the steps of performing a backup on the primary database. (Restoration requires multiple steps including storing the backup locally on the primary database, transferring the backup over the network, storing the backup locally on the standby database, and then restoring the backup on the standby database.)

• With active duplication you can backup a database (as it is running) from Oracle ASM, and restore the backup to a host over the network and place the files directly into Oracle ASM.

  Before this feature, restoration required you to backup the primary and copy the backup files on the primary host file system, transfer the backup files over the network, place the backup files on the standby host file system, and then restore the files into Oracle ASM.

9.3.3 Use Flashback Database for Reinstatement After Failover

Enable Flashback Database on both the primary and standby database so that, in case 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. For more information, see Section 5.1.4, "Enable Flashback Database".

9.3.4 Use FORCE LOGGING Mode

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 this is not possible because you require the load performance with NOLOGGING operations, then you must ensure that the corresponding physical standby data files are subsequently synchronized. To synchronize the physical standby data files, either apply an incremental backup created from the primary database or replace the affected standby data files with a backup of the primary data files taken after the nologging operation. Before the file transfer, you must stop Redo Apply on the physical standby database.

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 unlogged operations to finish.

9.3.5 Use a Simple, Robust Archiving Strategy and Configuration

This archiving strategy is based on the following assumptions:

• Each database uses a fast recovery area.

• The primary database instances archive remotely to only one apply instance.

Table 9-2 describes the recommendations for a robust archiving strategy when managing a Data Guard configuration through SQL*Plus. All of the following items are handled automatically when Oracle Data Guard broker is managing a configuration.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;

The following example illustrates the recommended initialization parameters for a primary database communicating to a physical standby database. There are two instances, SALES1 and SALES2, running in maximum protection mode.

*.DB_RECOVERY_FILE_DEST=+RECO
*.LOG_ARCHIVE_DEST_1='SERVICE=SALES_stby SYNC AFFIRM NET_TIMEOUT=30
    REOPEN=300 VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=SALES_stby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

The fast recovery area must be accessible to any node within the cluster and use a shared file system technology such as automatic storage management (Oracle ASM), a cluster file system, a global file system, or high availability network file system (HA NFS). You can also mount the file system manually to any node within the cluster very quickly. This is necessary for recovery because all archived redo log files must be accessible on all nodes.

On the standby database nodes, recovery from a different node is required when a failure occurs on the node applying redo and the apply service cannot be restarted. In that case, any of the existing standby instances residing on a different node can initiate managed recovery. In the worst case, when the standby archived redo log files are inaccessible, the managed recovery process (MRP) on the different node fetches the archived redo log files using the FAL server to retrieve from the primary node directly.

When configuring hardware vendor shared file system technology, verify the performance and availability implications. Investigate the following issues before adopting this strategy:

• Is the shared file system accessible by any node regardless of the number of node failures?

• What is the performance impact when implementing a shared file system?

• Is there any effect on the interconnect traffic?

9.3.6 Use Standby Redo Logs and Configure Size Appropriately

You should configure standby redo logs on both sites for improved availability and performance. To determine the recommended number of standby redo logs, use the following formula:

(maximum # of logfile groups +1) * maximum # of threads

For example, if a primary database has two instances (threads) and each thread has three online log groups, then there should be eight standby redo logs ((3 + 1) * 2 = 8), this reduces the likelihood that the logwriter process for the primary instance is blocked because a standby redo log cannot be allocated on the standby database.

The statements in Example 9-1 create two standby log members for each group and each member is 1 GB. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are three online redo log groups in two threads, the next group is group seven.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 7 ('+RECO') SIZE 1G,
GROUP 8 ('+RECO') SIZE 1G,
GROUP 9 ('+RECO') SIZE 1G,
Group 10 ('+RECO') SIZE 1G;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 11 ('+RECO') SIZE 1G,
GROUP 12 ('+RECO') SIZE 1G,
GROUP 13 ('+RECO') SIZE 1G,
GROUP 14 ('+RECO') SIZE 1G;

Consider the following additional guidelines when creating standby redo logs:

• Create the same number of standby redo logs on both the primary and standby databases.

• Create all online redo logs and standby redo logs for both primary and standby databases so that they are the same size.

• Create standby redo logs in the first available ASM high redundancy disk group, or ensure that the logs are protected using external storage redundancy.

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

• In an Oracle RAC environment, assign a thread when the standby redo log is created as described in Example 9-1.

• Do not multiplex the standby redo logs.

To check the number and group numbers of the 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;

You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

9.3.7 Use Data Guard Transport and Network Configuration Best Practices

The best practices for Data Guard transport and network configuration include:

• Set the LOG_ARCHIVE_MAX_PROCESSES Parameter

• Set the Network Configuration and Highest Network Redo Rates

LOG_ARCHIVE_MAX_PROCESSES = sum(remote_destinations) + count(threads)

9.3.8 Use Data Guard Redo Apply Best Practices

To improve the Redo Apply rate of a physical standby database (and media recovery):

• Maximize I/O Rates on Standby Redo Logs and Archived Redo Logs

• Assess Recovery Rate

• Set DB_BLOCK_CHECKSUM=FULL and DB_BLOCK_CHECKING=MEDIUM or FULL

• Set DB_CACHE_SIZE to a Value Greater than on the Primary Database

• Assess Database Wait Events

• Tune I/O Operations

• Assess System Resources

SELECT * FROM V$RECOVERY_PROGRESS;

9.3.9 Implement Multiple Standby Databases

You should deploy multiple standby databases for any of the following purposes. When desired, use standby databases for these purposes while reserving at least one standby database to serve as the primary failover target:

• To provide continuous protection following failover

  The standby databases in a multiple standby configuration that are not the target of the role transition (these databases are referred to as bystander standby databases) automatically apply redo data received from the new primary database.

• To achieve zero data loss protection while also guarding against widespread geographic disasters that extend beyond the limits of synchronous communication

  For example, one standby database that receives redo data synchronously is located 200 miles away, and a second standby database that receives redo data asynchronously is located 1,500 miles away from the primary.

• To perform rolling database upgrades while maintaining disaster protection throughout the rolling upgrade process

• To perform testing and other ad-hoc tasks while maintaining disaster-recovery protection

Use Multiple Standby Databases Best Practices

The Oracle Database High Availability Overview describes how a multiple standby database architecture is virtually identical to that of single standby database architectures. Therefore, the configuration guidelines for implementing multiple standby databases described in this section complement the existing best practices for physical and logical standby databases.

When deploying multiple standby databases, use the following best practices:

• Use Oracle Data Guard broker (described in Chapter 12, "Monitoring for High Availability") to manage your configuration and perform role transitions. However, if you choose to use SQL*Plus statements, see the MAA white paper "Multiple Standby Databases Best Practices" for best practices from the MAA Best Practices area for Oracle Database at

  http://www.oracle.com/goto/maa

• If you are using Flashback Database for the sole purpose of reinstating databases following a failover, a DB_FLASHBACK_RETENTION_TARGET of 120 minutes is the minimum recommended value. When you use Flashback Database to quickly reinstate the original primary as the standby after a failover, instead of re-creating the entire standby database from backups or from the primary database, when using Fast-start Failover, ensure the UNDO_RETENTION and DB_FLASHBACK_RETENTION_TARGET initialization parameters are set to a minimum of 120 so that reinstatement is still possible after a prolonged outage. On a standby the flashback barrier cannot be guaranteed to be published every 30 minutes as it is on a primary. Thus, when enabling flashback database on a standby, the DB_FLASHBACK_RETENTION_TARGET should be a minimum of 120. Since the primary and standby should match, this implies the same for the primary.

• Enable supplemental logging in configurations containing logical standby databases. When creating a configuration with both physical and logical standby databases, issue the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement to enable supplemental logging in the following situations:

  • When adding a logical standby database to an existing configuration consisting of all physical standby databases, you must enable supplemental logging on all existing physical standby databases in the configuration.

  • When adding a physical standby database to an existing configuration that contains a logical standby database, you must enable supplemental logging on the physical standby database when you create it.

  As part of the logical standby database creation supplemental logging is automatically enabled on the primary. Enabling supplemental logging is a control file change and therefore the change is not propagated to each physical standby database. Supplemental logging is enabled automatically on a logical standby database when it is first converted from a physical standby database to a logical standby database as part of the dictionary build process.To enable supplemental logging, issue the following SQL*Plus statement when connected to a physical standby database:

  SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
  

• If logical standby databases are not configured to perform real-time queries, then consider configuring SQL Apply to delay applying redo data to the logical standby database. By delaying the application of redo, you can minimize the need to manually reinstate the logical standby database after failing over to a physical standby database.

  To set a time delay, use the DELAY=minutes attribute of the LOG_ARCHIVE_DEST_n initialization parameter.

9.4.1 Oracle Data Guard Switchovers Best Practices

A database switchover performed by Oracle Data Guard is a planned transition that includes a series of steps to switch roles between a standby database and a primary database. Following a successful switchover operation, the standby database assumes the primary role and the primary database becomes a standby database. Switchovers are typically completed in only seconds to minutes. At times the term switchback is also used within the scope of database role management. A switchback operation is a subsequent switchover operation to return the roles to their original state.

Data Guard enables you to change these roles dynamically by:

• Using Oracle Enterprise Manager

• Using the Oracle Data Guard broker's DGMGRL command-line interface

• Issuing SQL statements, as described in Section 14.2.1.3, "How to Perform Data Guard Switchover"

To optimize switchover processing, perform the following steps before performing a switchover:

• Disconnect all sessions possible using the ALTER SYSTEM KILL SESSION SQL*Plus command.

• Stop job processing by setting the AQ_TM_PROCESSES parameter to 0.

• Cancel any specified apply delay by using the NODELAY keyword to stop and restart log apply services on the standby database.

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY;
  

  You can view the current delay setting on the primary database by querying the DELAY_MINS column of the V$ARCHIVE_DEST view.

• For physical standby databases in an Oracle RAC environment, ensure there is only one instance active for each primary and standby database.

• Configure the standby database to use real-time apply and, if possible, ensure the databases are synchronized before the switchover operation to optimize switchover processing.

  For the fastest switchover, use real-time apply so that redo data is applied to the standby database as soon as it is received, and the standby database is synchronized with the primary database before the switchover operation to minimize switchover time. To enable real-time apply use the following SQL*Plus statement:

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
  

• For a physical standby database, reduce the number of archiver (ARCn) processes to the minimum needed for both remote and local archiving. Additional archiver processes can take additional time to shut down, thereby increasing the overall time it takes to perform a switchover. After the switchover has completed you can reenable the additional archiver processes.

• Set the LOG_FILE_NAME_CONVERT initialization parameter to any valid value for the environment, or if it is not needed set the parameter to null.

  As part of a switchover, the standby database must clear the online redo log files on the standby database before opening as a primary database. The time needed to complete the I/O can significantly increase the overall switchover time. By setting the LOG_FILE_NAME_CONVERT parameter, the standby database can pre-create the online redo logs the first time the MRP process is started. You can also pre-create empty online redo logs by issuing the SQL*Plus ALTER DATABASE CLEAR LOGFILE statement on the standby database.

9.4.2 Oracle Data Guard Failovers Best Practices

A failover is typically used only when the primary database becomes unavailable, and there is no possibility of restoring it to service within a reasonable period. During a failover the primary database is taken offline at one site and a standby database is brought online as the primary database.

With Data Guard the process of failover can be completely automated using fast-start failover or it can be a manual, user driven process. Oracle recommends using fast-start failover to eliminate the uncertainty inherent in a process that requires manual intervention. Fast-start failover automatically executes a failover within seconds of an outage being detected.

For more on Data Guard failover best practices, see:

• Comparing Fast-Start Failover and Manual Failover

• Failover Best Practices (Manual Failover and Fast-Start Failover)

• Fast-Start Failover Best Practices

• Manual Failover Best Practices