Create idle MWA session logging table for sessions to be terminated

This script is intended to be used with the Post:

Automatically Terminate old MWA sessions using PL/SQL and DBMS scheduler


CREATE TABLE XXX.XXX_KILLED_MWA_USER_SESSIONS
(
  SCHEMANAME    VARCHAR2(30 BYTE),
  INST_ID       NUMBER,
  SID           NUMBER,
  SERIAL#       NUMBER,
  LOGON_TIME    DATE,
  LAST_CALL_ET  NUMBER,
  MODULE        VARCHAR2(64 BYTE),
  ACTION        VARCHAR2(64 BYTE),
  KILLED_TIME   DATE,
  SQL_ID        VARCHAR2(64 BYTE)
)

TABLESPACE XXX
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


GRANT SELECT ON XXX.XXX_KILLED_MWA_USER_SESSIONS TO APPS;

MWA PL/SQL Procedure to kill idle sessions longer than 12 hours

--This script is to be referenced by the Post:

Automatically Terminate old MWA sessions using PL/SQL and DBMS scheduler

**Note: Special thanks to Justin Hartman.  He made this procedure for other issues, but let me use and alter it for our MWA purposes.


CREATE OR REPLACE PROCEDURE XXX.XXX_MWA_STALE_SESSION_KILL
AS
   V_DB       VARCHAR2 (10);
   V_SQLERR   VARCHAR2 (255);
BEGIN
   SELECT NAME INTO V_DB FROM V$DATABASE;

   DECLARE
      CURSOR GET_MWA_SESSIONS_CUR
      IS
           SELECT A.SCHEMANAME,
                  A.INST_ID,
                  A.SID,
                  A.SERIAL#,
                  A.LOGON_TIME,
                  ROUND (A.last_call_et / 3600) "HOURS_INACTIVE",
                  A.MODULE,
                  LTRIM (A.ACTION, 'IDLE_LAST') "ACTION",
                  A.SQL_ID,
                     'ALTER SYSTEM DISCONNECT SESSION '
                  || ''''
                  || A.SID
                  || ', '
                  || A.SERIAL#
                  || ',@'
                  || A.INST_ID
                  || ''''
                  || ' IMMEDIATE'
                     KILL
             FROM GV$SESSION A
            WHERE     status = 'INACTIVE'
                  AND module = 'MWAJDBC'
                  AND action NOT LIKE '%GENERIC%'
                  AND last_call_et > (3600 * 12) ----INACTIVE FOR GREATER THAN 24 HOURS
         ORDER BY last_call_et DESC;
   BEGIN
      FOR X IN GET_MWA_SESSIONS_CUR
      LOOP
         UTL_MAIL.SEND (
            SENDER       => 'ABC@XYZ.com',
            RECIPIENTS   => 'ABC@XYZ.com',
            SUBJECT      => 'Stale MWA session was killed on ' || V_DB,
            MESSAGE      =>    'The Following MWA session was killed due to being idle longer than 24 hours:'
                            || CHR (13)
                            || CHR (13)
                            || CHR (10)
                            || CHR (13)
                            || CHR (10)
                            || 'Logon Time:     '
                            || TO_CHAR (X.LOGON_TIME,
                                        'DD-MON-YYYY HH24:MI:SS')
                            || CHR (13)
                            || CHR (10)
                            || 'Module:           '
                            || X.MODULE
                            || CHR (13)
                            || CHR (10)
                            || 'REPORT:         '
                            || X.ACTION
                            || CHR (13)
                            || CHR (10)
                            || 'SQL ID:           '
                            || X.SQL_ID);


         DBMS_OUTPUT.PUT_LINE ('EMAIL SENT');
      END LOOP;

      BEGIN
         FOR I IN GET_MWA_SESSIONS_CUR
         LOOP
            INSERT INTO XXX.XXX_KILLED_MWA_USER_SESSIONS
                 VALUES (I.SCHEMANAME,
                         I.INST_ID,
                         I.SID,
                         I.SERIAL#,
                         I.LOGON_TIME,
                         I.HOURS_INACTIVE,
                         I.MODULE,
                         I.ACTION,
                         SYSDATE,
                         I.SQL_ID);

            COMMIT;

            EXECUTE IMMEDIATE I.KILL;

            DBMS_OUTPUT.put_line ('# of rows inserted: ' || SQL%ROWCOUNT);
         END LOOP;
      END;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      V_SQLERR := SQLERRM;

      BEGIN
         UTL_MAIL.SEND (SENDER       => 'ABC@XYZ.com',
                        RECIPIENTS   => 'ABC@XYZ.com',
                        SUBJECT      => 'MWA_USER check error ' || V_DB,
                        MESSAGE      => V_SQLERR);
      EXCEPTION
         WHEN OTHERS
         THEN
            RAISE;
      END;
END XXX_MWA_STALE_SESSION_KILL;
/

SHOW ERRORS;

Automatically Terminate old MWA sessions using PL/SQL and DBMS scheduler

We've been experiencing MWA servers freezing and not allowing new connections and causing anyone else connected to the particular server in question to get locked up.

I was doing some research on Oracle support, and found a note related to MWA troubleshooting for R12.

Doc ID 782162.1

What I found was that Oracle recommends that you terminate old idle sessions that have been connected for longer than 12 hours.  They actually provide you with a simple sql statement that dynamically generates the kill statements for those database sessions.

We've taken it a step further here in the sense that - our team altered the script a little bit for some more information, turned it into a PL/SQL procedure, logged the information to a custom table, and then scheduled a DBMS job to run the procedure hourly to terminate the idle sessions.  Lastly with the procedure, it will also notify you of the sessions it terminated via UTL_MAIL.

This may sound like it takes a lot of work, but its very simple.  Let's walk through our setup quickly, and then what all you will need to do to make it work for yourself.

We are currently using:

-R12.2.4
-a load balancer to round robin the connections, instead of the mwa dispatcher
-shared appl_top, with 3 application nodes
-2 to 4 MWA servers per application node


The first thing we did was alter the sql to our liking so that it provided the information we wanted to log as well as provide the dynamic kill script to run.  We wanted to log this information for reporting purposes and how patches or changes in configuration might change the behavior/performance of the MWA servers themselves.

Here is the script to create a logging table (replace XXX with your custom schema with DBA privs):

Create logging table script


After you create the backup table, go ahead and create the PL/SQL procedure that you can find here:

PL/SQL procedure

***Note this has calls in the procedure to the logging table, if you do not wish to log these, you can remove this and not create the logging table.



You can then schedule this to run as a job via OEM, at least that is how we did this.

Log into OEM, and navigate to your database or clustered database.  Go to the Administration drop down and Oracle Scheduler, then Jobs. Create a new job.

Name the job with something other than the name of your procedure, you can just add _JOB or whatever you want to name it.  Put in the schema you developed the procedure in, and fill out the description of the what the job is doing, such as "Terminating Idle MWA sessions."

In the PL/SQL block, all you will need to do is replace the "-- Insert PL/SQL code here" with the name of your PL/SQL procedure. 

Example:

begin

 XXX.MWA_STALE_SESSION_KILL --replace XXX with your schema, that's it!

end;

On the schedule tab, and options, you can choose to your liking.

After this is setup, schedule it to run and then check your logging table, if you chose to log it.  Please do this in a DEVELOPMENT environment first! After you have successfully tested it, you now have a working scheduled procedure (you could also use cron for this if you want) that will terminate MWA sessions that have been IDLE for longer than 12 hours.  Good luck and be careful!

Until next time!

-Lance