Oracle OEM and User-Defined Metrics

When you are using a Oracle Enterprise Manager Grid Control approach within your Oracle landscape to monitor and maintain your application and database landscape you can make use of all kinds of predefined checks Oracle will provide you. Even do this is already a great library of important checks and alerts it can always (and will) happen that after some time you would like to extend the functionality. For example if you have build some custom applications you might want to have some checks in place to do some very specific checking on things that will only be applicable for your specific application. To enable you to create your own checks Oracle has provided the "User-Defined Metrics" UDM in short.

User-Defined Metrics can be build using SQL or using scripting languages such as bash. For now we will quickly focus on how to build a simple check with SQL. In the example below I will show how you can build a check on the number of invalid objects in your database.

The first thing you need to do is to find a way how you can achieve what you want via a SQL script. In case of the number of invalid objects you can use the query below;

SELECT
     COUNT(1)
FROM   dba_objects
WHERE  status = 'INVALID'

Now we have to define the User-Defined metrics in Oracle Enterprise Manager. To do so navigate to "targets" -> "Databases". Below at the end of the page you will find a link "User-Defined Metrics" which will bring you to the page as show in the screenshot below.

You can see I have already created a couple of them. If you want to create a new User-Defined Metrics you can create one by using the "Create Like" button or the "create" button. We will be using the create button which brings you to the page shown below:

We will have to complete a couple of things, first we have to name the UDM. For now I have named it "TEST-invalid". The query is the same as the query shown above. Now we have to enter a username and password. I have used system for this. This might however not be the most optimal choice from a security point of view and you might run into some issues with the security department. What you might want to consider is creating a specific user for the checks to be executed however that is up to the security standards and compliancy you have to keep in mind in your specific environment.

In below screenshot we have to set some some more things. For example the thresholds. This will determine when to raise a warning or state a critical state. I have set in this example that if the value of the script is between 10 and 50 it will be a warning and if it is above 50 it will become critical. In the real world you do not want a single invalid object however this is to state what you can do with it. I have also set a alert message which will state in case of 15 invalid objects "uhoh 50 objects are wrong".

I have also stated that every 15 minutes the system will have to check. If you push test you will get the value and you will see if it is working. When you press OK the check will be created.

In our case 94 objects are invalid so now we can see it on the database page in Oracle Enterprise manager as you can see below. This is only a warning. You can automatilcy have script started and all kind of things however I will go into that at a later stage in another blogpost.

When you click on the message you will be able to see in more detail the current status and the history. This can be good to keep a close eye on your system and the trends in the system.