Check database availability

[radd]

Hi,
I am new in ELK and I have one question regarding monitoring oracle DB availability.
Please advice me, because I wish, with your help and knowledge, to find the best solution.
I have read that I can monitor for Oracle DB Connection (checking listener availability), using Heartbeat.
But here I want to monitor database availability (periodically logon with one specific user for this purpose on Oracle database), not only to check listener availability.
Please advice me, what will be the best way to do it.

Thanks in advance!

[Andrew_Cholakian1 (Andrew Cholakian)]

Heartbeat supports TCP monitoring which can monitor if the TCP port your Oracle DB runs on is available, but it can't initiate a full connection to the database.

You could, alternatively, write a web service that exposes an HTTP(s) endpoint that indicates whether the DB is up or down and then monitor that with heartbeat.

[radd]

Many thanks on this answer!
Please advice me is there any other solution for this problem (for example logstash or some another beat). Because if we are using some specific service for this purpose of monitoring DB availability, we need additional monitoring of this APP server and one additional layer for monitoring.

Kind regards!

[Andrew_Cholakian1 (Andrew Cholakian)]

You could use logstash with the Logstash input JDBC plugin to execute a query on a regular basis and use a custom dashboard to track its availability.

[radd]

Many thanks Andrew,
I am not sure that I am correctly understand your suggestion (please sorry I am new in this area of the ELK).
I have created specific configuration file with this "db availability check" with this "input" structure, which just logon on the database with
specific user and execute one dummy sql on the target database:

input {
jdbc {
jdbc_connection_string => "jdbc:oracle:thin:@//*********:1521/hcs"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_driver_library => "/home/elastic/jdbc_drivers/ojdbc6.jar"
jdbc_user => "elastic"
jdbc_password => "**************"
jdbc_validate_connection => true
schedule => " * * * *"
statement => "select dummy
from dual"
}
}

Everything works fine, until the moment when I have logon ERROR (when I have simulated shutdown of DB instance), related with DB availability (ORA-12514, TNS:listener does not currently know of service reques).
Log related with this check is below:

[2020-01-08T13:37:00,235][INFO ][logstash.inputs.jdbc ] (0.000626s) select dummy
from dual
[2020-01-08T13:38:00,302][INFO ][logstash.inputs.jdbc ] (0.002105s) select dummy
from dual
[2020-01-08T13:39:00,065][ERROR][logstash.inputs.jdbc ] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaSql:: SQLException: Listener refused the connection with the following error:\nORA-12514, TNS:listener does not currently know of service reques ted in connect descriptor\n "}
{ 2026 rufus-scheduler intercepted an error:

Could you please advice me how to handle this DB unavailability (SQLException) and fill in specific elastic index during this downtime period with data about this unavailability.

Kind regards!

[elasticforme (Sachin)]

as I can see it, you have to use some logic here.

for example change schedule => "*/5 * * *"

execute sql for example
select to_char(sysdate,'dd-MON-YY HH24:mi') uptime from dual;

08-JAN-20 15:03

now you got a field called uptime

convert that to date filed using
filter {
date {
match => ["uptime", "dd-MMM-yy HH:mm", "]
target => "uptime"
}
}

now if you plot your graph by this timestamp where ever you have zero record you database is not available.

now when you don't have uptime record on top of 5th minute your database was down that time

[radd]

Thanks a lot Sachin,
I have implemented your suggested solution and as you see from "Discover" menu I get the next JSON indexes:

As you have seen from this image I don't have any JSON document during unavailability DB period (because I am not able to create session on the DB side). I wish, if it is possible of course , to have also JSON documents during this unavailability DB period (08:30, 08:31, 08:32, 08:33) which describing with some value that DB is unavailable.
Additionally if I am using this DB time from database which is based on OS time, what will be happened if this OS time is not synchronized with NTP, and I my JSON documents are related with this time?

Many thanks!