[Oracle] Create dataguard configuration in Oracle
ENVIRONMENT
- Oracle 12
- Red Hat Linux 7
- DBA privileges
VARIABLES
- PRIMARY_DB_NAME: database unique name of the primary database
- STANDBY_DB_NAME: database unique name of the standby database
- PRIMARY_DB_HOST: hostname or IP address of the server where the primary database resides
- PRIMARY_DB_PORT: port on the primary server where the database listens to
- STANDBY_DB_HOST: hostname or IP address of the server where the standby database resides
- STANDBY_DB_PORT: port on the standby server where the database listens to
- ORACLE_HOME_DIR: directory where Oracle binary and configuration files are located. For simplicity, it is the same in primary and standby sites
Hi all,
in this tutorial we are going to create a dataguard configuration in Oracle. All the network and OS configuration is supposed to be already done.
Primary database configuration
First, the log_archive_config must be configured in the following way:
show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(PRIMARY_DB_NAME,STANDBY_DB_NAME)
Next, assuming that the log_archive_dest_1 is the Recovery Area, the log_archive_dest_2 must be configured:
show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="STANDBY_DB_NAME_DG", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="STANDBY_DB_NAME" net_timeout=30, valid_for=(online_logfile,all_roles)
In this example, the log destination is configured in syncronous mode and the net service name is STANDBY_DB_NAME_DG. Consequently, the tnsnames.ora must be correctly configured with the following entries:
STANDBY_DB_NAME_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY_DB_HOST)(PORT = STANDBY_DB_PORT))
(CONNECT_DATA =
(SERVICE_NAME = STANDBY_DB_NAME)
)
)
PRIMARY_DB_NAME_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY_DB_HOST)(PORT = PRIMARY_DB_PORT))
(CONNECT_DATA =
(SERVICE_NAME = PRIMARY_DB_NAME)
)
)
Both entries for the primary and standby databases are added to the tnsnames.ora file in order to be able to use the same file for both primary and standby sites.
Another important parameter is the following:
show parameter FAL_SERVER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string (PRIMARY_DB_NAME_DG, STANDBY_DB_NAME_DG)
Next, static listeners must be configured on primary and standby servers. For example, the following entries can be inserted in the listener.ora files of the listener dedicated to dataguard both in primary and standby hosts:
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY_DB_HOST)
#SID_NAME must match the instance running on this node
(SID_NAME = PRIMARY_DATABASE_INSTANCE)
#oracle_home must match the intended instance
(ORACLE_HOME = ORACLE_HOME_DIR)
)
(SID_DESC =
(GLOBAL_DBNAME = STANDBY_DB_HOST)
#SID_NAME must match the instance running on this node
(SID_NAME = STANDBY_DATABASE_INSTANCE)
#oracle_home must match the intended instance
(ORACLE_HOME = ORACLE_HOME_DIR)
)
After that, just reload the listeners on both hosts.
Lastly, the following two parameters need to be configured:
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> show parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
Duplicate
Now, it is necessary to duplicate the primary database to the standby site in order to have a consistent replica. Just follow this article to do that: Duplicate from active
NOTE: the only difference from the article is the exact syntax of the duplicate command.
duplicate target database to STANDBY_DB_NAME from active database;
Dataguard configuration
Once the database has been duplicated and the standby has started to apply the logs, check that the following parameter is true (and if it is not, just enable it):
SQL> show parameter DG_BROKER_START
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
Next, create the dataguard configuration trough the broker:
DGMGRL /
DGMGRL> create configuration 'MY_FIRST_DATAGUARD_CONFIG' as primary database is 'PRIMARY_DB_NAME' connect identifier is PRIMARY_DB_NAME_DG;
DGMGRL> add database 'STANDBY_DB_NAME' as connect identifier is STANDBY_DB_NAME_DG;
DGMGRL> enable configuration
The configuration should look like the following:
DGMGRL> show configuration
Configuration - MY_FIRST_DATAGUARD_CONFIG
Protection Mode: MaxPerformance
Members:
PRIMARY_DB_NAME - Primary database
STANDBY_DB_NAME - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 30 seconds ago)
That’s all. See you in the next tutorial!