CODEWITHSUNDEEP
oracle-databasebashshellmonitoringboot
dacracot
dacracot

Reputation: 22348

Is there a tool that can check the availability of an Oracle 11g database on a remote machine?

I'm configuring the boot sequence on a Linux box, and one of the startup applications is dependent upon an Oracle instance being available on a different box. I would like a bash scriptable tool to check the availability of the Oracle instance. Likely, the power button on both boxes will have been hit within seconds of each other, and I need the longer Oracle process to finish before my application attempts to connect.

EDIT: I don't have Oracle installed other than the thin JDBC jar, and would prefer not to on the client box.

Upvotes: 2

Views: 3324

Answers (3)

derobert
derobert

Reputation: 51147

The first thing to try is tnsping which will tell you if the listener is running. If the listener is running, you can just try and connect to the instance, and run e.g., select 1 from dual. You can do that on the command line using sqlplus. Depending on how your Oracle database is configured (and which options you purchased), SNMP may also be an option.

You could also have your app better handle the database not being up yet (but sleeping 10 seconds and trying again, for example).

Also, you may want to consider using a switched PDU (e.g. like one from APC) to control the order machines are booted after the power comes back. If you want other sysadmin answers (as they've all solved this problem), I suggest asking on Serverfault.

in response to your edit:

If you don't want to install the Oracle Client, then you won't have sqlplus or tnsping. Instead, just write a trivial Java program to attempt to connect to the database using the JDBC thin drivers. If you can connect, try the select 1 from dual. If that works, then Oracle is up. If you instead get failures or exceptions, then its not up.

Upvotes: 2

Khaled
Khaled

Reputation: 1194

You can write a simple script that uses a for loop to check for oracle port using nc. Something like this:

#!/bin/bash
for i in `seq 1 10`
do
    nc oracle_server oracle_port -w 2
    res=$?
    if [ $res -eq 0 ] ; then
        echo "OK"
        exit 0
    else
        echo "Sleeping for 5 second and retry"
        sleep 5
    fi
done

You can customize the number of loop iterations and waiting times (-w 2 and sleep 5).

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231661

The only way to be sure that the remote database is up and that the listener is up and that the database is registered with the listener properly would be to actually make a connection. You could use the SQL*Plus utility (assuming the Oracle client is installed on the linux box your application runs on) to attempt to make a connection. Something like

Create a file check_db_up.sql

whenever sqlerror exit 1;
connect username/password@<<TNS alias>>
select 1 from dual;
exit 0;

Then invoke this script in your bash shell script and look at the return code

sqlplus /nolog @check_db_up.sql

If that returns 1, there was an error and the database isn't up. If it returns a 0, the database is up and accepting connections.

Upvotes: 3

Related Questions