故障描述

某次，一套執行已經有點歲月的 O racle 11 g R2 單機環境，使用者反饋某個物化檢視不會定時自動重新整理，進一步瞭解後，確認是 D BMS_JOB 無法按照指定的時間和間隔執行，根據以往的運維經驗，估計大機率是觸發 J OB 自動執行的條件不具備導致，該問題是比較容易處理。但實際情況並沒有想的那麼簡單，詳情如下。

根因分析

1 、對異常 2 17 J OB 初步分析與處理

建立多個物化檢視，但沒有一個能定時自動觸發

 

選擇 2 17 號 J OB ，修改其下一次執行時間和間隔

 

修改 2 17 JOB 下一次執行時間 next_date

begin
   dbms_job.next_date(217,to_date('2022-12-07 17:35:00','yyyy-mm-dd hh24:mi:ss'));
   commit;
end;
/

修改 2 17 JOB 執行間隔 interval

begin
  dbms_job.interval(217,interval => 'sysdate+10/1440');
  commit;
end;
/

刪除 2 17 JOB ，並重建

exec dbms_job.remove(217);
commit;
 
declare job2 number;
begin
dbms_job.submit(job2,'dbms_mview.refresh(''"PMS"."MV_HEX_PMS_USER_XBRL"'');',sysdate,'sysdate+10/1440');
commit;
end;
/

重建之後的 2 19 號 J OB 依舊沒有執行

select job,
       what,
       last_date,
       next_date,
       interval,
       broken,
       failures,
       schema_user,
       log_user,
       priv_user
  from dba_jobs
 WHERE JOB = 219;

 

2 、模擬使用者場景，在測試環境上進行驗證

建立業務表 znfx_agg_zc_GT_bb

create table xuh.znfx_agg_zc_GT_bb(
    MONTHID   varchar2(20),
    ZCZT      varchar2(20),
    QYLX      varchar2(20),
    CLRQ      varchar2(20),
    SFXS      varchar2(20),
    NUM       number(20)  ,
    ZCZB      number(20)  ,
    COUNT     number(20)  ,
    NUM_CNT   number(20)  ,
    ZCZB_CNT  number(20)  );

建立儲存過程 znfx_tab_pro

create or replace procedure xuh.znfx_tab_pro(arg1 in varchar2) is
  v_count number := 1;
begin
  while v_count <= 10000 loop
    insert into xuh.znfx_agg_zc_GT_bb
    values
      (arg1,
       'AAA',
       'BBB',
       'CCC',
       'DDD',
       v_count,
       v_count,
       v_count,
       v_count,
       v_count);
    v_count := v_count + 1;
  end loop;
  commit;
end;
/

執行儲存過程，模擬資料插入

SQL> exec xuh.znfx_tab_pro('202212');

 

建立物化檢視 JCD_AGG_ZC_GT_BB_MV

CREATE MATERIALIZED VIEW "XUH"."JCD_AGG_ZC_GT_BB_MV" ("MONTHID", "ZCZT", "QYLX", "CLRQ", "SFXS", "NUM", "ZCZB", "COUNT", "NUM_CNT", "ZCZB_CNT")
REFRESH force ON DEMAND
AS
select monthid,
       zczt,
       qylx,
       clrq,
       sfxs,
       sum(num) num,
       sum(zczb) zczb,
       count(*) count,
       Count(num) num_cnt,
       Count(zczb) zczb_cnt
  from xuh.znfx_agg_zc_GT_bb
group by monthid, zczt, qylx, clrq, sfxs;

手動執行物化檢視重新整理，確保重新整理沒有問題

SQL> exec dbms_mview.refresh('XUH.JCD_AGG_ZC_GT_BB_MV');

 

建立 J OB ，並跟蹤觀察 J OB 執行情況

declare job3 number;
begin
dbms_job.submit(job3,'dbms_mview.refresh(''"XUH"."JCD_AGG_ZC_GT_BB_MV"'');',sysdate,'sysdate+1/1440');
commit;
end;
/

 

可以看到該 J OB 確實可以定時自動成功執行

 

 

3 、排查可能造成 J OB 無法定時自動執行的原因

