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

---

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

What is Geographic Resiliency ?

 

Geographic Resiliency

Geographic resiliency (also called geographic redundancy) refers to the practice of deploying applications, databases, and services across multiple geographic locations (regions) to ensure continuous service availability, business continuity, and disaster recovery readiness.

Unlike Multi‑AZ—where resiliency is confined within a single region—geographic resiliency protects against entire region‑level failures, large‑scale disasters, and regulatory boundaries.

---

What Geographic Redundancy Involves

A foundational geographic redundancy setup typically includes:

• Applications, services, or databases deployed in multiple regions
• Infrastructure instantiated under multiple subaccounts/subscriptions/projects
• Cross‑region replication of:
  • Artifacts
  • Data
  • Events
  • State
  • Infrastructure definitions
• Failover mechanisms at DNS, application, and/or database layers
• Monitoring, automation, and governance across dispersed geographic zones

While basic deployments may work with simple cross‑region backups or passive DR sites, true geographic resiliency requires advanced synchronization, failover orchestration, and application‑level design changes.

---

Benefits of Geographic Resiliency

1. Protection Against Region‑Level Disasters

Region‑wide failures—caused by natural disasters, power grid collapse, or cloud platform outages—cannot be mitigated with Multi‑AZ setups.
Geographic redundancy ensures services remain operational even if an entire region is down.

2. Zero or Near‑Zero Downtime (Depending on Architecture)

Active-active or active‑passive models allow:

• Seamless traffic redirection
• Automatic database failover (with async/sync replication patterns)
• Minimal interruption during failover events

3. Regulatory & Geo‑Local Compliance

Many industries require:

• Data to reside within specific countries
• Processing to occur in‑region
• Disaster recovery to include geographically distant sites

Geo‑redundancy aligns with these mandates.

4. Reduced Latency for Global Users

Serving traffic from the region closest to each user:

• Minimizes round‑trip time
• Improves performance and responsiveness
• Creates globally consistent UX

5. Business Continuity During Major Outages

By eliminating the “region as a single point of failure,” organizations maintain:

• SLA commitments
• Customer trust
• Operational continuity
• Disaster survivability

---

Challenges and Considerations

1. Cross‑Region Database Synchronization Latency

Due to physical distance between regions:

• Synchronous replication is rare or impossible
• Asynchronous replication introduces RPO > 0
• Conflict resolution logic may be required (multi‑write systems)

2. Increased Architectural & Operational Complexity

You must manage:

• Two or more parallel deployments
• Cross‑region orchestration
• Multi‑region CI/CD
• Configuration drift prevention
• Monitoring/logging across geographies

3. Cost of Duplicate Deployments

Multi‑region often requires:

• Multiple active clusters
• Extra storage
• Additional bandwidth
• Redundant monitoring and networking components

Cost optimization becomes a continuous exercise.

4. Application Redesign to Support Statelessness

To function in multiple regions, applications must:

• Be stateless, or rely on distributed caching
• Avoid local file writes
• Handle eventual consistency
• Support idempotent operations
• Use region‑aware routing and retries

5. Holistic Monitoring Across Regions

Visibility challenges include:

• Disparate logs
• Distributed traces
• Cross‑region health checks
• Coordinated alerting
• Multi‑region SLO enforcement

A central monitoring strategy is mandatory.

---

Summary: When to Choose Geographic Resiliency

You should adopt geographic redundancy if:

• The workload is mission‑critical
• The business requires continuous global availability
• You must meet stringent RPO/RTO expectations
• You operate in regulated environments (finance, healthcare, government)
• Your users are globally distributed
• Regional outages are unacceptable

Category	Single‑AZ	Multi‑AZ (Single Region)	Multi‑Region
Availability Level	Low – no AZ fault tolerance	High – survives AZ failure	Very High – survives region failure
Fault Tolerance	Instance‑level only	AZ‑level redundancy	Region‑level redundancy
Data Replication	Local or single‑node	Synchronous across AZs	Async / semi‑sync across regions
RPO	Minutes–hours (backup‑based)	Near‑zero (sync replication)	Seconds–minutes (async replication)
RTO	Hours (manual recovery)	Seconds–minutes (auto failover)	Minutes–hours (regional failover)
Latency Between Nodes	Lowest (same AZ)	Low (inter‑AZ)	Highest (cross‑region)
Service Continuity	Outage if AZ fails	Automatic AZ failover	Continues from secondary region after failover
Compliance & Residency	Basic	Regional compliance	Geo‑residency and DR support
Cost	Lowest	Moderate	Highest
Use Cases	Dev/Test, non‑critical	Business‑critical (HA)	Mission‑critical (full DR)
Strengths	Simple, cost‑effective	High availability, strong consistency	Max resilience & geography‑level protection
Weaknesses	No AZ/Region protection	No region‑level DR	Expensive & operational complexity

What Is Multi‑Region Resiliency Architecture ?

 

Multi‑Region Resiliency

Enterprises typically begin by strengthening availability within a single region, often through Multi‑AZ deployments for database and application redundancy. While this greatly improves availability, it does not protect against region‑wide failures. The next maturity step is Multi‑Region resiliency—the capability of applications and databases to continue operating even when an entire region becomes unavailable.

