Oracle 19c solves ORA-00026 when killing PDB sessions

Oracle Multitenant was a big architectural change for Oracle Database. I guess it was not so easy to introduce it to such a complex system. Unsurprisingly, there were some side effects introduced, but Oracle works hard to correct them. One example is ORA-00026: missing or invalid session ID thrown while trying to kill specific sessions, e.g. Data Pump workers or DBMS_SCHEDULER ones.

Looking into Alter System Kill Session Failed With ORA-00026 When Kill a Job Session From PDB (Doc ID 2491701.1) (last update Jan 30, 2020) we can see that this is an expected behaviour:

Screenshot 2020-07-17 at 11.24.17

However, if we check description of Bug 27457666 – Unable To Kill Data Pump Worker Sessions (Doc ID 27457666.8), we can see that the problem should be solved since 19c (also in RU updates for 18c). As you can see below, my test cases confirmed that it really works now, both for Data Pump workers and DBMS_SCHEDULER.

12.2 – DBMS_SCHEDULER – user setup is listed here once, the same has been done for 19.6

-- 1st session
SQL> alter session set container = PDB12C;

Session altered.

SQL> create user sess_kill identified by "**********" quota unlimited on ts_default;

User created.

SQL> grant create session, create job to sess_kill;

Grant succeeded.

SQL> grant execute on sys.dbms_lock to sess_kill;

Grant succeeded.

SQL> create user sess_kill_admin identified by "**********";

User created.

SQL> grant dba to sess_kill_admin;

Grant succeeded.

