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;