We have been using Oracle Cloud Control to monitor an OSB Cluster environment and send us notifications via email based on metrics we defined. One of the features of Cloud Control lets you define jobs and raise incidents based on the result of its execution. In this blog entry I will explain you how to create one of these jobs to monitor a database table.
Our OSB cluster uses Database Leasing to control its singleton resources. We also use the table “WEBLOGIC_TIMERS” to manage the cronjobs inside the cluster, guaranteeing that they will only run on one managed server while being highly available. In our environment the table should always look like this:
Unfortunately we have had some cases where this table becomes corrupt and it contains are more entries than expected. Is in these scenarios that we want to extend the Oracle Cloud Control monitoring in order to send us an email every time the data in the table is not correct.
Creating the Job In Oracle Cloud Control
The first thing to do is to create a job in Cloud Control. The following steps describe how to do it:
1) Login into the console and go to Enterprise -> Job -> Library
2) Select “SQL Script” from the drop down list and click “Go”
3) Enter the name for the job and select the target Database. Then click on “Parameters”
4) Enter the SQL Script you want to use to monitor the Database. In our case, we are using the exit code “10” to raise an error if the table has more than 2 rows:
variable exit_code number;
SELECT COUNT(*) INTO anzahl FROM weblogic_timers;
IF anzahl > 2 THEN
:exit_code := 10;
:exit_code := 0;
5) In the credentials tab you will need two type of credentials. The first one is to connect to the database and the second one is to connect to the host where the DB is running and use SQL Plus from there.
6) Finally, in the “Schedule” Tab you can configure when and how often the SQL Script needs to be executed. In our example we have configured it to run every 10 minutes:
Now you can click on “Save to library” and the Job will be created.
Submitting the Job
The steps from above created a job in the library, but it is still not active. You will have to submit it in order to be executed at the desired schedule. So, in Enterprise -> Job -> Library, select the job previously created and click on „Submit“.
You will get a wizard similar to the job creation one, where you can change some parameters for a specific execution.
After submitting the job, you can see it in the “Activity” table (Enterprise -> Job -> Activity)
Create an incident rule
Right now we only have a job that executes a SQL Query every 10 minutes and will throw an error if the table we are monitoring has more than two rows. But you will only see these “errors” in the activity table from above. The next step is to tell Cloud Control to send us an email every time this execution throws an error. To achieve this, we need to define a job event and a rule.
1) Go to Setup -> Inicidents -> Job Events
2) Normally here you only need to add the target Database. By default, an event is going to be generated if the status of the execution is “Action Required” or “Problems”. Only a Super Administrator can change these settings.
3) Now we need to create the rule. Go to to Setup -> Incidents -> Incident Rules -> Create Rule set.
4) Give the rule set a name and select “Job” in the “Applies To” drop down list.
5) In the Job frame click on “Add” and select “Specific jobs” to find the previously created job.
6) In the Rules frame click on “Add” to create a Rule
7) Select “Incoming events and updates to events” and click on “Continue”
8) Select “Job Status Change” and “All events of type Job Status Change” and click on “Continue”.
9) Click on add to create a Rule action. Here select “Always execute actions” in conditions for actions, “Create Incident” and “Each event creates a new incident” in create incident or update incident and enter the account that will receive the email in the send notifications frame.
10) After creating the action, click on “Next” and enter a name for the rule. Click on “Next” again.
11) Review the rule and click on “Continue”.
12) Review the whole Rule set information and click on “Save”.
Now that everything is ready you will start receiving emails from Cloud Control every time the SQL script detects more than two rows in the table.
The job monitoring activity is a very powerful feature from Oracle Cloud Control that lets you extend and customize the way you watch your systems. In this example we viewed how we can define SQL Jobs to monitor a specific database table. Nevertheless, this feature is not limited to that. You can create jobs that execute WLST commands and even Shell scripts (consider reviewing if your license allows you to do this first) opening a huge window of possibilities and new opportunities.
References and Acknowledgement
Many thanks to my friend and colleague Ulf Lämmerhirt who explained me how to do this and save me the work of having to look for it in the documentation. But here is the link to the official documentation if you want to read it.