Sending Email Alert for WebLogic DataSource Monitoring State & Connections Usages


Jay SenSharma

Jay SenSharma

As almost in every production environment monitoring the resources is the most important thing. We have started a chain of posts related to getting Alerts whenever something goes wrong or the WebLogic Application Server starts behaving abnormal. In the current demo we are going to see how to get an E-Mail alert whenever the DataSource is not “Running”  or the “ActiveConnectionCurrentCount” crosses the limit specified by us in the  Properties file.

This script will monitor A perticular instanace of WebLogic Server and the dataSource which is targeted to that particular server.  This WLST script can be further enhanced to monitor more than 1 WebLogic Server Instance by putting the complete Script inside a for Loop. For Simplicity of the concept we are doing it only on a single Server.

Note: Below script can be executed as a Cron Job at a regular interval of lets say 10 minutes or 5 minutes based on the requirement.  This WLST Script is supported from WLS 9.x onwards .

Steps to Create an Email Alert for WebLogic DataSource Monitoring State & Connections Usages

Step1) Create a Directory somewhere in your file system like : “C:WLST”

Step2) Write a Properties file “details.properties” inside “C:WLST” like following:

#### Datasource.targetServer.url   Is the URL of the WebLogic Server in which the DataSource is Targeted.

#### connectionPool.alert.limit    is the total number of Used Connections after which you want to get an alert

Step-3) Create a WLST Script somewhere in your file system with some name like “monitorDS.py” inside “C:WLST” contents will be something like following:

# @author Copyright (c) 2010 - 2011 by Middleware Magic, All Rights Reserved.
from java.io import FileInputStream
import java.lang
import os
import string

propInputStream = FileInputStream("details.properties")
configProps = Properties()

targetServerURL = configProps.get("datasource.targetServer.url")
adminUser = configProps.get("admin.username")
adminPassword = configProps.get("admin.password")
connectionPoolAlertLimit = configProps.get("connectionPool.alert.limit")

#############  This method would send the Alert Email  #################
def sendMailString():
	os.system('/bin/mailx -s  "ALERT: Connection Pool Health is in WARNING !!! " abcd@company.com < commectionLimit_file')
	print ''

def checkConnectionUsage(activeConnectionsCurrentCount,dataSourceName,dataSourceState):
	state=" DataSourceName: " + dataSourceName
	check = string.find(dataSourceState,"Running")
	print 'Zero 0 Means Running :  Check State ' , str(check)
	if check != 0:
		state = state + " Checking Connection Pool HealthState: " + dataSourceState
		print '!!!! ALERT !!!! Connection Pool Health is in Not OK Sending E-Mail Alert.'

	if int(activeConnectionsCurrentCount) >= int (connectionPoolAlertLimit):
		state = state + " Connection Pool Is Crossing the Alert Limit ActiveConnectionsCurrentCount = " + str(activeConnectionsCurrentCount)
		print '!!!! ALERT !!!! Connection Pool Connection Pool Is Crossing The Alert Limit.'
		print ''

	checkData_A = string.find(dsStateSendMail,"yes")
	checkData_B = string.find(dsLimitSendMail,"yes")
	if  ((checkData_A==0) | (checkData_B==0)):
		cmd = "echo " + state +" > commectionLimit_file"
		print 'Every Thing in Pool Is OK For DataSource ', dataSourceName

#############  Main Execution @ Middleware Magic 2010 #################
connect(adminUser, adminPassword, targetServerURL)
dsMBeans = cmo.getJDBCServiceRuntime().getJDBCDataSourceRuntimeMBeans()
for ds in dsMBeans:
	print 'DS name is: '+ds.getName()
	print 'State is ' +ds.getState()
	# ActiveConnectionsCurrentCount: Means The number of connections currently in use by applications.
	print 'DS ActiveConnectionsCurrentCount: ', activeConnectionsCurrentCount
	print '------------------------------------'

