Standby Recommendations

Data Guard RECOMMENDATIONS

This section outlines the best practices and recommendations for using Data Guard standby databases. A standby database can be either a physical standby database or a logical standby database. A physical standby database provides a physically identical copy of the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, is the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.

A logical standby database contains the same logical information as the production database, although the physical organization and structure of the data can be different. It is kept synchronized with the primary database by transforming the data in the redo logs received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database

The tables below will assist with the decision of which standby database type to use.

Table 1: Standby Database Type

Standby Type	Advantages	Considerations
Physical Standby (redo apply)	· minimal resource overhead on the primary and the standby databases · redo apply is fastest and most efficient approach to apply changes to the standby database · can be used to offload backups which can be used to recover the primary database	· physically identical copy of the primary database · when open in READ ONLY no redo is applied
Logical Standby (SQL apply)	· allows the standby database to be open for normal operations. · allows additional objects to be built and maintained. · performs all of its operations on the standby node, and requires minimal additional processing on the primary nodes.	· physical organization and structure of the data can be different · if used in conjunction with a physical standby and a failover is done to the physical standby, the logical standby must be reinstantiated · uses more system resources than a physical standby

Standby Type

Advantages

Considerations

Physical Standby (redo apply)

· minimal resource overhead on the primary and the standby databases

· redo apply is fastest and most efficient approach to apply changes to the standby database

· can be used to offload backups which can be used to recover the primary database

· physically identical copy of the primary database

· when open in READ ONLY no redo is applied

Logical Standby (SQL apply)

· allows the standby database to be open for normal operations.

· allows additional objects to be built and maintained.

· performs all of its operations on the standby node, and requires minimal additional processing on the primary nodes.

· physical organization and structure of the data can be different

· if used in conjunction with a physical standby and a failover is done to the physical standby, the logical standby must be reinstantiated

· uses more system resources than a physical standby

Table 2: Determining the Standby Database Type

Questions	Recommendations
Do you require strict zero data loss without any allowance for data divergence?	Yes - use a physical standby database No – go to next question
Do you have any unsupported logical standby data types?	run this query: SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME; Rows returned – Use a physical standby or investigate switching to supported data type No rows returned – go to next question
Do you need to have the standby database open for read and/or write access?	Yes – go to next question No - use a physical standby
Can a logical standby keep up with your primary redo rate? For more information, please see Technical Paper, “Oracle9i Data Guard: SQL Apply Best Practices” paper at http://otn.oracle.com/deploy/availability/htdocs/maa.htm	Yes – use a logical standby and/or a physical standby No – use a physical standby or increase available system resources on the logical standby

Questions

Recommendations

Do you require strict zero data loss without any allowance for data divergence?

Yes - use a physical standby database

No – go to next question

Do you have any unsupported logical standby data types?

run this query:

SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
ORDER BY OWNER,TABLE_NAME;

Rows returned – Use a physical standby or investigate switching to supported data type

No rows returned – go to next question

Do you need to have the standby database open for read and/or write access?

Yes – go to next question

No - use a physical standby

Can a logical standby keep up with your primary redo rate?

For more information, please see Technical Paper, “Oracle9i Data Guard: SQL Apply Best Practices” paper at http://otn.oracle.com/deploy/availability/htdocs/maa.htm

Yes – use a logical standby and/or a physical standby

No – use a physical standby or increase available system resources on the logical standby

Data Guard setup is essential to ensuring that at the time of switchover or failover operations, both databases work properly and perform their roles within service levels. The following is a list of configuration recommendations that are categorized by General (applies to either type of standby), Physical Standby Database Only recommendations, and Logical Standby only recommendations. If both a physical standby and a logical standby are used then the apply processes (Managed Recovery (MRP) on a physical standby and the logical standby apply process (LSP) on a logical standby) should run on separate nodes in the RAC. When both are used then all recommendations below apply.

General

· Use a simple, robust archiving strategy and configuration

· Enable FORCE LOGGING mode

· Establish a recovery delay

· Configure multiple standby instances

· Disable time-based thread advance feature (ARCHIVE_LAG_TARGET=0) for LGWR

· Unset DB_CREATE_ONLINE_LOG_DEST

· Use Data Guard instead of remote mirroring technology

· Determine the proper database protection mode

· Evaluate tuning the network in a WAN environment

· For no data loss protection modes, utilize a LAN or MAN network environment

· For no data loss protection modes, set SYNC=NOPARALLEL attribute

· Use the archive transport for the greatest performance throughput

· In Maximum Performance mode over a WAN, evaluate implementing SSH port-forwarding with compression

Physical Standby Database Only

· Use standby redo logs

· In Maximum Performance mode, use the ASYNC attribute with 10MB buffer size

· Tune the standby database and host for optimal recovery performance

· Set parallel recovery to 2 times number of CPUs on one standby host

· Set parallel recovery buffer size to 4K

· Clear online redo log groups on the standby

Logical Standby Only

· Use supplemental logging and primary key constraints on all production tables

· Set the logical standby MAX_SERVERS SQL Apply parameter to max(9, 3 x CPU)

· Increase the initialization parameter PARALLEL_MAX_SERVERS to <current setting> + MAX_SERVERS

· Use the default for MAX_SGA SQL Apply parameter

· Set the _EAGER_SIZE SQL Apply parameter to 1000

· Set the APPLY_DELAY SQL Apply parameter to be greater than detection interval

· Set the TRANSACTION_CONSISTENCY SQL Apply parameter

o For a disaster recovery solution or when the SQL Apply engine needs to catch-up, NONE transaction consistency is provided.

o If the standby database has only one instance (non Real Application Clusters), then choose READ_ONLY.

o If the standby database has multiple instances (Real Application Clusters), then choose FULL.

· Skip SQL Apply for unnecessary objects