I'm using Oracle 18.3 with pluggable database and I have an architecture like this:
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?
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(?)