unblock startup triggers

 Oracle has a lot of triggering events which enables a lot of useful actions. One of these events is AFTER STARTUP OF DATABASE. The idea is to set some actions whenever a database opens. These can be quite short time actions as checking the environment, ensuring some settings or application health checks. 
Of course there also can be long running tasks, like populate a table into buffer cache or doing some heavy lifting of PL/SQL into library cache. 

Unfortunately, this startup trigger is AFTER STARTUP, but at the same time it's kind of BEFORE OPEN as a little testcase shows: 

CREATE TRIGGER do_work_on_open
AFTER STARTUP ON DATABASE
BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => 'on_open',
                                   action_name => 'slow_task_one');
  dbms_session.sleep(30);
                                   
  DBMS_APPLICATION_INFO.set_module(module_name => 'on_open',
                                   action_name => 'slow_task_two');
  dbms_session.sleep(45);
  DBMS_APPLICATION_INFO.set_module(module_name => '🍺',
                                   action_name => '🍻');
END my_on_open_trigger;
/

Leads to a significant delay:

07:55:10 SQL> alter pluggable database freepdb1 open;

Pluggable database altered.

Elapsed: 00:01:16.31
07:56:41 SQL>

and also in alert.log it's visible the PDB is in state OPEN after the trigger completed. 

2023-10-22T07:55:25.049902+00:00
alter pluggable database freepdb1 open
2023-10-22T07:55:25.073129+00:00
FREEPDB1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
FREEPDB1(3):Autotune of undo retention is turned on.
FREEPDB1(3):Undo initialization recovery: Parallel FPTR complete: start:774697 end:774699 diff:2 ms (0.0 seconds)
FREEPDB1(3):[40010] Successfully onlined Undo Tablespace 2.
FREEPDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
2023-10-22T07:56:41.340929+00:00
Completed: Pluggable database FREEPDB1 opened read write
Completed: alter pluggable database freepdb1 open

That's all well intended, but sometimes it would be nice to have the DB available even when the startup tasks are not all completed so far. 

The trigger can not provide this feature, but the long running tasks can be delegated to scheduler jobs - and (if the tasks are independent) they even can run in parallel and this speed up the time until all activities are done - at the cost of higher resource consumption. 

A simple example can be: 

CREATE or replace TRIGGER do_work_on_open
AFTER STARTUP ON DATABASE
BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => 'on_open',
                                   action_name => 'fire_scheduler_jobs');
    dbms_scheduler.create_job 
    (  
      job_name      =>  'open_schedule_job_1',  
      job_type      =>  'PLSQL_BLOCK',  
      job_action    =>  q'[begin
        DBMS_APPLICATION_INFO.set_module(module_name => 'open_schedule_job',
                                   action_name => 'slow_task_one');
        dbms_session.sleep(30);
      DBMS_APPLICATION_INFO.set_module(module_name => '🍺',
                                   action_name => '🍻');
      end;                ]',  
      start_date    =>  sysdate,  
      enabled       =>  TRUE,  
      auto_drop     =>  TRUE,  
      comments      =>  'open_schedule_job 1');


    dbms_scheduler.create_job 
    (  
      job_name      =>  'open_schedule_job_2',  
      job_type      =>  'PLSQL_BLOCK',  
      job_action    =>  q'[begin
        DBMS_APPLICATION_INFO.set_module(module_name => 'open_schedule_job',
                                   action_name => 'slow_task_two');
        dbms_session.sleep(45);
      DBMS_APPLICATION_INFO.set_module(module_name => '🍺',
                                   action_name => '🍻');
      end;                ]',  
      start_date    =>  sysdate,  
      enabled       =>  TRUE,  
      auto_drop     =>  TRUE,  
      comments      =>  'open_schedule_job 2');

  DBMS_APPLICATION_INFO.set_module(module_name => '🍺',
                                   action_name => '🍻');
END my_on_open_trigger;
/

Make sure the owner of the trigger has direct CREATE (any) JOB permissions to avoid ORA-27486: insufficient privileges.

With this improved startup trigger, the startup command is fast:

09:18:07 SQL> alter pluggable database freepdb1  open;


Pluggable database altered.

Elapsed: 00:00:01.21
09:18:14 SQL>

alert.log is happy:

2023-10-22T09:18:13.546272+00:00
FREEPDB1(3):alter pluggable database freepdb1  open
2023-10-22T09:18:13.568044+00:00
FREEPDB1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
FREEPDB1(3):Autotune of undo retention is turned on.
FREEPDB1(3):Undo initialization recovery: Parallel FPTR complete: start:5743210 end:5743212 diff:2 ms (0.0 seconds)
FREEPDB1(3):[40010] Successfully onlined Undo Tablespace 2.
FREEPDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
2023-10-22T09:18:14.737334+00:00
Completed: Pluggable database FREEPDB1 opened read write
FREEPDB1(3):Completed: alter pluggable database freepdb1  open

And the jobs are really executed: 

col job_name for A20
select job_name, req_start_date, run_duration, status
from DBA_SCHEDULER_JOB_RUN_DETAILS 
where job_name like 'OPEN_SCHEDULE_JOB%'
  and req_start_date > to_date('2023-10-22 09:18')
order by log_date asc;

JOB_NAME             REQ_START_DATE                     RUN_DURATION        STATUS                        
-------------------- ---------------------------------- ------------------- ---------
OPEN_SCHEDULE_JOB_1  2023-10-22 09:18:14,680948000 GMT  +00 00:00:30.000000 SUCCEEDED                     
OPEN_SCHEDULE_JOB_2  2023-10-22 09:18:14,685483000 GMT  +00 00:00:45.000000 SUCCEEDED                     

A small addition in case this approach is required to do activities on a specific instance in RAC: dbms_scheduler.create_job does not (directly) have any method to pin the job execution to an instance. To circumvent this, a specific SERVICE must be created and bound to the instance. This service can be used in a dedicated schedulers JOB CLASS and this specific class can be used in DBMS_SCHEDULER.CREATE_JOB. Don't forget to GRANT EXECUTE on this JOB CLASS to the calling user to avoid ORA-27486: insufficient privileges