SQL> conn sess_kill_admin/**********@//host.domain:1521/pdb12c
Connected.

-- 2nd session (SESS_KILL)
SQL> conn sess_kill/**********@//host.domain:1521/pdb12c
Connected.

SQL> exec dbms_scheduler.create_job (job_name => 'SESS_KILL_TEST', job_type => 'PLSQL_BLOCK', job_action => 'begin sys.dbms_lock.sleep(300); end;', enabled => TRUE);

PL/SQL procedure successfully completed.

-- 1st session (SESS_KILL_ADMIN)

SQL> select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'SESS_KILL%';

   INST_ID        SID    SERIAL# USERNAME             ACTION               PROGRAM                       SERVICE_NAME      CON_ID
---------- ---------- ---------- -------------------- -------------------- ----------------------------- ------------- ----------
         1        423      19725 SESS_KILL_ADMIN                           sqlplus@host.domain (TNS V1-  pdb12c                 9
         1        792       7895 SESS_KILL_ADMIN                           oracle@host.domain (PPA7)     pdb12c                 9
         1       1176      18578 SESS_KILL                                 sqlplus@host.domain (TNS V1-  pdb12c                 9
         1       1181      34483 SESS_KILL            SESS_KILL_TEST       oracle@host.domain (J000)     SYS$USERS              9
         2         77      20157 SESS_KILL_ADMIN                           oracle@host.domain (PPA7)     pdb12c                 9

SQL> alter system kill session '1181,34483';
alter system kill session '1181,34483'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

SQL> exec dbms_scheduler.stop_job(job_name => 'SESS_KILL.SESS_KILL_TEST');

PL/SQL procedure successfully completed.

19.6 – DBMS_SCHEDULER

-- 1st session (SESS_KILL_ADMIN)

SQL> select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'SESS_KILL%';

   INST_ID        SID    SERIAL# USERNAME             ACTION               PROGRAM                       SERVICE_NAME      CON_ID
---------- ---------- ---------- -------------------- -------------------- ----------------------------- ------------- ----------
         2        430      53071 SESS_KILL                                 sqlplus@host.domain (TNS V1-  pdb19c                 5
         2       1127      47636 SESS_KILL_ADMIN                           sqlplus@host.domain (TNS V1-  pdb19c                 5
         2       1409      11706 SESS_KILL            SESS_KILL_TEST       oracle@host.domain (J000)     SYS$USERS              5

SQL> alter system kill session '1409,11706';

System altered.

SQL> select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'SESS_KILL%';

   INST_ID        SID    SERIAL# USERNAME             ACTION               PROGRAM                       SERVICE_NAME      CON_ID
---------- ---------- ---------- -------------------- -------------------- ----------------------------- ------------- ----------
         2        430      53071 SESS_KILL                                 sqlplus@host.domain (TNS V1-  pdb19c                 5
         2       1127      47636 SESS_KILL_ADMIN                           sqlplus@host.domain (TNS V1-  pdb19c                 5

12.2 – EXPDP

-- 1st session (SESS_KILL_ADMIN)

SQL> select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'SESS_KILL%';

   INST_ID        SID    SERIAL# USERNAME             ACTION               PROGRAM                       SERVICE_NAME      CON_ID
---------- ---------- ---------- -------------------- -------------------- ----------------------------- ------------- ----------
         1        804      21138 SESS_KILL_ADMIN                           oracle@host.domain (PPA7)     pdb12c                 9
         1        806      37686 SESS_KILL_ADMIN                           sqlplus@host.domain (TNS V1-  pdb12c                 9
         2         72       2478 SESS_KILL_ADMIN                           oracle@host.domain (PPA7)     pdb12c                 9

-- 2nd session (OS)
        
expdp sess_kill_admin/**********@//host.domain:1521/pdb12c full=y directory=data_pump_dir dumpfile=test.dmp logfile=test.log parallel=1
 
-- 1st session (SESS_KILL_ADMIN)

SQL> select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'SESS_KILL%';

   INST_ID        SID    SERIAL# USERNAME             ACTION               PROGRAM                       SERVICE_NAME                       CON_ID
---------- ---------- ---------- -------------------- -------------------- ----------------------------- ------------------------------ ----------
         1         21      10518 SESS_KILL_ADMIN      SYS_EXPORT_FULL_02   oracle@host.domain (DW00)     SYS$BACKGROUND                          0
         1        411      52219 SESS_KILL_ADMIN      SYS_EXPORT_FULL_02   oracle@host.domain (DM00)     SYS$USERS                               9
         1        804      28948 SESS_KILL_ADMIN                           oracle@host.domain (PPA7)     pdb12c                                  9
         1        806      37686 SESS_KILL_ADMIN                           sqlplus@host.domain (TNS V1-  pdb12c                                  9
         1       1177      32506 SESS_KILL_ADMIN                           ude@host.domain (TNS V1-V3)   pdb12c                                  9
         2         81      27287 SESS_KILL_ADMIN                           oracle@host.domain (PPA7)     pdb12c                                  9

6 rows selected.

SQL> alter system kill session '21,10518';
alter system kill session '21,10518'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

-- 2nd session (OS)

^C
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

19.6 – EXPDP

-- 1st session (SESS_KILL_ADMIN)

SQL> select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'SESS_KILL%';

   INST_ID        SID    SERIAL# USERNAME             ACTION               PROGRAM                       SERVICE_NAME      CON_ID
---------- ---------- ---------- -------------------- -------------------- ----------------------------  ------------- ----------
         2       1409      59216 SESS_KILL_ADMIN                           sqlplus@host.domain (TNS V1-  pdb19c                 5
         
-- 2nd session (OS)

expdp sess_kill_admin/**********@//host.domain:1521/pdb19c full=y directory=data_pump_dir dumpfile=test.dmp logfile=test.log parallel=1
 
-- 1st session (SESS_KILL_ADMIN)

SQL> select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'SESS_KILL%';

   INST_ID        SID    SERIAL# USERNAME             ACTION               PROGRAM                       SERVICE_NAME      CON_ID
---------- ---------- ---------- -------------------- -------------------- ----------------------------- ------------- ----------
         2        430      21455 SESS_KILL_ADMIN                           ude@host.domain (TNS V1-V3)   pdb19c                 5
         2        842      45379 SESS_KILL_ADMIN      SYS_EXPORT_FULL_01   oracle@host.domain (DM00)     pdb19c                 5
         2       1127       1685 SESS_KILL_ADMIN      SYS_EXPORT_FULL_01   oracle@host.domain (DW00)     pdb19c                 5
         2       1409      59216 SESS_KILL_ADMIN                           sqlplus@host.domain (TNS V1-  pdb19c                 5

SQL> alter system kill session '1127,1685';

System altered.

-- 2nd session (OS)

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00028: your session has been killed

Job "SESS_KILL_ADMIN"."SYS_EXPORT_FULL_01" stopped due to fatal error at Fri Jul 17 11:33:14 2020 elapsed 0 00:00:30

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s