Step-4) Open a command prompt and then run the “setWLSEnv.cmd” or “setWLSEnv.sh” to set the CLASSPATH and PATH variables. Better you do echo %CLASSPATH% or echo $CLASSPATH to see whether the CLASSPATH is set properly or not. If you see an Empty Classpath even after running the “setWLSEnv.sh” then please refer to the Note mentioned at Step3) in the Following post:


Step-5) Now run the WLST Script in the same command prompt like following:

<br />
java weblogic.WLST monitorDS_WORKING.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Connecting to t3:// with userid weblogic ...
Successfully connected to Admin Server 'AdminServer' that belongs to domain 'base_domain'.

Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.

Location changed to serverRuntime tree. This is a read-only tree with ServerRuntimeMBean as the root.
For more help, use help(serverRuntime)

DS name is: SQLAuthDS
State is Suspended
DS ActiveConnectionsCurrentCount:  0
Zero 0 Means Running :  Check State  -1
!!!! ALERT !!!! Connection Pool Health is in Not OK Sending E-Mail Alert.

DS name is: PointBaseDataSource
State is Running
DS ActiveConnectionsCurrentCount:  12
Zero 0 Means Running :  Check State  0
!!!! ALERT !!!! Connection Pool Connection Pool Is Crossing The Alert Limit.


NOTE: This script is using mailx (i.e. but Windows box does not have mailx utility) so please do check if your mailx is configured properly or else script would run properly but the mail would not be sent.

ConnectionPool LimitExceeded

ConnectionPool LimitExceeded

DataSource State

DataSource State




Jay SenSharma

Creating WebLogic DataSource Using WLST and Properties File

Hi All,

Jay SenSharma

Jay SenSharma

We have seen many demonstrations of reading a properties file inside a WLST script to make is fully automated and easily managable without Hardcoding anything inside it. We will see many demos of creating and configuring various resources in WebLogic Server using the WLST where we used properties file data as an input to the resource configuration. Here is an example in which we will see How to Configure WebLogic DataSource using WLST?

In this sample we will use a properties file to  avoid hard coding. This script will be same for various DataSource configuration just we need to change the Properties files entry to make a new DataSource.

Step1). Create a Directory somewhere in your file system like:  “C:WLST_DS_Creation” and place a properties file like below in this directory with name “details.properties”



datasource.test.query=SQL SELECT * FROM DUAL

Step2). Now in the same directory write the following WLST Script “createDataSource.py” like following:

from java.io import FileInputStream

propInputStream = FileInputStream("details.properties")
configProps = Properties()



connect(adminUserName, adminPassword, adminURL)
cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName)

cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCDataSourceParams/' + dsName )
set('JNDINames',jarray.array([String('jdbc/' + dsName )], String))

cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCDriverParams/' + dsName )
cmo.setDriverName( dsDriverName )

cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCConnectionPoolParams/' + dsName )
cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCDriverParams/' + dsName + '/Properties/' + dsName )

cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCDriverParams/' + dsName + '/Properties/' + dsName + '/Properties/user')

cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCDriverParams/' + dsName + '/Properties/' + dsName )

cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCDriverParams/' + dsName + '/Properties/' + dsName + '/Properties/databaseName')

cd('/JDBCSystemResources/' + dsName + '/JDBCResource/' + dsName + '/JDBCDataSourceParams/' + dsName )

cd('/SystemResources/' + dsName )
set('Targets',jarray.array([ObjectName('com.bea:Name=' + datasourceTarget + ',Type=Server')], ObjectName))


Step3). Now Open a Command/Shell Prompt and then run the “setWLSEnv.sh” script to set the CLASSPATH and PATH environment variables. Run the “. ./setWLSEnv.sh” by adding two DOTs separated by a single space …..before the actual script like following : (use ‘cd’ command to move inside the <BEA_HOME>/wlserver_10.3/server/bin) then run the following command….
.  ./setWLSEnv.sh

Note: Here The first DOT represents that set the Environment in the current Shell, AND the second ./ represents execute the script from the current directory.

Step4). Run the Above WLST Script like following:

