HOME

Automatically check Multiple Oracle DB instances

Sometimes we need to check/verify the status of multiple Oracle databases which might be placed on different servers or we may be required to run a common query on all such databases.
Sometimes DBAs need to checkout status of several DBs after an infra upgrade to verify if DBs are up by firing only a query as simple as select * from dual;
One way is to login to each server, sudo to oracle user, set environment, connect to DB and then run the query. Or we can open each connection via SQL developer before running the query. Similar steps need to be repeated for each DB.

In any case, it becomes a pretty time consuming task as the number of databases become large.

Idea here is to automate this task by putting all DB names in an input file and feeding the same to a script which will connect to each DB and run the query task and get the response back.

In this demo, I am doing it via a windows batch script. It may be translated to Unix shell script and can be be used for other databases also.


Pre-requisites for Oracle DB


Sample input file with DB instance names: input.txt

Orcl_1
Orcl_2
Orcl_3
Orcl_4
Orcl_5

Script to connect and run query in DB instances

@echo off
SET INPUT=input.txt
SET ID={give user id here}
SET PWD={give password here}
SET ERRORLEVEL=

echo Input file: %INPUT%
cd %cd%
for /F "delims="  %%i in (%INPUT%) do call :set_param %%i

:set_param
set db=%1
SET CONN=%ID%/%PWD%@%db%
echo:
echo ==========================================================================
echo Checking %db% :
echo SELECT 'OK' status FROM DUAL; | sqlplus -S %CONN%


Put the above script in a file and rename it as mon.bat.
Place the bat file & input file in same directory and run it via cmd prompt.


Sample Output

C:\Users\AV\Documents\scripts>mon.bat
Input file: input.txt

================================================================================
Checking Orcl_1 :

ST
--
OK
================================================================================
Checking Orcl_2 :
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where logon ::= username[/password][@connect_identifier]
      proxy ::= proxyuser[username][/password>][@connect_identifier]

================================================================================
Checking Orcl_3 :

ST
--
OK
================================================================================
Checking Orcl_4 :
ERROR:
ORA-28002: the password will expire within 6 days

ST
--
OK
================================================================================
Checking Orcl_5 :
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where logon ::= username[/password][@connect_identifier]
      proxy ::= proxyuser[username][/password][@connect_identifier]

C:\Users\AV\Documents\scripts>

Observations:

From the script, we get response whether the DB connection was OK or if login credentials were invalid or if the supplied TNS name itself was bad. 
    
So it will display any valid error which Oracle gives out when connection is attempted.



Thanks...

TOP