How To Configure Client Failover For Dataguard Connections Using Database Services

In a Data Guard environment you have primary database which is open in read write mode and the standby database which can be in mount mode or open in read only mode for reporting purpose.

This document describes how to setup clients to connect to Data Guard databases (primary and standby) and configure automatic client failover such that in case there is role change due to switchover or failover, the client connections should still be valid i.e. the clients that need to connect to read only standby should always connect to the current standby irrespective of which database in the Data Guard configuration is currently in standby role and same for primary connections.

This goal is achieved via database services.

In 11gR2, we have the concept of role-based database services. For Data Guard environments running older release, this is achieved via a database startup trigger.

Pre 11.2 Configuration
11.2 or later Configuration

Solution

Pre 11.2 Configuration:

+ On the current primary, create 2 services, one to connect to the primary (prim_db) and another (stby_db) to connect to the read only standby:

SQL> exec dbms_service.create_service('prim_db','prim_db');
SQL> exec dbms_service.create_service('stby_db','stby_db');

+ On the current primary, start the service that is needed to connect to primary:

SQL> exec dbms_service.start_service('prim_db');

+ Now, on the current primary, create the trigger to manage these services accordingly:

CREATE OR REPLACE TRIGGER startDgServices after startup on database
DECLARE
  db_role VARCHAR(30);
  db_open_mode VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE;
  IF db_role = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('prim_db'); END IF;
  IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN DBMS_SERVICE.START_SERVICE('stby_db'); END IF;
END;
/ 

+ Note down the current online redo log sequence on primary and switch the current logfile:

SQL> select thread#, sequence# from v$log where status = 'CURRENT';
SQL> alter system archive log current;

Ensure that the archive with sequence# which was shown as current is shipped and applied on standby. This ensures that the redo from CREATE TRIGGER command is applied on standby.
Now, shutdown and startup the standby database to make the trigger take effect:

SQL> shut immediate;
SQL> startup;

.
+ Configure client TNSNAMES.ORA entry. In below example PRIM_DB should be used by clients that need to connect to primary database. STBY_DB should be used by clients that need to access the read only standby database:

PRIM_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (FAILOVER = ON)
      (LOAD_BALANCE = OFF)
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prim_db)
    )
  )
  
STBY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (FAILOVER = ON)
      (LOAD_BALANCE = OFF)
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby_db)
    )
  )  

Configuration in 11.2 or later release:

We will be using role-based database services introduced in 11.2. To use role-based services, Oracle Clusterware must be installed and active on the primary and standby sites for both single instance (using Oracle Restart) and Oracle RAC databases.

+ On the primary and standby hosts create the service (prim_db) that the clients will use to connect to the primary database. The service should be created such that it is associated with and runs on the database when it is in the ‘PRIMARY’ database role:

On primary:
[oracle@vmOraLinux6 ~]$ srvctl add service -d ora11gR2 -s prim_db -l PRIMARY -e SESSION -m BASIC -w 10 -z 10

On standby:
[oracle@vmOraLinux6 ~]$ srvctl add service -d sby11gR2 -s prim_db -l PRIMARY -e SESSION -m BASIC -w 10 -z 10

+ Start the primary database service (prim_db) on the current primary:

[oracle@vmOraLinux6 admin]$ srvctl start service -d ora11gR2 -s prim_db

+ Next, on the primary and standby hosts create the service (stby_db) that the clients will use to connect to the read only standby database. The service should be created such that it is associated with and runs on the database when it is in the ‘PHYSICAL_STANDBY’ database role:

On primary:
[oracle@vmOraLinux6 ~]$ srvctl add service -d ora11gR2 -s stby_db -l PHYSICAL_STANDBY -e SESSION -m BASIC -w 10 -z 10

On standby:
[oracle@vmOraLinux6 ~]$ srvctl add service -d sby11gR2 -s stby_db -l PHYSICAL_STANDBY -e SESSION -m BASIC -w 10 -z 10

+ Now, start the standby service (stby_db) on standby host:

[oracle@vmOraLinux6 ~]$ srvctl start service -d sby11gR2 -s stby_db

+ Configure client TNSNAMES.ORA entry. In below example PRIM_DB should be used by clients that need to connect to primary database. STBY_DB should be used by clients that need to access the read only standby database:

PRIM_DB =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (FAILOVER = ON)
       (LOAD_BALANCE = OFF)
       (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = prim_db)
     )
  )

STBY_DB =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (FAILOVER = ON)
       (LOAD_BALANCE = OFF)
       (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
     )
    (CONNECT_DATA =
       (SERVICE_NAME = stby_db)
    )
  )

Thanks
Srini