How to Monitor When Oracle Management Packs Are Activated or Used in Oracle database ?

 

How to Monitor When Oracle Management Packs Are Activated or Used

Oracle provides multiple internal views, advisor logs, and AWR/ADDM activity indicators that reveal accidental or unauthorized use of:

• Diagnostics Pack
• Tuning Pack

Below is a complete monitoring framework.

---

✅ 1. Check if Diagnostic/Tuning Features Were Used (Primary Indicator)

Oracle logs all usage in DBA_FEATURE_USAGE_STATISTICS.

Run:

SELECT name, detected_usages, total_samples, last_usage_date

FROM dba_feature_usage_statistics

WHERE name IN (

'Automatic Workload Repository',

'ADDM',

'SQL Tuning Advisor',

'SQL Access Advisor',

'ASH',

'AWR Baseline',

'Real-Time SQL Monitoring'

)

ORDER BY last_usage_date DESC;

What to look for:

• DETECTED_USAGES > 0 → feature was used
• LAST_USAGE_DATE not null → pack was accessed
• If you disabled packs but this still updates → OEM or some script is still invoking features

This is the most authoritative licensing indicator.

---

✅ 2. Monitor the Management Pack Parameter (Active Status)

Run:

SHOW PARAMETER control_management_pack_access;

Expected safe value:

NONE

If it changes to:

• DIAGNOSTIC
• DIAGNOSTIC+TUNING

➡️ Packs are active and billable.

For automated monitoring, query:

SELECT value

FROM v$parameter

WHERE name='control_management_pack_access';

---

✅ 3. Check if AWR Snapshots Are Being Generated (Diagnostics Pack Usage)

If snapshots exist, AWR is active → Diagnostic Pack is considered used.

SELECT snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

ORDER BY snap_id DESC

FETCH FIRST 10 ROWS ONLY;

If you see new snapshots after disabling:

➡️ Something is still collecting AWR data.

---

✅ 4. Monitor ADDM Execution (Diagnostics Pack)

SELECT task_name, created, status

FROM dba_advisor_tasks

WHERE advisor_name='ADDM';

If any task appears → Diagnostics Pack used.

---

✅ 5. Monitor SQL Tuning Advisor Activity (Tuning Pack)

SELECT task_name, created, status

FROM dba_advisor_tasks

WHERE advisor_name='SQL TUNING ADVISOR';

If tasks ran → Tuning Pack usage triggered.

Also check:

SELECT COUNT(*) FROM DBA_ADVISOR_LOG WHERE advisor_name='SQL TUNING ADVISOR';

---

✅ 6. Monitor SQL Access Advisor Activity (Tuning Pack)

SELECT task_name, created, execution_start

FROM dba_advisor_tasks

WHERE advisor_name='SQL ACCESS ADVISOR';

Any appearance = Tuning Pack usage.

---

✅ 7. Detect Automatic SQL Monitoring (Diagnostics + Tuning Pack)

SQL Monitoring is part of Tuning Pack.

Check ASH/SQL Monitor usage:

SELECT sql_id, status, sid, username, elapsed_time

FROM v$sql_monitor;

If rows appear → Tuning Pack active.

---

✅ 8. OEM Activity Monitoring (Common Source of Accidental Usage)

OEM may trigger pack usage even if DBAs don't run anything manually.

Check OEM repository:

For AWR/ASH report generation:

SELECT * FROM mgmt$awr_reports ORDER BY time_start DESC;

For SQL Tuning Advisor tasks created by OEM:

SELECT task_name, created

FROM dba_advisor_tasks

WHERE task_name LIKE 'SYS_AUTO_SQL%';

If OEM is generating:

• AWR Reports
• ADDM Reports
• SQL Tuning tasks

➡️ Packs are being used even if parameter says NONE.

---

✅ 9. Enable Audit Monitoring for Parameter Changes

Track if someone re-enabled packs:

AUDIT ALTER SYSTEM;

Then check:

SELECT username, timestamp, sql_text

FROM dba_audit_trail

WHERE sql_text LIKE '%control_management_pack_access%';

This is extremely valuable during SOX audits.

---

📈 10. Recommended Scheduled Monitoring Script

Run daily/weekly through OEM/cron.

SELECT 'FEATURE_USAGE', name, detected_usages, last_usage_date

FROM dba_feature_usage_statistics

WHERE detected_usages > 0

UNION ALL

SELECT 'AWR_SNAP', snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

WHERE begin_interval_time > SYSDATE-1;

Generate alerts if ANY usage is detected.

---

🎯 Summary – What You Must Monitor

Check Area	What It Detects	Indicates Usage
DBA_FEATURE_USAGE_STATISTICS	Core features	Primary licensing indicator
DBA_HIST_SNAPSHOT	AWR snapshots	Diagnostics Pack
ADDM Tasks	Diagnostics Pack	Yes
SQL Tuning Advisor	Tuning Pack	Yes
SQL Access Advisor	Tuning Pack	Yes
SQL Monitoring	Tuning Pack	Yes
OEM Activity	Auto usage triggers	Very common
Audit of parameter change	Re-enabling packs	Compliance breach

---