I need a powershell script to check remote Oracle DB connected status from a Windows Server 2012

I need to check the Oracle database connectivity for some schemas from Windows Server 2012 as part of a health checkup. How can I get this done automated and connection status report sent an email id?
Rohit SuseelanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
once you get the connected status ...
what will be the next piece of information you need in the health (or availability) check ?

you might want to consider a more complete solution
than just checking if it's available
0
slightwv (䄆 Netminder) Commented:
>>connectivity for some schemas
This comment makes me think SQL Server not Oracle.  In Oracle a schema is a user in a database that owns objects.

I'm guessing you are wanting to connect to a database to make sure it is up not connect to a schema.

There are other issues that you might need to check:  The database can be up but for some reason it hasn't registered with the listener or the listener is down.



There is an excellent blog post that has everything you need to connect to Oracle using Powershell and the new Oracle Managed Client.  It has exactly what I would do.  

The blog was written when it was in beta but it has been released for production for a while now.

I use the Managed Client for all my .Net code and am happy with it.  The best part is you don't need an Oracle client install so you can skip over that part of the blog.

http://blogs.technet.com/b/heyscriptingguy/archive/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access.aspx

You will just need to add the loop for the list of databases you want to check and the email piece.  I'm sure there are Powershell samples out there for those.
0
Rohit SuseelanAuthor Commented:
I have Oracle DB in 11g hosted in a remote server whose status I need to check daily. My requirement is to check the status of the DB and if status returned NOT CONNECTED then to trigger an email using powershell script reason being I need to run this script from a WIndows 2012 server.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rohit SuseelanAuthor Commented:
Since the database is managed by a separate dedicated team I need to know just check if the DB is UP for my application to access it. a very simple requirement.
0
Geert GOracle dbaCommented:
daily ? ... just 1 check ? ... really ?

Do  you have any clue what things can go wrong with an oracle database ?


have you read the book oracle concepts
just read this book to start, it will give an idea of what can go wrong
http://docs.oracle.com/cd/E11882_01/server.112/e10897/toc.htm

there is no point in just checking connection status
if your database is in archive log mode, which it should be if a production db, and your archive log destination fills up, then the database just freezes

your script will start connecting and will neither return a success and neither an error message
it will simply freeze too
0
Geert GOracle dbaCommented:
ask the dedicated team to send the mail
they'll probably have this in place already
0
slightwv (䄆 Netminder) Commented:
Rohit Suseelan,

I agree with the previous post that just connecting may not let you know everything is OK.  That said, did you check the link I provided?  It has a script to connect to the database with PowerShell.
0
Rohit SuseelanAuthor Commented:
Hi Everyone,

I am sorry that I could not update on this post further. I was able to create a rather simple script to monitor my Oracle DB schema remotely and trigger email in case of DB connection issues from my application to those schema.

My approach may not be the best approach but it works charms for my purpose.  My Oracle DB is hosted at another location and my aim is to monitor few schema tables and its connectivity to my application. I created a small bat file calling to run a simple powershell script. "powershell.exe path\DBStatusScript.ps1". Along with it I also created an sql script (SQLSCRIPT.sql) file and a batch file to run this sql script like "echo @SQLSCRIPT.sql | sqlplus SCHEMA_USER/SCHEMA_PASSWORD@ORACLE_SID >PATH\OUTPUT.txt". This batch file runs the sql connect statement using oracle client installed on Server 2012 and calls the sql file. SQLSCRIPT.sql calls the sql SELECT statement like "SELECT database_status FROM v$instance" which returns the result and writes it to OUTPUT.txt file.
This OUTPUT.txt file is read by the powershell script DBStatusScript.ps1 and depending on the output of the SELECT query triggers an email alert  to my smtp email alert if the SELECT query fails to execute.

The have scheduled this to run every one hour to monitor the DB status. It works beautifully.

Thanks :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rohit SuseelanAuthor Commented:
I was able to achieve monitoring the Oracle DB with a simple script using a combination on bat, sql and powershell scripts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.