APEX Upgrade Failed While Creating JOBS, ORA-20001: FAILED Existence check fo...

刘泽宇

现象：
On : APEX19.2 version or higher up to 23.2, Admin

A new install or upgrade failed at the end while creating/checking the jobs


Oracle Application Express in dba_registry shows
19.2.0.00.18 INVALID

ERROR
-----------------------
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_WS_NOTIFICATIONS
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_MAIL_QUEUE
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_PURGE_SESSIONS
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_DAILY_MAINTENANCE
...(12:09:16) Setting DBMS registry for APEX to INVALID
...(12:09:17) Exiting validate_apex




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Install APEX running the apexins.sql script (with or without a previous APEX version)
2. When the install failed check the logs



改动：
Installing or Upgrading APEX

原因：
There are at least 2 potential causes:

A) DBA have set  job_queue_processes to 0 (zero)

Or

B) There are missing packages or privileges

If the install<date-time>.log is showing:

# Error: ORA-27476: "SYS"."DEFAULT_JOB_CLASS" does not exist  <<<<<<<<<<# Statement: begin
  sys.dbms_scheduler.create_job
...
ORA-06512: at "SYS.DBMS_ISCHED", line 146
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1707
ORA-06512: at "APEX_190200.WWV_FLOW_UPGRADE", line 1075
...

(<time>) Key object existence check
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_WS_NOTIFICATIONS
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_MAIL_QUEUE
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_PURGE_SESSIONS
ORA-20001: FAILED Existence check for APEX_190200.ORACLE_APEX_DAILY_MAINTENANCE
...(<time>) Setting DBMS registry for APEX to INVALID

处理方法：
A) Before the installation make sure that job_queue_processes is at least to the maximum number of concurrently running jobs

Or

B) After the failed installation do the following:

- Connect in sql*plus to the DB where APEX was installed as SYS and do:

SQL> grant EXECUTE ANY CLASS to APEX_190200;                     
SQL>  begin sys.validate_apex; end;
-- Confirm the Jobs are created and enabled now

SQL> select JOB_NAME,STATE,ENABLED  from dba_scheduler_jobs where owner = 'APEX_190200'

JOB_NAME                       STATE           ENABLED
------------------------------ --------------- -------
ORACLE_APEX_AUTO_APPROVAL      DISABLED        FALSE
ORACLE_APEX_DAILY_MAINTENANCE  SCHEDULED       TRUE
ORACLE_APEX_DICTIONARY_CACHE   SCHEDULED       TRUE
ORACLE_APEX_ISSUE_EMAILS       SCHEDULED       TRUE
ORACLE_APEX_MAIL_QUEUE         SCHEDULED       TRUE
ORACLE_APEX_PURGE_SESSIONS     SCHEDULED       TRUE
ORACLE_APEX_WS_NOTIFICATIONS   SCHEDULED       TRUE

-- you can also see that apex is valid in dba_registry