Out of curiosity I wanted to figure out when and how often startup triggers fire in RAC 19c. Do they fire when all instances of the database are up (e.g. only once), or do they fire for once each instance? A little experiment reveals the behaviour. As the title reads this is mostly academic, I hope it’ll save me 5 minutes some time in the future. Maybe it saves you 5 minutes right now?
I am using Oracle 19.8.0 RAC in a two node configuration, with the database created as a container database (CDB). To figure out what happens with the startup trigger I created a little bit of infrastructure, like so:
CREATE TABLE c##martin.logtab ( id NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL, ts TIMESTAMP(6) NOT NULL, inst_id NUMBER NOT NULL, inst_name VARCHAR2(200) NOT NULL, CONSTRAINT pk_logtab PRIMARY KEY ( id ) ) TABLESPACE users;
Normally I’d be more careful about the sequence’s properties, especially in RAC, but not with a startup trigger …
With the logging table in place I can create a stored procedure to log the data:
CREATE OR REPLACE PROCEDURE c##martin.logproc AS BEGIN INSERT INTO c##martin.logtab ( ts, inst_id, inst_name ) VALUES ( systimestamp, sys_context('userenv', 'instance'), sys_context('userenv', 'instance_name') ); COMMIT; END; /
This way I can see what’s going on. Now the only part missing is the actual startup trigger. It has to be created by SYS.
CREATE OR REPLACE TRIGGER sys.startup_trig AFTER STARTUP ON DATABASE BEGIN c##martin.logproc; END; /
Restart the database
Now that everything is in place I can restart the database to see what happens.
[oracle@rac19pri1 ~]$ srvctl stop database -db RCDB ; srvctl start database -db RCDB [oracle@rac19pri1 ~]$ srvctl status database -d RCDB -verbose Instance RCDB1 is running on node rac19pri1. Instance status: Open. Instance RCDB2 is running on node rac19pri2. Instance status: Open. [oracle@rac19pri1 ~]$
Looking into the logging table after the database came up, I can see the trigger fired twice:
ID TS INST_ID INST_NAME ---------- ------------------------------ ---------- ------------------------------ 1 11-JAN-21 07.08.04.205358 PM 2 RCDB2 21 11-JAN-21 07.08.04.465076 PM 1 RCDB1
This proves that startup triggers fire per instance. Which makes total sense now that I think about it, but it’s nice to know for sure. Well, for some people that might be.
In a RAC environment a startup trigger fires once the instance opens, irrespective of how many instances there are.
You should probably know that triggers have somewhat fallen out of fashion and should only be used with great care and after understanding their advantages as well as disadvantages. Reading the 19c Database PL/SQL Language Reference is a first step towards understanding trigger concepts.