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

ORA-21700 in data dictionary

This whole story started with a nice little ticket:

When I try to edit a scheduler job in <DB>  with user <USER> I am getting error:
ORA-21700: object does not exist or is marked for delete
even I can see the object and the object is not deleted or marked as delete.

At first I could not reproduce the error, so I asked for something more specific, and I got 2 screenshots. (one shown here)
Unfortunately (for me) the screenshots were from Toad and PL/SQL Developer - 2 tools I don't have install or licensed or know anything about.






So, to give it a try I connected to the DB with SQL Developer and randomly clicked around several scheduler related tabs. I was lucky to generate ORA-21700 there also. And the big value of SQL Developer in this case is the log it has for all SQLs it's sending to the DB.

It shows the statement which failed - together with it's arguments.

select job_name, argument_name, argument_position, argument_type, value, out_argument 
from SYS.Dba_SCHEDULER_JOB_ARGS  
WHERE "JOB_NAME" = :OBJECT_NAME  AND "OWNER" = :OBJECT_OWNER
order by argument_position

With this query it's much easier to analyse the problem. Executing the quera ys the specific user really created an ORA-21700. But where does it come from? DBMS_UTILITY.EXPAND_SQL_TEXT is very handy to get the "real" sql for a query with views. Of course it's possible to see the views text, but if it's using views again, this can be real work (which I try to avoid).

Trying all the tables in the expanded query the culprit was found easily: sys.SCHEDULER$_JOB_ARGUMENT
The table is not complicated:

NAME            DATA TYPE            NULL  DEFAULT    COMMENTS
*OID            NUMBER               No               
*POSITION       NUMBER               No               
 NAME           VARCHAR2(128 BYTE)   Yes              
 TYPE_NUMBER    NUMBER               Yes              
 USER_TYPE_NUM  NUMBER               Yes              
 VALUE          ANYDATA              Yes              
 FLAGS          NUMBER               Yes              

And with some more trying the problem was tracked down to 1 column in 1 row:

select VALUE from sys.SCHEDULER$_JOB_ARGUMENT where oid=2736824 

This shows a little misunderstanding in the first request, the error code and the problem. The full error description is

ORA-21700: object does not exist or is marked for delete
21700. 00000 -  "object does not exist or is marked for delete"
*Cause:    User attempted to perform an inappropriate operation to
           an object that is non-existent or marked for delete.
           Operations such as pinning, deleting and updating cannot be
           applied to an object that is non-existent or marked for delete.
*Action:   User needs to re-initialize the reference to reference an
           existent object or the user needs to unmark the object.

The objects it is referring to are things like a TYPE, whereas in the original content it was interpreted as a SCHEDULER JOB.

But why is there a problem with an anydata column at all? couldn't it hold simply ANY data?
there is a Note at MOS which describes the situation quite good: Used Type Can Be Dropped Causing ORA-21700 When Querying A Table With SYS.ANYDATA Column ( Doc ID 1503628.1 )
When selecting an anydata column which contains atype, the given TYPE must be known. If the TYPE was deleted in the meantime, the column (and related functions like anydata.gettypename) will fail.
The block or row itself is not corrupted and it can be queried with dump().
Typ=58 Len=630: 0,1,0,0,0,0,0,1,0,0,5,189,61,90,2,96,9,0,0,0,0,0,2,80,0,0,0,0,0,...
Unfortunately I don't know the internal structure of this Type. Datatype 58 is opaque (DTYOPQ) which can hold ANYDATA, ANYTYPE, XMLTYPE, ANYDATASET (and maybe others). But how to distinguish which exactly, or how to interpred the bytes I got from dump() I couldn't find somewhere.

The problem in this particular database is this one row in sys.SCHEDULER$_JOB_ARGUMENT. To be visible in Dba_SCHEDULER_JOB_ARGS, there is a join of SCHEDULER$_JOB_ARGUMENT and SCHEDULER$_JOB on OID = OBJ#.
In my particular case there is no row anymore on SCHEDULER$_JOB. So no row is visible and there is nothing which can be dropped with DBMS_SCHEDULER.DROP_JOB. I have no idea how this happened. But it's there.

That's the situation to create a SR at MOS and ask for assistance.

Even a testcase is simply done:

CREATE OR REPLACE TYPE random_type AS OBJECT (
    n NUMBER
);
/

CREATE OR REPLACE PROCEDURE do_nothing (
    param1 IN random_type
) AS
BEGIN
    NULL;
END do_nothing;
/

DECLARE
    t random_type;
BEGIN
    t := random_type(NULL);
    t.n := 1;
    do_nothing(t);
END;
/

BEGIN
    dbms_scheduler.create_job(job_name => 'TEST_NOTHING', job_type => 'STORED_PROCEDURE', job_action => 'DO_NOTHING', number_of_arguments
    => 1, start_date => SYSDATE + 1, repeat_interval => NULL, end_date => NULL, enabled => false, 
auto_drop => true, 
comments => 'job to test parameter of ANYDATA'
    );
END;
/

DECLARE
    t random_type;
BEGIN
    t := random_type(NULL);
    t.n := 1;
    dbms_scheduler.set_job_anydata_value(job_name => 'TEST_NOTHING', argument_position => 1, argument_value => SYS.anydata.convertobject
    (t) );

END;
/

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

DROP TYPE random_type;

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

That's exactly where the ORA-21700 occurs.
In the testcase, a simple cleanup can be done by (trying to) execute this job, so it's cleared:

PAUSE "let's initiate some cleanup" 

