How to Configure Data Guard Parameters for a Primary and Physical Standby Database (Doc ID 2504633.1

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 19.3.0.0.0 [Release 11.2 to 18]
Information in this document applies to any platform.
This note is intended for customers that are not using the Data Guard Broker. The Data Guard Broker configures and manages the Data Guard Primary and Physical Standby database parameters for you.

GOAL

To configure Data Guard parameters within a primary and physical standby environment.    This information applies to Data Guard in both RAC and Non-RAC enviroment.  

Note:  For examples of creating the Tns alias’s for Chicago and Boston see step 8 “TNS Aliases used in the Oracle Data Guard configuration” within Creating a Physical Standby using RMAN Duplicate (RAC or Non-RAC) (Note 1617946.1). 

SOLUTION

I. Initialization Parameters setup on the primary database

On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and apply services when the primary database is transitioned to the standby role.

The following example shows the primary role initialization parameters that you maintain on the primary database. This example represents an Oracle Data Guard configuration with a primary database located in Chicago and one physical standby database located in Boston.

 

Database	Db_unique_name	Oracle Net Service Name
Primary	chicago	chicago
Standby	boston	boston

 

Setup parameters needed to control how redo transport is transmitted to the standby system. These parameters include:

log_archive_config - for controlling the Redo shipping and receiving to within this Data guard configuration list of db_unique_names only
log_archive_dest_1 – for local archiving
log_archive_dest_2 – for remote archiving

There are several options to these parameters.   Review the Oracle documentation for all available options.  In general:

1. log_archive_config is set to a list of db_unique_name(s) of ALL databases in the dataguard environment. For example:

LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'

2. log_archive_dest_1 is set to the local location where the primary database will be writing it’s archivelog files. If using an FRA, the location should be set to use_db_recovery_file_dest. For Example:

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Chicago'

    ALL_LOGFILES indicates the location to store all online logfiles and standby logfiles.
    ALL_ROLES     indicates the location if the database is in either primary or standby role.  Best practice is to include the db_unique_name of the database.

Note: setting LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST’ will default to valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=<default to current db_name or db_unique_name if specified for the Primary database>.

If an FRA is not configured and you want the primary archivelog files to be created on a file system file, the parameter setting would be something like:

LOG_ARCHIVE_DEST_1='LOCATION=/<path for archives>/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Chicago'

3. log_archive_dest_2 is set to the standby service. Normally a tns alias name for the standby is created for the standby service in the tnsnames.ora. The service will be used to send redo remotely to the standby. For example:

LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'

  Notice that now, we include in the Valid_for ONLINE_LOGFILES and PRIMARY_ROLE, meaning this log_archive_dest will ship the primary database online logfiles only when the database role is PRIMARY.

Note: We are using ASYNC transport on log_archive_dest_2 example. You may change to SYNC for Max Availability or Max Protection modes. SYNC NOAFFIRM is allowed in 12.1 and up for Max Availability mode. 

Note: There are other options to these parameters, review the Oracle Data Guard Administration documentation under "Creating a Physical Standby Database" for details https://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00200

The below parameters are set in the primary and will only used when the database is in the standby Role.  They are set for switchover purposes:

4. Fal_server is set to the standby service for gap fetching when after switchover the primary takes on the standby Role. For Example:

FAL_SERVER=boston

5. Db_file_name_convert converts the standby path(Boston) to the primary path (Chicago).  This conversion is necessary when primary becomes the standby so any added datafiles are created in the correct location. For Example:

DB_FILE_NAME_CONVERT='/boston/','/chicago/'

 

Note:  if db_file_name_convert is not set then it uses the same paths as primary to create the datafiles.

6. Log_file_name_convert converts the standby path(Boston) to primary path(Chicago).  This conversion is necessary when the primary becomes the standby so online and standby redo logfiles are created in the correct location. For Example:

LOG_FILE_NAME_CONVERT='/boston/','/chicago/'

7. Standby_file_management should be set to AUTO so when the primary is in standby role, datafiles are automatically created (based on db_file_name_convert setting).  Setting this parameter MANUAL requires the DBA to manually add/rename the new datafiles to the standby.

STANDBY_FILE_MANAGEMENT=AUTO

 

II.  Initialization Parameter setup on the standby database.  

Although most of the initialization parameter settings in the primary parameter file are also appropriate for the physical standby database, some modifications must be made to the standby parameter file:

1. Db_name is set to the primary db_name. The standby’s db_name must be the same as the primary. For Example:

DB_NAME=Chicago

2. Db_unique_name is set to a different db_unique_name from the primary. For example:

DB_UNIQUE_NAME=boston

3. Log_archive_config is set to the db_unique_name(s) of ALL databases in this dataguard environment. For Example:

LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'

4. Db_file_name_convert is set the opposite of the primary as the datafiles' location is converted from the primary location to the standby location.  For example:

DB_FILE_NAME_CONVERT='/chicago/','/boston/'

5. Log_file_name_convert is set the opposite of the primary as the log files' location is converted from the primary location to the standby location. For Example:

LOG_FILE_NAME_CONVERT='/chicago/','/boston/'

6. Log_archive_format is set to the same format as the primary. For example:

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

7. Log_archive_dest_1 is set using the same rules for the primary log_archive_dest_1 apply to the standby except that you would set the standby db_unique_name.  For example:

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=boston'

8. Log_archive_dest_2 is set to the primary service. Normally a tns alias name for the primary is created for the primary service in the tnsnames.ora. The service will be used to send redo remotely to the primary after switchover when the primary becomes a standby. This is only used when the database is in the primary role.  For example:

LOG_ARCHIVE_DEST_2='SERVICE=chicago ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=chicago'

9. Remote_login_passwordfile is set when using password file authentication. Copy the password file from the primary to the standby and rename it to the standby’ database's ORACLE_SID respectively.  For example:

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

10. Standby_file_management is set to AUTO. This setting causes the standby database to automatically create newly added datafile based on db_file_name_convert setting.  If db_file_name_convert is not set, then the datafile is created using the same path as used by the primary to create the datafiles.   Setting this parameter MANUAL means that you will manually add any new datafiles created in the primary to the standby. For example:

STANDBY_FILE_MANAGEMENT=AUTO

11. Fal_server is set to the primary service for gap fetching resolution. For Example:

FAL_SERVER=Chicago

 

Note: Ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases. If the values differ, then redo transport services may be unable to transmit redo data from the primary database to the standby databases.

Note: Review the initialization parameter file for additional parameters that may need to be modified. For example, you may need to modify the dump destination parameters if the directory location on the standby database is different from those specified on the primary database.