Disable Diagnostics Pack & Tuning Pack in Oracle

 These packs are controlled by the Oracle parameter control_management_pack_access.

🎯 Step 1 — Check Current Status

SHOW PARAMETER control_management_pack_access;

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):

ALTER SYSTEM SET control_management_pack_access = NONE SCOPE=BOTH;

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:

SHUTDOWN IMMEDIATE;

STARTUP;

---

⚠️ 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)

EXEC dbms_workload_repository.modify_snapshot_settings(interval => 0);

---

✔️ 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

1. SYSDBA access to the database.
2. Approval from Application Owners (if restart is required).
3. Confirm no features dependent on AWR/ADDM are needed.
4. 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+TUNING
• DIAGNOSTIC
• NONE ← 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):

ALTER SYSTEM SET control_management_pack_access = NONE SCOPE=BOTH;

If using PFILE:

1. Edit the pfile:

control_management_pack_access = NONE

2. Recreate the spfile (if required):

CREATE SPFILE FROM PFILE;

---

6.3 Step 3 — Restart Database (Required in Some Versions)

Some Oracle versions apply immediately, but restart ensures full enforcement:

SHUTDOWN IMMEDIATE;

STARTUP;

---

6.4 Step 4 — Validate the Change

SHOW PARAMETER control_management_pack_access;

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:

-- Disable SQL Tuning Advisor tasks

EXEC dbms_sqltune.drop_tuning_task(task_name => '<task_name>');

-- Disable ADDM tasks

EXEC dbms_addm.delete_db_advisor_run(run_name => '<run_name>');

---

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