begin
    dbms_scheduler.set_attribute(name => '"BERX"."TEST_NOTHING"', attribute => 'logging_level', value => dbms_scheduler.logging_full);
    dbms_scheduler.set_attribute_null(name => '"BERX"."TEST_NOTHING"', attribute => 'start_date');
    dbms_scheduler.enable(name => '"BERX"."TEST_NOTHING"');
end;
/

PAUSE "sleep for some time until the job is executed"

SELECT
    sys.anydata.gettypename(value) d
FROM
    sys.scheduler$_job_argument;

SELECT
    *
FROM
    dba_scheduler_job_log
WHERE
    owner = user;

All I can do now is to convince an MOS to approve me deleting the problematic row.
Update: 2019-06-13
Oracle Support approved to fix this situation by running

delete from SCHEDULER$_JOB_ARGUMENT WHERE OID=2736824; 
commit

So my issue is fixed.

I do NOT recommend anyone to delete rows in data dictionary without asking Oracle Support for approval first!

restore DBMS_SCHEDULER.CREATE_CREDENTIAL cleartext password

If you want to use Oracle file watcher, you need to Create a Credential. As there a password needs to be stored in the database, Oracle tries to save it in a secure way. But as the password must be decrypted for the purpose to login on the file watchers agent side, it is not safe at all:
The credentials are stored with DBMS_SCHEDULER.CREATE_CREDENTIAL. Here an example:

exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential',
  username => 'oracle',  password => 'welcome1');
exec DBMS_SCHEDULER.CREATE_CREDENTIAL(
  credential_name => 'local_credential2',
  username => 'oracle2', password => 'welcome1');


It's quite easy to see the values again:

select o.object_name credential_name, username, password
 FROM SYS.SCHEDULER$_CREDENTIAL c, DBA_OBJECTS o
 WHERE c.obj# = o.object_id;

CREDENTIAL_NAME    USERNAME PASSWORD
------------------ -------- ------------------------------------
LOCAL_CREDENTIAL   oracle   BWVYxxK0fiEGAmtiKXULyfXXgjULdvHNLg==
LOCAL_CREDENTIAL2  oracle2  BWyCCRtd8F0zAVYl44IhvVcJ2i8wNUniDQ==


At least the password is somehow encrypted, and even the password was welcome1 for both credentials, the encrypted string is not identical.

Nothing to blame here, but I mentioned, the password can be decrypted. So let's do so:

SELECT u.name CREDENTIAL_OWNER, O.NAME CREDENTIAL_NAME, C.USERNAME,
  DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd
FROM SYS.SCHEDULER$_CREDENTIAL C, SYS.OBJ$ O, SYS.USER$ U
WHERE U.USER# = O.OWNER#
  AND C.OBJ#  = O.OBJ# ;

CREDENTIAL_OWNER CREDENTIAL_NAME      USERNAME PWD
---------------- -------------------- -------- --------
SYS              LOCAL_CREDENTIAL     oracle   welcome1
SYS              LOCAL_CREDENTIAL2    oracle2  welcome1


Can you see it? It's there. Try it at home!
I don't blame anyone here. It's hard to store anything really safe in case you need to decrypt it also.
But don't expect your password save, if you store it with DBMS_SCHEDULER.CREATE_CREDENTIAL.
Maybe it's slightly to easy to use DBMS_ISCHED.GET_CREDENTIAL_PASSWORD (ok, only SYS can do so) but even it might be slightly more difficult in the future, the basic problem will still exist.

some tracing events in DBMS_SCHEDULER

I currently have the fun to review DBMS_SCHEDULER. As I'm always interested in ways to trace anything, to dig deeper in case of problems, I searched for ways to trace it.
As I did not find a collected list of events anywhere, I start them here. It's by far not a complete list, so feel free to discuss and contribute, if you want!

event 10862

resolve default queue owner to current user in enqueue/dequeue

Cause: resolve default queue owner to current user in enqueue/dequeue.

Action: turn on if client wish to resolve the default queue owner to the current user. If not turned on, the default queue owner will be resolved to the login user.

This event is not checked the way you might imagine. Just in the area of REMOVE_JOB_EMAIL_NOTIFICATION if it's 0, it's set to 1 for a call of DBMS_AQADM.REMOVE_SUBSCRIBER and set to 0 afterwards.

27401

scheduler tracing event

• bit 0x10000 - Logging e-mail to table and returning
  bitand( ,65536)
  logs informations about sending emails into table sys.scheduler$_sent_emails
  
• bit 0x20000 - start DBMS_DEBUG_JDWP.CONNECT_TCP in file watcher
  bitand( ,131072)
  starts DBMS_DEBUG_JDWP.CONNECT_TCP on localhost, port 4444
  I'm not sure if I like this event. In general I don't want any software opening connections without my knowing. And I could not find this documented anywhere.
  Is it fair to call this a backdoor?
  
• bit 0x40000 - starts tracing in file watcher
  bitand( ,262144)
  logs informations about file watcher into trace file
  

27402

scheduler tracing event

• bit 0x40 - starts tracing about emails
  bitand( ,64)
  similar to event 27401 bit 0x10000, but tracefile instead of table
  
• bit 0x80 - starts tracing about emails
  bitand( ,128)
  logs information about email jobs into trace file
  
• bit 0x100 - starts tracing in chains
  bitand( ,256)
  logs information about chains into trace file

I guess there is at least also a bit 0x200, but could not prove it right now.

27403

scheduler stop job event

I did not find anything about it yet. comments are most welcome!