Skip to main content

11g AFTER STARTUP Trigger

AFTER STARTUP trigger checks the database role and open mode of the database. Based on the values it starts the appropriate service using DBMS_SERVICE.START_SERVICE procedure.


Here is an example of AFTER STARTUP trigger:
CREATE TRIGGER SERVICE_TRG AFTER STARTUP ON DATABASE
DECLARE
DBROLE VARCHAR(30);
OPENMODE VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO DBROLE FROM V$DATABASE;
SELECT OPEN_MODE INTO OPENMODE FROM V$DATABASE;
IF DBROLE = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('PRIM');
ELSIF DBROLE = 'PHYSICAL STANDBY' THEN
IF OPENMODE = 'READ ONLY' THEN
DBMS_SERVICE.START_SERVICE('RTQ');
END IF;
ELSIF DBROLE = 'LOGICAL STANDBY' THEN
DBMS_SERVICE.START_SERVICE('LSBY');
ELSIF DBROLE = 'SNAPSHOT STANDBY' THEN
DBMS_SERVICE.START_SERVICE('SNAP');
END IF;
END;

Comments

Popular posts from this blog