Download the pdf version: Auto Fix TBS alert
Introduction:
With database systems growing, database administrators increasingly have to deal with routine tasks. Small tasks may consume time, resources and increase the cost. Oracle 11g version provides solutions allowing to automatically fix some of those routine tasks.
Oracle 11g provides tools like Data Recovery Advisor DRA (check my article about this tool here: http://www.oracle-class.com/?p=1801) helping the DBA to take corrective actions. In this article, I am going to show you how to automate the fix of a tablespace alert.
Auto fix tablespace alert:
The manageability monitor process (MMON) wakes up every minute to check, capture and calculate metric values. MMON compares the actual values with the threshold value and raises and alert in case the threshold is exceeded. All alerts are then published to the queue ALERT_QUE.
To get the information about the alert, every agent must be subscribed to the queue. Every alert category has its own reason_id and every agent accessing the queue must know the reason_id to get information about the alert. For example, a tablespace alert has the reason id 9, an ORA 1555 has a reason id of 10.
You can get the reason id by executing the following PL/SQL block:
Auto fix a given tablespace alert:
In this section, we will see how to fix the tablespace alert for a given tablespace.
Step by Step:
First create a tablespace:
For this tablespace TBS_TST_QUE, we will define the alert threashold. We define the warning alert value from 80% and the critical value from 97% of tablespace usage.
We create the agent who is going to dequeue the ALERT_QUE queue. To catch the tablespace error, the agent must subscribe to the queue following the rule with reason id 9 and the tablespace name as the object name .
Give the appropriate privileges to the user SYSTEM for example. The SYSTEM user will be associated with subscribing agent and can access to any queued message.
Create a log table.
Create a callback procedure; we define the automatic repair of the alert in this procedure.
Now, we have to register the callback procedure to the subscriber AGENT_TBS_TST_QUE.
To test the procedure, I am going to create a test table and fill it with data.
As you have see above, an ORA-01653 was raised. An automatic fix has been performed and the datafile size has been increased by 5 MB. Let ‘s have a look into the alert log file content;
We raise again the error;
Auto fix all tablespace alerts:
I have tested defining for every tablespace a dedicated callback procedure as well as a consumer ( or subscriber). I started doing it for 2 different tablespaces. The issue is by doing that, I was not able to fix an alert on the second tablespace and I have received ORA-25242: cannot change
subscriber name from AGENT_TBS_TST_QUE t
o AGENT_TBS_TST_QUE2 without FIRST_MESSA
GE option.
The easier solution I have found is to create a single callback procedure for all tablespaces.
Let’s do the test on two different tablespaces.
One you understand the idea, you can optimize the code above by reducing the number of commits.
Now, let’s test having two tablespace alerts at the same time and we will see what will happen.
I know, a commit inside a loop is not good. But, I am using that code just to get the alert.
As you have seen above, both alerts got cleared automatically and there is no more entry in the dba_outstanding_alerts table.
Conclusion:
The same idea can be applied on other category of alerts like for example; recovery area size alert, undo tablespace alert and others. The common procedure starts by creating an agent, subscribe it to the queue, dequeue the ALERT_QUE and register a callback procedure with the subscribed agent.
The callback procedure contains all the actions required to fix the alert.
Another solution to fix the alerts would be the creation of an action script (PL/SQL, Shell, …) and call it from the Enterprise Manager Console. In this case, make sure you have given the appropiate privileges to the action script.