Client Failover For Data Guard Connections

admin

Goal

In a Data Guard environment  primary database  is open in read write mode and the standby database 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
Note: Starting from 12cR1 (12.1), you can consider using Global Data Services to seamlessly manage client connections for dataguard or Golden Gate replicated database

Also check below technical brief for latest information on this topic:
Client Failover Best Practices for Highly Available Oracle Databases
http://www.oracle.com/technetwor ... ailover-2280805.pdf
Application Continuity with Oracle Database12c Release 2
http://www.oracle.com/technetwor ... -wp-12c-1966213.pdf

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 databaseDECLARE  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 = standby.oracle.com)(PORT = 1521))       (ADDRESS = (PROTOCOL = TCP)(HOST = primary.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
If you get below error when trying to start the service on standby:PRCD-1084 : Failed to start service stby_dbPRCR-1079 : Failed to start resource ora.sby11gR2.stby_db.svcCRS-5017: The resource action "ora.sby11gR2.stby_db.svc start" encountered the following error:ORA-44317: database open read-onlyORA-06512: at "SYS.DBMS_SERVICE", line 478ORA-06512: at "SYS.DBMS_SERVICE", line 229ORA-06512: at line 1Then as workaroud, follow below steps: a. Start the service on primary instead:[oracle@vmOraLinux6 ~]$ srvctl start service -d ora11gR2 -s stby_db b. Perform a few log switches on primary and allow standby to catch up. c. Now, the service should start on standby:[oracle@vmOraLinux6 ~]$ srvctl start service -d sby11gR2 -s stby_dbd. Stop the service on primary:[oracle@vmOraLinux6 ~]$ srvctl stop service -d ora11gR2 -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 = standby.oracle.com)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
     )
    (CONNECT_DATA =
       (SERVICE_NAME = stby_db)
    )
  )

Also see the MAA Technical Brief "Client Failover Best Practices for Data Guard 11g Release 2" for further Details and Reference.