Claudemar Martins de Sá

Compartilhando conhecimento com o mundo

Com tecnologia do Blogger.

Pesquisa

Download

Blogger Tricks

Blogger Themes

Visitas

Assuntos

1/28/2014

Matar Sessão de Usuário no Oracle

  janeiro 28, 2014    

Trabalhando com desenvolvedores, muitas vezes me perguntam para matar sessões órfãos presos no Oracle. Estas sessões em algum momento usam um monte de recursos ou bloqueiam os objetos e linhas das tabelas. Os desenvolvedores geralmente trabalham com aplicações instáveis ​​que fazem transações, ou depuradores permitindo que o desenvolvedor possa parar e interromper o processo de aplicação abruptamente assim para pular a parte commit/rollback e até mesmo a parte de desconexão do código. O programa cliente para de rodar mais e não informa ao banco de dados Oracle para cancelar a declaração e desligar de forma adequada. Isso pode produzir um processo órfão no banco de dados. Na verdade o Oracle (o processo PMON em segundo plano) vai matar esses processos órfãos, por si só, mas pode demorar um pouco antes que ele seja identificados e morto, neste meio tempo os desenvolvedores podem estar enfrentando bloqueios ou lentidão no Oracle. Quando o ambiente de desenvolvimento é relativamente pequeno, é fácil de identificar as sessões que precisam de ser matadas. Mas em um ambiente grande, com vários desenvolvedores conectados ao mesmo servidor de banco de dados com o mesmo nome, às vezes, é um pouco complicado. Para enfrentar esta tarefa demorada e repetitiva, criei algumas maneiras para facilitar e agilizar este processo de matar processos.

Matar Manualmente

Primeiro temos que descobrir algumas identificadores da sessão para matar manualmente que é o PID e o serial number (que identifica unicamente objetos de uma sessão): Acessando o sqlplus
[s401:oracle]$/home/oracle> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 28 13:26:30 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

