Eliminar sessão

Matar uma sessão do usuário é um dos maiores prazeres que um DBA pode ter, ainda mais sem a ciência daquele usuário mala :) . Brincadeiras a parte, as vezes temos que eliminar algumas sessões, o que deveria ser algo fácil.

Por exemplo, para matar a sessao do "zemane" que está consumindo recursos pois foi executada erroneamente, deve-se executar o comando:

SQL> ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

Onde SID e SERIAL# são obtidos pelo select abaixo. Acontece que em alguns casos, a sessão morta, continuá lá atrapalhando o banco com o status de KILLED.

SQL> SELECT S.USERNAME, S.MACHINE, S.OSUSER, S.STATUS,
S.LAST_CALL_ET LAST,  S.SID, S.SERIAL#, P.SPID
FROM V$SESSION S, V$PROCESS P
WHERE S.PADDR = P.ADDR
ORDER BY S.USERNAME, S.MACHINE, S.OSUSER

USERNAME  MACHINE  OSUSER    STATUS   LAST   SID   SERIAL# SPID                
--------- -------- --------- -------- ------ ----- ------- ----
LOC_DEV   DEV314   jninguem  INACTIVE 10494  51    3589    618                
LOC_DEV   DEV317   zemane    ACTIVE   18126  82    21437   222                 
LOC_HOM   SRV004   brockbus  INACTIVE 10917  120   19171   123                 
...

47 rows selected

SQL>alter system kill session '82, 21437' ;

alter system kill succeeded.

SQL> SELECT S.USERNAME, S.MACHINE, S.OSUSER, S.STATUS, ...

USERNAME  MACHINE  OSUSER    STATUS   LAST   SID   SERIAL# SPID                
--------- -------- --------- -------- ------ ----- ------- ----
LOC_DEV   DEV314   jninguem  INACTIVE 10494  51    3589    618                
LOC_DEV   DEV317   zemane    KILLED   18126  82    21437   222                 
LOC_HOM   SRV004   brockbus  INACTIVE 10917  120   19171   123                 
...

47 rows selected

Nesses casos, temos que matar o processo no sistema operacional.

No linux:

$ kill -9 2222

Onde 222 é o SPID obtido no select acima.

Agora no windows, acontecem 2 coisas interessantes.
1. O Windows não abre um processo para cada cliente. Os clientes são threads do processo principal (Oracle).
2. Não existe o comando kill no Windows.
Por isso a Oracle disponibilizou o comando ORAKILL <ORACLE_SID> <serial#>

> orakill ORCL 21437

Assim o processo é eliminado do banco de dados.
PS. Matar um processo via sistema operacional não deixa log no banco de dados.

Transações pendentes

Keywords: distributed transaction, ORA-01519, Pending trans, Two Phase Commit

Quando uma transação distribuída(Two Phase Commit) não consegue ser completada por problemas de conexão ou qualquer outro motivo, ocorre uma transação pendente. Geralmente este problema é detectado pelo usuário a partir do erro ORA-01591.

SQL> select count(1) from client;

select count(1) from client
*
ERROR on line 1:
ORA-01591: lock held by in-doubt distributed transaction 24.10.45567

Para eliminar a transação, basta verificar o local_tran_id da transação na tabela PENDING_TRANS$ e forçar o rollback. Outras views úteis em ambientes distribuídos são a dba_2pc_pending e dba_2pc_neighbors.

SQL> SELECT LOCAL_TRAN_ID, STATE, FAIL_TIME FROM PENDING_TRANS$

LOCAL_TRAN_ID  STATE             FAIL_TIME
-------------- ----------------- -----------
24.10.45567    pending           15-JAN-09
12.33.2760     collecting        05-JAN-09
17.37.2807     forced rollback   15-JAN-09

3 rows selected

SQL> rollback force '12.33.2760';

Rollback complete.


SQL> alter system enable distributed recovery;

System altered.

SQL> -- Para Oracle 9i, limpar no segmento de undo as transacoes presas
SQL> -- com o comando não documentado:
SQL> -- alter session set "_smu_debug_mode" = 4;

SQL> exec dbms_transaction.purge_lost_db_entry ('12.33.2760')

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(1) from client;

COUNT(1)            
----------------------
36                  

1 rows selected

A transação 12.33.2760 está com o state collecting e fail_time 05-JAN-08 também deve ser eliminada já que hoje "estamos" em 16 de janeiro de 2009, portanto teremos que fazer isso manualmente(mesmo procedimento acima) já que o processo RECO não conseguiu fazê-lo.

A parte Oracle Internals!

Se você tiver problemas para identificar a transação, transação fantasmta, você pode inserir valores nas tabelas pending_sessions$ e pending_trans$. O único valor variável é o id da transação. Não sei se isso será útil a alguém, mas como encontrei isso e achei interessante, segue o log.

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
 2  KTUXESTA Status,
 3  KTUXECFL Flags
 4  FROM x$ktuxe

 KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
        4         18       4935 PREPARED         SCO|COL|REV|DEAD

SQL> -- Detectou a transação fantasma

SQL> INSERT INTO PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT,
 2  GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR, RECO_VECTOR,TYPE#,
 3  FAIL_TIME,RECO_TIME) VALUES(
 4  '4.18.4935', 306206, 'xxxxxxx.12345.1.2.3', 'prepared','p',
 5  HEXTORAW('00000001'), HEXTORAW('00000000'), 0, SYSDATE, SYSDATE );

1 row created.

SQL> -- Todos os valores são constantes exceto o local_tran_id.

SQL> INSERT INTO PENDING_SESSIONS$ VALUES('4.18.4935',1,
 2  HEXTORAW('05004F003A1500000104'),'C',0,30258592,'',146);

1 row created.

commit;

SQL> SELECT LOCAL_TRAN_ID FROM PENDING_SESSIONS$

LOCAL_TRAN_ID        
----------------------
4.18.4935       

SQL> SELECT LOCAL_TRAN_ID, STATE, FAIL_TIME FROM PENDING_TRANS$;

LOCAL_TRAN_ID      STATE            FAIL_TIME   
------------------ ---------------- ------------------
4.18.4935          prepared         15-JAN-09

SQL> ROLLBACK FORCE '4.18.4935';

Rollback complete.

SQL> ALTER SESSION SET "_smu_debug_mode" = 4;

Session altered.

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935');

PL/SQL procedure successfully completed.

Bibliografia :)
http://profissionaloracle.com.br/blogs/rodrigoalmeida/2008/08/11/transacoes-pendentes-em-ambiente-distribuido/
http://www.freelists.org/post/oracle-l/lock-held-by-indoubt-distributed-transaction,4