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