Oracle Startup and Role Change Triggers

Oracle Startup and Role Change Triggers

# Triggers to change service name for Dataguard Standby databases.

# Create the services

alter system set service_names = 'a';
alter system set service_names = 'b';

exec DBMS_SERVICE.CREATE_SERVICE('PROD','PROD');
exec DBMS_SERVICE.CREATE_SERVICE('STANDBY','STANDBY');

# Role Change Trigger

CREATE OR REPLACE TRIGGER service_name_trg AFTER DB_ROLE_CHANGE ON DATABASE DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('PROD');
ELSE
DBMS_SERVICE.START_SERVICE('STANDBY');
END IF;
END;

# Startup Trigger - will only fire after the database is open.
CREATE OR REPLACE TRIGGER startup_trg
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('PROD');
ELSE
DBMS_SERVICE.STOP_SERVICE('STANDBY');
END IF;
END;

# Diagnostics scripts for service status

SELECT name, network_name FROM dba_services;
SELECT service_id, name, network_name FROM gv$active_services;

# Assorted scripts
exec DBMS_SERVICE.STOP_SERVICE('PROD');
exec DBMS_SERVICE.STOP_SERVICE('STANDBY');
exec DBMS_SERVICE.DELETE_SERVICE('PROD');
exec DBMS_SERVICE.START_SERVICE('PROD');