Forum Discussion

Jorge_Herran_14's avatar
Jorge_Herran_14
Icon for Altostratus rankAltostratus
Jul 06, 2015

How to correctly monitor a Database Oracle

we are configuring a monitor health for a Oracle database which has the next configuration parameters:

 

Send String: select * from dual Response: X user:CONSULTA_ANALISTA password:xxxxxxx connection string:

 

PRODM1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = %node_ip%)(PORT = %node_port%)) ) (CONNECT_DATA = (SID = PRODM1) ) )

 

Row:3 Column:1 alias address:172.20.1.73 alias service port:1527

 

the monitor doesn't work and the pool member never is seen up, i have looked at the debug of the connection and this is what i see in a portion of it: [root@ltm1:Active:Changes Pending] monitors tail -30 Common_BD_monitor_PDN-Common_BD-1527.log DATABASE=PRODM1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = %node_ip%)(PORT = %node_port%)) ) (CONNECT_DATA = (SID = PRODM1) ) ) DEBUG=yes MON_INST_LOG_NAME=/var/log/monitors/Common_BD_monitor_PDN-Common_BD-1527.log MON_TMPL_NAME=/Common/BD_monitor_PDN NODE_IP=::ffff:172.20.1.73 NODE_PORT=1527 PASSWORD=nc5gf56y RECVCOLUMN=1 RECVROW=3 RECV_I=X SEND=select * from dual

 

USERNAME=CONSULTA_ANALISTA

TMOS_RD: 0 (0) Daemon port: 1521 count='0' converts to '0' Command-line PID filename: /var/run/ORACLE__Common_BD_monitor_PDN_::ffff:172.20. 1.73-0_1527.pid PID file /var/run/DBDaemon-0.pid exists. Checking for correctness of PID. DBDaemon on port 1521 says its PID is 19578. PID matches EXCEPTION connecting to DBDaemon: fflush(): Connection reset by peer

 

i have also tried putting all the info directly like this:

 

********** Debugging session beginning at: Mon Jul 6 17:07:02 2015

 

Arguments 1-2: ::ffff:172.20.1.73 1527

 

Environment variables: COUNT=0 DATABASE=PRODM1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.73)(PORT = 1527)) ) (CONNECT_DATA = (SID = PRODM1) ) ) DEBUG=yes MON_INST_LOG_NAME=/var/log/monitors/Common_BD_monitor_PDN-Common_BD-1527.log MON_TMPL_NAME=/Common/BD_monitor_PDN NODE_IP=::ffff:172.20.1.73 NODE_PORT=1527 PASSWORD=nc5gf56y RECVCOLUMN=1 RECVROW=1 RECV_I=ok SEND=TNSPING 172.20.1.73 1527

 

USERNAME=CONSULTA_ANALISTA

TMOS_RD: 0 (0) Daemon port: 1521 count='0' converts to '0' Command-line PID filename: /var/run/ORACLE__Common_BD_monitor_PDN_::ffff:172.20.1.73-0_1527.pid PID file /var/run/DBDaemon-0.pid exists. Checking for correctness of PID. DBDaemon on port 1521 says its PID is 19578. PID matches Asking daemon to ping remote database. Expected result not received: Database down, see /var/log/DBDaemon.log for details.

 

Database down, see /var/log/DBDaemon.log for details.

 

If i look into /var/log/DBDaemon.log; it isn't updating. It seems that somehow the process is attached to other monitor over port 1521 an maybe that is the origin of the conflicto and fail of Oracle monitoring: [root@ltm1:Active:Changes Pending] monitors ps -fe|grep DB root 19578 1 0 Jun16 ? Ssl 43:33 /usr/lib/jvm/jre-1.7.0-openjd k.x86_64/bin/java -cp /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/lib/rt.jar:/usr/lib/ jvm/jre-1.7.0-openjdk.x86_64/lib/charsets.jar:/usr/share/monitors/mysql-connecto r-java.jar:/usr/share/monitors/DB_monitor.jar:/usr/share/monitors/sqljdbc4.jar:/ usr/share/monitors/ojdbc6.jar:/usr/share/monitors/postgresql-8.3-604.jdbc3.jar - Xmx64m com.f5.eav.DBDaemon 1521 19578 0

 

application delivery
availability
deployment
LTM
monitor
oracle
  • Samir_Jha_52506's avatar
    Samir_Jha_52506
    Icon for Noctilucent rankNoctilucent
    Jul 07, 2015

    Can u please try this..

        gtm monitor oracle /Common/monitor_oracle_test {
    count 1
    database (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=%node_ip%)(PORT=%node_port%))(CONNECT_DATA=
    (SERVICE_NAME=PQR_ONLINE_XYZ))(SERVER=dedicated))
debug no
defaults-from /Common/oracle
description 
destination *:*
interval 30
password xxxxx
probe-timeout 30
recv PRIMARY
recv-column 1
recv-row 1
send "SELECT database_role FROM database_name"
timeout 91
username CHECK_DB_ROLE_YES
}
  • Jorge_Herran_14's avatar
    Jorge_Herran_14
    Icon for Altostratus rankAltostratus
    Jul 08, 2015

    amir thanks for your response i will try your solution and let you know in a couple of hours

     

  • Jorge_Herran_14's avatar
    Jorge_Herran_14
    Icon for Altostratus rankAltostratus
    Jul 10, 2015

    I did a new test modifying the connection as you suggested and now this is waht i get:

     

    2015-07-10 11:30:28.737: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp(H OST=172.20.1.73)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=PRODM1))(SERVER=dedicate d))(Thread-328123): DB DriverManager.getConnection failed: "NL Exception was gen erated" java.sql.SQLException: NL Exception was generated at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412) at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java: 531) at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:221) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio n.java:32) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503) at java.sql.DriverManager.getConnection(DriverManager.java:579) at java.sql.DriverManager.getConnection(DriverManager.java:221) at com.f5.eav.DB_Pinger.db_Connect(DBDaemon.java:1052) at com.f5.eav.DB_Pinger.db_Ping(DBDaemon.java:996) at com.f5.eav.MonitorWorker.run(DBDaemon.java:501) at java.lang.Thread.run(Thread.java:722) Caused by: oracle.net.ns.NetException: NL Exception was generated at oracle.net.resolver.AddrResolution.resolveAddrTree(AddrResolution.jav a:614) at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.j ava:411) at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:672) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:237) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1042) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:301) ... 10 more

     

    Any ideas where should i look for the problema or what can i do?

     

  • Jorge_Herran_14's avatar
    Jorge_Herran_14
    Icon for Altostratus rankAltostratus
    Jul 10, 2015

    After trying different option i made it, this is how i did it:

     

    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%node_ip%)(PORT=%node_port%)) (CONNECT_DATA=(SID=PRODM1)(SERVER=DEDICATED)))

     

    The server doesn't recognice or have configured correctly the SERVICE_NAME.

     

  • Giant_K_333645's avatar
    Giant_K_333645
    Icon for Nimbostratus rankNimbostratus
    Jun 25, 2018

    Hi, Jorge. I am facing the same issue even with your string. (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%node_ip%)(PORT=%node_port%)) (CONNECT_DATA=(SID=PRODM1)(SERVER=DEDICATED))).

     

    I am suspecting that, 1. We have three VIP for Oracle Servers and It's causing the disruption with conflict. 2. Firewall Port for 1521 is not Open, Do we need to Open Daemon Port 1521 for the Communication? Because, we have everything setup at 1590 and I see both 1521 and 1590 Ports for traffic.

     

    Please suggest me something. I need to Resolve this issue in 1 week before we go live..