Resolvendo problema de sessões em wait eternos por SQL*Net from/to DBLink

This post is also available in: English

Em grandes corporações, ainda é comum encontrar diversas versões diferentes do Oracle se comunicando como 9i, 10g e 11g. Entre algumas delas, a matriz de compatibilidade do Oracle alerta para falhas mas mesmo assim o DBA cria o DBLink com a impressão que funcionará sempre.

Se analisarmos o Doc ID 207303.1 (Client / Server / Interoperability Support Matrix For Different Oracle Versions), veremos que para um DBLink funcionar corretamente a tabela de compatibilidade cliente-servidor deve funcionar em ambas as direções, o que normalmente nos restringe praticamente a mesma versão de Oracle.

Durante muitos meses tive problemas constantes de usuários me ligando reclamando de sessões marcadas como KILLED que não sumiam, objetos lockados ou jobs rodando eternamente. Ao analisar a causa, normalmente se tratavam de versões 9i ou 10g com um dos waits abaixo:

• SQL*Net message from dblink
• SQL*Net message to dblink
• SQL*Net more data from dblink
• SQL*Net more data to dblink

Além de ser causada pela diferença de versões, essa falha de comunicação entre os bancos também pode estar associado a bugs do Oracle que precisam ser corrigidos, lapsos na rede e/ou falhas no S.O.

Como não era possível alinhar todas as versões de Oracle da empresa ou resolver os problemas acima, desenvolvi o job abaixo que busca essas sessões que ficam "perdidas" esporadicamente, as elimina se estiverem em wait acima de X segundos e por fim mata o seu processo no sistema operacional para garantir que ela será eliminada.

Atenção: Matar um processo do Oracle no sistema operacional nunca é uma tarefa recomendada pela Oracle. Faça sob sua própria conta e risco. Esse procedimento foi testado em vários bancos rodando em modo "Dedicated Server Mode", não em "Shared Server Mode".

Etapas:
1- Criar um java source que executa comando "kill" no Sistema Operacional.
2- Criar uma interface que executa essa função.
3- Criar uma procedure que irá buscar os processos mortos e executar o kill.
4- Criar um job que executará a cada hora a procedure do item 3.

1) Java Source para matar o processo:

Antes de mais nada, certifique-se que o Java está ativo em sua instância Oracle:

SQL> select comp_name, version from dba_registry where comp_name like '%JAVA%';

COMP_NAME                                                                        VERSION
-------------------------------------------------------------------------------- ------------------------------
JServer JAVA Virtual Machine                                                     9.2.0.8.0

SQL>

Vamos então começar criando o Java Source:

