Real‑World Oracle DBA Lessons: Roles, Monitoring, and Optimization

Introduction

The author aims to turn painful Oracle DBA experiences into useful lessons rather than competing over who suffered more.

DBA Roles and Division of Labor

Physical DBA : Handles environment deployment, data recovery, patch upgrades, and system/storage/network tasks.

Performance DBA : Focuses on product‑level performance tuning, often derived from deep product knowledge.

Dev DBA : Develops automation tools for DBA operations and requires strong coding skills.

Delivery DBA : Manages data delivery across UT, UAT, PROD environments, including patch, baseline, and replication tasks.

Application DBA : Works closely with developers, provides frontline data support, and understands system architecture and business processes.

Site DBA : Oversees all product and business aspects, ensuring end‑to‑end solution implementation.

Industry Environment

Transitioning from traditional to internet industries demands broader considerations beyond pure technical choices, such as hardware selection and certification requirements. Strict certification ties specific Oracle sub‑versions (e.g., 11.2.0.2.x) to products. Many customers still use RAC in an active/passive mode, and features like histograms, recycle bin, and skip scan are often disabled for safety.

Monitoring

Typical monitoring tools include commercial or open‑source solutions. The author mentions GCEM12c , which natively supports Oracle and can be extended, and Zabbix paired with Orabbix for database instance monitoring.

Common blind spots:

Using select 'Oracle is alive' from dual to check instance status is unreliable because the dual table is accessible in NOMOUNT, MOUNT, and OPEN phases.

In 11g, the automatic archiving deletion policy for v$flash_recovery_area_usage can waste space during backup mount phases.

Hardware monitoring is critical. The author shows charts indicating high failure rates for motherboards and memory, and emphasizes the need for comprehensive, fine‑grained hardware metrics.

Optimization

SQL profile performance bomb : A profile that worked for a year degraded when underlying data changed dramatically.

Long‑running PL/SQL : A four‑hour script was later rewritten as a one‑minute SQL statement.

SQL*Loader bottleneck : Network issues were identified via SCP file transfer tests.

Case study: Database login failure caused by audit space consumption. Frequent archive switches revealed redundant data; removing the redundant rows reduced archive switches from 14 to 0 in a seven‑minute run.

Development & Business

Custom AWR, ASH, and ADDM reports can dramatically improve efficiency.

Connection‑leak problems were traced to JDBC not releasing connections.

Business‑driven SQL optimization can yield the greatest performance gains.

Partition rule issues: multi‑key partitions can cause poor data layout; examples of partition metadata and actual data distribution are illustrated.

Outlook

Future DBA expectations include broader skill sets (development, architecture, full‑stack DBA) and adoption of new technologies. Automation and fine‑grained operations, such as enhanced Zabbix monitoring of DB time, are highlighted as current improvement directions.