In the previous Article, we have seen understanding and managing user connectivity in Data Guard Environment.
In this Article, we will see Creating After Startup Trigger which will manage service for different database roles.
Step 1: Create services in a Primary database with DBMS_SERVICE package.
Note: We need to create service and trigger in a primary database only that will be propagated to standby server with redo apply or SQL apply.
SQL> EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'DB_PRIM',NETWORK_NAME=>'DB_PRIM'); EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'DB_ADG',NETWORK_NAME=>'DB_ADG'); EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'SNAP_DG',NETWORK_NAME=>'SNAP_DG'); EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'LOGICAL_DG',NETWORK_NAME=>'LOGICAL_DG'); PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL>
Step 2: Create After startup trigger
CREATE OR REPLACE TRIGGER SER_AC_ROLE AFTER STARTUP ON DATABASE
DECLARE
DBROLE VARCHAR(30);
OPEN_MODE VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO DBROLE FROM V$DATABASE;
SELECT OPEN_MODE INTO OPEN_MODE FROM V$DATABASE;
IF DBROLE = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE ('DB_PRIM');
ELSIF DBROLE = 'PHYSICAL STANDBY' THEN
IF OPEN_MODE LIKE 'READ ONLY%' THEN
DBMS_SERVICE.START_SERVICE ('DB_ADG');
END IF;
ELSIF DBROLE = 'LOGICAL STANDBY' THEN
DBMS_SERVICE.START_SERVICE ('LOGICAL_DG');
ELSIF DBROLE = 'SNAPSHOT STANDBY' THEN
DBMS_SERVICE.START_SERVICE ('SNAP_DG');
END IF;
END;
/
Step 3: Check trigger is propagated to standby or not
SQL> SELECT TRIGGER_NAME,OWNER,TRIGGER_TYPE FROM DBA_TRIGGERS WHERE TRIGGER_NAME='SER_AC_ROLE'; TRIGGER_NAME OWNER TRIGGER_TYPE ------------------------------ ------------------------------ ---------------- SER_AC_ROLE SYS AFTER EVENT
To check trigger works correctly I am restarting my physical standby with Active Data Guard enabled.
Check service before restart:
SQL> show parameter service; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string STD_TESTDB SQL>
Restart the standby database :
SQL> STARTUP FORCE ORACLE instance started. Total System Global Area 663908352 bytes Fixed Size 2256192 bytes Variable Size 578814656 bytes Database Buffers 79691776 bytes Redo Buffers 3145728 bytes Database mounted. Database opened. SQL>
See service name
SQL> SHOW PARAMETER SERVICE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string DB_ADG SQL>
We can see here DB_ADG service is enabled. So whenever a client needs to access this service it will always up with correct role regardless of a database server.
Stay tuned for More articles on Oracle DataGuard
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel : https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
