Oracle problems

RMAN Backup Failed or Overdue: The Last Good Backup, the Error, and the Fix

3 November 2026

A backup that fails is loud. A backup that quietly stops running is the one that ends careers, because nobody looks until the day a restore is needed. Both are recorded in the control file, where RMAN keeps every job: V$RMAN_BACKUP_JOB_DETAILS has the status, kind and size of each run, V$RMAN_OUTPUT has the messages of the recent ones, and V$ARCHIVED_LOG knows which logs have never been backed up. Four queries answer when the last good backup of each kind ran, what the failed one said, how far the running one has got, and what is exposed right now.

The last good backup of each kind

One row per job, by the kind of backup RMAN recorded. Oracle lists the kinds in the order of preference it gives a job that does several things at once: DB FULL, RECVR AREA, DB INCR, DATAFILE FULL, DATAFILE INCR, ARCHIVELOG, CONTROLFILE, SPFILE:

SELECT input_type,
       MAX(CASE WHEN status IN ('COMPLETED',
                                'COMPLETED WITH WARNINGS')
                THEN end_time END)         AS last_good,
       MAX(end_time)                       AS last_run,
       COUNT(*)                            AS runs
FROM   v$rman_backup_job_details
WHERE  start_time > SYSDATE - 30
GROUP  BY input_type
ORDER  BY last_good NULLS FIRST;

A DB FULL or DB INCR row whose last good run is older than the schedule says is the overdue backup. An ARCHIVELOG row that is hours old on a busy database means the logs are piling up in the fast recovery area. A last run newer than the last good run is a job that fails now. A kind with no row at all has not run in the window, and the window itself is limited: the control file keeps these records for CONTROL_FILE_RECORD_KEEP_TIME days, 7 by default, so a recovery catalog is where the long history lives, in RC_RMAN_BACKUP_JOB_DETAILS.

What the failed run said

V$RMAN_OUTPUT is RMAN's own log, kept in memory for the recent jobs (32,768 lines, gone at the next restart). The error lines of the jobs that failed this week:

SELECT o.session_recid, o.output
FROM   v$rman_output o
WHERE  (o.session_recid, o.session_stamp) IN (
         SELECT session_recid, session_stamp
         FROM   v$rman_backup_job_details
         WHERE  status IN ('FAILED', 'COMPLETED WITH ERRORS')
         AND    start_time > SYSDATE - 7)
AND    (o.output LIKE '%RMAN-%' OR o.output LIKE '%ORA-%')
ORDER  BY o.session_recid, o.recid;

Six errors account for most failed backups:

How far the running one has got

RMAN reports progress in V$SESSION_LONGOPS, one row for the whole job and one per channel, named RMAN: aggregate input and RMAN: full datafile backup or RMAN: incremental datafile backup:

SELECT sid, serial#, opname,
       ROUND(sofar / NULLIF(totalwork, 0) * 100, 1) AS pct,
       ROUND(time_remaining / 60)                    AS min_left,
       ROUND(elapsed_seconds / 60)                   AS min_run,
       last_update_time
FROM   v$session_longops
WHERE  opname LIKE 'RMAN%'
AND    totalwork > 0
AND    sofar < totalwork;

A row whose last_update_time stopped moving is a job that waits rather than works. The channel sessions say on what; RMAN names them in CLIENT_INFO:

SELECT sid, client_info, event, state,
       ROUND(wait_time_micro / 1e6)                  AS wait_s
FROM   v$session
WHERE  client_info LIKE 'rman%'
ORDER  BY sid;

Minutes on a Backup: MML wait are the tape side. Minutes on control file sequential read or an enqueue are a control file that another job holds, often a second RMAN session or a long CROSSCHECK. V$RMAN_STATUS shows which command the job is on right now, BACKUP, DELETE or CROSSCHECK, with STATUS LIKE 'RUNNING%'.

What is exposed

The archived logs that have never been backed up, by thread, with the oldest. Every one of them is redo that exists in one copy:

SELECT thread#,
       MIN(sequence#)                                AS first_seq,
       MAX(sequence#)                                AS last_seq,
       COUNT(*)                                      AS logs,
       ROUND(SUM(blocks * block_size) / POWER(1024, 3), 1) AS gb,
       MIN(completion_time)                          AS oldest
FROM   v$archived_log
WHERE  backup_count = 0
AND    deleted = 'NO'
AND    standby_dest = 'NO'
GROUP  BY thread#;

RMAN itself reports the datafiles that need a backup under the retention policy, the ones that would need more than three days of archived logs to recover, and what a restore would use, without reading a single piece:

RMAN> REPORT NEED BACKUP;
RMAN> REPORT NEED BACKUP DAYS 3;
RMAN> RESTORE DATABASE PREVIEW SUMMARY;
RMAN> CROSSCHECK BACKUP;

The fix, in order

1. Run the archivelog backup now, then the rest. On a busy database the fast recovery area fills while the cause is investigated, and a full FRA stops the database. The archived logs go first, to disk if tape is the problem.
2. Fix what the error said. The table above covers the six usual ones. After RMAN-06059, a new level 0 is part of the fix, not an option: the logs between the last backup and the missing sequence can no longer be applied.
3. Find out why nobody knew. A backup that stopped running has a reason outside Oracle: a scheduler job that was disabled during maintenance, a cron entry on a host that was rebuilt, a script that mails on error and said nothing when it never started. Whatever it was, the thing that should have told you did not, and that is the second fix.
4. Schedule for the rate, not the calendar. A level 0 at the weekend and a level 1 each night, and the archived logs every hour or two on a database that generates them fast, so a missed night costs one night and not the FRA. Control file autobackup on, which Oracle turns on by default from COMPATIBLE 12.2; a retention policy as a recovery window; DELETE OBSOLETE in the script.
5. Prove it restores. RESTORE DATABASE VALIDATE reads every piece a restore would need and writes nothing, so it fits in a weekly job. A real restore to a test host, once a quarter, is the only proof that the tape library, the catalog and the script agree.
6. Count the standby. On a Data Guard pair, an archivelog backup taken on the standby counts, and a primary whose standby is not monitored still needs its own.

See it coming

Radar Engine, the detection layer of Scops, reads the same control file records on every database, learns the usual interval of each backup kind, and reports a backup that is late, failing, stuck or slowing down, without an agent and without a script on the host. A pluggable database shares the backup findings of its container:

The finding names the database, the kind of backup, the last good run and the interval it was measured against, so the first question of the morning, which databases have no backup since when, is already answered on the Daily Overview.

Questions

How do I find the last successful RMAN backup?
Group V$RMAN_BACKUP_JOB_DETAILS by INPUT_TYPE and take the latest END_TIME with a COMPLETED status. The control file keeps these rows for CONTROL_FILE_RECORD_KEEP_TIME days, 7 by default; a recovery catalog keeps them longer.

Why did the RMAN backup fail with ORA-19809?
The fast recovery area is at its limit. Back up and delete archived logs, delete obsolete backups, or raise DB_RECOVERY_FILE_DEST_SIZE, then re-run the backup.

How can I see the progress of a running RMAN backup?
V$SESSION_LONGOPS, rows whose OPNAME starts with RMAN: SOFAR against TOTALWORK, with TIME_REMAINING in seconds. A row whose LAST_UPDATE_TIME stops moving is a job that is stuck, usually on the media manager.

Which archived logs have not been backed up yet?
V$ARCHIVED_LOG with BACKUP_COUNT = 0, DELETED = 'NO' and STANDBY_DEST = 'NO'. Every one of them exists in a single copy until the next archivelog backup.

Related problems

• Fast recovery area full: ORA-00257
• Data Guard standby lag and gap
• ORA-28002: the password will expire
• NOLOGGING with a standby: ORA-26040
• Scheduler job failed, broken or stuck
• Archiver stuck: ORA-16014
• All Oracle problems

Sources

• Oracle: V$RMAN_BACKUP_JOB_DETAILS, V$RMAN_STATUS, V$RMAN_OUTPUT, V$SESSION_LONGOPS, V$ARCHIVED_LOG
• Oracle: REPORT, RESTORE, Reporting on RMAN operations, Configuring the RMAN environment
• Scops docs: Detector reference