Howto solve Session waiting SQL*Net from/to DBLink forever

This post is also available in: Português

In large corporations, it is still common to find several different Oracle versions (9i, 10g and 11g) communicating with each other. Among some of them, the Oracle Compatibility Matrix alerts that some error may occurs. Even so, the DBA still creates the database link with the impression that will always work.

If we analyze the Doc ID 207303.1 (Client / Server / Interoperability Support Matrix For Different Oracle Versions), we'll see that for a properly functioning DBLink, the client-server compatibility table should work in both directions, which usually restricts us practically to the same version of Oracle on both sides.

For many months I have had constant problems of clients calling me complaining of sessions marked KILLED not disappearing, locked objects or jobs running forever. When analyzing the cause, I usually faced Oracle 9i or 10g versions with one waits below:

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

In addition to being caused by the difference in versions, this failure of communication between databases may also be associated with the Oracle bugs that need to be corrected, lapses in network and/or failures in O.S.

Since it was not possible to align all Oracle versions of the company or solve the above problems, I developed the following job that searches the sessions that are "waiting for nothing" sporadically, remove them if they are in wait for more than X seconds, and finally kill the process on the operating system to ensure that it will be eliminated.

Warning: Killing an Oracle process on the operating system is never a task recommended by Oracle. Use it at your own risk. This procedure was tested on several databases running on "Dedicated Server Mode", not on "Shared Server Mode".

Steps:
1- Create a java source that runs the "kill" command on the Operating System
2- Create a procedure that calls this java source.
3- Create a procedure which will fetch the dead sessions and kill the processes.
4- Create a job to run the procedure on item every hour.

1) Java Source to kill the process:

First of all, make sure that Java is enabled in your Oracle instance:

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>

Let's start creating the 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"

NOTE: If necessary, make the appropriate adjustments according to the location of your OS binaries

2) Procedure to execute Java Source

Create a procedure to do the interface with the user who created the Java Source.

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

Now grant the following privileges to the same user (change the script with the appropriate username).

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) Create procedure to kill the "everlasting wait" sessions

To create this procedure, first grant the privileges below to the same user who owns the procedure in step 2.

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

Now create a procedure that will search sessions that are waiting DBLink for over 2 hours to eliminate them. After that, this same process will "kill" those sessions in OS.

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) Create cleaning job

Finally, create a job that will run every hour:

-- 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;
/

Great, now the "everlasting wait" and killed Oracle sessions will be automaticaly eliminated.

This post was based on Article http://www.oracle-base.com/articles/8i/shell-commands-from-plsql.php.

+9