Configure Oracle Data Guard

This playbook provides scripts to help you configure your Oracle Data Guard environment. These scripts setup a standby database for an existing primary database using the restore from service feature and Oracle Data Guard Broker.

The dg-setup-scripts use a DG_properties.ini file to define the primary and standby environments.

The scripts create 2 tar files, a tar of the primary password file and a tar of the primary Transparent Data Encryption (TDE) wallet, that you'll copy to the standby database node.

The scripts do not make changes to the operating system, such as net.core.rmem_max and net.core.wmem_max, or maximum transmission unit (MTU). It is a best practice to adjust the net.core.rmem_max and net.core.wmem_max for optimal redo transport.

The following tasks describe how to run the scripts to configure Oracle Data Guard for your database.

Define the Environment Variables for Oracle Data Guard

Use a DG_properties.ini file to prepare the primary system for an Oracle Data Guard configuration. The scripts use the parameters in the DG_properties.ini to configure the primary and standby systems. The file contains all the input parameters required by the scripts. See Examples of DG_properties.ini Files in Explore More.

A sample DG_properties.ini file is available in the dg-setup-scripts zip file. You can customize the file with your environment's values.

Download the dg-setup-scripts zip file and extract the contents.
Edit the sample DG_properties.ini property file in a Linux operating system to customize the file with your environment's specific values.
Editing in Linux avoids encoding related issues, such as getting Windows CRLF at the end of the lines.

Edit the General Properties section to define the export parameters, if the system is an Oracle Real Application Clusters (Oracle RAC), and if the password file is in Oracle Automatic Storage Management (Oracle ASM).

########################################################################################################
# GENERAL PROPERTIES
########################################################################################################
export ORACLE_OSUSER=oracle
export ORACLE_OSGROUP=oinstall
export GRID_OSUSER=grid
export GRID_HOME=/u01/app/19.0.0.0/grid
export ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle

export DB_NAME=DBTEST4	    # The database name (the value of db parameter db_name)
export SYS_USERNAME=sys

export RAC=NO	             # Set to YES (if DB is a RAC) or to NO (if single instance DB)
export PASSWORD_FILE_IN_ASM=YES  # Set to YES when the password file is placed in ASM (in case of RAC, it will always be placed in ASM).
export ADDITIONAL_STANDBY=NO     # Set to YES only if the primary db has another standby database already configured, and you are using the scripts to add a second standby.

Edit the Primary Env Properties section and change the parameters to define your primary system environment.

#########################################################################################################
# PRIMARY ENV PROPERTIES
#########################################################################################################
export A_PORT=1521					# The primary listener's port (scan's listener port if RAC)
export A_SERVICE=ORCL_PRI.primarydomainexample.com	# The default CDB service name in primary (format is <db_unique_name>.<primary_domain>) 
export A_DBNM=ORCL_PRI					# The DB UNIQUE NAME of primary DB

# For single instance (will be ignored if RAC=YES)
	export A_DB_IP=10.10.10.10			# Provide the primary listener's IP. This needs to be reachable from the standby DB.

# For RAC (will be ignored if RAC=NO)
	# Specify the PRIMARY RAC's scan IPs and scan address name
	export A_SCAN_IP1=10.10.10.1
	export A_SCAN_IP2=10.10.10.2
	export A_SCAN_IP3=10.10.10.3
	export A_SCAN_ADDRESS=primary-scan.primarydomainexample.com
	# Provide the ORACLE_SID of the primary RAC instances
	export A_SID1=ORCL1
	export A_SID2=ORCL2

export A_FILE_DEST="+DATA"              # the value of db_create_file_dest db parameter in primary DB
export A_ONLINE_LOG_DEST1="+RECO"       # the value of db_create_online_log_dest_1 parameter in primary DB
export A_RECOVERY_FILE_DEST="+RECO"     # the value of db_recovery_file_dest parameter in primary DB

# Other properties required by primary setup script
export TDE_LOC=/opt/oracle/dcs/commonstore/wallets/tde/${A_DBNM}	# The the TDE wallet folder in primary (where the .p12 file is located). Leave it EMPTY if TDE is not used.
export CREATE_PASSWORD_FILE=YES                                         # If password file already exists in primary and you do not want to override it, set this to NO.
export OUTPUT_WALLET_TAR=/tmp/PRIMARY_TDE_WALLET.GZ         		# Absolute file name for the output tar file that will be generated in primary, containing the primary TDE wallet.
export OUTPUT_PASWORD_TAR=/tmp/PRIMARY_PASSWORD_FILE.GZ        		# Absolute file name for the output tar file that will be generated in primary, containing the primary password file.

Edit the Standby Env Properties section and change the parameters to define your standby system environment.

