Trigger for automatic connection from Primary to Standby database

I was asked by one of my readers about “transparent transition” between the Primary and Standby databases when using a Data Guard environment. 

The question sounded relatively simple: how do you configure the clients so it will automatically work with the active site at any point in time?

The truth is that the solution is a lot easier than what you might think, and I even got to implement it in on an Oracle 19c database.

To resolve this issue, all we can do is to create a service for Oracle and enable at any point in time only on one side (the primary side) to turn it on.

In the first step, we need to create a Service in the primary environment:

SQL> begin
dbms_service.create_service('ORCLPROD','ORCLPROD');
end;
/  

PL/SQL procedure successfully completed.

Start the service:-

SQL> begin
 DBMS_SERVICE.START_SERVICE('ORCLPROD');
end;
/ 

PL/SQL procedure successfully completed.

Now create the Trigger as role Primary- myapptrigg


SQL> create trigger myapptrigg after startup on database
declare
 v_role varchar(30);
 begin
 select database_role into v_role from v$database;
 if v_role = 'PRIMARY' then
 DBMS_SERVICE.START_SERVICE('ORCLPROD');
 else
 DBMS_SERVICE.STOP_SERVICE('ORCLPROD');
 end if;
end;
/  

Trigger created.

If we are using TAF (i.e. Transparent Application Failover) then we have to add a few more parameters to create the service:

SQL> begin
 dbms_service.modify_service
 ('ORCLPROD',
 FAILOVER_METHOD => 'BASIC',
 FAILOVER_TYPE => 'SELECT',
 FAILOVER_RETRIES => 200,
 FAILOVER_DELAY => 1);
end;
/  

PL/SQL procedure successfully completed.

Crosscheck the service :-

set lines 200 pages 200;
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT name,network_name FROM dba_services where name='ORCLPROD' ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
ORCLPROD                         ORCLPROD

[oracle@rac201dms ~]$ lsnrctl status LISTSENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-FEB-2022 07:30:44

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

TNS-01101: Could not find listener name or service name LISTSENER
[oracle@rac201dms ~]$
[oracle@rac201dms ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-FEB-2022 07:30:51

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                18-AUG-2021 05:29:49
Uptime                    169 days 3 hr. 1 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /applications/oracle/g19.3.0/grid_home/network/admin/listener.ora
Listener Log File         /applications/oracle/g19.3.0/grid_base/diag/tnslsnr/rac201dms/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.1.22)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.1.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac201dms.samik.com)(PORT=5500))(Security=(my_wallet_directory=/applications/oracle/d19.3.0/oracle_home/admin/XPLIVEGC/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac201dms.samik.com)(PORT=2200))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_ARCH" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCRVD" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCLPROD" has 1 instance(s).
  Instance "ORCLPRD1", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@rac201dms ~]$
[oracle@rac201dms ~]$

ORCLPROD=
(DESCRIPTION_LIST=
            (LOAD_BALANCE=OFF)
            (FAILOVER=ON)
            (DESCRIPTION=
              (CONNECT_TIMEOUT=5)
              (RETRY_COUNT=3)
              (ADDRESS_LIST=
                (LOAD_BALANCE=ON)
                  (ADDRESS=(PROTOCOL=TCP)(HOST=172.20.1.22)(PORT=1521))
                  (ADDRESS=(PROTOCOL=TCP)(HOST=172.20.1.23)(PORT=1521))
             )
              (CONNECT_DATA=(SERVICE_NAME=ORCLPROD))
            )
            (DESCRIPTION=
              (ADDRESS_LIST=
                (LOAD_BALANCE=OFF)
                (ADDRESS= (PROTOCOL=TCP)(HOST=172.20.1.150)(PORT=1521))
             )
              (CONNECT_DATA=(SERVICE_NAME=ORCLPROD))
            )
          )

SQL> set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
SELECT DBMS_METADATA.GET_DDL('TRIGGER','MYAPPTRIGG','SYS') FROM DUAL;SQL> SQL>

CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."MYAPPTRIGG" after startup on database
declare
 v_role varchar(30);
 begin
 select database_role into v_role from v$database;
 if v_role = 'PRIMARY' then
 DBMS_SERVICE.START_SERVICE('ORCLPRD');
 else
 DBMS_SERVICE.STOP_SERVICE('ORCLPRD');
 end if;
end;
ALTER TRIGGER "SYS"."MYAPPTRIGG" ENABLE

COLUMN name FORMAT A35
COLUMN network_name FORMAT A35
COLUMN ENABLED for a15
SELECT name,ENABLED,network_name FROM dba_services ORDER BY 1;


NAME                                ENABLED         NETWORK_NAME
----------------------------------- --------------- ------------------------------
ORCLPRD                             NO              ORCLPRD