Check the most common reasons why jobs don't execute automatically and as scheduled:
1) Instance in RESTRICTED SESSIONS mode?
Check if the instance is in restricted sessions mode:
select instance_name,logins from v$instance;
If logins=RESTRICTED, then:
alter system disable restricted session;
^-- Checked!
2) JOB_QUEUE_PROCESSES=0
Make sure that job_queue_processes is > 0
show parameter job_queue_processes
^-- Checked!
3) _SYSTEM_TRIG_ENABLED=FALSE
Check if _system_enabled_trigger=false
col parameter format a25
col value format a15
select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b
where a.indx=b.indx and ksppinm='_system_trig_enabled';
If _system_trig_enabled=false, then
alter system set "_system_trig_enabled"=TRUE scope=both;
^-- Checked!
4) Is the job BROKEN?
select job,broken from dba_jobs where job=<job_number>;
If broken, then check the alert log and trace files to diagnose the issue.
^-- Checked! The job is not broken.
5) Is the job COMMITted?
Make sure a commit is issued after submitting the job:
DECLARE X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'dbms_utility.analyze_schema
(''SCOTT'',''COMPUTE'',NULL,NULL,NULL);'
,next_date => to_date('08/06/2005 09:35:00','dd/mm/yyyy hh24:mi:ss')
,no_parse => FALSE
);
COMMIT;
END;
/
If the job executes fine if forced (i.e., exec dbms_jobs.run(<job_no>);), then likely a commit
is missing.
^-- Checked! The job is committed after submission.
6) UPTIME > 497 days
Check if the server (machine) has been up for more than 497 days:
For SUN, use 'uptime' OS command.
If uptime>497 and the jobs do not execute automatically, then you are hitting unpublished bug 3427424
(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102
^-- Checked! The server in this case has been up 126 days only
 
7) DBA_JOBS_RUNNING
Check dba_jobs_running to see if the job is still running:
select * from dba_jobs_running;
^-- Checked! The job is not running.
 
8) LAST_DATE and NEXT_DATE
Check if the last_date and next_date for the job are proper:
select Job,Next_date,Last_date from dba_jobs where job=<job_number>;
^-- NEXT_DATE is proper, however LAST_DATE is null since the job never executes automatically.
9) NEXT_DATE and INTERVAL
Check if the Next_date is changing properly as per the interval set in dba_jobs:
select Job,Interval,Next_date,Last_date from dba_jobs where job=<job_number>;
^-- This is not possible since the job never gets executed automatically.
10) Toggle value for JOB_QUEUE_PROCESSES
Stop and restart CJQ process(es)
alter system set job_queue_processes=0 ;
--<Wait for some time to ensure CJQ process stopped>
alter system set job_queue_processes=4 ;
Ref: Bug 2649244 (fixed by: 9015, 9203, 10201)
^-- Done but did not help
11) Check for unsuccessful shutdowns:
 
A shutdown immediate may get canceled because active sessions prevent the database close operation.
Please review the alert log for the last two shutdown / startups and the messages:
SHUTDOWN: Active sessions prevent database close operation 
Please refer to
Note 434690.1 - Database Jobs Do Not Run After a Failed 'Shutdown Immediate'
12) DBMS_IJOB (non-documented package):
Either restart the database or try the following:
exec dbms_ijob.set_enabled(true);
Ref: Bug 3505718 (Closed, Not a Bug)
^-- Done but did not help
13) Check view DBA_SCHEDULER_GLOBAL_ATTRIBUTE for CURRENT_OPEN_WINDOW:
SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW'; 
If a window is open close it (e.g.): 
ATTRIBUTE_NAME                       VALUE 
---------------------------------       ----------------------------
CURRENT_OPEN_WINDOW       WEEKNIGHT_WINDOW
SQL> exec DBMS_SCHEDULER.close_window ('WEEKNIGHT_WINDOW');
^-- Done but did not help
 These are the most common causes for this behavior.

透過分析 M OS 文章 Jobs Not Executing Automatically (Doc ID 313102.1) ， 檢查所有的可能原因，並根據官方建議調整，但是問題依舊。此時，想短時間透過 D BMS_JOB 方式解決問題不太現實，因此嘗試使用高版本官方建議的 DBMS_SCHEDULER ，確認可行性。

 

 