SQL>
Para matar uma sessão no Oracle precisamos de dois dados importantes ( SID e SERIAL# ), ambos podem ser obtidos da VIEW V$SESSION. Quero matar alguns processos do usuário ADRH que estão rodando no PROGRAM GFP804910G - este é o select para descobrir o SID e o SERIAL desse usuário. Para pegarmos estes dados podemos executar o seguinte comando. 
SQL> select sid, serial#, STATUS, PROGRAM from v$session where username = 'ADRH';

       SID    SERIAL# STATUS   PROGRAM
---------- ---------- -------- ------------------------------------------------
      1574      28283 ACTIVE   GFP804910G@s401.lm (TNS V1-V3)
      1594      53302 ACTIVE   GFP804910G@s401.lm (TNS V1-V3)
      1604      58352 ACTIVE   GFP804910G@s401.lm (TNS V1-V3)
      1618      45607 INACTIVE SQL Developer
      1630          7 ACTIVE   GPRDAEMO@s401.lm (TNS V1-V3)
      1642      45102 INACTIVE w3wp.exe

6 rows selected.

SQL>
Peguei o SID e o SERIAL# do que quero matar e executei este comando, importante o usuário pediu para matar os processos processos que estão rodando com o program GFP804910G - O IMMEDIATE especifica para o banco Oracle efetuar o final da transação com rollback, liberando e retornando o controle para você imediatamente. 
SQL> ALTER SYSTEM KILL SESSION '1574,28283' IMMEDIATE;
SQL> ALTER SYSTEM KILL SESSION '1594,53302' IMMEDIATE;
SQL> ALTER SYSTEM KILL SESSION '1604,58352' IMMEDIATE;
Pronto, processos mortos; 
SQL> select sid, serial#, STATUS, PROGRAM from v$session where username = 'ADRH';

       SID    SERIAL# STATUS   PROGRAM
---------- ---------- -------- ------------------------------------------------
      1618      45607 INACTIVE SQL Developer
      1630          7 ACTIVE   GPRDAEMO@s401.lm (TNS V1-V3)
      1642      45102 INACTIVE w3wp.exe

3 rows selected.

SQL>
Script em Shell

Uma alternativa é criar uma rotina em shell para facilitar a vida do dba, dá para checar os usuários online e tem uma opção para matar;
-------------------------------------------------------------------------------------------------
27/01/2014                        ***** ORACLE BASIC SERVICES *****                      15:22:07
-------------------------------------------------------------------------------------------------
                                  s401.lm - oracle - ORACLE_SID=URHDES

USERNAME        OSUSER                 SID    SERIAL# SPID         STATUS   PROGRAM                        LOGON_TIME
--------------- --------------- ---------- ---------- ------------ -------- ------------------------------ --------------------
                oracle                1592      16336 19810        ACTIVE   oracle@s401.lm (J000)          27-JAN-2014 15:17:27
                oracle                1556         20 19893        ACTIVE   oracle@s401.lm (J001)          27-JAN-2014 15:17:22
SYS             oracle                1562         29 19959        ACTIVE   sqlplus@s401.lm (TNS V1-V3)    27-JAN-2014 15:17:51
ADRH            oracle                1620      12770 15011        ACTIVE   GFP804910G@s401.lm (TNS V1-V3) 27-JAN-2014 11:20:20
ADRH            oracle                1572      10408 13151        ACTIVE   GFP804910G@s401.lm (TNS V1-V3) 27-JAN-2014 09:53:57
ADRH            oracle                1603      44946 14731        ACTIVE   GFP804910G@s401.lm (TNS V1-V3) 27-JAN-2014 11:09:44

        Codigo   Funcao                                      
        -------  -------------------------------------------
           1     Checar Determinado Processo              
           2     Matar Processo                              
           0     Sair da Aplicacao                        
        -------  -------------------------------------------


     Digite um Codigo – 2
A opção 2 escolhida dá para matar o processo 
-------------------------------------------------------------------------------------------------
27/01/2014                        ***** ORACLE BASIC SERVICES *****                      15:28:38
-------------------------------------------------------------------------------------------------
                                  s401.lm - oracle - ORACLE_SID=URHDES


Digite o SID do usuario    :  1620

Digite o SERIAL do usuario :  12770
Processo SID 1620 com a SERIAL 12770 não esta mais ativo; 
-------------------------------------------------------------------------------------------------
27/01/2014                        ***** ORACLE BASIC SERVICES *****                      15:22:07
-------------------------------------------------------------------------------------------------
                                  s401.lm - oracle - ORACLE_SID=URHDES

USERNAME        OSUSER                 SID    SERIAL# SPID         STATUS   PROGRAM                        LOGON_TIME
--------------- --------------- ---------- ---------- ------------ -------- ------------------------------ --------------------
                oracle                1592      16336 19810        ACTIVE   oracle@s401.lm (J000)          27-JAN-2014 15:17:27
                oracle                1556         20 19893        ACTIVE   oracle@s401.lm (J001)          27-JAN-2014 15:17:22
SYS             oracle                1562         29 19959        ACTIVE   sqlplus@s401.lm (TNS V1-V3)    27-JAN-2014 15:17:51
ADRH            oracle                1572      10408 13151        ACTIVE   GFP804910G@s401.lm (TNS V1-V3) 27-JAN-2014 09:53:57
ADRH            oracle                1603      44946 14731        ACTIVE   GFP804910G@s401.lm (TNS V1-V3) 27-JAN-2014 11:09:44

        Codigo   Funcao                                      
        -------  -------------------------------------------
           1     Checar Determinado Processo              
           2     Matar Processo                              
           0     Sair da Aplicacao                        
        -------  -------------------------------------------


     Digite um Codigo – 2
Script completo em shell script 
# ------------------------------------------------------------------------------------------------------------------------- 
# Funcao: Mata uma sessao imediatamente e faz  rollback na transacao; 
# Criada: 28/01/2013 
#  Autor: Claudemar Martins
# -------------------------------------------------------------------------------------------------------------------------

Principal () {

clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n "      "
echo -n "                  ***** ORACLE BASIC SERVICES *****   "
echo -n "                   "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo "                                  $(hostname) - $(whoami) - $(env | grep ORACLE_SID)"

sqlplus -s '/as sysdba' << endofcommand

set linesize 220
set pagesize 100

col USERNAME for a15
col OSUSER   for a15
col PROGRAM  for a30

SELECT s.username, s.osuser, s.sid, s.serial#, p.spid, s.status, s.program, TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM v\$session s  inner join v\$process p     on s.paddr = p.addr   WHERE s.STATUS <> 'INACTIVE'  and s.type <> 'BACKGROUND'

/ 
EXIT
endofcommand
echo 
echo ""
echo "        Codigo   Funcao                                      "
echo "        -------  ------------------------------------------- "
echo "           1     Checar Determinado Processo                 "
echo "           2     Matar Processo                              "
echo "           0     Sair da Aplicacao                           "
echo "        -------  ------------------------------------------- "
echo ""

echo
echo -n "     Digite um Codigo - "   
   read OPCAO2
   case $OPCAO2 in
      1) CHECAR_DETERMINADO_PROCESSO_RODANDO ;;
      2) MATAR_PROCESSO ;;
      0) exit ;;
      *) Principal ;;
   esac
}