########################################################################################################
# STANDBY ENV PROPERTIES
########################################################################################################
export B_PORT=1521							# The standby listener's port (scan's listener port if RAC)
export B_SERVICE=ORCL_STBY.standbydomainexample.com			# The default CDB service name in standby (format is <db_unique_name>.<secondary_domain>)
export B_DBNM=ORCL_STBY							# The DB UNIQUE NAME of standby DB

# For single instance (will be ignored if RAC=YES)
	export B_DB_IP=10.20.20.20	# Provide the primary listener's IP. This needs to be reachable from the standby DB

# For RAC (will be ignored if RAC=NO) 
	# Specify STANDBY RAC's scan IPs and scan address name
	export B_SCAN_IP1=10.20.20.1
	export B_SCAN_IP2=10.20.20.2
	export B_SCAN_IP3=10.20.20.3
	export B_SCAN_ADDRESS=standby-scan.standbydomainexample.com
	# Standby node's VIPs (provide the IPS, not the names)
	export B_VIP1=10.20.20.10
	export B_VIP2=10.20.20.20
	# Provide the ORACLE_SID of the standby RAC instances
	export B_SID1=ORCL1
	export B_SID2=ORCL2
	# (normally only needed in Exadata) Provide interconnect IPs if they must be specified in the parameter cluster_interconnects. Leave them empty if cluster_interconnects is empty.
        export B_INTERCONNECT_IP1=
        export B_INTERCONNECT_IP2=


export B_FILE_DEST="+DATA"              # the value of db_create_file_dest db parameter in standby DB
export B_ONLINE_LOG_DEST1="+RECO"       # the value of db_create_online_log_dest_1 parameter in standby DB
export B_RECOVERY_FILE_DEST="+RECO"     # the value of db_recovery_file_dest parameter in standby DB

# Other properties required by the standby setup script
export INPUT_WALLET_TAR=/tmp/PRIMARY_TDE_WALLET.GZ		   # Absolute file name for the input tar file that contains the primary TDE wallet.
export INPUT_PASWORD_TAR=/tmp/PRIMARY_PASSWORD_FILE.GZ	      # Absolute file name for the input tar file that contains the primary password file.
export B_TDE_LOC=/opt/oracle/dcs/commonstore/wallets/$B_DBNM/tde   # Absolute  path where the wallet files (.p12) will be created in standby hosts. 
# Verify which is the value used by your system, it may differ. Leave it empty if TDE is not used.

Upload the Scripts

Upload the scripts to the primary and secondary database hosts as follows:

Upload the following scripts and files to the primary database host or hosts:
- 1_prepare_primary_maa_parameters.sh: Upload to database host 1 (for either single instance or Oracle RAC).
- 2_dataguardit_primary.sh: Upload to all database nodes.
- create_pw_tar_from_asm_root.sh: Upload to all database nodes.
- DG_properties.ini: Upload to all database nodes.
For the primary database host or hosts, grant execute permissions on the scripts to the oracle OS user on all nodes.
Upload the following files to the new secondary database host or hosts:
- 3_dataguardit_standby_root.sh: Upload to all database nodes.
- DG_properties.ini: Upload to all database nodes.
For the secondary database host or hosts, grant execute permissions on the scripts to the root OS user.

Prepare the Oracle Data Guard Parameters for the Primary Database

Set the Oracle Maximum Availability Architecture (MAA) recommended Oracle Data Guard parameters on the primary database before configuring Oracle Data Guard. The 1_prepare_primary_maa_parameters.sh script configures the recommended MAA parameters for Oracle Data Guard and creates the standby log files.

Script name:

1_prepare_primary_maa_parameters.sh

Where to run:

In PRIMARY db host1 (regardless of whether the primary is an Oracle Real Application Clusters (Oracle RAC) or a single instance)

Run with user:

oracle

MAA provides architecture, configuration, and lifecycle best practices for Oracle Databases, enabling high-availability service levels for databases residing in on-premises, cloud, or hybrid configurations.

The script sets the following MAA recommended parameter values:

export DB_FLASHBACK_RETENTION_TARGET=1440
export DB_BLOCK_CHECKSUM=FULL
export DB_BLOCK_CHECKING=FULL
export DB_LOST_WRITE_PROTECT=TYPICAL
export LOG_BUFFER=256M
export STANDBY_FILE_MANAGEMENT=AUTO

Note:

Per MAA best practices, the parameter DB_BLOCK_CHECKING should be set to MEDIUM or FULL in the primary database. The script sets it to FULL. 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 database only.

Run the script to set the parameters before configuring Oracle Data Guard. Run this script only once, whether the primary is an Oracle Real Application Clusters (Oracle RAC) or a single instance database.