4 、使用 DBMS_SCHEDULER 建立任務，觀察任務執行情況

建立 J OB2 ，並立即執行

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
   job_name          =>  'job2',
   job_type          =>  'PLSQL_BLOCK',
   job_action        =>  'dbms_mview.refresh(''"PMS"."MV_HEX_PMS_USER_XBRL"'');',
   start_date        =>  SYSDATE,
   repeat_interval   =>  'FREQ = MINUTELY; INTERVAL = 10',
   enabled           =>  true);
END;
/

檢查 DBA_SCHEDULER_JOBS 字典檢視，發現任務沒有自動執行

 

select OWNER,
       JOB_NAME,
       ENABLED,
       to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,
       REPEAT_INTERVAL,
       RUN_COUNT,
       to_char(LAST_START_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_START_DATE,
       to_char(LAST_RUN_DURATION, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DURATION,
       to_char(NEXT_RUN_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_RUN_DATE
  from DBA_SCHEDULER_JOBS
 where job_name = 'JOB2';
[object Object]

 
select * from dba_scheduler_job_log t where owner='PMS' and job_name='JOB2';

 

5 、建立測試任務 test _job ，並重啟 D BMS_JOB& DBMS_SCHEDULER 屬性

建立測試 test_job 任務

begin
dbms_scheduler.create_job (
job_name => 'test_job',
job_type => 'plsql_block',
job_action => 'null;',
start_date => SYSDATE,
repeat_interval => 'FREQ = MINUTELY; INTERVAL = 1',
enabled => true);
end;
/

 

檢查 D BMS_JOB& DBMS_SCHEDULER 屬性生效情況

select value from dba_scheduler_global_attribute where attribute_name='SCHEDULER_DISABLED';
未選定行
 
SQL> oradebug setmypid
SQL> oradebug dumpvar sga kkjsre
sword kkjsre_ [060040500, 060040504) = 00000001

 

先禁用任務屬性，再重新觸發生效

exec dbms_ijob.set_enabled(FALSE);
exec dbms_ijob.set_enabled(TRUE);
alter system set job_queue_processes=0;
alter system set job_queue_processes=500;
--使用者環境重啟DBMS_SCHEDULER屬性報錯分析
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
--exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

 

關閉 DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE 屬性，命中 O RA-1033 報錯

 

 

透過分析 M OS 文章 DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE Failing With ORA-01033. (Doc ID 756740.1) ，官方未給出明確的故障原因，等待一會之後重試成功，同時觀測到 DBMS_SCHEDULER 建立的任務正常定時自動執行。

 

 

這跟其他執行正常的 Oracle 相比有些異常，甚至是相反，但不排除是這套環境自身的原因，甚至是 B UG 。其實，官方原文中也有提到 Oracle 文件， 並 沒有支援完全禁用排程器的方法 ，只是確保在升級模式下，自動任務不會執行。

 

 

此時檢查任務 test_job 執行情況，發現該任務已經在自動排程了

select OWNER,
       JOB_NAME,
       ENABLED,
       to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,
       REPEAT_INTERVAL,
       RUN_COUNT,
       to_char(LAST_START_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_START_DATE,
       to_char(LAST_RUN_DURATION, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DURATION,
       to_char(NEXT_RUN_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_RUN_DATE
  from DBA_SCHEDULER_JOBS
 where job_name = 'TEST_JOB';

 

同理，此時業務任務 job 2 ，同樣已經在自動排程了，如下：

select OWNER,
       JOB_NAME,
       ENABLED,
       to_char(START_DATE, 'yyyy-mm-dd hh24:mi:ss') START_DATE,
       REPEAT_INTERVAL,
       RUN_COUNT,
       to_char(LAST_START_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_START_DATE,
       to_char(LAST_RUN_DURATION, 'yyyy-mm-dd hh24:mi:ss') LAST_RUN_DURATION,
       to_char(NEXT_RUN_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_RUN_DATE
  from DBA_SCHEDULER_JOBS
 where job_name = 'JOB2';

 

select * from dba_scheduler_job_log t where owner='PMS' and job_name='JOB2';

 

 

解決辦法

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');