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.
Have you enjoyed? Please leave a comment or give a 👍!
3 comments
Hi Rj,
i am prakash from india, nice explanation in all post, i am working as an junior dba with 1.5 years of experience,
i want to know, how session works ,
1. how to manage user sessions
2.how to lock and unlock
3. how to monitor session by dba and how to kill it ,
4.what sql query makes our db slow , how to find that ?
5. how to find out how much a query runs in a db , how to find out in log
i hope you help me to learn that ,
thanks in advance RJ..!
by
prakash..!
Author
Hi Pushpa! Thanks for visiting.
I recommend you to read this link http://docs.oracle.com/cd/E11882_01/server.112/e40540/process.htm#CNCPT008.
Go down until "Connections and Sessions".
This will give you the Database Concepts about the questions you made.
Regards,
RJ
wow. thank you for your lesson. ^^