# ======================================================================================================
# Matando o processo desejado
# ======================================================================================================

MATAR_PROCESSO ()
{
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n "      "
echo -n "                  ***** ORACLE BASIC SERVICES *****   "
echo -n "                   "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo "                                  $(hostname) - $(whoami) - $(env | grep ORACLE_SID)"
echo
echo
echo -n "Digite o SID do usuario    :  "
read SID_DESEJADO
echo
echo -n "Digite o SERIAL do usuario :  "
read SERIAL_DESEJADO

sqlplus -s '/as sysdba' << endofcommand

ALTER SYSTEM KILL SESSION '$SID_DESEJADO, $SERIAL_DESEJADO' IMMEDIATE

/ 
EXIT
endofcommand

clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n "      "
echo -n "                  ***** ORACLE BASIC SERVICES *****   "
echo -n "                   "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo "                                  $(hostname) - $(whoami) - $(env | grep ORACLE_SID)"
echo
echo
echo "                    +---------------------------------------------------------+"
echo "                    |                                                         |"
echo "                    |                aguarde alguns segundos ...              |"
echo "                    |                                                         |"
echo "                    +---------------------------------------------------------+"
echo
sleep 2

clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n "      "
echo -n "                  ***** ORACLE BASIC SERVICES *****   "
echo -n "                   "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo "                                  $(hostname) - $(whoami) - $(env | grep ORACLE_SID)"
echo

sqlplus -s '/as sysdba' << endofcommand

set linesize 220
set pagesize 100
col USERNAME for a15
col OSUSER   for a15
col PROGRAM  for a30

SELECT s.username, s.osuser, s.sid, s.serial#, p.spid, s.status, s.program, TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM v\$session s  inner join v\$process p on s.paddr = p.addr WHERE s.sid = '$SID_DESEJADO'

/ EXIT
endofcommand

echo -n "Check se o processo continua rodando;"
echo
echo -n "Pressione qualquer tecla para continuar..."
read MSG
clear
Principal
}

# ======================================================================================================
# ======================================================================================================

CHECAR_DETERMINADO_PROCESSO_RODANDO ()
{
# ALTER SYSTEM KILL SESSION '$SID_DESEJADO, $SERIAL_DESEJADO' IMMEDIATE;
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n "      "
echo -n "                  ***** ORACLE BASIC SERVICES *****   "
echo -n "                   "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo "                                  $(hostname) - $(whoami) - $(env | grep ORACLE_SID)"
echo
echo
echo -n "Digite o SID do usuario    :  "
read SID_DESEJADO
echo
echo -n "Digite o SERIAL do usuario :  "
read SERIAL_DESEJADO

echo 

clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n "      "
echo -n "                  ***** ORACLE BASIC SERVICES *****   "
echo -n "                   "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo "                                  $(hostname) - $(whoami) - $(env | grep ORACLE_SID)"
echo

sqlplus -s '/as sysdba' << endofcommand

set linesize 220
set pagesize 100

col USERNAME for a15
col OSUSER   for a15
col PROGRAM  for a30

SELECT s.username, s.osuser, s.sid, s.serial#, p.spid, s.status, s.program, TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM v\$session s  inner join v\$process p on s.paddr = p.addr WHERE s.sid = '$SID_DESEJADO'

/ 
EXIT
endofcommand

echo
echo -n "Pressione qualquer tecla para continuar..."
read MSG
clear
Principal
}

# ======================================================================================================
# Nao remover a linha abaixo
# ======================================================================================================

Principal
Matar Todas as Sessões usando um script PL/SQL

Dá para matar todas as sessões de um usuário fazendo um PL/SQL loop. Primeiro vamos acessar o sqlplus e rodar um script PL/SQL
[s401:oracle]$/home/oracle> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 28 13:26:30 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

