SCOM 2012 – Monitoring Oracle Database OLE DB Management Pack Template

As you know there are different ways to achieve certain things. It always depends on your requirements. In my latest blog post I would like to show how relatively easy it is to monitor an OLE DB connection to an Oracle database and also get some performance data.

My well-respected fellow SCSM Guru  and MVP Marcel Zehner (http://blog.scsmfaq.ch) provided me with this solution here, all credits to him.

The screenshots are in German but I will try to translate the options to English where necessary. It could be that my translation does not exactly match your setup in English but I do my best.

We have multiple steps to take in order to make the monitor function properly…

• Step 1 – Install OLE DB provider
• Step 2 – Configure database configuration file TNSNAMES.ora
• Step 3 – Configure OLE DB Management Pack Template
• Step 4 – Configure RunAs accounts

1) Install OLE DB provider

I assume you know what a watcher node is. On each watcher node where you want to perform the connection tests from install the OLE DB provider. For this provider you need to install the Oracle client tools.

Go to the Oracle site http://www.oracle.com and make sure you download the correct version in my case it is version 11g ~600MB to download.

Start the setup.exe…

Choose “Benutzerdefiniert” (Custom)…

Choose language…

Select installation path…

Select “Oracle Provider for OLE DB” and finish the installation…

Well done! Next step is to configure the connection, so that the client tools know where to find the database…

2) Configure Database Configuration File TNSNAMES.ora

In order to access the Oracle databases it is a must to configure the TNSNAMES.ora configuration file. In this file you define the server name, database name and alias.

You find the path to the file in the registry…

HKLM\Software\Oracle\Installation\TNS_ADMIN

Because you know now where the file should be, open Windows Explorer and go to this directory and create a file (regular text file) called TNSNAMES.ora…

Write down your configuration settings as needed in the following format…

You could also define more alias/servers if needed. To find more information about this kind of configuration file just use bing.

3) Configure OLE DB Management Pack Template

Start the wizard by clicking “OLE DB Data Source”…

Choose a name and a custom management pack where you want to store the monitor…

Click “Build…” and enter the information as shown in the screenshot. You can choose any provider, server and database because we are going to change this information/connection string on the next step…

If you select “Query to execute:” you actually can provide a select statement which will be execute against the database. If you don’t provide this query you won’t be able to select the performance thresholds on the next step…

Here you can select the performance thresholds which you want to measure and get warnings/errors about…

• Connection time => How long it takes (maximum) to make a connection
• Query time => How long it takes (maximum) to run the query statement
• Fetch time => How long it takes (maximum) to fetch the results of the query

Next select the watcher nodes where you have installed the Oracle client tools from step 1…

After finishing the wizard we go to the “Properties” of the created monitor template…

Select the “Connection String” tab and modify the connection string to…

• Provider=ORAOLEDB.Oracle
• Data Source=[Alias you have defined in the TNSNAMES.ora file]

That’s it, the connection is setup. Next we define the access account…

4) Configure RunAs Accounts

In order to access the database we need to create the RunAs accounts. First we need to create an account type “Basic”…

Select “Basic Authentication” and a display name…

Set the credentials which have permission in the database…

Choose the distribution method you need in my case less secure and click “Create”…

Now we need to map the created user account to the appropriate profile. In our case we choose the “Oracle xxx Simple Authentication Profile”…

Click “Add…”…

Select the account we have created and select “All targeted objects”. Click “OK” and finish the wizards…

That’s it, now you should be able to monitor the Oracle connection…

Troubleshooting:

It could be that you might see theses errors in the OperationsManager event log on you watcher nodes…

If this happens it could be that..

• Your TNSNAMES.ora file contains the wrong e.g. alias or connection settings
• Your RunAs account/profile is not configured properly

I hope you like this post…

Cheers,

Stefan