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 |
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 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 |