Oracle trigger starting services in standby

by gipsy   Last Updated August 14, 2019 12:06 PM

I'm using Oracle 18.3 with pluggable database and I have an architecture like this:

  • host 10.0.0.1 has two instances: 1 PRIMARY and 1 STANDBY SYNC
  • host 10.0.0.2 has one instance: 1 STANDBY ASYNC

I want to manage a situation in which a trigger starts a different service according to the pluggable connected.

CREATE OR REPLACE TRIGGER managed_service_start
AFTER STARTUP ON DATABASE

DECLARE
pdb_role VARCHAR(30);
pdb_name VARCHAR(40);
open_mode VARCHAR(30);

BEGIN
SELECT Upper(sys_context ('userenv', 'con_name')) INTO pdb_name FROM dual;

SELECT database_role INTO pdb_role FROM v$database;   
IF pdb_role = 'PRIMARY' THEN       
   EXECUTE DBMS_SERVICE.START_SERVICE(pdb_name||'_RW');      
   EXECUTE DBMS_SERVICE.STOP_SERVICE(pdb_name||'_RO');  
ELSE
    SELECT open_mode INTO open_mode FROM v$database;
    IF open_mode LIKE 'READ ONLY WITH APPLY' THEN
       EXECUTE DBMS_SERVICE.START_SERVICE(pdb_name||'_RO');   
       EXECUTE DBMS_SERVICE.STOP_SERVICE(pdb_name||'_RW');
    ELSE
        EXECUTE DBMS_SERVICE.STOP_SERVICE(pdb_name||'_RW');   
        EXECUTE DBMS_SERVICE.STOP_SERVICE(pdb_name||'_RO');
   END IF;
END IF;
END;
/

I created the trigger in the pluggable contained in the primary instance and I would like to start 'my_pdb_rw' service in that one and 'my_pdb_ro' in pluggable contained in the async instance.

This worked with single instance db but not with cdb architecture. In fact the listener doesn't start any of those services RW and RO.

Is maybe wrong AFTER STARTUP ON DATABASE statement?



Answers 1


If you start a service that is already started or stop a service that is already stopped, you will get an error and the procedure stops(?)

Airell
Airell
August 14, 2019 12:01 PM

Related Questions




Oracle Standby License

Updated June 23, 2017 04:06 AM

Oracle DataGuard: Find primary from standby DB

Updated July 11, 2015 19:02 PM