Páginas

26 de mai. de 2014

Matando sessões no Oracle Database

 
Olá pessoal,

     No artigo de hoje vou comentar sobre como Matar (ou eliminar) sessões de usuários em Bancos de Dados Oracle, outro assunto que acrescento em meu blog e que já vi muitas dúvidas em fóruns e grupos de discussão. Essas dúvidas normalmente surgem em profissionais que trabalham com Banco de Dados (BD) Oracle, e que não sabem ao certo qual o melhor método (ou método efetivo) para matar uma sessão de usuário do BD.


     Em 1º lugar, é importante entender que existem várias formas de executar esta atividade e que cada uma tem as suas características, vantagens e desvantagens, e é isso que vou explicar neste artigo! Algumas das causas mais comuns que demandam a atividade de matar sessões de usuários no BD são:

          a) Atualizações de sistemas:
             Surge a necessidade de atualizar um sistema e para fazer essa atualização é necessário que nenhum usuário desse sistema esteja conectado no BD.

          b) Sistemas lentos ou congelados:
             Um determinado sistema, executando um SQL no BD, apresenta lentidão ou encontra-se em um estado praticamente congelado. Nestes casos, devemos matar as sessão(ões) que está(ão) bloqueando ou consumindo muitos recursos, para depois encontrar e corrigir a origem o problema.
   
          c) Execução de operações indevidas:
             Uma determinada funcionalidade de um sistema está executando uma instrução SQL no BD, mas foi iniciada erroneamente e o seu SQL precisa ser cancelado.
 
     Quando nos deparamos com alguma dessas situações, para matar a sessão de usuário, precisamos primeiro identificar qual é a sessão, para depois matá-la através de 1 de 3 métodos disponíveis, que apresentarei ao longo deste artigo.

     Para identificar a sessão de usuário que precisamos matar, execute 1 de 2 consultas que apresentarei abaixo:

          1- Pesquisar nas visões V$SESSION e V$PROCESS, o valor das colunas SID, SERIAL# e SPID, filtrando por alguma informação que temos do usuário, tal como: nome da conta de usuário no BD ou do Sistema Operacional (SO), nome do programa ou módulo que ele está executando, nome da máquina que abriu conexão com o BD etc. 
  
              Exemplo (descomente as linhas AND da cláusula WHERE, se necessário, fazendo as devidas substituições):
         SELECT  S.SIDS.SERIAL#P.SPIDS.USERNAME, 
               S.STATUS, S.OSUSER, S.MACHINE, 
               S.PROGRAM, S.MODULE, 
               TO_CHAR(S.LOGON_TIME, 'dd/mm/yyyy hh24:mi:ss') LOGON_TIME, 
               S.blocking_session -- id da sessao bloqueadora (qdo for o caso)
       FROM    V$SESSION S
       JOIN    V$PROCESS P
         ON    P.addr = S.paddr
       WHERE   S.TYPE = 'USER'          
       -- AND  S.USERNAME = 'X1' -- substituir X1 pelo nome do usuario no BD
       -- AND  S.OSUSER = 'X2'  -- substituir X2 pelo nome do usuario no SO
       -- AND  S.MACHINE = 'X3' -- substituir X3 pelo nome da máquina q está se conectando no BD
       -- AND  S.PROGRAM = 'X4-- substituir X4 pelo nome do programa q está se conectando no BD

          2- Pesquisar nas visões V$SESSION e V$SQL, o valor das colunas SID, SERIAL# SPID,  utilizando os mesmos filtros do item anterior (se for necessário) + 1 filtro pela instrução SQL que foi executada.

              Exemplo (descomente as linhas AND da cláusula WHERE, se necessário, fazendo as devidas substituições):
         SELECT  S.SID, S.SERIAL#P.SPID, S.USERNAME,
               S.STATUS, S.OSUSER, S.MACHINE,
               S.PROGRAM, S.MODULE,
               TO_CHAR(S.LOGON_TIME, 'dd/mm/yyyy hh24:mi:ss') LOGON_TIME,
               S.blocking_session, -- id da sessao bloqueadora (qdo for o caso)
               DBMS_LOB.SUBSTR(a.SQL_FULLTEXT, 4000,1) sql_text
         FROM    V$SESSION S
       JOIN    V$PROCESS P
         ON    P.addr = S.paddr
         JOIN    V$SQLAREA A
          ON   s.sql_hash_value = a.hash_value
         WHERE   TYPE = 'USER'
         -- AND  S.USERNAME = 'X1' -- substituir X1 pelo nome do usuario no BD
         -- AND  S.OSUSER = 'X2'  -- substituir X2 pelo nome do usuario no SO
         -- AND  S.MACHINE = 'X3' -- subst. X3 pelo nome da máquina q está se conectando no BD
         -- AND  S.PROGRAM = 'X4' -- subst. X4 pelo nome do programa q está se conectando no BD
       -- AND  A.sql_text like '%X5%' -- subst. X5 por parte do SQL desejado
     
  
     Agora com o SID, SERIAL# e SPID da sessão desejada em mãos, podemos matá-la utilizando 1 de 3 métodos disponíveis, que apresentarei abaixo:

      
     1- Comando ALTER SYSTEM KILL SESSION
  
     O comando ALTER SYSTEM KILL SESSION é a opção mais segura para matar uma sessão de usuário. Para executá-lo, precisamos apenas do SID e SERIAL# da sessão. A sintaxe básica dele é:
SQL> ALTER SYSTEM KILL SESSION 'sid, serial#'; -- substitua sid e serial# pelos valores encontrados no passo anterior
   
     Se você for executar este comando em um ambiente RAC, opcionalmente acrescente o identificador da instância (ou INST_ID), consultando a visão GV$SESSION, ao invés de V$SESSION, como no exemplo abaixo:
SQL> ALTER SYSTEM KILL SESSION 'sid, serial#, @inst_id'; 

     Um problema do comando apresentado acima é que ele não mata a sessão imediatamente. Ele apenas pede para ela se matar. Este comando foi projetado para finalizar a sessão de forma sutil, enviando uma mensagem para o usuário do tipo ORA-00028: your session was killed, e aguardando uma resposta dele. Se a sessão tiver uma transação ativa, um rollback deverá ocorrer e somente depois desta operação é que o usuário receberá a mensagem. Se a sessão estiver ociosa, o usuário só receberá a mensagem de erro quando tentar usar novamente o sistema vinculado àquela sessão. O mais importante é entender que após executar este comando, a sessão só será encerrada definitivamente quando o usuário tentar operar o sistema, após a mensagem de erro. Neste momento, o processo de usuário envia uma resposta para o processo servidor do SGBD e finalmente a sessão é encerrada! Em alguns casos, a sessão poderá ficar bloqueada (ou poderá falhar) e o processo de usuário não conseguirá notificar o processo servidor. Nestes casos, adivinhe o que irá acontecer? O processo de servidor ficará eternamente aguardando a resposta da sessão de usuário e a sessão nunca será finalizada (isso já aconteceu comigo várias vezes)! Por este motivo, existem os métodos que apresentarei nos itens 2 e 3.

     O comando ALTER SYSTEM KILL SESSION possui a cláusula IMMEDIATE, que pode ser utilizada para retornar o controle da sessão imediatamente.  Sem esta cláusula, ao executá-lo, você terá que aguardar o rollback de alguma transação existente. Exemplo:
SQL> ALTER SYSTEM KILL SESSION 'sid, serial#' IMMEDIATE;
  
     2- Comando ALTER SYSTEM DISCONNECT SESSION
  
        O comando ALTER SYSTEM DISCONNECT SESSION é um método mais efetivo para matar sessões de BD e deve ser usado quando for desejado matar e desconectar uma sessão de modo forçado, imediatamente. Este método libera os recursos utilizados pela sessão de forma realmente imediata, mas pode ser perigoso! Ao invés de solicitar que a sessão faça a sua auto-eliminação, ele mata diretamente o processo servidor do SO vinculado à ela. Por este motivo, tenha cuidado ao executá-lo em ambientes com Conexões Compartilhadas, pois ao tentar matar uma sessão, você poderá matar várias sessões que estão vinculadas ao mesmo processo servidor do SO.
  
     A sintaxe básica deste comando é similar ao do comando ALTER SYSTEM KILL SESSION com a cláusula adicional POST TRANSACTION (aguarda transações em execução terminarem, sem forçar rollback) ou IMMEDIATE (finaliza e faz rollback nas transações em execução, imediatamente). Veja um exemplo na Imagem 01.


Imagem 01 - Matando sessão com ALTER SYSTEM DISCONNECT SESSION

     Para executar o comando ALTER SYSTEM KILL SESSION, precisamos apenas do SID SERIAL# da sessão, como nos exemplos abaixo:
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid, serial#' POST_TRANSACTION
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid, serial#' IMMEDIATE;

     A grande vantagem de executar este comando é que ele permite liberar os recursos da sessão imediatamente, pois ele desconecta a sessão e deixa para o PMON (Process Monitor) finalizar a transação!

   
3- Comando KILL
  
     O comando KILL é um método similar ao anterior, com uma pequena diferença: ao invés de matar a sessão através de um comando do SGBD, ele irá matá-la através de um comando do SO. Para executá-lo você precisará apenas do SPID da sessão. 

     Exemplo de como executar o KILL em SO Windows (fornecendo também o ORACLE_SID do BD desejado):
C:> orakill ORACLE_SID spid 

     Exemplos de como executar o KILL em SO Unix/Linux:
$> kill spid   -- pede p/ o processo se auto-eliminar de forma segura
     ou  :
$> kill -9 spid    -- pede p/ o processo se auto-eliminar de forma bruta, imediatamente

     Para aprender mais, sugiro também a leitura do artigo Matando sessões bloqueadoras automaticamente.


Bom pessoal, por hoje é só! Espero que o artigo seja útil!

[]s


Referências:
   - Killing Oracle Sessions, ORACLE-BASE
   - V$SESSIONOracle® Database Reference 11g Release 2 (11.2)
   - Terminating Sessions: Oracle® Database Administrator's Guide 11g Release 1 (11.1)
  

11 comentários:

  1. Prezado Fábio Prado,

    Agradeço a oportunidade de ler um artigo seu, realmente ACE.

    Gostaria de perguntar, se a opção @INST_ID foi adicionada em algum patch final da 10.2 ou se foi somente na versão 11.x ?

    Trabalho em um RAC 10.2.0.4 e precisei fazer alguns malabarismos com DBMS_JOB.Submit para implementar o recurso de @inst_id.

    Obrigado.

    Ederson Elias

    ResponderExcluir
    Respostas
    1. Ederson, no link http://docs.oracle.com/cd/B28359_01/rac.111/b28254/whatsnew.htm#CHDFJADH diz que este recurso foi adicionado no 11GR1. Também pesquisei no MOS p/ ver se o comando funciona em algum release/patch do 10G e não encontrei nada, portanto, acho que só funciona mesmo no 11G, ok?

      []s

      Excluir
    2. Ederson e Fábio, Bom Artigo primeiramente. Pelo que eu vi essa coluna existe em ambientes RAC e presentes em visões do tipo GV$, para viabilizar algumas tarefas em ambientes custerizados, não precisando assim o DBA navegar para o host onde esta a base de dados com a sessão em questão para elimina-la. Contudo há alguns links que explicam melhor essa questão da coluna INST_ID para gerenciamento de ambientes em Cluster.
      http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3.htm#1109169

      http://docs.oracle.com/cd/B28359_01/rac.111/b28254/admin.htm

      Abraço á todos!!!!

      Excluir
    3. David, obrigado pela contribuição!
      []s

      Excluir
  2. Fábio,

    Muito bom o seu material, fiz uns ajustes na suas select para meu ambiente RAC, funcionou perfeitamente.

    Obrigado

    ResponderExcluir
    Respostas
    1. Paulo, obrigado pelo comentário! Qual a versão e release do Oracle q vc executou o comando?
      []s

      Excluir
    2. Bom dia Fábio!
      Aqui trabalho com 11g R2 11.2.0.4 em RAC com dois nós, os ajustes que fiz servem basicamente para duas coisas, identificar as sessões e mostrar bloqueios quando estes ocorrerem.
      Ainda vai dar para utilizar para ativar e desativar o rastreamento da sessão, ainda vou fazer uns testes. quero ver se a coluna TRACEFILE vai me ajudar a localizar o TRC da sessão.

      Veja como ficou.

      SELECT s.INST_ID, S.SID, S.SERIAL#, P.SPID,
      S.USERNAME, S.STATUS, S.OSUSER, S.MACHINE,
      S.PROGRAM, S.MODULE,
      TO_CHAR(S.LOGON_TIME, 'dd/mm/yyyy hh24:mi:ss') LOGON_TIME,
      S.blocking_session, -- id da sessao bloqueadora (qdo for o caso)
      s2.INST_ID, s2.SID,s2.USERNAME,
      DBMS_LOB.SUBSTR(a.SQL_FULLTEXT, 4000, 1) sql_text,
      p.TRACEFILE
      FROM GV$SESSION S
      inner JOIN GV$PROCESS P ON S.paddr = P.addr and s.INST_ID = p.INST_ID
      inner JOIN GV$SQLAREA A ON a.hash_value = s.sql_hash_value and a.INST_ID=s.INST_ID
      left join gv$session s2 on s2.INST_ID=s.BLOCKING_INSTANCE and s2.SID=s.BLOCKING_SESSION
      left join gv$lock l on l.INST_ID=s.INST_ID and l.ADDR=s.PADDR
      WHERE S.TYPE = 'USER'
      -- AND S.USERNAME = '&usuario' -- substituir X1 pelo nome do usuario no BD
      -- AND S.OSUSER = '&usuario_os' -- substituir X2 pelo nome do usuario no SO
      -- AND S.MACHINE = '&maquina' -- substituir X3 pelo nome da máquina q está se conectando no BD
      -- AND S.PROGRAM = '&programa' -- substituir X4 pelo nome do programa q está se conectando no BD
      order by s.BLOCKING_SESSION

      Excluir
    3. Paulo, obrigado por compartilhar o script!
      []s

      Excluir