During one of the recent cloud migration I was facing lot of below error in alert logs:
========================================
PDB1(4):Errors in file /u01/app/oracle/diag/rdbms/TESTprd1/TESTPRD1/trace/TESTPRD1_j001_19167.trc:
ORA-12012: Fehler beim autom Ausführen von Job 3
ORA-04067: Ausführung nicht erfolgreich, package body "SYSMAN.EMD_MAINTENANCE" ist nicht vorhanden
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "SYSMAN.EMD_MAINTENANCE" konnte nicht gefunden werden
===========================
Alert log is getting filled up with the above error causing DB down.
==
Reason: This is mainly because of broken Jobs in the Oracle scheduler.
Action taken:
SQL> select ERROR_NUMBER,MESSAGE from PDB_PLUG_IN_VIOLATIONS;
SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;
JOB WHAT B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
3 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); N SYSMAN
4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24); Y APEX_030200
4002 wwv_flow.push_queue(wwv_flow_platform.get_preference('SMTP) N APEX_030200
T_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT'));
21 PA_VERTEILUNG.VERTEILE_DATEN; Y CLOUDIT
24 PA_REORG.REORGANISATION; Y CLOUDIT
22 PA_BERECHNUNG.ABMW; Y CLOUDIT
23 PA_BERECHNUNG.DOSISKONTO; Y CLOUDIT
7 rows selected.
Check for N
SQL>
SQL> exec sys.dbms_ijob.broken(3,TRUE);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_ijob.broken(4002,TRUE);
PL/SQL procedure successfully completed.
SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;
JOB WHAT B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
3 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); Y SYSMAN
4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24); Y APEX_030200
4002 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOS Y APEX_030200
T_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT'));
21 PA_VERTEILUNG.VERTEILE_DATEN; Y CLOUDIT
24 PA_REORG.REORGANISATION; Y CLOUDIT
22 PA_BERECHNUNG.ABMW; Y CLOUDIT
23 PA_BERECHNUNG.DOSISKONTO; Y CLOUDIT
7 rows selected.
SQL>
SQL>
SQL>
SQL> exec sys.dbms_ijob.remove(4001);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_ijob.remove(4002);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_ijob.remove(3);
PL/SQL procedure successfully completed.
SQL>
SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;
JOB WHAT B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
21 PA_VERTEILUNG.VERTEILE_DATEN; Y CLOUDIT
24 PA_REORG.REORGANISATION; Y CLOUDIT
22 PA_BERECHNUNG.ABMW; Y CLOUDIT
23 PA_BERECHNUNG.DOSISKONTO; Y CLOUDIT
SQL> exec sys.dbms_ijob.remove(21);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_ijob.remove(22);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_ijob.remove(23);
PL/SQL procedure successfully completed.
SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;
JOB WHAT B SCHEMA_USER
---------- ------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------
24 PA_REORG.REORGANISATION; Y CLOUDIT
SQL> exec sys.dbms_ijob.remove(24);
PL/SQL procedure successfully completed.
SQL>
Issue resolved:
SQL>
SQL> Select job,what,BROKEN,SCHEMA_USER from dba_jobs;
no rows selected
No more error in alert logs now..
Restart the DB and reschedule the jobs.
