oracle schedule 任務失敗處理

故障現象：
    收到zabbix告警郵件，具體內容如下：
    告警內容：
    ZABBIX-監控系統:
    ------------------------------------
    報警內容: Found failed scheduler jobs
    ------------------------------------
    報警級別: PROBLEM
    ------------------------------------
    監控專案: app_job_fail_cnt：1
    ------------------------------------
    報警時間:2016.11.04-10:43:05

故障原因：
    由於有個專案的DB下線，而這個db的部分表資料需要同步到另一資料庫（統計庫），統計庫有定時任務定期同步資料，一旦下線DB關閉，統計庫定時任務同步資料失敗，從而觸發郵件告警。

故障處理過程：
    檢視失敗Job資訊
        select log_id,log_date,owner,job_name,status  from dba_scheduler_job_log where status  = 'FAILED' ;
            626593    04-11月-16 10.17.52.065773 上午 +08:00    ASTA_STAT    SYN_SERINFO    FAILED
    禁用失敗job
        exec dbms_scheduler.disable('DBUSER.SYN_SERINFO') ;
    刪除失敗日誌：（由於dba_scheduler_job_log是檢視，根據其SQL找到對應的物理表，然後刪除資料）
        SQL> delete from dba_scheduler_job_log where status  = 'FAILED' ;
        delete from dba_scheduler_job_log where status  = 'FAILED'
            *
        ERROR at line 1:
        ORA-01752: cannot delete from view without exactly one key-preserved table

        select owner,object_type from dba_objects where object_name=upper('dba_scheduler_job_log');
        SYS    VIEW
        PUBLIC    SYNONYM

        CREATE OR REPLACE VIEW SYS.DBA_SCHEDULER_JOB_LOG AS
        (SELECT
         LOG_ID, LOG_DATE, OWNER,
         DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
         DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
         co.NAME, OPERATION,e.STATUS, USER_NAME, CLIENT_ID, GUID,
         decode(e.credential, NULL, NULL,
        substr(e.credential, 1, instr(e.credential, '"')-1)),
         decode(e.credential, NULL, NULL,
        substr(e.credential, instr(e.credential, '"')+1,
           length(e.credential) - instr(e.credential, '"'))),
         decode(bitand(e.flags, 1), 0, NULL,
        substr(e.destination, 1, instr(e.destination, '"')-1)),
         decode(bitand(e.flags, 1), 0, e.destination,
        substr(e.destination, instr(e.destination, '"')+1,
           length(e.destination) - instr(e.destination, '"'))),
         ADDITIONAL_INFO
      FROM scheduler$_event_log e, obj$ co
      WHERE e.type# = 66 and e.dbid is null and e.class_id = co.obj#(+));

    根據物理表刪除日誌
        select object_name,object_type ,owner from dba_objects where object_name=upper('scheduler$_event_log');
        select * from sys.SCHEDULER$_EVENT_LOG where status='FAILED';
        DELETE FROM  sys.SCHEDULER$_EVENT_LOG where status='FAILED';
        commit;



擴充套件：
oracle 定時任務管理，請參考之前的文章
http://blog.itpub.net/27067062/viewspace-2127806/