java weblogic.WLST createDataSource.py

Make sure that the Database is running while you are creating the DataSource.




Jay SenSharma

Debugging Basic JDBC Issues


Jay SenSharma

Jay SenSharma

Here is a Simple demonstration of Debugging Jdbc N/W Connectivity issues. Some very basic and common tools and simple Jdbc programs helps us a lot in debugging the JDBC issues.

You can also refer to the WebLogic JDBC related some very common tips: http://middlewaremagic.com/weblogic/2010/02/10/jdbc-tips/

Common Jdbc Issues-1).

If you get the following Error:

<BEA-149205> <Failed to initialize the application ‘TestDataSource’ due to error weblogic.application.ModuleException: .
at weblogic.jdbc.module.JDBCModule.prepare(JDBCModule.java:289)
at weblogic.application.internal.flow.ModuleListenerInvoker.prepare(ModuleListenerInvoker.java:93)
at weblogic.application.internal.flow.DeploymentCallbackFlow$1.next(DeploymentCallbackFlow.java:387)
weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: The Network Adapter could not establish the connection
- – - – - – or - – - – - -
weblogic.common.ResourceException: weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: Io exception: The Network Adapter

Degugging the root Cause of above Issue:
The Above Exception: java.io.IOException: The Network Adapter could not establish the connection

It simply suggest that the Database URL may not be correct in the DataSource XML file locatied inside “<DOMAIN_HOME>configjdbc” directoryor may be the TNS name OR some n/w issue between WLS BOX and the DB Box. Please try the following to Double Check it.

Step1). Add JDBC Driver also in the Classpath or Better run “. ./setWLSEnv.sh”

(NOTE: While running the above script please use two DOTs like mentioned above. The first DOT represents that set the Environment in the Current Shell and the second DOT (./) Slash represents that pick up the Script from the current Location. Both DOTs are separated by a single space. Once u run “. ./setWLSEnv.sh” after that try to echo the values of $CLASSPATH and $PATH to make sure that the env is set properly)

Step2). Use WLS   dbping utility to test the Database Network Connectivity from the WebLogic Server Box:
java -classpath /bea103/wl_server103/server/lib/weblogic.jar utils.dbping ORACLE_THIN <dbUserName> <dbPasswoes> <dbURL>

java -classpath /bea103/wl_server103/server/lib/weblogic.jar utils.dbping ORACLE_THIN scott tiger databaseHostName:1521:P15215h

Step 3). Try doing a telnet to connect to the Database Box on the Database Listen Port like following Just to make sure that the Database has started listening on the Mentioned Listen Port or Not …..:

telnet   databaseHostName   1521

Common Jdbc Issues-2).

Many times we see that DataSource configured on WebLogic Server was running fine sometimes back and suddenly we start seeing the following Error:

Caused by: java.sql.SQLException: Io exception: Got minus one from a read call
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:420)

Degugging the root Cause of above Issue:
The error “Caused by: java.sql.SQLException: Io exception: Got minus one from a read call” clearly indicates the root cause of this issue. “We get the following error “Got minus one from a read call” if the Database goes under Maintenance…..or Database is in inconsistent State (Due to any reason like Database Maintenance or Database unmounting…etc ).

WLS is asking a JDBC driver for a connection and getting that exception, which means the DBMS or network dropped the socket during the driver-DBMS handshake.

The above error ensures that there is Nothing Wrong from WebLogic Side …we need to contact the Database Administrator only.

Step1). Open the Server Log and check the timestamp of the first occurance of that Error in the Server log.

Step2). Confirm with the Database Administrator…. What was the activity happening on the Database side at that time (90% cases u will find that the Database might be under Maintenance that time)

——-Make Use of Appropriate Debug Flags:———

Where xx is the amount of time, in seconds, WebLogic Server waits for connection test before considering the connection test failed. By default, a server instance is assigned a value of 10 seconds. This command line flag manages failures, such as a DBMS network failure, which can cause connection tests and applications to hang for extended periods of time (for example, 10 minutes). If the assigned time period expires, the server instance purges and disables the pool (closes all connections and blocks further reserve attempts) and re-enables the pool as soon as it is possible to reconnect.

