Database trigger to start services

Database trigger to start services 






to Create service 

BEGIN
  DBMS_SERVICE.create_service(
    service_name => 'ANUJ_P',
    network_name => 'ANUJ_P'
  );
END;
/

====

BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'ANUJ_P'
  );
END;
/



-- SQL> exec DBMS_SERVICE.stop_SERVICE('ANUJ_P');

PL/SQL procedure successfully completed.






COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT 'ACTIVE' name,CON_ID,
       network_name
FROM   v$active_services
where 1=1
and NETWORK_NAME is not null
union all
SELECT 'DBA***' name,CON_ID,
       network_name
FROM   cdb_services
where 1=1
and NETWORK_NAME is not null
order by 2
;


SET LINESIZE 200 PAGESIZE 100

COLUMN CON_NAME      FORMAT A30 TRUNC HEADING 'CON_NAME'
COLUMN INSTANCE_NAME FORMAT A15 TRUNC HEADING 'INSTANCE_NAME'
COLUMN STATE         FORMAT A15 TRUNC HEADING 'STATE'

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;



CON_NAME                       INSTANCE_NAME   STATE
------------------------------ --------------- ---------------
EMPDBREPOS                     orcl258         OPEN
EMPDBREPOS                     oem             OPEN





CREATE OR REPLACE TRIGGER start_db_service_startup
AFTER STARTUP ON DATABASE
DECLARE
    v_role VARCHAR2(30);
    v_open_mode VARCHAR2(30);
BEGIN
    SELECT DATABASE_ROLE INTO v_role FROM V$DATABASE;

    IF v_role = 'PRIMARY' THEN
        EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE EMPDBREPOS OPEN';
        BEGIN
            DBMS_SERVICE.START_SERVICE('ANUJ_P');
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    ELSE 
        BEGIN
            SELECT OPEN_MODE INTO v_open_mode 
            FROM V$PDBS 
            WHERE NAME = 'EMPDBREPOS';
            
            IF v_open_mode NOT LIKE '%READ ONLY%' THEN
                EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE EMPDBREPOS OPEN READ ONLY';
            END IF;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;

        BEGIN
            DBMS_SERVICE.STOP_SERVICE('ANUJ_P');
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END IF;
END start_db_service_startup;
/





CREATE OR REPLACE TRIGGER start_db_service_role_change
AFTER DB_ROLE_CHANGE ON DATABASE
DECLARE
    v_role VARCHAR2(30);
    v_open_mode VARCHAR2(30);
BEGIN
    SELECT DATABASE_ROLE INTO v_role FROM V$DATABASE;

    IF v_role = 'PRIMARY' THEN
        EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE EMPDBREPOS OPEN';
        BEGIN
            DBMS_SERVICE.START_SERVICE('ANUJ_P');
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    ELSE 
        BEGIN
            SELECT OPEN_MODE INTO v_open_mode 
            FROM V$PDBS 
            WHERE NAME = 'EMPDBREPOS';
            
            IF v_open_mode NOT LIKE '%READ ONLY%' THEN
                EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE EMPDBREPOS OPEN READ ONLY';
            END IF;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;

        BEGIN
            DBMS_SERVICE.STOP_SERVICE('ANUJ_P');
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END IF;
END start_db_service_role_change;
/