There are no free management packs for monitoring Oracle databases in SCOM 2012. However, a free solution is to use the OLE DB Data Source monitor and install Oracle client on the watcher node. There are several blogs about how to set this up, so I’m not going to write about that.

 

What I am going to write about is the “ORA-00900 invalid SQL statement” error one might receive from these monitors. Some weeks ago I was at a costumer who wanted to monitor some Oracle databases and I configured the monitor without a query, which worked fine. I then put in the query and I got the following error:

clip_image001

(in Danish, sorry)

 

Depending on which program you use when executing a stored procedure you simply put ”exec” or ”execute” before the query, but both of these failed in SCOM.

 

I then starting investigating with the local Oracle guy and we found that you use BEGIN and END when executing the stored procedure;

BEGIN

your_stored_procedure;

END;


In the query you cannot use line breaks, so you need to make the query a one-liner:

BEGIN your_stored_procedure; END

clip_image003

 

I made this change and the monitor turned green right away!

 

Conclusion:

When you want to execute an Oracle query you need to put in BEGIN your_stored_procedure; END;

Happy monitoring your Oracle databases for free!