You can use the following Debug Flags:

Use the Following WLST Script to Enable the JDBC related Debug Flags
connect(user, password, url)

Common Jdbc Issues-3).

If you see that the Database is going into DataSource is moving to SUSPENDED/Disabled State….The first of all check the database connectivity. If the dfatabase connectivity is OK and WLS is able to establish the Connectivity with the database Successfully then Please try to use the “weblogic.Admin” utility to Restore (Resume) the DataSource.

You can use “weblogic.Admin” utility to Enable and Disable the Pool (DataSource) Just to Confirm whether the dataSource is actually active or not:

Step1). run “. ./setWLSEnv.sh” first in the same Shell prompt….then do the following:

Step2). To suspend:
java weblogic.Admin -url t3://localhost:7001 -username weblogic -password weblogic SUSPEND_POOL YourDataSourceName

Step3). To re-enable:
java weblogic.Admin -url t3://localhost:7001 -username weblogic -password weblogic RESUME_POOL YourDataSourceName

To test whether you are getting any Exception or Error while doing this….If yes u are getting any Error or exception then It means there May be some Database connectivity issue…
If a datasource gets destroyed then even if u are having DataSource Configuration Entries in the “config.xml” and the “<DOMAIN_HOME>/config/jdbc/*.xml” files …still u wont be able to see that datasource in the Monitoring Tab in the AdminConsole. Because as soon as the datasource gets destroyed (Due to N/W disconnect or someother reason) the MBean Object gets destroyed.

Common Jdbc Issues-4).

<Warning> <JDBC> <BEA-001153> <Forcibly releasing inactive connection
“weblogic.jdbc.wrapper.PoolConnection_oracle_jdbc_driver_T4CConnection@3″ back into the connection pool “TestDataSource”, currently reserved by: java.lang.Exception
at weblogic.jdbc.common.internal.ConnectionEnv.setup(ConnectionEnv.java:291)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:314)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:292)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:425)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:316)

If we see above kind of exceptions in our Server Logs then definately we need to look into the Application Code.  “That is either your application reserving and Hholding the jdbc connections (they aren’t lost means leaked), or maybe they are hanging waiting for the DBMS to respond.”

Usually we see this Warning When the Application code obtains a JDBC connection from the WLS datasource, then not using it and not closing it, Means Just Holding the Connection Reference, for longer than your datasource/pool is configured to allow (IdleConnectionTimeout). Make sure that u close all the JDBC related Objects in a proper sequence. like exactly in the following  Order…Always close the ResultSet, Statement and Connection objects insode the finally{} Block in the following order:

finally {
try {resultset.close();}
catch (Exception rse) {}
try {statement.close();}
catch (Exception sse) {}
try {connection.close();
catch (Exception cse) {}

Common Jdbc Issues-5).

Many times we get the following kind of error/exception while using a Connection object:

java.sql.SQLException: Closed Connection
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:3620)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3853)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:99)

In these cases we need to make sure that you enable the “Test Connection On Reserve” is disabled. for your Connection Pool.This is the best way the pool can ensure That connection pool provides us a good connection when an application ask for it.
Also provide a simple SQL query for connection testing in the DataSource configuration like ‘SELECT * FROM DUAL’

Common Jdbc Issues-6).

If you get the following Error:

java.sql.SQLException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1200)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:853)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1882)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1847)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)

This error occurs if an applications that uses a database connections from the DataSource/Pool and When the application checked out a connection that has been already “timed out” or has been “staled”, still the application uses it to connect to the database, this error occurs.

In this case eother we need to start the Oracle Database Server or the Application Server which is maintaining the Connection Pool (Reseting the DataSource is also a valid option) sothat the staled connection should be cleared out and a new connection should be establised automatically.
Jay SenSharma

Copyright © 2010-2012 Middleware Magic. All rights reserved. |