Log into the primary database node as an oracle user.
Run the 1_prepare_primary_maa_parameters.sh script in the primary database host for single instance database or Node1 for Oracle Real Application Clusters (Oracle RAC).
The script will prompt for the sys user’s password.

After the MAA parameters are set, the script creates standby logs in the ONLINE_LOG_DEST1 directory. The standby logs are the same size as the online logs (REDOLOG_SIZE). They have one more group than the online logs, but the same number of threads.

Prepare the Primary System for Oracle Data Guard

Prepare the primary hosts for an Oracle Data Guard configuration and create output files that are needed later in the standby database hosts.

Script name:

2_dataguardit_primary.sh

Where to run:

In PRIMARY db hosts.

If Oracle Real Application Clusters (Oracle RAC): run it first in the primary db host 1, and then in the primary db host 2.

Run with user:

oracle

The 2_dataguardit_primary.sh script creates the following output files:

TAR file containing the password file
TAR file containing the TDE wallet (only if TDE encryption is used).

Log into the primary database node as an oracle user.
Verify that the DG_properties.ini file is in the same folder as the 2_dataguardit_primary.sh script, and that it has been correctly customized with the environment values.
Run the 2_dataguardit_primary.sh script in the primary DB Node1.

The script will prompt for the sys user’s password.

When the password file is stored in ASM, the script will ask the user to run the create_pw_tar_from_asm_root.sh script.

The script creates a tar file for the password file and a tar file for the Transparent Data Encryption (TDE) wallet (if TDE is used).
If the primary is an Oracle Real Application Clusters (Oracle RAC) database, then run the script in Node2.
(Recommended) Set the operating system parameters for net.core.rmem_max, net.core.wmem_max to optimize redo transport.
If Oracle RAC, then optimize in both nodes.

See Explore More for a link to the MAA recommendations.
(Recommended) Set the Maximum transmission unit (MTU) to optimize redo transport.
If Oracle RAC, then optimize in both nodes.

See Explore More for a link to the MAA recommendations.

Copy the Output Files

Copy the output tar files generated from the primary database node and upload them to the secondary database hosts. If you have an Oracle RAC, then the tar files are created in Node1. Upload the tar files to both nodes in the secondary hosts.

Ensure that you place the tar files in the locations indicated by the properties INPUT_PASWORD_TAR and INPUT_WALLET_TAR (when TDE is used) that are defined in the DG_properties.ini file.

Prepare the Secondary System for Oracle Data Guard

Prepare the new secondary (standby) host, create the standby database, and configure Oracle Data Guard broker in the secondary database node.

Script name:

3_dataguardit_standby_root.sh

Where to run:

In new STANDBY db hosts.

If Oracle Real Application Clusters (Oracle RAC): run first in the standby db host 1, and then in standby db host 2.

Run with user:

root

The 3_dataguardit_standby_root.sh script prepares the new standby database hosts by deleting the existing database and configuring the required artifacts (TNS aliases, NET encryption, password file, and Transparent Data Encryption (TDE) wallet). Then it creates and configures the standby database using the Oracle Recovery Manager (RMAN) restore from service feature, and configures the Oracle Data Guard broker.

The 3_dataguardit_standby_root.sh script uses the environment variables that you defined in the DG_properties.ini file. If the secondary is an Oracle RAC database, then run the script in both nodes. Most of the actions are performed when the script runs in Node1 and a subset of the steps are performed when it runs in Node2. Don't run the script in Node2 before the script completes in Node1.

Log into the secondary database node as the root user.
For Oracle RAC, log into Node1.
Locate the 3_dataguardit_standby_root.sh script in the directory where you uploaded the script.
Verify that the DG_properties.ini file is in the same folder as the 3_dataguardit_standby_root.sh script, and that the customization in the script is correct.
Run the 3_dataguardit_standby_root.sh script.
The script will prompt for the sys user’s password.
If you have an Oracle RAC, then log into Node2 as root and run the 3_dataguardit_standby_root.sh script in Node 2 once the script completes in Node1.
(Recommended) Set the operating system parameters for net.core.rmem_max, net.core.wmem_max to optimize redo transport.
If Oracle RAC, then optimize in both nodes.

See Explore More for a link to Oracle Maximum Availability Architecture (MAA) recommendations.
(Recommended) Set the Maximum transmission unit (MTU) to optimize redo transport.
If Oracle RAC, then optimize in both nodes.

See Explore More for a link to MAA recommendations.

Add a TNS Entry for a New Standby Database

If you added a new standby database to an existing Oracle Data Guard instance (ADDITIONAL_STANDBY=YES), then add the TNS entry that points to the previously existing standby database, in the new standby database tnsnames.ora file, and conversely. Ensure that the standby databases are able to connect each other to the listener port.