- Configure a standby database for disaster recovery
- 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=oracleexport ORACLE_OSGROUP=oinstallexport GRID_OSUSER=gridexport GRID_HOME=/u01/app/19.0.0.0/gridexport ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/dbhome_1export ORACLE_BASE=/u01/app/oracleexport DB_NAME=DBTEST4 # The database name (the value of db parameter db_name)export SYS_USERNAME=sysexport 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 nameexport A_SCAN_IP1=10.10.10.1export A_SCAN_IP2=10.10.10.2export A_SCAN_IP3=10.10.10.3export A_SCAN_ADDRESS=primary-scan.primarydomainexample.com# Provide the ORACLE_SID of the primary RAC instancesexport A_SID1=ORCL1export A_SID2=ORCL2export A_FILE_DEST="+DATA" # the value of db_create_file_dest db parameter in primary DBexport A_ONLINE_LOG_DEST1="+RECO" # the value of db_create_online_log_dest_1 parameter in primary DBexport A_RECOVERY_FILE_DEST="+RECO" # the value of db_recovery_file_dest parameter in primary DB# Other properties required by primary setup scriptexport 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 nameexport B_SCAN_IP1=10.20.20.1export B_SCAN_IP2=10.20.20.2export B_SCAN_IP3=10.20.20.3export B_SCAN_ADDRESS=standby-scan.standbydomainexample.com# Standby node's VIPs (provide the IPS, not the names)export B_VIP1=10.20.20.10export B_VIP2=10.20.20.20# Provide the ORACLE_SID of the standby RAC instancesexport B_SID1=ORCL1export 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 DBexport B_ONLINE_LOG_DEST1="+RECO" # the value of db_create_online_log_dest_1 parameter in standby DBexport B_RECOVERY_FILE_DEST="+RECO" # the value of db_recovery_file_dest parameter in standby DB# Other properties required by the standby setup scriptexport 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: |
|
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: |
|
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 the2_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 optimizeredo 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: |
|
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 the3_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 the3_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 optimizeredo 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.