[20211209]pdb資料庫kill job遇到的奇怪情況.txt

[20211209]pdb資料庫kill job遇到的奇怪情況.txt

--//在18c上測試遇到的問題，做一個記錄。job程式無法清除在pdb層面上，並且在cdb層面也出現一點點情況，不過程式倒是可以kill。

1.環境：
orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 18.0.0.0.0
BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.測試：
orcl> @ spid
       SID    SERIAL# PROCESS                                          SERVER             SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------------------------------ ------------------ ------ ------- ---------- --------------------------------------------------
        14      63048 62441                                            DEDICATED          2890        64     115089 alter system kill session '14,63048' immediate;

--//開啟另外會話。
orcl> alter system kill session '14,63048' immediate;
System altered.

--//可以發現程式倒是可以kill掉。也就是在pdb層面上這樣殺會話程式沒有問題。

3.測試2：
create or replace procedure test_load_as_select is
  type  num_tab is table of number;
 l_tab num_tab;
begin
  for i in 1..1000000 loop
   with sq1 as (select /*+ materialize */ level x from dual connect by level <= 1000000)
       ,sq2 as (select /*+ materialize */ x from sq1 where x <= 999999)
       ,sq3 as (select /*+ materialize */ x from sq1 where x <= 888888)
   select x bulk collect into l_tab from sq3
   where x <= 3;
 end loop;
end;
/
--//注意：測試使用提示materialize，使用臨時表空間，產生少量日誌，主要是懶得再做例子。

create or replace procedure test_load_as_select_job(p_job_cnt number) as
  l_job_id pls_integer;
begin
  for i in 1..p_job_cnt loop
    dbms_job.submit(l_job_id, 'begin while true loop test_load_as_select; end loop; end;');
  end loop;
  commit;
end;    
/
--//注:程式碼是死迴圈。

create or replace procedure clean_jobs as
begin
  for c in (select job from dba_jobs) loop
    begin
       dbms_job.remove (c.job);
--  exception when others then null;
    end;
    commit;
  end loop;

  for c in (select d.job, d.sid, (select serial# from v$session where sid = d.sid) ser from dba_jobs_running d) loop
    begin
      execute immediate 'alter system kill session '''|| c.sid|| ',' || c.ser|| ''' immediate';
      dbms_job.remove (c.job);
--  exception when others then null;
    end;
    commit;
  end loop;
 
  -- select * from dba_jobs;
  -- select * from dba_jobs_running;
end;
/

--//注：我註解了exception部分。

orcl> exec test_load_as_select_job(1);
PL/SQL procedure successfully completed.

orcl> select * from DBA_JOBS
  2  @ pr
==============================
JOB                           : 28
LOG_USER                      : TTT
PRIV_USER                     : TTT
SCHEMA_USER                   : TTT
LAST_DATE                     :
LAST_SEC                      :
THIS_DATE                     : 2021-12-09 09:39:37
THIS_SEC                      : 09:39:37
NEXT_DATE                     : 2021-12-09 09:39:14
NEXT_SEC                      : 09:39:14
TOTAL_TIME                    : 26
BROKEN                        : N
INTERVAL                      : null
FAILURES                      :
WHAT                          : begin while true loop test_load_as_select; end loop; end;
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
MISC_ENV                      : 0102000000000000
INSTANCE                      : 0
PL/SQL procedure successfully completed.

orcl> select * from DBA_JOBS_RUNNING ;
       SID        JOB   FAILURES LAST_DATE           LAST_SEC         THIS_DATE           THIS_SEC           INSTANCE
---------- ---------- ---------- ------------------- ---------------- ------------------- ---------------- ----------
        27         28                                                 2021-12-09 09:39:37 09:39:37                  0

orcl> column status format a10
orcl> @ trans
       SID    SERIAL# USERNAME                  TADDR            SES_ADDR          USED_UBLK  USED_UREC             0xFLAG STATUS     START_DATE              XIDUSN    XIDSLOT     XIDSQN XID              PRV_XID          PTX_XID
---------- ---------- ------------------------- ---------------- ---------------- ---------- ---------- ------------------ ---------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
        27      46598 TTT                       000000008C2F6BF8 0000000093F7E380          1          1   4001623          ACTIVE     2021-12-09 09:40:44          7         21     108059 070015001BA60100 0000000000000000 0000000000000000
--//產生少量日誌。

orcl> @ sid 27
sid = 27
SPID       PID        SID    SERIAL# CLIENT_INFO          PNAME  TRACEFILE                                                                    PROGRAM                                  TERMINAL     SQL_ID                     STATUS           C50
------ ------- ---------- ---------- -------------------- ------ ---------------------------------------------------------------------------- ---------------------------------------- ------------ -------------------------- ---------------- --------------------------------------------------
5212        64         27      46598                      J000   /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_j000_5212.trc       oracle@xxxx2 (J000)                      UNKNOWN      a3376g221j75v              ACTIVE           alter system kill session '27,46598' immediate;

orcl> exec clean_jobs
BEGIN clean_jobs; END;

*
ERROR at line 1:
ORA-00026: missing or invalid session ID
ORA-06512: at "TTT.CLEAN_JOBS", line 14
ORA-06512: at "TTT.CLEAN_JOBS", line 14
ORA-06512: at line 1
--//可以發現clean_jobs指令碼報錯，出現ORA-00026: missing or invalid session ID。也就是無法清除job程式。

orcl> select * from DBA_JOBS_RUNNING ;
       SID        JOB   FAILURES LAST_DATE           LAST_SEC         THIS_DATE           THIS_SEC           INSTANCE
---------- ---------- ---------- ------------------- ---------------- ------------------- ---------------- ----------
        27         28
--//清除其它資訊，但是job還在執行。

orcl> @ trans
       SID    SERIAL# USERNAME                  TADDR            SES_ADDR          USED_UBLK  USED_UREC             0xFLAG STATUS     START_DATE              XIDUSN    XIDSLOT     XIDSQN XID              PRV_XID          PTX_XID
---------- ---------- ------------------------- ---------------- ---------------- ---------- ---------- ------------------ ---------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
        27      46598 TTT                       000000008C2F6BF8 0000000093F7E380          1          1   4001623          ACTIVE     2021-12-09 09:43:27          6         28      90712 06001C0058620100 0000000000000000 0000000000000000
--//可以發現job程式還在執行。

orcl> @ killi sid=27
COMMANDS_TO_VERIFY_AND_RUN
----------------------------------------------------------------------------------
alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000));

orcl> alter system kill session '27,46598' immediate ;
alter system kill session '27,46598' immediate
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

--//報ORA-00026，你可以發現job一直在執行。如果不使用clean_jobs指令碼，手工刪除session報錯一樣。使用sys使用者操作也是一樣。

# ps -elf | egrep "j0[0]|PI[D]"
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 R oracle    5212     1 62  80   0 - 1350671 -    09:39 ?        00:04:15 ora_j000_orclcdb

--//在cdb層面操作。
SYS@192.168.2.7:1521/orclcdb> alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000));
alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000))
*
ERROR at line 1:
ORA-00031: session marked for kill
--//注意提示是ORA-00031: session marked for kill。你可能注意前面沒有這個提示。

SYS@192.168.2.7:1521/orclcdb> alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000));
alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000))
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

--//實際上這時程式kill，已經不存在了。也許遇到這樣情況直接kill程式還是最方便，不知道為什麼出現這樣的情況。

4.收尾檢查：
orcl> select * from DBA_JOBS_RUNNING ;
no rows selected

orcl> select * from DBA_JOBS ;
no rows selected

orcl> @ trans
no rows selected