SQL> @MataUsuarioADRH.sql

PL/SQL procedure successfully completed.
Script PL/SQL loop 1 -- em username tenho que colocar o usuário que vou matar - este não tem o IMMEDIATE !!! 
begin
    for x in ( select *
                 from v$session
                where (username = 'ADRH' OR username = 'PUBLICACAO') AND module LIKE 'GFP804910G%')
    loop
        execute immediate 'alter system kill session ''' || x.sid || ',' || x.serial# || '''';
        dbms_output.put_line( 'Alter session done' );
    end loop;
end;
/
Script PL/SQL loop 2 -- em username tenho que colocar o usuário que vou matar 
begin
    for x in ( select * from v$session where (username = 'ADRH' OR username = 'PUBLICACAO') AND module LIKE 'GFP804910%')
    loop
        execute immediate 'alter system kill session ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE';
        dbms_output.put_line( 'Alter session done' );
    end loop;
end;
/
Script PL/SQL loop 3 - Este pede para digitar o username para matar no sqlplus;
PROMPT Digite o Username para Matar;
DEFINE username = &1
BEGIN
  FOR c IN (SELECT s.sid, s.serial# FROM v$session s WHERE  s.username = '&&username' and module LIKE 'GFP804910%')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE';
  END LOOP;
END;
/
Matar Usuário via Procedure

Uma outra alternativa é criar uma procedure para fazer este procedimento de matar usuário - Clique Aqui 
1. Os desenvolvedores não tem a permissão de alter system / alter user privileges.
2. Ao criar usuários para desenvolvedores o DBA pode adicionar um prefixo ao nome (DEV_USER1, DEV_JOHN, DEV_RACHEL …).
3. Desenvolvedores não compartilham o mesmo nome de usuário (username).
4. Vários desenvolvedores podem executar a partir do mesmo cliente usando diferentes nomes de usernames para acessar Oracle.
5. Um desenvolvedor pode executar a partir de várias máquinas cliente - neste caso, ele vai querer matar sessões abertas apenas
   a partir de uma máquina cliente específico.
Testado em: 
1. Oracle 10g
2. Oracle 10g RAC
3. Oracle 11g
4. Oracle 11g RAC
Agora, vou mostrar o código de configuração, seguida por uma explicação: Create owner for the procedure (schema); 
1 - create user killer identified by killer;
2 - grant alter system to killer;
3 - grant select on v_$session, gv_$session to killer;
4 - alter user killer account lock;
Criando a procedure; 
create or replace PROCEDURE killer.KILL_MY_CLIENTS AUTHID DEFINER AS
   job_no number;
   user_prefix varchar(20);
   domain_name varchar(20);
   num_of_kills number := 0;
   BACKSLASH constant char(1) := chr(92);
begin
   user_prefix := 'DEV';       -- All my developer username prefixed with DEV
                               -- (DEV_USER1, DEV_USER2, DEV_MIKE, DEV_JOHN ...)
   domain_name := 'WORKGROUP'; -- We work in a workgroup.
                               -- Once we work in a windows domain this variable should
                               -- hold the domain name

   for REC in
       (SELECT SID, SERIAL#, INST_ID, MODULE,
               SYS_CONTEXT('USERENV', 'INSTANCE') MY_INST_ID
        from gv$session s
        where s.username like user_prefix || '%'                  -- precaution
          and s.username = sys_context('USERENV', 'SESSION_USER') -- only my username
          and not (S.SID = SYS_CONTEXT('USERENV', 'SID')          -- excluding my session
          and INST_ID = SYS_CONTEXT('USERENV', 'INSTANCE'))
          and replace(upper(machine), domain_name || BACKSLASH, '')     -- my machine
                 = replace(upper(sys_context('USERENV', 'HOST')), domain_name || BACKSLASH, '')
          and s.module not in ('SQL*Plus', 'SQL Developer')       -- not sqlplus
                                                                  -- nor sql developer
          and s.module not like 'sqlplus@%'
          and S.LOGON_TIME < sysdate-(1/24/60/60)                 -- logged-on more
                                                                  -- than a second
        ORDER BY inst_id asc
       ) LOOP
      if rec.inst_id = rec.my_inst_id then
         ---------------------------------------------------------------------------
         -- kill local sessions immediately
         ---------------------------------------------------------------------------
         dbms_output.put('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
         execute immediate 'alter system kill session ''' || rec.sid || ', ' ||
                            rec.serial# || '''' ;
         dbms_output.put_line('. killed locally ' || job_no);
      ELSE
         ---------------------------------------------------------------------------
         -- kill remote sessions (for RAC) as a job
         ---------------------------------------------------------------------------
         DBMS_OUTPUT.PUT('REMOTE SID ' || REC.SID || ' on instance ' || REC.INST_ID  ||
                         '(' || rec.module || ')');
         DBMS_JOB.SUBMIT (
                 job => job_no,
                 what => 'execute immediate ''alter system kill session ''''' || rec.sid ||', ' ||
                          rec.serial# || ''''''';',
                 INSTANCE => rec.inst_id);
         dbms_output.put_line('. killed with job ' || job_no);
         COMMIT;
      end if;
      num_of_kills := num_of_kills + 1;
   end loop;
   dbms_output.put_line ('number of killed sessions: ' || num_of_kills);
end kill_my_clients;
Fazer com que a procedure fique disponível para todos 
1 - create public synonym kill_my_clients for killer.kill_my_clients;
2 - grant execute on killer.kill_my_clients to public;
3 - alter user killer account lock;
Use: O login como o desenvolvedor na máquina do cliente para qualquer aplicativo que possa executar instruções como o SQL * Plus 
1 - exec kill_my_clients
Explicação: Os comandos da primeira seção criam um usuário chamado "killer". Esse usuário é o owner (proprietário) da procedure descrita na próxima seção. Observe que este usuário é muito poderoso, pois pode matar qualquer usuário com a permissão "alter system". É por isso que temos de limitar o acesso a ele para que ele esteja bloqueado. Os grants as tabelas v_$session e gv_$session devem ser feito pelo SYS ou outro usuário com opções de administração sobre essas views. A segunda seção é a procedure em si. Deve ser compilado em "kill" (como o código demonstra). Aqui está uma explicação deste procedure:

1. Identifica todas as sessões em execução da máquina do desenvolvedor a partir dele/dela username, exceto para a sessão de executar esta consulta no nó atual e nós RAC remotos.

1. Se a sessão é na mesma instância da conexão atual - mata imediatamente.
2. Se a sessão estiver em um nó RAC diferente - mata através da apresentação de um job nesse nó.

Existem 2 variáveis ​​que eu costumo mudar quando eu implanto esse procedimento em um ambiente:

* USER_PREFIX – Eu tento nomear todos os usuários para desenvolvedores com um prefixo como DEV_USER1, DEV_USER2, DEV_MIKE, DEV_JOHN ... Eu uso esse prefixo, como medida de precaução - Eu não quero que a minha procedure mate todos os usuários não comecem com este prefixo. Dessa forma, se o meu desenvolvedor está executando esse procedimento a partir do usuário sys ou system por engano ele não vai fazer mal nenhum.

* DOMAIN_NAME – quando se trabalha com clientes Windows, por vezes, a aplicação é prefixado com o nome de domínio (como o SQL Developer) e às vezes não (como o SQL*Plus). Esta variável se tornará crucial para identificar sessões abertas da máquina do desenvolvedor.

Outro fator que ter em conta é que eu não quero matar conexões do SQL * Plus ou de SQL Developer. Se você estiver trabalhando com outros programas, tais como PL / SQL Developer ou Toad você pode querer incluí-los na cláusula onde também.

A terceira seção mostra como usar esse mecanismo: Você só precisa se logar como o desenvolvedor da máquina do cliente para qualquer aplicativo que possa executar instruções como o SQL * Plus e executar o procedimento (você não tem que usar o nome do proprietário na chamada porque você declarou um sinônimo público para ele). 
Importante; Para efetuar este procedimento o usuário que der o privilégio de ALTER SYSTEM.

  

  1 Comment

1 comentário:

  1. Unknown10 de agosto de 2016 às 14:45

    Olá Claudemar,

    achei a ideia de usar jobs para disparar em nós diferentes foi muito boa :-)
    Entretanto, para funcionar tive que disparar o job:

    dbms_job.run(job_no);

    após sua submissão;

    agradeço,
    Gilberto

    ResponderEliminar

Subscrever: Enviar feedback (Atom)