Anyone have luck with using database external monitors?
I'm attempting to setup a simple external monitor to check on a couple of Oracle instances. After using the example script provided in the documentation and tailoring it to my environment I've yet to get it to work correctly. Unfortunately, debugging a script on the AX is difficult it not impossible to do from that I've seen. (Maybe HM results are in logging?)
In any event, I've tried to debug the script locally using python + pyodb. I can connect to the Oracle instance via SQL developer and the cx_Oracle python module. However, I've yet to find any luck with pyodb.
Script I am using on the AX:
If I tweak this around on my box:
I get this error:
Like I mentioned earlier, doing this using cx_Oracle works fine, so I am somewhat certain that the Oracle DB isn't the issue or my connectivity to it
Results:
In any event, I've tried to debug the script locally using python + pyodb. I can connect to the Oracle instance via SQL developer and the cx_Oracle python module. However, I've yet to find any luck with pyodb.
Script I am using on the AX:
#!/usr/bin/python
import sys
import os
import pyodb
# Oracle connection string format:
#Driver=OracleODBCDriver;DBQ=;UID=;PWD=
# by a10hm convention get host and/or port from environment
host = os.environ['HM_SRV_IPADDR']
if os.environ.has_key('HM_SRV_PORT'):
port = int(os.environ['HM_SRV_PORT'])
if 0 >= port or 65536 <= port:
port = 0
else:
port = 0
if 0 != port:
conn_str = ("Driver=OracleODBCDriver;DBQ=//%s:%d/xe;UID=system;PWD=oracle" % (host,port))
else:
conn_str = ("Driver=OracleODBCDriver;DBQ=//%s/xe;UID=system;PWD=oracle" % (host))
sql_stmt = "select * from employees where employee_id = 100"
try:
rv = 0
print "Connecting %s" % (conn_str)
conn = pyodb.Connect(conn = conn_str)
print "Doing SQL query '%s'" % (sql_stmt)
conn.execute(sql_stmt)
print "Fetching query results"
rows = conn.fetch()
print "Verifying results are OK"
if ( 0 == len(rows)):
rv = -1;
print "Cleaning up the database connection"
conn.disconnect()
del(conn)
except:
print "Something went wrong"
# by a10hm convention must exit with something other than 0
rv = -1
# by a10hm convention must exit with 0
sys.exit(rv)
If I tweak this around on my box:
#!/usr/bin/python
import sys
import os
import pyodb
host = "127.0.0.1"
port = 1521
conn_str = ("Driver=OracleODBCDriver;DBQ=//%s:%d/xe;UID=system;PWD=oracle;" % (host,port))
#sql_stmt = "select * from employees where employee_id = 100"
conn = pyodb.Connect(conn = conn_str)
I get this error:
python Oracle_Test.py
Traceback (most recent call last):
File "Oracle_Test.py", line 10, in
conn = pyodb.Connect(conn = conn_str)
File "/usr/lib64/python2.7/site-packages/pyodb.py", line 162, in __init__
raise ConnectError, e
pyodb.ConnectError: Failed to connect to the data source (01000:Warning)
Like I mentioned earlier, doing this using cx_Oracle works fine, so I am somewhat certain that the Oracle DB isn't the issue or my connectivity to it
#!/usr/bin/python
import sys
import os
import cx_Oracle
con = cx_Oracle.connect('system/oracle@127.0.0.1/xe')
print con.version
con.close()
Results:
python Oracle_Test_cx.py
11.2.0.2.0
0
Comments
The end goal for this is to load balance between two Oracle DB servers in a Data Guard configuration. One DB would be active and the other would be passive. In the event that the active DB server is down, we want the AX to direct incoming connections to the secondary DB server once it's available. In order to do this, we need to somehow have the AX be aware of what server is primary and what server is secondary. This can be accomplished via a simple SQL select. It would be nice to do this via the AX as opposed to writing out a script + cron job + file result and then health checking that file result through a HTTP get.
Enter this command at the global configuration level. The command creates
the monitor and accesses the configuration level for it.
Use the following command to configure the specific database options:
method database {mssql | mysql | oracle | postgresql}
db-name name
username username-string password password-string
send query
receive expected-reply