Lot’s of companies do not use Grid- or Cloud Control for monitoring their Oracle databases for various reasons but rather use open source tools like nagios. And lot of those either implemented custom script frameworks or rely on plug-ins available for the monitoring solution of choice. This post shall show on how you can let Oracle check a lot of its own metrics and you only need one script for alerting various metrics. Oracle provides a plsql package called DBMS_SERVER_ALERT. This package defines plenty of metrics which might be used for alerting. Let’s take the famous tablespace percent full metric as an example and do a little demo:

SQL> create tablespace tbs1 datafile size 1m;

Tablespace created.

SQL> create table t1 ( a varchar2(2000) ) tablespace tbs1;

Table created.

So you want to get a warning if the tbs1 tablespace grows up to 80% and you want to get a critical message if the tablespace exceeds 90%? Easy:

SQL> !cat a.sql
begin
      dbms_server_alert.set_threshold
        ( metrics_id => dbms_server_alert.tablespace_pct_full
        , warning_operator => dbms_server_alert.operator_ge
        , warning_value => 80
        , critical_operator => dbms_server_alert.operator_ge
        , critical_value => 90
        , observation_period => 1
        , consecutive_occurrences => 1
        , instance_name => null
        , object_type => dbms_server_alert.object_type_tablespace
        , object_name => 'TBS1'
        );
end;
/

SQL> @a

PL/SQL procedure successfully completed.

SQL> 

Let’s generate some data:

SQL> insert into t1 select lpad('0',2000,'0') from dual;

1 row created.

SQL> insert into t1 select * from t1;

1 row created.

SQL> insert into t1 select * from t1;

2 rows created.

SQL> insert into t1 select * from t1;

4 rows created.

insert into t1 select * from t1;

8 rows created.

SQL> insert into t1 select * from t1;

16 rows created.

insert into t1 select * from t1;

32 rows created.

SQL> insert into t1 select * from t1;

64 rows created.

SQL> commit;

Commit complete.

Did this already generate an alert?

SQL> set lines 264
SQL> col OWNER for a10
SQL> col OBJECT_NAME for a10
SQL> col REASON for a50
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,MESSAGE_LEVEL from dba_outstanding_alerts;

OWNER	   OBJECT_NAM OBJECT_TYPE						       MESSAGE_LEVEL
---------- ---------- ---------------------------------------------------------------- -------------
	   TBS1       TABLESPACE								   1
SQL> 

Yes it did. So all you need is a script that periodically checks the dba_outstanding_alerts view for any alerts and depending on the message_level send a warning or critical message to wherever you want. As soon as the issue is resolved the message in dba_outstanding_alerts will disappear after the next observation_period:

SQL> alter tablespace tbs1 add datafile size 10m;

Tablespace altered.

(after one minute):

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,MESSAGE_LEVEL from dba_outstanding_alerts;

no rows selected

SQL>

The good thing with this is: You only need one script for alerting all the metrics defined in the package. But be careful, not all metrics are really useful and you might end up in getting hundreds of notifications. Another goody: As far as I know you do not need the diagnostic pack for this.