I've already written a post describing two options for datasource monitoring; http://javaoraclesoa.blogspot.nl/2012/09/monitoring-datasources-on-weblogic.html. The methods described in that post have some drawbacks;
- by using a servlet, you are exposing server datasource status and you are using a custom developed servlet to achieve functionality. the servlet does not show connection errors, just OK or NOK. Also it does not take into account different managed servers and datasource targets.
- by using WLST as in the example in the post, you're not actually testing creating a connection but are just monitoring current status
What the script should do
At a customer I noticed database availability was an issue. This often caused data sources to go to 'Suspended' state. Also when the database was available again, we often encountered connection errors like 'ORA-12514: TNS:listener does not currently know of service requested in connect', 'ORA-011033: ORACLE initialization or shutdown in progress' and 'ORA-01017: invalid username/password; logon denied'. The customer used a multitude of datasources. We wanted a quick way to resume every one of them and determine connection exceptions in order to inform the DBA to fix it. Since the script would run on several machines, it should be able to determine the IP to connect to and the required paths (to for example SerializedSystemIni.dat) on it's own.
In the below image, the 'call chain' is illustrated. This post focusses on the WLST script. In a second post (http://javaoraclesoa.blogspot.nl/2013/09/inform-dba-if-weblogic-cant-connect-to.html) I'll describe how we automated the process of calling the script over SSH by using an Ant plugin in Maven on several environments so we could schedule this to run every morning in Jenkins and automatically mail the DBA's to go and fix their DB's.
Obtaining local information
Since the script needed to be as environment neutral as possible, I obtained several pieces of information from the machine the script runs on.
Obtaining the physical interface IP address
I obtained the IP address of the local machine by using the ip command. See; http://stackoverflow.com/questions/6243276/how-to-get-the-physical-interface-ip-address-from-an-interface
intf = 'eth0'
intf_ip = commands.getoutput("/sbin/ip address show dev " + intf).split()
intf_ip = intf_ip[intf_ip.index('inet') + 1].split('/')[0]
print 'Using IP: ',intf_ip
Obtaining the path to SerializedSystemIni.dat
I needed to obtain the path to SerializedSystemIni.dat for decrypting passwords (see later in this post). I obtained the path by (after connecting);
rootdir=cmo.getRootDirectory()
secdir=rootdir+'/security'
secdir is the directory where the SerializedSystemIni.dat file is usually located.
Obtaining parameters
I wanted my script to be flexible. I wanted to have the option to use a properties file (useful for the development environment), to use command-line arguments (useful when calling the script from Maven) and I wanted the script to ask for login details otherwise (useful for a DBA executing the script). This required 3 methods of obtaining parameters. The logic used was as followed;
- do we have a file? if so, use it else continue
- do we have command line arguments. if so, use them. if not, ask for them
Command line arguments
In Weblogic 10.3.6, Python/Jython 2.2.1 is used (https://forums.oracle.com/thread/2210448). This limits the use of several of the more recent Python libraries to make working with parameters more easy (such as optparse and argparse). We can however use getopt; http://davidmichaelkarr.blogspot.nl/2008/10/make-wlst-scripts-more-flexible-with.html
The above described library also covered asking for the parameters if they were not supplied. If this failed for whatever reason, raw_input can be used.
Properties file
Using a properties file was relatively easy. See for example; http://java-brew.blogspot.nl/2011/01/reading-properties-file-in-wlst-jython.html
Because we are using Jython with WLST, we can use the Properties class from java.util. No need to reinvent the wheel here.
Obtaining login details and testing connections
When a datasource is suspended and the connection pool is tested, you can the following exception;
Connection test failed with the following exception: weblogic.common.resourcepool.ResourceDisabledException: Pool testuserNonXa is Suspended, cannot allocate resources to applications.
When however a connection cannot be made, you can resume the datasource and test it and nothing will appear to be wrong. In the log file however you might see one of the previously mentioned exceptions; you won't be able to use the datasource to get to the database. So testing just the datasource from the Weblogic console is not enough to confirm it is working.
To determine if a connection could be made, I wanted to create a new connection to the database while not using the datasource but with the same connection details. For this I needed to obtain the connection information the datasource was using. Then I encountered the following challenge; the cleartext passwords could not be read due to Weblogic server policies; "Access to sensitive attribute in clear text is not allowed due to the setting of ClearTextCredentialAccessEnabled attribute in SecurityConfigurationMBean". Also see; http://serverfault.com/questions/386724/clear-text-credential-access-enabled-field
I did not want to change this policy as it would introduce a security vulnerability. I had to decode the passwords. Based on several blog posts such as; http://connectionserver.blogspot.nl/2009/06/recovering-weblogic-passwords.html I could recover the database user passwords (which is of course very useful...). The following which I found online is also interesting; http://recover-weblogic-password.appspot.com/. As you can see in the code below, you can uncomment a line to display the passwords in plain text on the console.
First I specified the path where the SerializedSystemIni.dat file could be found. Then I used that to decrypt the encrypted passwords I obtained from the MBeans. Then I used zxJDBC to connect to the database using the obtained credentials; http://www.informit.com/articles/article.aspx?p=26143
The script
Mind the indentation! It's Jython. You should execute this with the wlst.sh script in your Weblogic server installation.
import commands
import os
import weblogic.security.internal.SerializedSystemIni
import weblogic.security.internal.encryption.ClearOrEncryptedService
import traceback
import sys
import getopt
from com.ziclix.python.sql import zxJDBC
from java.io import FileInputStream
intf = 'eth0'
intf_ip = commands.getoutput("/sbin/ip address show dev " + intf).split()
intf_ip = intf_ip[intf_ip.index('inet') + 1].split('/')[0]
print 'Using IP: ',intf_ip
var_user=''
var_pass=''
try:
fh = open("resume.properties", "r")
fh.close()
print 'Using resume.properties'
propInputStream = FileInputStream("resume.properties")
configProps = Properties()
configProps.load(propInputStream)
var_user=configProps.get("userName")
var_pass=configProps.get("passWord")
except IOError:
try:
opts, args = getopt.getopt(sys.argv[1:], "", ["username=", "password="])
for o, a in opts:
if o == "--username":
var_user=a
print 'User: ',var_user
elif o == "--password":
var_pass=a
print 'Pass: ',var_pass
else:
assert False, "unhandled option"
except getopt.GetoptError, err:
print 'No -u and -p commandline arguments and no resume.properties...'
var_user = raw_input("Enter user: ")
var_pass = raw_input("Enter pass: ")
connect(var_user,var_pass,intf_ip+':7001')
rootdir=cmo.getRootDirectory()
secdir=rootdir+'/security'
allServers=domainRuntimeService.getServerRuntimes();
if (len(allServers) > 0):
for tempServer in allServers:
print 'Processing: ',tempServer.getName()
jdbcServiceRT = tempServer.getJDBCServiceRuntime();
dataSources = jdbcServiceRT.getJDBCDataSourceRuntimeMBeans();
if (len(dataSources) > 0):
for dataSource in dataSources:
#print 'Resuming: ',dataSource.getName()
dataSource.resume()
dataSource.testPool()
cd('/JDBCSystemResources/' + dataSource.getName() + '/JDBCResource/' + dataSource.getName() + '/JDBCDriverParams/' + dataSource.getName() + '/Properties/' + dataSource.getName())
dbuser=cmo.lookupProperty('user').getValue()
#print 'User: ',dbuser
cd('/JDBCSystemResources/' + dataSource.getName() + '/JDBCResource/' + dataSource.getName() + '/JDBCDriverParams/' + dataSource.getName())
dburl=cmo.getUrl()
#print 'DbUrl: ',dburl
dbpassword=cmo.getPasswordEncrypted()
es=weblogic.security.internal.SerializedSystemIni.getEncryptionService(secdir)
ces=weblogic.security.internal.encryption.ClearOrEncryptedService(es)
dbpassword_decrypted=str(ces.decrypt("".join(map(chr, dbpassword))))
#print 'DbPassword: ',dbpassword_decrypted
dbdriver=cmo.getDriverName()
#print 'DbDriverName: ',dbdriver
try:
con=zxJDBC.connect(dburl,dbuser,dbpassword_decrypted,dbdriver)
cursor=con.cursor()
result=cursor.execute('select sysdate from dual')
except:
print 'ERROR: Url: ',dburl,' User: ',dbuser
traceback.print_exc()
