Article

Catching Oracle Problems Before They Become Outages

1 November 2026

A threshold alarm answers one question: is this number above that number right now? Most Oracle outages need better questions. When will this run out? Is this normal for a Tuesday at 02:00? Did the work grow, or did each unit of work get slower?

We built Radar Engine, the detection layer of Scops, to ask those questions across 75+ detectors. It uses statistics and rules, not an AI model. This article explains how it decides, using its real defaults. The numbers are our choices, not Oracle recommendations, and you can use the same ideas with your own scripts.

Three clocks, because reads have a cost

A detector is only as good as the data it can afford to read, so Radar runs on three clocks.

A daily scan that hits its limit twice in a day stops until the next day. A monitor must never become the incident, and a slow dictionary on a sick database is exactly when it could.

Countdowns: the horizon has to fit the failure

Anything that runs out gets a date, not a percentage. But a single warning window does not suit every resource, so Radar uses two sets.

Slow resources, such as tablespaces, ASM disk groups, datafile space, PDB storage against MAX_PDB_STORAGE, and CPU, use horizons of 14, 45 and 90 days. There is time to order disks. Fast ones, such as the fast recovery area, processes, sessions, db_files and user quotas, use 2, 7 and 21 days.

Some countdowns need no regression, only arithmetic at the current rate:

Two details matter more than the formula. A forecast only starts after three days of hourly readings, because a line through a few points means little. And some resources need a shape, not a level: sessions of one module rising for days is reported as a connection leak, and open cursors rising while sessions do not is reported as a cursor leak. Both look harmless at any single reading.

The fast recovery area shows why rules combine. It goes Critical at 80% if it will be full within 6 hours, or at 92% regardless. A peak that repeats on earlier days stays at Watch. An archive log backup with no progress while the area fills is Critical, because the two facts together mean something neither means alone.

Pile-ups: one number is not a finding

Fast problems have no trend to fit, so live detectors compare with the recent past and then ask for a second sign.

Wait surges. A wait class at twice the hour before, and well above its usual spread, is only Watch. With a second sign, such as 5 blocked sessions or commits three times slower, it is Warning. It goes Critical above max(4, 4 × usual) active sessions, with an effect you can see. The max stops a quiet database from alarming on small numbers.

Blocking. 10 sessions blocked for 3 minutes is Warning. 30 sessions, or 10 for 5 minutes, is Critical. Short lock waits are normal, so time is part of the rule. Throughput has its own check: executions down to 10% of normal for 5 minutes, or 30 sessions behind one blocker.

Waits with no innocent reading. log file switch (archiving needed) fires at just 0.3 active sessions, because redo is now waiting for the archiver and ORA-00257 may be next. log file switch (checkpoint incomplete), free buffer waits and write complete waits start at 1.

Latency against load. Slow reads start at 9 ms, well above usual, in 3 of 10 minutes. Critical needs five times the usual latency without twice the reads. That last clause is the point: if reads doubled too, the workload is the first suspect, and if they did not, look at the storage. Slow commits go Critical at eight times the usual and 50 ms or more, and the check runs only at 5 commits a second or more, because a few commits tell you little.

CPU that is not yours. Host CPU at 85% or more, with less than half of it from the database, is its own finding. Tuning SQL will not help when a backup agent is eating the machine.

SQL. A new plan alone is Warning. It goes Critical only when the statement is three times slower and adds a full active session. A SQL finding also needs 15 minutes of database time an hour from application SQL, so a slow query that nobody feels stays quiet.

You can check the core of this yourself from GV$SESSION:

SELECT final_blocking_instance, final_blocking_session,
       COUNT(*) AS blocked,
       ROUND(MAX(wait_time_micro) / 1e6) AS longest_s
FROM   gv$session
WHERE  final_blocking_session IS NOT NULL
GROUP  BY final_blocking_instance, final_blocking_session
ORDER  BY blocked DESC;

SELECT event, COUNT(*) AS sessions
FROM   gv$session
WHERE  state = 'WAITING'
AND    event IN ('log file switch (archiving needed)',
                 'log file switch (checkpoint incomplete)',
                 'free buffer waits', 'write complete waits')
GROUP  BY event;

And the limits, in the root of a container database:

SELECT resource_name, current_utilization, max_utilization, limit_value,
       ROUND(current_utilization / TO_NUMBER(limit_value) * 100) AS pct_now
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions')
AND    TRIM(limit_value) <> 'UNLIMITED';

Learning what is usual

A nightly backup pushes I/O waits up every night. An alarm for that gets ignored, and then the real one gets ignored too.

Radar learns the usual level of each hour, with weekdays and weekends apart. That takes about 10 days of history for weekdays and three weeks for weekends. A spike must clear the usual band by a threshold K, 3.5 by default, and a lasting change of level has its own threshold of 3. A repeat spike must be 1.5 times larger to count again.

If a finding returns at the same hour on two of the last seven days, it drops to Watch and its title says usual at this hour. A schedule is not an incident. Two exceptions keep this honest. An episode ten times larger than all earlier ones keeps its level, and a log switch waiting for archiving is never lowered, however regular it is. A stuck archiver at the same time every night is still a stuck archiver.

Flapping and fatigue

Severity moves slowly on purpose. A live finding is Warning the first time and becomes Critical only after two cycles in a row. It goes down only after two cycles at the lower level. A finding that returns within 25 minutes continues the same episode. A risk that comes and goes five times in a week drops to Watch.

Mail has its own rules. A finding must still exist after 5 minutes to be mailed, except urgent Criticals such as a blocking chain or full temp, which go at once. An open finding repeats at most every 24 hours, unless it gets worse. Hourly anomalies send one mail at most for each database. When a user rates a finding a false alarm, that database mails it only at Critical for 30 days.

A finding should say why, or say it does not know

Radar grades its own explanation:

This feeds severity. An hourly wait anomaly goes Critical only with a named cause, high for 3 hours, while blocking, stalled executions or slow reads are open. Otherwise it stops at Warning. A spike nobody can explain is worth a look, not a phone call at night.

The last two grades are the honest ones. The same goes for closing: a finding closed by a restart of Scops is marked as such, because that is not a recovery. A finding with no reading for two days closes as no data, not as cleared.

Tuning without guessing

Thresholds are opinions, so they have to be testable. Test on History runs the checks over the last 3 to 30 days of one database with the settings in the dialog, saved or not, and sends nothing. Compare K tries spike thresholds from 2.5 to 5, so you can see the trade before you make it. Operations checks are not part of the test.

Trying it

Radar Engine is part of Scops, an Oracle monitoring platform that needs no agents on database servers and no Diagnostics Pack for monitoring. The detector reference lists every detector and threshold. It is included in the free Community edition for up to 2 databases, and the live demo shows it on made-up data.

Sources

• Scops docs: How Radar works, Detector reference
• Oracle: ORA-00257
• Oracle: V$SESSION