Important notice: This post is a working example of OMS and Oracle. Please note that as the OMS platform continues to be extended our solutions will be updated concurrently to reflect and support these coming changes. During the writing of this blog all features described here within are accurate and current. The described OpsLogix OMS extension Management Pack is, at present, available at no cost to all customers that already have an OpsLogix Oracle Management Pack License.
Here at OpsLogix we are always looking to integrate our products with the latest technologies. For SCOM this means that we have created a fantastic Oracle Management Pack that monitors your Oracle environment on any platform without any extra software installation on your Oracle boxes. Yes, that’s right, no extra software on your Oracle production servers is required. The Oracle Management Pack has been tried, tested and proven to work in multiple industry sectors, including Fortune 500 companies for the past 3 years. OMS (Operations Management Suite) is a brand new, exciting product created by Microsoft. For this reason, we decided to integrate it with our Oracle MP.
In this blog I will demonstrate how easy it is to collect all of your Oracle alert logs and analyze them using OMS.
Before we begin, ensure that your infrastructure meets the prerequisites below:
- A working SCOM 2012 R2 environment
- Oracle server access (windows or Linux)
- A OpsLogix Oracle Management Pack & license
- The OpsLogix OMS Extension management Pack
- A Microsoft account (Live, Hotmail and/or Office 365)
Setup the OMS account
The process of setting up the OMS account has already been outlined in the link below. Click the link and follow the “onboarding” instructions to set up your OMS account.
Notice: You don’t have to pay for OMS. There is a free plan! The only restrictions are daily upload limits of 500 MB of data and a retention period of 7 days. However, for Oracle alerts or event analyzing it is a great place to begin. After you have followed the steps above you should have added your SCOM management group to OMS. Check this by opening the OMS dashboard, shown below, and select Overview -> Settings -> Connected Sources. You should see your management group listed alongside the number of servers and last data input.
Setup the Oracle Monitoring
Below are the steps for setting up Oracle Monitoring. Please also read the installation guide included in the software package.
- Open as SCOM administrator the Operations manager Console and Import the “OpsLogix Oracle Management Pack” files included in the software package.
- Apply the Oracle License using the OpsLogix Licensing dashboard in the SCOM Operations management console.
- The next step is to add the Oracle instances you want to monitor. This you can do in the SCOM console using the OpsLogix Oracle configuration dashboard.
Since the management pack only uses ODAC connections you only have to specify the connection parameters (IP,user, pwd etc.) to the Oracle instance so that NO other software needs to be installed (for example: the Oracle client) for this Oracle instance. You can add Oracle to Windows, Linux, Unix etc., provided you have the correct connection parameters.
- If you have successfully added the Oracle instance you should now see the monitoring being populated. You should wait until you see the Oracle instance’s state view.
- Oracle’s state view is active with a health state.
Setup the OMS Oracle Monitoring
We are now going to enable the Oracle Alerts in OMS.
- Using the SCOM console, import the OpsLogix OMS Oracle extension management pack. (This is a separate download and not included in the Oracle MP software package.)
- By default, only the Oracle instances that are members of the SCOM group, “OpsLogix Oracle OMS group” are enabled to collect the alert logs.
- For every Oracle member in this group, configure the alert log monitoring by creating an extra view as described in the Oracle Management installation manual. This view will enable you to monitor the Oracle alert logs without having to have SYSDBA privileges. (You will, however, need a one-time SYSDBA account to create this view.)
Viewing the OMS Oracle Alert collection
The next step is to verify that the Oracle alerts are picked up by OMS.
- Open a web browser and go to : https://login.mms.microsoft.com/signin.aspx?ref=ms_mms.
- Login using the Microsoft account you created in the “Setup the OMS account” section.
- The main OMS dashboard should appear. Select the “Log Search”.
- Now do a log search to retrieve all Oracle alert events. Type in the search box “Type=Event EventLog=Oracle”.
The first time you do this you will probably see 0 events found.
- Now we can generate some test Oracle Alerts so OMS can collect them.
Open an SQLPLUS connection to the Oracle instance you have configured to collect the alerts for and logon as SYSDBA. Type the following command to generate a test alert into the Oracle Alerts log: exec dbms_system.ksdwrt(2, ‘ORA-00000 Test Event’);
- It could take up to five minutes for OMS to pick up this generated alert. If everything is working you will see an event in the log search.
*Please note that this alert will NOT be visible in SCOM because we don’t store these alerts in the SCOM databases, remember that we are collecting ALL Oracle messages. If we did store these alerts the sheer volume of Oracle alert log messages could kill the performance and free space existing within the SCOM environment. That being said, the OMS environment is configured precisely to handle massive amounts of data without any problems!!
- We can now see that our test events are collected in OMS. Now we should let OMS collect the test events for a couple of days so that we have some data to work on.
ORACLE OMS search query’s
Now it’s time to write OMS queries that we can use to help us analyze the Oracle alert logs. I have given you some examples below:
All Oracle Events
All Oracle Events with only the message and time
Type=Event EventLog=Oracle | Select Source , TimeGenerated , RenderedDescription
All Oracle Events With the word Test in it
Type=Event EventLog=Oracle Test | Select Source , TimeGenerated , RenderedDescription
Count the number of messages that have test in it
Type=Event EventLog=Oracle Test | measure count() by Source
Count per oracle instance with the number of events with ORA ID 500 in it
Type=Event EventLog=Oracle EventID=500 | measure count() by Source
Group by ORA event ID and count
Type=Event EventLog=Oracle | measure count() by EventID
Another great resource on how to make queries OMS can be found here: https://azure.microsoft.com/nl-nl/documentation/articles/operational-insights-search/
Keep in mind that you can also use the Table view instead of the default List view. With some outputs it may be more user friendly to export and read the Table or List views in Excel.
I hope this post helps you discover for yourself the Power of OMS and Oracle together while using the OpsLogix Oracle Management Pack. Check back often, as I will be creating follow-up posts. I will be giving you examples of how to collect Oracle AUDITING events to OMS so that you can do security checks using OMS!
(If you are interested in more OMS details, you can follow my blog. Touching SCOM – Michel Kamp )