CREATE AND COMPILE JAVA SOURCE NAMED "Kill" AS
import java.io.*;
public class Kill {
  public static void executeCommand(String spid) {
    try {
      String[] finalCommand;
      if (isWindows()) {
        finalCommand = new String[4];
        // Use the appropriate path for your windows version.
        //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";    // Windows NT/2000
        //finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe";  // Windows 64-bit
        finalCommand[0] = "C:\\windows\\system32\\taskkill.exe";    // Windows XP/2003
        finalCommand[1] = "/f";
        finalCommand[2] = "/pid";
        finalCommand[3] = spid;
      }
      else {
        finalCommand = new String[3];
        finalCommand[0] = "/bin/kill";
        finalCommand[1] = "-9";
        finalCommand[2] = spid;
      }

      final Process pr = Runtime.getRuntime().exec(finalCommand);
      pr.waitFor();

      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_in = null;
          try {
            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
            String buff = null;
            while ((buff = br_in.readLine()) != null) {
              System.out.println("Process out :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_in.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process output.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_in.close();
            } catch (Exception ex) {}
          }
        }
      }).start();

      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_err = null;
          try {
            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
            String buff = null;
            while ((buff = br_err.readLine()) != null) {
              System.out.println("Process err :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_err.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process error.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_err.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }

  public static boolean isWindows() {
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
      return true;
    else
      return false;
  }

};
/

show errors java source "Kill"

OBS: Caso necessário, faça as adaptações convenientes de acordo com a localização dos binários do seu S.O.

2) Procedure que executará o Java Source

Crie a procedure de interface com o usuário que criou o Java Source.

CREATE PROCEDURE host_kill (p_spid  IN  VARCHAR2)
AS LANGUAGE JAVA 
NAME 'Kill.executeCommand (java.lang.String)';
/

Conceda agora os privilégios abaixo ao mesmo usuário (altere o script com o username correto).

DECLARE
  l_schema VARCHAR2(30) := 'USERNAME' -- Adjust as required.
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.io.FilePermission', '/bin/kill', 'execute' );
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/

3) Criar procedure para matar as sessões "perdidas"

Para criar esta procedure, primeiro conceda o grant abaixo ao mesmo usuário dono da procedure no passo 2.

GRANT SELECT ON V_$SESSION TO USERNAME;
GRANT SELECT ON V_$SESSION_WAIT TO USERNAME;
GRANT SELECT ON V_$PROCESS TO USERNAME;

Agora crie uma procedure que irá buscar as sessões que estão em wait por conta do DBLink em mais de 2 horas e eliminá-las. Em seguida, esse mesmo processo irá eliminar via "kill" as sessões no S.O.

CREATE OR REPLACE PROCEDURE KILL_EXPIRED_DBLINK_CONNECTION AS
  SESSION_MARKED_KILL EXCEPTION;
  PRAGMA EXCEPTION_INIT(SESSION_MARKED_KILL, -31);
  -------------------
  CURSOR C1 IS
    SELECT 'alter system kill session ''' || A.SID || ',' || A.SERIAL# || ''' immediate' KILL
    FROM   V$SESSION A,
           V$SESSION_WAIT B
    WHERE  A.SID = B.SID
    AND    B.EVENT LIKE 'SQL*Net % from dblink'
    AND    B.STATE = 'WAITING'
    AND    B.SECONDS_IN_WAIT > 7200; -- 2 hours
  -------------------
  CURSOR C2 IS
    SELECT A.SPID
    FROM   V$PROCESS A,
           V$SESSION B
    WHERE  A.ADDR = B.PADDR
    AND    B.STATUS LIKE 'KILLED';
BEGIN
  FOR I1 IN C1
  LOOP
    BEGIN
      EXECUTE IMMEDIATE I1.KILL;
    EXCEPTION
      WHEN SESSION_MARKED_KILL THEN
        NULL;
    END;
  END LOOP;
  -------------------
  FOR I2 IN C2
  LOOP
    HOST_KILL(P_SPID => I2.SPID);
  END LOOP;
END;
/

4) Criar job de limpeza

Por fim, crie o job que irá executará a cada hora a limpeza:

-- Oracle 9i

DECLARE
  JOBN NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT(JOB => JOBN, WHAT => 'BEGIN KILL_EXPIRED_DBLINK_CONNECTION; END;', NEXT_DATE => SYSDATE, INTERVAL => 'SYSDATE + 1 / 24');
  COMMIT;
END;
/

-- Oracle >= 10g
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_KILL_EXPIRED_DBLINK_CONN',
   job_type             => 'STORED_PROCEDURE',
   job_action           => 'KILL_EXPIRED_DBLINK_CONNECTION',
   number_of_arguments  => 0,
   start_date           => SYSDATE,
   repeat_interval      => 'FREQ=HOURLY;INTERVAL=1;BYMINUTE=0',
   end_date             => NULL,
   job_class            => 'DEFAULT_JOB_CLASS',
   enabled              => TRUE,
   auto_drop            => FALSE,
   comments             => 'Job to kill expired DBLink''s connections');
END;
/

Pronto, agora as sessões "perdidas" do Oracle e marcadas com kill serão eliminadas automaticamente.

Essa postagem foi baseada no artigo http://www.oracle-base.com/articles/8i/shell-commands-from-plsql.php.

+9