資料庫恢復狀態可能導致JOB無法自動執行

這是一個測試資料庫。前一段時間執行了一個建立分割槽表的SQL語句，目的是測試最大分割槽數，因此設定的分割槽數是1048575。關於這個測試的詳細資訊可以參考：http://yangtingkun.itpub.net/post/468/499902

這個SQL執行時間超過了一個星期，由於是測試資料庫，我也沒有在意，本打算看看是否能最終跑完。沒想到前兩天測試機房斷電，於是這個伺服器就異常關閉了。

嘗試啟動資料庫，並未發現異常，資料庫的啟動也不算太慢。不過當使用者訪問TAB同義詞的時候，出現了長時間的等待，顯然Oracle運用了快速恢復啟動的技術，由於前面的CREATE TABLE已經建立了大量的分割槽資訊，而這些資訊由於沒有最終提交因此都需要進行回滾。可以想象，這個執行時間超過1個星期的SQL，在回滾的時候顯然也會需要很長的時間，Oracle為了避免資料庫長時間無法開啟，採用了延遲迴滾的技術。資料庫的日誌一旦恢復完成，就可以開啟使用，回滾並不會馬上進行。如果使用者觸發到需要回滾的資料，則會發生等待，直到資料庫處理完需要回滾的操作為止。

正是這種技術，使得Oracle可以快速的開啟並使用，而後臺會繼續處理這些回滾的操作，除非使用者訪問到需要回滾的資料，否則不會意識到資料庫的操作。

不過除了上面提到的訪問TAB等檢視被鎖之外，還發現了一個奇怪的現象，資料庫的JOB無法自動執行：

SQL> conn yangtk/yangtk
已連線。
SQL> create table t_id (id number);

表已建立。

SQL> select * from user_jobs;

未選定行

SQL> show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     100
SQL> declare
  2  v_job number;
  3  begin
  4  dbms_job.submit(v_job, 'begin insert into t_id values (1); commit; end;', sysdate, 'sysdate + 1');
  5  commit;
  6  end;
  7  /

PL/SQL 過程已成功完成。

SQL> select * from t_id;

未選定行

SQL> select job, this_date, next_date, total_time, broken, failures
  2  from user_jobs;

       JOB THIS_DATE      NEXT_DATE      TOTAL_TIME B   FAILURES
---------- -------------- -------------- ---------- - ----------
        63                20-5月 -10              0 N

可以看到，JOB並未自動執行，手工執行JOB則沒有問題：

SQL> exec dbms_job.interval(63, 'sysdate + 1/1440');

PL/SQL 過程已成功完成。

SQL> commit;

提交完成。

SQL> exec dbms_job.run(63)

PL/SQL 過程已成功完成。

SQL> select * from t_id;

        ID
----------
         1

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> select job, this_date, next_date, total_time, broken, failures
  2  from user_jobs;

       JOB THIS_DATE           NEXT_DATE           TOTAL_TIME B   FAILURES
---------- ------------------- ------------------- ---------- - ----------
        63                     2010-05-20 02:18:13          0 N          0

SQL> select sysdate from dual;

SYSDATE
-------------------
2010-05-20 02:19:12

SQL> select * from t_id;   

        ID
----------
         1

SQL> select job, this_date, next_date, total_time, broken, failures
  2  from user_jobs;

       JOB THIS_DATE           NEXT_DATE           TOTAL_TIME B   FAILURES
---------- ------------------- ------------------- ---------- - ----------
        63                     2010-05-20 02:18:13          0 N          0

但是執行JOB後，這個JOB並沒有繼續自動執行，而是停了下來。檢查作業系統發現cjq0程式存在，但是不存在J00N程式。檢查alert檔案，沒有發現JOB相關的錯誤。

嘗試透過設定JOB_QUEUE_PROCESSES為0，然後在設定一個非0值，來重新喚起JOB程式：

SQL> alter system set job_queue_processes = 0;

系統已更改。

SQL> alter system set job_queue_processes = 20;

系統已更改。

正常情況下，設定JOB_QUEUE_PROCESSES引數為0，會導致CJQ0程式推出，但是等待了很長時間發現CJQ0程式並未退出，只好重新設定JOB_QUEUE_PROCESSES為非零值。

透過DBMS_IJOB.ENABLE檢查資料庫的JOB是否正常：

SQL> CONN / AS SYSDBA
已連線。
SQL> SET SERVEROUT ON
SQL> BEGIN
  2  IF DBMS_IJOB.ENABLED THEN
  3  DBMS_OUTPUT.PUT_LINE('TRUE');
  4  ELSE
  5  DBMS_OUTPUT.PUT_LINE('FALSE');
  6  END IF;
  7  END;
  8  /
TRUE

PL/SQL 過程已成功完成。

強制關閉JOB，然後在啟用，問題依舊：

SQL> EXEC DBMS_IJOB.SET_ENABLED(FALSE)

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_IJOB.SET_ENABLED(TRUE)

PL/SQL 過程已成功完成。

登陸目標使用者，JOB仍然沒有執行：

SQL> conn yangtk/yangtk
已連線。
SQL> select * from t_id;   

        ID
----------
         1

SQL> select job, this_date, next_date, total_time, broken, failures
  2  from user_jobs;

       JOB THIS_DATE           NEXT_DATE           TOTAL_TIME B   FAILURES
---------- ------------------- ------------------- ---------- - ----------
        63                     2010-05-20 02:18:13          0 N          0

由於時間比較晚，沒有再繼續追查下去。而隨後一天比較忙，也沒有來得及看

等到第三天準備繼續檢查這個問題的時候，發現JOB已經開始自動執行了：

SQL> select job, this_date, next_date, total_time, broken, failures
  2  from user_jobs;
       JOB THIS_DATE           NEXT_DATE           TOTAL_TIME B   FAILURES
---------- ------------------- ------------------- ---------- - ----------
        63 21-5月 -10          21-5月 -10                  42 N          0

SQL> select * from t_id;

        ID
----------
         1
         1
.
.
.
         1
         1
         1
         1

已選擇2113行。

和JOB沒有啟動時唯一的區別在於，Oracle後臺的回滾操作已經完成。

存在兩種可能性，一種是Oracle在自動回滾的過程中禁止JOB的執行，避免衝突的發生；另一種可能是嘗試啟動JOB的時候，Oracle訪問了資料字典，但是被自動回滾任務鎖住。