A Multi‑Region architecture distributes workloads, data, and infrastructure across geographically distinct cloud regions, providing the highest level of fault tolerance, business continuity, and global performance.

---

Why Do We Need Multi‑Region Resiliency?

Multi‑Region resiliency protects against large‑scale, catastrophic outages such as:

• Natural disasters
• Power grid failures
• Large‑scale cloud outages
• Control‑plane failures
• Geo‑specific compliance violations

Beyond disaster recovery, it also provides strategic benefits:

1. Minimizing Downtime & Eliminating Single‑Region Risk

If one region fails, another region continues operations seamlessly—maintaining service continuity and drastically improving RTO/RPO.

2. Compliance & Data Sovereignty

Many regulations mandate that data must remain within certain geographies. Multi‑Region deployments enable:

• Region‑specific data residency
• Local processing requirements
• Geo‑fenced workloads for regulatory compliance

3. Reduced Latency for Global Users

By serving traffic from the geographically closest region, applications achieve:

• Faster response times
• Better user experience
• Region‑aware routing

4. Consistent Global User Experience

Global load balancing ensures that users always connect to the optimal region, providing uniform performance worldwide.

---

Core Components of a Multi‑Region Architecture

1. Geographic Redundancy

Multi‑Region architectures replicate applications, databases, storage, caches, and services across geographically separated regions.

This ensures:

• High fault isolation
• Regional disaster recovery
• Global performance optimization

---

2. Global Load Balancing

Global load balancers (e.g., AWS Route 53, Azure Traffic Manager, GCP Cloud Load Balancing) distribute traffic across regions using:

• Latency‑based routing (send users to nearest region)
• Geo‑location routing (comply with data residency laws)
• Health‑based routing (avoid unhealthy regions)
• Weighted routing (control traffic distribution)
• Custom business‑logic routing

This layer ensures that user traffic is intelligently routed for optimal performance and availability.

---

3. Data Synchronization Across Regions

Multi‑Region architectures require robust cross‑region data replication to keep databases consistent. Data synchronization solutions include:

✔ Synchronous Replication (rare across regions)

• Very low RPO
• High network latency
• Possible only for extremely close regions

✔ Asynchronous Replication (most common)

• Low cross‑region network impact
• Minimal RPO (seconds)
• High scalability

Custom Multi‑Region Data Sync (Oracle GoldenGate etc.)

Tools like Oracle GoldenGate, Debezium, or cloud‑native replication services can:

• Synchronize tables across regions
• Handle conflict resolution
• Manage cross‑region schema changes
• Ensure near real‑time replication

These techniques ensure consistent database state across the globe.

---

4. Failover Mechanisms

Failover ensures seamless continuity when a region fails.

Types of Failover

• Automatic failover: Triggered by health checks
• Manual failover: Triggered by administrators

Key Failover Layers

DNS-Level Failover

• Global DNS routing
• Health‑check‑based DNS updates
• Used by Route 53, Traffic Manager, Cloud DNS

Application-Level Failover

• Client‑side logic or service mesh detects failures
• Redirects API calls to a healthy region

Database-Level Failover

• Replica promotion in secondary region
• Cross‑region failover of primary databases
• Transaction log shipping, GoldenGate, or cloud‑native DR

Failover Policies

Policies must define:

• Trigger conditions
• RTO/RPO targets
• Re‑routing rules
• Failback procedures

---

5. Monitoring & Management

A Multi‑Region architecture requires holistic observability across all regions.

Monitoring Tools

• AWS CloudWatch
• Azure Monitor
• GCP Cloud Operations
• Prometheus / Grafana
• Datadog, Splunk

Centralized Logging

Use ELK, Splunk, or Fluentd to aggregate logs across regions for:

• Auditing
• Troubleshooting
• Incident response

Automated Alerts

Load balancers and DNS health checks send alerts for:

• Regional outages
• Latency spikes
• Database failover events

---

Challenges of Multi‑Region Resiliency

1. Data Consistency

• Cross‑region latency impacts replication speed
• Eventual consistency is often required
• Conflict resolution mechanisms are needed

Techniques include:

• CRDTs
• Paxos / Raft
• GoldenGate conflict handlers

---

2. Increased Operational Complexity

Running multiple regions requires:

• Independent deployments
• Region‑specific monitoring
• More complex CI/CD pipelines
• Configuration drift prevention

---

3. Higher Cost

Costs increase due to:

• Duplicate infrastructure
• Inter‑region data transfer
• More monitoring/logging overhead

Cost management requires:

• Autoscaling
• Reserved instances
• Region‑specific optimizations

---

4. Application Design Changes

Applications may need:

• Stateless architecture
• Distributed databases
• Event‑driven communication
• CQRS
• Global session management

---

What Is Multi‑Region Database Deployment?

Multi‑Region database deployment distributes data across multiple geographically separated regions.

Key Aspects

• Data distribution: Data stored in multiple regions
• Replication: Continuous cross‑region sync
• Load balancing: Route queries to optimal region

Benefits

• High availability even during regional disasters
• Reduced latency for global users
• Improved disaster recovery RPO/RTO
• Compliance with local data laws

Challenges

• Complex to operate
• Expensive
• Ensuring global data consistency is difficult
• Requires advanced replication solutions (GoldenGate, etc.)