Oracle JOB exception and interruption Cause Analysis

Oracle JOB exception and interruption Cause Analysis

Note:

Today, my R & D colleague asked me to check whether the job stored in PKG_WMS.proc_TaskMain is still running. After all, I found that dba_jobs.NEXT_DATE = 4000/1/1. See the following for the reason ~

JOB information:

SQL> select JOB, SCHEMA_USER, LAST_DATE, LAST_SEC, NEXT_DATE, NEXT_SEC, BROKEN, FAILURES, INTERVAL, WHAT from dba_jobs where WHAT like 'pkg _ WMS. % ';

JOB SCHEMA_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC BROKEN FAILURES INTERVAL WHAT

-------------------------------------------------------------------------------------------------------------------

1543 fws 13:00:57 4000/1/1 00:00:00 Y 16 sysdate + 1/1440 PKG_WMS.proc_TaskMain;

Parameters:

BROKEN: indicates the interrupt mark. 'n' start and Y interrupt '--> DBMS_JOBS.BROKEN (job_id, TRUE/FALSE); stop/start a job, and then COMMIT is required; otherwise, the setting is invalid.

FAILURES: number of errors

Last_date: time when the job was successfully executed

Next_date: The next execution time of the job (affected by last_date and interval)

Total_time: total time of job running (the total time of each running)

This_date: the time when the job is being executed (if the job is being executed at the time of query, there is a value)

* ** Analysis:
* ** If an exception occurs when calling a scheduled task stored procedure (for example, insufficient space or writing the stored procedure itself ..)
* ** The job automatically tries 16 consecutive failures before changing the broken status to Y and changing next-date to "-1". You can only manually exec dbms_job.run (: id ); to start a job;

For example, during the storage process p1, The begin/end line is commented randomly to reflect the syntax problem. The job runs normally 16 times according to the p1 storage process, and then broken the job,

If it reaches 10 times, the stored procedure is changed to the correct state, the number of errors is changed to 0, and the job is changed to normal;

* ** How to determine the problem:

1. Manually call the storage to determine what the problem is. If no error is reported during the call storage, it indicates that the storage has been faulty. If the job has been running for more than 16 times, you need to manually execute dbms_job.run (: id); start the job;

2. view the alert Log. An Oracle job exception records the alarm log;

Try to start...

SQL> begin

2 dbms_job.execute (1543 );

3 end;

4/

ORA-12011: unable to execute 1 job

ORA-06512: In "SYS. DBMS_IJOB", line 648

ORA-06512: In "SYS. DBMS_JOB", line 284

ORA-06512: In line 2

SQL>

An error is reported. Check alert content again (solution 2 ):

1,

ORA-12012: Automatic job 1543 Error

ORA-12899: the value of column "FWS". "RECODE_ERROR_MSG". "ERROR_MSG" is too large (actual value: 704, maximum value: 500)

ORA-06512: In "FWS. PROC_WRITEERRMSG", line 22

ORA-06512: In "FWS. PKG_WMS", line 132

ORA-01688: Table FWS. RECODE_ERROR_MSG partition SYS_P6181 cannot be extended through 8192 (in tablespace TBS_WMS_CITY_JK_DATA)

ORA-06512: In "FWS. PROC_WRITEERRMSG", line 22

ORA-06512: In "FWS. PKG_WMS", line 514

ORA-01688: Table FWS. RECODE_ERROR_MSG partition SYS_P6181 cannot be extended through 8192 (in tablespace TBS_WMS_CITY_JK_DATA)

ORA-06512: In "FWS. PROC_WRITEERRMSG", line 22

ORA-06512: In "FWS. PKG_WMS", line 502

ORA-01400: cannot insert NULL ("FWS". "BILL_RECEIPT_CITY". "CREATOR ")

ORA-06512: In line 1

ORA-1688: unable to extend table FWS. RECODE_ERROR_MSG partition SYS_P6181 by 128 in tablespace TBS_WMS_CITY_JK_DATA

ORA-1688: unable to extend table FWS. RECODE_ERROR_MSG partition SYS_P6181 by 8192 in tablespace TBS_WMS_CITY_JK_DATA

ORA-1688: unable to extend table FWS. RECODE_ERROR_MSG partition SYS_P6181 by 128 in tablespace TBS_WMS_CITY_JK_DATA

ORA-1688: unable to extend table FWS. RECODE_ERROR_MSG partition SYS_P6181 by 8192 in tablespace TBS_WMS_CITY_JK_DATA

2,

ORA-12012: Automatic job 26 Error
ORA-06550: 1st rows, 96th columns:
PLS-00905: the LOTTERY. P_LOCK_CHECK_HD object is invalid
ORA-06550: 1st rows, 96th columns:
PL/SQL: Statement ignored
Mon Jul 13 14:39:55 2015
Errors in file/u01/app/oracle/diag/rdbms/bjcc/ccem01/trace/ccem01_j001_69.trc:

Follow the alert log to solve the problem ..

* ** 1. insufficient space is due to the increase of 0.1 billion of the data volume in the RECODE_ERROR_MSG table every day. You can just truncate table RECODE_ERROR_MSG. The reason for the table surge is the Oracle archive surge analysis.

* ** 2. Test by yourself to store the error information that is invalidated;