[Oracle] Create dataguard configuration in Oracle

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!