Simplify Oracle DBA Tasks with the OraZ Automation Toolkit

How to Simplify DBA Workflows

Database administration is traditionally a labor‑intensive job that requires deep knowledge of servers, storage, networks, operating systems, SQL, shell scripting, and often Java. The author, a veteran DBA, created a simple shell‑based tool called ora in 2008 to unify repetitive scripts into a single entry point, dramatically reducing manual copy‑paste work and error rates.

Ora Script Set Benefits

Standardizes daily monitoring and maintenance operations.

Reduces the chance of human error and boosts efficiency.

Enables DBAs to respond to incidents more calmly.

The tool’s widespread adoption also unintentionally led some DBAs to lose SQL proficiency when working off‑site.

Intelligent Hang Analysis

When Oracle experiences kernel‑level resource contention (e.g., latch hangs) prior to 11g, it cannot automatically detect or resolve the deadlock. The author developed a second program that uses the hanganalyze utility to dump resource relationships, identify the hang source early, record relevant information, and terminate the offending process.

Typical analysis steps:

Identify hang symptoms via wait events.

Trigger hanganalyze collection.

Parse the dump to confirm a hang.

Record the source and resolve the issue.

The program is patented, so source code is not shared.

Note: Hang Manager became available only after Oracle 11g R2 (11.2.0.2).

HM Configuration Parameters

Enabling automatic termination of instances or processes requires careful configuration. The article includes a screenshot of the relevant parameters (image retained for illustration).

OraZ Roadmap

The author envisions a more complete tool that monitors database performance, identifies risks, and automates many DBA tasks. Key functional areas include:

Space‑usage trends and fragmentation analysis.

Detailed job execution information.

Flashback and backup usage statistics.

Deep health checks.

Process tracing (10046, 10053) and trace analysis.

Automated optimization recommendations.

Graphical Alert log visualization.

Current OraZ Capabilities

OraZ is a Java application built on JDBC + SSH. It requires only a JDBC connection and installs no additional software on the target server.

Features include:

General database and instance information.

Tablespace, datafile, redo log, and archive log details, including usage statistics.

Memory details (SGA/PGA components, shared pool, buffer cache).

Instance activity insights: CPU consumption, wait events, top sessions, top SQL statements.

Session information: active and high‑priority sessions.

Top SQL statements with execution statistics, resource consumption, and execution plans.

Comprehensive Oracle system statistics, OS metrics, and time model data.

Automated Daily Inspection

Daily checks are scripted to flag anomalies. Example SQL used to detect disabled constraints:

SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = '&OWNER' AND status = 'DISABLED' AND constraint_type = 'P';

The results feed into a “one‑click health check” dashboard (images shown).

Instance Activity Insight

When performance problems arise, DBA may not be present to collect diagnostics. OraZ leverages Oracle’s AAS (Average Active Sessions) metric to quantify system load. AAS is calculated as DB Time / elapsed time and interpreted as:

AAS/CPU_Count ≈ 0 → Idle.

AAS/CPU_Count ≤ 0.5 → No congestion.

AAS/CPU_Count < 1 → Partial saturation (applications may feel slow).

AAS/CPU_Count ≫ 1 → Performance issues or hangs.

Oracle OEM and ASH visualizations (images retained) display top‑consuming sessions and SQL statements. The V$ACTIVE_SESSION_HISTORY view, introduced in Oracle 10g, provides lightweight session‑level wait event data without heavy tracing.

Background Processes

MMON and MMNL processes periodically sample metrics, generate alerts, create snapshot slaves, and capture recent SQL statistics. AWR snapshots run hourly; ASH data is stored in a circular buffer whose size is calculated as MAX(MIN(CPU_Count*2MB, 5% of Shared Pool, 30MB), 1MB), defaulting to about 1 MB.

Future Development Plans

Planned extensions include deeper space‑usage analysis, detailed job monitoring, flashback/backup usage reporting, advanced health checks, process tracing integration, automated optimization, and graphical alert log views.

Selected Q&A

Q: OraZ fails on Windows 2008 R2. A: Avoid Chinese‑character paths; use an English directory such as D:\dbaplus.

Q: Can the program be customized? A: Customization is not currently supported.

Q: Will statistical checks be added? A: Yes, they are planned for the next release.

Q: Where to download the latest OraZ? A: From the DBA+ community website or official WeChat service.

Q: Does OraZ support MySQL? A: MySQL is supported in the one‑click health check; other tools will be added as needed.

The author, Zhou Deyu, is a chief expert at Xinju Network, co‑founder of the DBA+ community, and the architect of the OraZ product, with over ten years of Oracle DBA experience.