These packs are controlled by the Oracle parameter control_management_pack_access.
🎯 Step 1 — Check Current Status
You’ll see one of the following:
DIAGNOSTIC+TUNING(both packs enabled)DIAGNOSTIC(Diagnostics only)NONE(all packs disabled → fully license‑safe)
🎯 Step 2 — Disable Both Packs
You must set the parameter to NONE at the instance level:
For RAC or Single Instance (spfile):
For pfile:
Edit the pfile and add:
control_management_pack_access = NONE
Restart the database.
🎯 Step 3 — Restart (Required for Some Versions)
Some Oracle versions apply the change immediately; others require a bounce.
To be safe:
⚠️ Important Notes for Compliance
Disabling these packs automatically disables:
- AWR (Automatic Workload Repository)
- ADDM (Automatic Database Diagnostic Monitor)
- SQL Tuning Advisor
- SQL Access Advisor
- Database Monitoring pages in OEM that use diagnostic data
If you're using OEM, make sure:
- AWR snapshots are not scheduled
- OEM does not trigger diagnostics-related jobs
🎯 Optional — Disable AWR Snapshot Collection (prevents accidental usage)
✔️ Good Practice Checklist for SOX & Licensing
Since you’re running DB architecture at NXP and focusing heavily on audit readiness, here’s a quick compliance checklist:
| Check | Status |
|---|---|
control_management_pack_access = NONE | ✅ |
| AWR snapshots disabled | ☐ |
| OEM monitoring avoids diag/tuning features | ☐ |
| No use of SQL Tuning Advisor | ☐ |
| No manual AWR report generation | ☐ |
1. Purpose
This SOP defines the detailed steps required to disable Oracle Diagnostics Pack and Tuning Pack across Oracle databases to ensure license compliance, prevent unintended usage, and support SOX / ITGC audit requirements.
2. Scope
This procedure applies to:
- All Oracle Enterprise Edition databases.
- All environments: Production, Non‑Prod, DR, QA, Dev.
- Both single-instance and Oracle RAC deployments.
- Systems integrated with Oracle Enterprise Manager (OEM).
3. Responsibilities
| Role | Responsibility |
|---|---|
| Database Architect | Approve the requirement & design compliance controls |
| DBA Team | Execute the SOP across environments |
| OEM Administrator | Ensure OEM jobs do not trigger Diagnostic/Tuning features |
| Internal Audit / SOX Team | Periodic validation |
4. Prerequisites
- SYSDBA access to the database.
- Approval from Application Owners (if restart is required).
- Confirm no features dependent on AWR/ADDM are needed.
- Recent database backup.
5. Background
Oracle Diagnostics & Tuning Packs are separately licensed. When enabled, Oracle automatically collects & stores performance data through:
- AWR (Automatic Workload Repository)
- ADDM (Automatic Database Diagnostic Monitor)
- SQL Tuning Advisor
- SQL Access Advisor
- OEM performance pages
To prevent unintentional usage, Oracle provides a parameter:
control_management_pack_access
Valid values:
DIAGNOSTIC+TUNINGDIAGNOSTICNONE← Fully disables both packs
6. Procedure
6.1 Step 1 — Verify Current License Pack Status
Execute as SYS:
SHOW PARAMETER control_management_pack_access;
Expected output will show the current value.
6.2 Step 2 — Disable Diagnostics & Tuning Packs
If using SPFILE (Recommended for most environments):
If using PFILE:
- Edit the pfile:
control_management_pack_access = NONE
- Recreate the spfile (if required):
6.3 Step 3 — Restart Database (Required in Some Versions)
Some Oracle versions apply immediately, but restart ensures full enforcement:
6.4 Step 4 — Validate the Change
Expected:
NAME VALUE
------------------------------------ -----
control_management_pack_access NONE
6.5 Step 5 — Disable AWR Snapshots (Optional but Recommended)
This prevents accidental AWR collection:
EXEC dbms_workload_repository.modify_snapshot_settings(interval => 0);
To verify:
SELECT interval FROM dba_hist_wr_control;
6.6 Step 6 — Adjust OEM Monitoring
If OEM is configured, ensure:
- AWR-based reports are disabled
- ASH Analytics not accessed
- Performance pages using Diagnostic data are not used
- No SQL Tuning Advisor jobs scheduled
Deactivate OEM jobs:
7. Post‑Implementation Checks
| Check | Validation Query / Step |
|---|---|
| Parameter set to NONE | SHOW PARAMETER control_management_pack_access |
| AWR snapshots disabled | SELECT interval FROM dba_hist_wr_control |
| No ADDM reports generated | Check OEM & DBA_ADVISOR_LOG |
| No SQL Tuning Advisor usage | SELECT * FROM dba_advisor_log WHERE advisor_name='SQL TUNING ADVISOR'; |
| OEM Performance pages do not show diagnostic data | Manual check |
8. Rollback Procedure
If the packs need re-enabling:
ALTER SYSTEM SET control_management_pack_access = 'DIAGNOSTIC+TUNING' SCOPE=BOTH;
Restart database if required.
9. Compliance & Audit Evidence
DBA team must retain the following evidence:
- Screenshot or spool log of parameter:
control_management_pack_access = NONE - AWR snapshot interval set to
0. - OEM tuning/diagnostic jobs disabled.
- Monthly validation logs.
10. Risks & Mitigations
| Risk | Mitigation |
|---|---|
| Loss of AWR data | Use Statspack instead |
| OEM features unavailable | Monitor via OS tools or Statspack |
| Application performance troubleshooting impact | Build custom scripts or enable temporarily with approval |
