Customizing the Management Agent

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:

✅ 1. Use Oracle Enterprise Manager Cloud Control
🎯 A. Create or Customize Metric Thresholds

Oracle provides metrics for CPU, tablespace usage, I/O, memory, etc.

Steps:

1. Login to OEM Cloud Control.
2. Navigate to: Targets > Databases > select your DB > Monitoring > Metric and Collection Settings
3. Locate the metric (e.g., Tablespace Usage %).
4. Click Edit.
5. Set Warning and Critical thresholds.
6. Choose Collection Frequency (e.g., every 5 mins).
7. Save.

🎯 B. Set Up Corrective Actions (Automatic Reactions)

Oracle can run scripts in response to alerts.

Steps:

1. Still in Metric and Collection Settings, find the desired metric.
2. Click "Set Corrective Action".
3. Attach a shell/SQL script, e.g.,:
   • Restart a listener
   • Resize a tablespace
   • Notify a DBA via Slack/email/SNMP
4. Define the scope (e.g., Warning or Critical level).
5. Save.

💡 Example: Automatically grow a tablespace when usage exceeds 95%.

🎯 C. Use Notification Rules

To define who is alerted and how.

Steps:

1. Go to Setup > Notifications > Rules.
2. Create a new rule set for a Target Type = Database Instance.
3. Choose specific metrics or alert severities.
4. Set up notification methods:
   • Email
   • SNMP Trap
   • Webhook
   • Custom scripts

✅ 2. OCI Database Management (for Oracle Cloud users)

If your database is in Oracle Cloud (OCI):

• Use OCI Database Management or OCI Monitoring + Alarms.
• Define Alarm Rules using:
  • OCI Metrics (e.g., CPUUtilization, FreeStorageSpace)
  • Thresholds
  • Actions (e.g., Send email via Notifications service or invoke Functions)

OCI Example:

"Send email and trigger autoscale function if CPU > 90% for 15 minutes"

✅ 3. Use 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;
/
        

✅ 4. Review and Display Alerts

• From OEM Console: Targets > Databases > [your DB] > Monitoring > All Metrics or Alert History
• From SQL/PLSQL:

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	✅	✅	❌