一次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 编辑 ]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-688360/，如需转载，请注明出处，否则将追究法律责任。