Monitoring Oracle database from Windows 7

To monitoring if all databases are up and running you can use this script with ease. Using this script you can check the status of database running in any operating system from your local windows client.I have written a small script. The approach I am following:

1.) Write a script (.sql file) to check the status of the database.
2.) Call this script (.sql file) from batch file.

We can add the the number of databases to the script(.sql) file we want to monitor. In below example, you will get better idea.


Prerequisites:

1.) Oracle client should be installed on your local windows machine.
2.) Your tnsnames.ora file should have all the database entries you want to monitor.


Example:

1.) Write a script (.sql file)

I will create file as health.sql

spool D:\Ashish\DBHEALTH.log
select 'Database '||name||' is '||status from v$database,v$instance;

conn "userid"/"password"@"service_name"
select 'Database '||name||' is '||status from v$database,v$instance;

conn "userid"/"password"@"service_name"
select 'Database '||name||' is '||status from v$database,v$instance;

spool off
exit;
/


2.) Create a batch file

sqlplus "userid"/"password"@"service_name" @D:\Ashish\health.sql

And save this file with .bat extension.

Note: Whatever service name you will mention in batch file, you will get first output for this database. And then the sequence you mentioned in script(.sql file)

say, If you create a batch file as,

sqlplus system/PASSWORD@DB1 @D:\Ashish\health.sql

Then in report, you will get the output of DB1, then DB2 and so on.

3.) Once your .sql file and .bat file are in place, you are good to go. Then simple double click the batch file and DBHEALTH.log file will be created.

Content of DBHEALTH.log


'DATABASE'||NAME||'IS'||STATUS                                                 
----------------------------------                                             
Database DB1 is OPEN                                                        

Connected.

'DATABASE'||NAME||'IS'||STATUS                                                 
----------------------------------                                             
Database DB2 is OPEN                                                        

Connected.

'DATABASE'||NAME||'IS'||STATUS                                                 
----------------------------------                                             
Database DB3 is OPEN       


I will be working on to get a better customized output and share once I am done.