Lesson 9 | Customize Oracle alerts and reactions |
Objective | Oracle Enterprise Manager (OEM) Cloud Control |
In Oracle 23ai, customizing alerts and reactions (responses) for database monitoring is achieved through Oracle Enterprise Manager (OEM) Cloud Control or OCI Database Management for cloud deployments. Note: While this guide references OEM 13c, the procedures are compatible with newer versions, such as OEM 14c, with minor UI differences.
Here is how you can customize Oracle alerts and responses:
Oracle provides metrics for CPU, tablespace usage, I/O, memory, etc.
Steps:
Oracle can run scripts in response to alerts.
Steps:
💡 Example: Automatically grow a tablespace when usage exceeds 95%.
To define who is alerted and how.
Steps:
If your database is in Oracle Cloud (OCI):
OCI Example:
"Send email and trigger autoscale function if CPU > 90% for 15 minutes"
DBMS_SERVER_ALERT
for PL/SQL-based Alerts
For on-prem or scripted environments, you can define alert thresholds using PL/SQL:
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
warning_value => '85',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
critical_value => '95',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS'
);
END;
/
SELECT * FROM DBA_OUTSTANDING_ALERTS;
SELECT * FROM DBA_THRESHOLDS;
🚀 Summary Table
Feature | OEM Cloud | OCI Database Mgmt | PL/SQL API |
---|---|---|---|
Custom Thresholds | ✅ | ✅ | ✅ (DBMS_SERVER_ALERT ) |
Automatic Corrective Action | ✅ | ❌ (use Functions) | ✅ (via scripts) |
Notification via Email | ✅ | ✅ | ✅ |
SNMP/Webhooks | ✅ | ✅ (limited) | ❌ |
Visual Dashboard | ✅ | ✅ | ❌ |