Oracle 19C重启库后警告日志报错ORA-12012&&ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist
Oracle 19C重启库后警告日志报错ORA-12012&&ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist
某次测试机器的19c重启后突然在警告日志定时冒出如下类似的报错：
2024-09-20T15:20:00.170202+08:00 TESTDB(7):Errors in file /u01/app/oracle/diag/rdbms/repomsdb/repomsdb/trace/repomsdb_j000_16307.trc: ORA-12012: error on auto execute of job "APEX_180200"."ORACLE_APEX_WS_NOTIFICATIONS" ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist 2024-09-20T15:25:00.313832+08:00 TESTDB(7):Errors in file /u01/app/oracle/diag/rdbms/repomsdb/repomsdb/trace/repomsdb_j000_16307.trc: ORA-12012: error on auto execute of job "APEX_180200"."ORACLE_APEX_MAIL_QUEUE" ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist
时隔20多天没头绪后终于发现引起报错的原因。
报错信息中可以看出，发生在con_id为7的testdb可拔插数据库上，trc文件内容没什么参考价值，拿最近一样报错的trc部分内容如下：
*** 2024-09-20T15:20:00.169859+08:00 (TESTDB(7)) *** SESSION ID:(770.49360) 2024-09-20T15:20:00.169928+08:00 *** CLIENT ID:() 2024-09-20T15:20:00.169940+08:00 *** SERVICE NAME:(testdb) 2024-09-20T15:20:00.169955+08:00 *** MODULE NAME:(DBMS_SCHEDULER) 2024-09-20T15:20:00.169964+08:00 *** ACTION NAME:(ORACLE_APEX_MAIL_QUEUE) 2024-09-20T15:20:00.169972+08:00 *** CLIENT DRIVER:(SERVER) 2024-09-20T15:20:00.169980+08:00 *** CONTAINER ID:(7) 2024-09-20T15:20:00.170010+08:00 ORA-12012: error on auto execute of job "APEX_180200"."ORACLE_APEX_MAIL_QUEUE" <error barrier> at 0x7fffffff9390 placed jslv.c@1951 ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist
于是网上搜索，没发现半点一样的信息。防。
于是从mos上搜，一样没有类似案例。
粗略看了job涉及的对象内容，也没发现类似"SYS"."MAX_JOB_SLAVE_PROCESSES"的突破点，搞了好几天也没搞定。
于是，简单粗暴的确定了 APEX 功能没有被用过，那么直接禁用相关job后问题就能解决了，如下：
然后观察了一两天，还是刷刷的报错。。。
--此SQL确定 apex 功能是否使用，无任何输出表示没有使用过 select workspace, view_date, seconds_ago from apex_workspace_activity_log; --确定上边sql无输出后，禁用相关调度job exec sys.dbms_scheduler.disable(name => 'APEX_180200.ORACLE_APEX_MAIL_QUEUE'); exec sys.dbms_scheduler.disable(name => 'APEX_180200.ORACLE_APEX_WS_NOTIFICATIONS'); exec sys.dbms_scheduler.disable(name => 'APEX_180200.ORACLE_APEX_DAILY_MAINTENANCE'); exec sys.dbms_scheduler.disable(name => 'APEX_180200.ORACLE_APEX_PURGE_SESSIONS');
到这里我就已经无能为力了，感觉有可能是触发了19c的bug了，只不过官方还没发现或者登记而已。
最后只能搞了个大招重新dbca个新库，然后不安装 apex 插件模块之后终于不再报错。
然而，前几天数据库正式完成数据转移后，将作为生产库上线之前，重启库打开归档后，发现又报错了。。
这次又是另外的调度job报错：
2024-09-17T01:01:01.070642+08:00 TESTDB(7):Errors in file /u01/app/oracle/diag/rdbms/reposmdb/reposmdb/trace/reposmdb_j000_3332.trc: ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB" ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist
这个甚至发现，testdb容器库实际连job都不存在，简直匪夷所思。
16:09:25 SYS@repomsdb(1351)> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 7 TESTDB READ WRITE NO 16:09:34 SYS@repomsdb(1351)> select * from dba_scheduler_jobs where owner='ORACLE_OCM'; no rows selected Elapsed: 00:00:00.01
后续确定 oracle_ocm 没用后，根据How To Drop The ORACLE_OCM Schema From An 18c Or 19c Database (文档 ID 2585330.1)卸载删除该用户，然而还是有问题。。
--How To Drop The ORACLE_OCM Schema From An 18c Or 19c Database (文档 ID 2585330.1) 09:07:20 SYS@reposmdb(780)> drop user oracle_ocm cascade; drop user oracle_ocm cascade * ERROR at line 1: ORA-28014: cannot drop administrative user or role Elapsed: 00:00:00.17 --ORA-28014: Cannot Drop Administrative Users (文档 ID 1566042.1) 09:08:22 SYS@reposmdb(780)> alter session set "_oracle_script"=true; Session altered. Elapsed: 00:00:00.00 09:11:42 SYS@reposmdb(780)> drop user oracle_ocm cascade; User dropped. Elapsed: 00:00:03.34 安装ORACLE_OCM --https://docs.oracle.com/cd/E28601_01/doc.1037/e26167/install.htm#CBHFFGFJ 报错信息变为： 2024-09-19T01:01:01.437589+08:00 TESTDB(7):Errors in file /u01/app/oracle/diag/rdbms/reposmdb/reposmdb/trace/reposmdb_j000_6261.trc: ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB" ORA-01435: user does not exist 2024-09-19T01:01:01.437918+08:00 TESTDB(7):Pluggable Database #3 switch restore failed err=604. Errors in file /u01/app/oracle/diag/rdbms/reposmdb/reposmdb/trace/reposmdb_j000_6261.trc (incident=40281) (PDBNAME=TESTDB): ORA-00603: ORACLE server session terminated by fatal error ORA-00604: error occurred at recursive SQL level 2 ORA-01023: Cursor context not found (Invalid cursor number) ORA-00604: error occurred at recursive SQL level 2 ORA-01023: Cursor context not found (Invalid cursor number) ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB" ORA-01435: user does not exist TESTDB(7):Incident details in: /u01/app/oracle/diag/rdbms/reposmdb/reposmdb/incident/incdir_40281/reposmdb_j000_6261_i40281.trc 2024-09-19T01:01:02.666152+08:00 TESTDB(7):opidrv aborting process J000 ospid (6261) as a result of ORA-603
今天突然灵光一闪，看了下cdb实际是有这两个job的：
16:23:22 SYS@reposmdb(1351)> col job_name for a35 16:23:28 SYS@reposmdb(1351)> alter session set container=cdb$root; Session altered. Elapsed: 00:00:00.00 16:23:32 SYS@reposmdb(1351)> select owner,job_name from dba_scheduler_jobs where owner='ORACLE_OCM'; OWNER JOB_NAME ------------------------------ ----------------------------------- ORACLE_OCM MGMT_CONFIG_JOB ORACLE_OCM MGMT_STATS_CONFIG_JOB Elapsed: 00:00:00.00
联想到昨天发现环境变量ORACLE_PDB_SID设置的情况下启动19c的库会导致通过cdb的service name连接后为pdb库，看我昨天写的：Oracle 19c通过cdb的service name连接后为pdb库
难道通篇下来的报错都是ORACLE_PDB_SID引起的？？
立刻找了另外个从来没报错过的测试环境19c，设置ORACLE_PDB_SID后，重启数据库
15:09:03 SYS@reposmdb(12)> shutdown immediate; [oracle@reposmdb ~]$ export ORACLE_PDB_SID=testdb [oracle@reposmdb ~]$ sqlplus / as sysdba 15:10:02 SYS@reposmdb(idle)> startup; [oracle@reposmdb ~]$ sqlplus sys/password@reposmdb/reposmdb as sysdba --这里cdb的service name连接后，实际连接到了pdb上 15:10:42 SYS@reposmdb/reposmdb(769)> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 7 TESTDB READ WRITE NO
然后果然，警告日志没多久出现报错了。。
2024-09-20T16:30:00.598826+08:00 TESTDB(7):Errors in file /u01/app/oracle/diag/rdbms/reposmdb/reposmdb/trace/reposmdb_j000_31649.trc: ORA-12012: error on auto execute of job "APEX_180200"."ORACLE_APEX_MAIL_QUEUE" ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist 2024-09-20T16:30:00.996481+08:00 TESTDB(7):Errors in file /u01/app/oracle/diag/rdbms/reposmdb/reposmdb/trace/reposmdb_j001_31651.trc: ORA-12012: error on auto execute of job "APEX_180200"."ORACLE_APEX_WS_NOTIFICATIONS" ORA-27476: "SYS"."MAX_JOB_SLAVE_PROCESSES" does not exist
看来，果然是因为环境变量ORACLE_PDB_SID设置的情况下启动库导致的。。。
好吧，看来发现了个mos上还没有的问题，总算是解决了。
