一次scheduler錯誤的處理

在產品庫的一個例項上，發現以下錯誤
Errors in file /data/oracle/diag/rdbms/justin/trace/justin_j000_17526.trc:
ORA-12012: error on auto execute of job 19610
ORA-06575: Package or function TEST_PROC is in an invalid state
Wed Jan 12 22:58:51 2011
Thread 1 advanced to log sequence 3518 (LGWR switch)
  Current log# 4 seq# 3518 mem# 0: /data/oracle/oradata/justin/redo4.log
Wed Jan 12 22:58:51 2011
依據提示，應該是一個job執行失敗了，檢視一下跟蹤檔案
[oracle@justin ~]$ more /data/oracle/diag/rdbms/justin/trace/justin_j000_17526.trc
Trace file /data/oracle/diag/rdbms/yhdstd/justin/trace/justin_j000_17526.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /data/oracle/product/1102/db1
System name:    Linux
Node name:      justin
Release:        2.6.9-89.0.0.0.1.ELlargesmp
Version:        #1 SMP Tue May 19 05:38:23 EDT 2009
Machine:        x86_64
Instance name: justin
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 17526, image: oracle@justin (J000)


*** 2011-01-12 22:23:49.264
*** SESSION ID(1155.62666) 2011-01-12 22:23:49.264
*** CLIENT ID() 2011-01-12 22:23:49.264
*** SERVICE NAME(SYS$USERS) 2011-01-12 22:23:49.264
*** MODULE NAME(DBMS_SCHEDULER) 2011-01-12 22:23:49.264
*** ACTION NAME(CALL_TEST_PROC2) 2011-01-12 22:23:49.264

ORA-12012: error on auto execute of job 19610
ORA-06575: Package or function TEST_PROC is in an invalid state
給出了更為詳細的資訊，是TEST_PROC執行出錯導致的CALL_TEST_PROC2執行錯誤，檢視相應的檢視，首先看一下相應的Job資訊
SQL>  select owner,job_name,program_name from dba_scheduler_jobs;

OWNER                          JOB_NAME                       PROGRAM_NAME
JUSTIN                          CALL_TEST_PROC2
JUSTIN                          CALL_TEST_PROC

SQL> col job_action format a30
SQL> select owner,job_name,job_action from dba_scheduler_jobs where job_name='CALL_TEST_PROC2';

OWNER                          JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------ ------------------------------
JUSTIN                          CALL_TEST_PROC2                test_proc

SQL> select owner,job_name,job_action from dba_scheduler_jobs where job_name='CALL_TEST_PROC';

OWNER                          JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------ ------------------------------
JUSTIN                          CALL_TEST_PROC                 test_proc

SQL> set linesize 300
SQL> select job_name,job_action,to_char(LAST_START_DATE,'yyyy-mm-dd hh24:mi:ss'), to_char(LAST_RUN_DURATION,'yyyy-mm-dd hh24:mi:ss'), to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') from dba_scheduler_jobs where job_name like 'CALL_TEST_PROC%';

JOB_NAME                       JOB_ACTION                     TO_CHAR(LAST_START_ TO_CHAR(LAST_RUN_DURATION,' TO_CHAR(NEXT_RUN_DA
------------------------------ ------------------------------ ------------------- --------------------------- -------------------
CALL_TEST_PROC                 test_proc
CALL_TEST_PROC2                test_proc                      2011-01-13 10:23:49 +000000000 00:00:00.007927  2011-01-13 12:23:49

可以看到有兩個job都呼叫了test_proc，只不過有一個一直沒有執行，接下來檢視test_proc的資訊
SQL> select owner,object_type from dba_objects where object_name='TEST_PROC';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
JUSTIN                          PROCEDURE

SQL> sqlplus justin/justin
SP2-0734: unknown command beginning "sqlplus qi..." - rest of line ignored.
SQL> conn justin/justin
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> alter procedure test_proc compile;

Warning: Procedure altered with compilation errors.

SQL> show errors;
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3      PL/SQL: SQL Statement ignored
9/15     PL/SQL: ORA-00942: table or view does not exist
SQL> desc user_sources;
ERROR:
ORA-04043: object user_sources does not exist

SQL> desc user_source;   
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(30)
TYPE                                               VARCHAR2(12)
LINE                                               NUMBER
TEXT                                               VARCHAR2(4000)

SQL> select text from user_source where name='TEST_PROC';

TEXT
--------------------------------------------------------------------------------
procedure test_proc is
  v_time date;
  x_time date;

begin
  v_time := TRUNC(sysdate, 'hh');
  x_time := TRUNC(sysdate, 'hh') - 1 / 24;

  insert into test_z
    select to_char(x_time, 'yyyy-mm-dd hh24:mi:ss') || '-' ||
           to_char(v_time, 'yyyy-mm-dd hh24:mi:ss'),

TEXT
--------------------------------------------------------------------------------
           count(*) cn
      from justin
     where order_create_time >= x_time
       and order_create_time < v_time;

  commit;
end;




21 rows selected.

SQL> select table_name from user_tables where table_name='TEST_Z';

no rows selected
由於表test_2被刪除導致，詢問了開發，這個procedure以及job都沒有用，於是全部drop了
SQL> exec dbms_scheduler.drop_job('CALL_TEST_PROC');

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.drop_job('CALL_TEST_PROC2');

PL/SQL procedure successfully completed.

SQL> select job_name,job_action,program_name,to_char(LAST_START_DATE,'yyyy-mm-dd hh24:mi:ss'), to_char(LAST_RUN_DURATION,'yyyy-mm-dd hh24:mi:ss'), to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss'),state from dba_scheduler_jobs where job_name like 'CALL_TEST_PROC%';

no rows selected


總結： oracle推出的scheduler貌似比以前的job複雜了很多，光檢視就有dba_scheduler_schedules，dba_scheduler_programs和dba_scheduler_jobs；
*** MODULE NAME(DBMS_SCHEDULER) 2011-01-12 22:23:49.264
*** ACTION NAME(CALL_TEST_PROC2) 2011-01-12 22:23:49.264
把上述三個檢視全查了一個遍，才找到所謂的ACTION NAME(CALL_TEST_PROC2).
我現在的這家公司，更傾向於使用crontab呼叫shell或perl指令碼來進行替代。

[ 本帖最後由 myownstars 於 2011-1-14 09:44 編輯 ]