Audit RMAN Backup Job History with V$RMAN_BACKUP_JOB_DETAILS

Audit RMAN Backup Job History with V$RMAN_BACKUP_JOB_DETAILS

Purpose

V$RMAN_BACKUP_JOB_DETAILS is the one dynamic view built specifically at the job level rather than the piece or file level. Where other RMAN views report on individual datafile backups or individual backup set pieces, this view collapses an entire backup job into a single row: when it started, when it finished, how much data it read and wrote, and whether it actually completed cleanly. That job-level summary is what a recurring health check actually needs — not "did this one datafile back up correctly," but "did last night's job run clean, and how does it compare to the job before it."

Each row carries a composite identity — SESSION_KEY, SESSION_RECID, and SESSION_STAMP together — that is what uniquely ties a job's summary row back to the detailed, line-by-line RMAN output captured in V$RMAN_OUTPUT. The view's STATUS column is not a simple pass/fail flag; it carries six distinct values covering both in-progress and finished states, so a query built to catch trouble has to account for more than just FAILED. INPUT_TYPE classifies what kind of backup the job actually was — a full database backup, an incremental, an archived-log backup, and several other categories — resolved through a documented preference order when a job's command doesn't map cleanly to one value. And because Oracle has documented this exact view, with this exact column set, all the way back to an 11g Release 2 reference and forward into the current 19c reference, it has been a stable place to build a monitoring query against for a long time.

This post covers the columns that matter for a job-history audit, a set of queries for pulling recent history, isolating failed or slow jobs, checking throughput and compression, and joining out to the raw RMAN log text for a specific job — plus the container-database scoping a multitenant environment needs to get right.

Code

 1-- Query 1: recent backup job history, newest first
 2SELECT session_key, start_time, end_time, status, input_type,
 3       time_taken_display, input_bytes_display, output_bytes_display
 4FROM   v$rman_backup_job_details
 5ORDER  BY start_time DESC;
 6
 7-- Query 2: jobs that did not finish cleanly
 8SELECT session_key, start_time, status, input_type, output_device_type
 9FROM   v$rman_backup_job_details
10WHERE  status != 'COMPLETED'
11ORDER  BY start_time DESC;
12
13-- Query 3: the slowest jobs by elapsed time, regardless of outcome
14SELECT session_key, start_time, input_type, status,
15       elapsed_seconds, time_taken_display
16FROM   v$rman_backup_job_details
17ORDER  BY elapsed_seconds DESC
18FETCH FIRST 10 ROWS ONLY;
19
20-- Query 4: throughput and compression, completed jobs only
21SELECT session_key, start_time, input_type,
22       input_bytes_per_sec_display, output_bytes_per_sec_display,
23       compression_ratio
24FROM   v$rman_backup_job_details
25WHERE  status = 'COMPLETED'
26ORDER  BY start_time DESC;
27
28-- Query 5: confirm whether a control file autobackup ran with the job
29SELECT session_key, start_time, input_type, autobackup_done, autobackup_count
30FROM   v$rman_backup_job_details
31ORDER  BY start_time DESC;
32
33-- Query 6: pull the full RMAN log text for one specific job
34SELECT o.output
35FROM   v$rman_output o, v$rman_backup_job_details j
36WHERE  o.session_key   = j.session_key
37AND    o.session_recid = j.session_recid
38AND    o.session_stamp = j.session_stamp
39AND    j.session_key   = 145
40ORDER  BY o.recid;
41
42-- Query 7: job counts by type and outcome over the last 30 days
43SELECT input_type, status, COUNT(*) AS job_count
44FROM   v$rman_backup_job_details
45WHERE  start_time >= SYSDATE - 30
46GROUP  BY input_type, status
47ORDER  BY input_type, status;
48
49-- Query 8: restrict to one pluggable database's jobs in a multitenant CDB
50SELECT session_key, start_time, status, input_type, con_id
51FROM   v$rman_backup_job_details
52WHERE  con_id = 3
53ORDER  BY start_time DESC;

Code Breakdown

Query 1: the baseline history pull

This is the query to run first on any instance: every backup job the view currently holds, newest first, with the human-readable _DISPLAY columns doing the unit conversion so TIME_TAKEN_DISPLAY reads as 01h:12m:04s and INPUT_BYTES_DISPLAY reads as 42G instead of a raw byte count. It is the fastest way to eyeball whether last night's job ran, and roughly how long it took.

Query 2: isolating trouble

Filtering on STATUS != 'COMPLETED' catches every state that isn't a clean finish — FAILED, COMPLETED WITH WARNINGS, COMPLETED WITH ERRORS, and the two RUNNING WITH... states for a job still in progress that has already logged a problem. A monitoring script built around a single STATUS = 'FAILED' check misses four of those six documented values.

Query 3: the slow-job leaderboard

Sorting on ELAPSED_SECONDS surfaces the jobs worth investigating even when STATUS says COMPLETED — a job that finished successfully but took three times as long as usual is still a signal something changed, whether that's data volume growth, a busier I/O subsystem, or a network-attached backup destination under load. FETCH FIRST 10 ROWS ONLY keeps the output to a reviewable size on an instance with a long job history.

Query 4: throughput and compression together

INPUT_BYTES_PER_SEC_DISPLAY and OUTPUT_BYTES_PER_SEC_DISPLAY report read and write rate for the job, and COMPRESSION_RATIO shows how much smaller the output was than the input. Reading these together over several weeks of jobs is how a gradually degrading backup destination — slower storage, a saturated network link — gets caught before a job outright fails.

Query 5: autobackup confirmation

AUTOBACKUP_DONE is a simple YES/NO flag reporting whether a control file autobackup happened as part of that specific job, and AUTOBACKUP_COUNT reports how many autobackups the job performed. This is the direct way to confirm the control file protection a backup strategy depends on is actually happening job over job, rather than assuming it from a policy setting alone.

Query 6: pulling the raw log for one job

SESSION_KEY, SESSION_RECID, and SESSION_STAMP are documented as the three columns that, together, uniquely identify a job's output in V$RMAN_OUTPUT. None of the three alone is guaranteed unique across the view's history — all three have to match to pull the correct job's log lines rather than a different job that happens to share one of the three values.

Query 7: trend counts by type and outcome

Grouping by INPUT_TYPE and STATUS over a rolling window turns individual job rows into a trend: how many full backups ran clean this month versus how many archived-log backups needed a retry. This is the shape of query a weekly or monthly backup health report is built from, rather than a row-by-row read of every job.

Query 8: multitenant scoping with CON_ID

CON_ID reports which container a row belongs to: 0 for rows spanning the entire CDB (or for a non-CDB database), 1 for the root only, and any other number for a specific pluggable database's own container ID. Filtering on a specific CON_ID is how a script isolates one PDB's backup job history in an environment where several PDBs' jobs are all landing in the same view.

Key Points

• STATUS has six documented values, not two. A filter looking only for FAILED silently passes over RUNNING WITH ERRORS, RUNNING WITH WARNINGS, COMPLETED WITH WARNINGS, and COMPLETED WITH ERRORS.
• INPUT_TYPE resolves through a preference order, not a direct one-to-one mapping to the RMAN command that was run — the documented order runs DB FULL, RECVR AREA, DB INCR, DATAFILE FULL, DATAFILE INCR, ARCHIVELOG, CONTROLFILE, SPFILE.
• OUTPUT_DEVICE_TYPE of * means more than one destination. DISK and SBT are the two named values; an asterisk signals the job wrote to more than one device type, most commonly disk and tape together.
• The three-column session key is required for the V$RMAN_OUTPUT join. SESSION_KEY, SESSION_RECID, and SESSION_STAMP together, not any one alone, uniquely identify a job's log output.
• The _DISPLAY columns are pre-formatted for reading, not for math. They convert raw byte and second counts into nM/nG/nT and h:m:s strings — the underlying INPUT_BYTES, OUTPUT_BYTES, and ELAPSED_SECONDS columns are what a trend calculation or a threshold comparison should use instead.
• CON_ID scoping matters in a multitenant database. 0 covers the whole CDB or a non-CDB database, 1 is root-only, and any other value is a specific PDB's container.
• BACKED_BY_OSB is specific to Oracle Secure Backup. A YES here means the job used Oracle's own tape backup product; a job backed up by a different third-party tape library reports something other than YES in that column.

Insights and Best Practices

Build the failed/slow check first, before a dashboard

Of everything this view reports, the two things worth alerting on immediately are STATUS outside COMPLETED and ELAPSED_SECONDS well outside a job's normal range. A dashboard covering throughput, compression, and autobackup confirmation is genuinely useful, but the pass/fail and slow/normal checks are the ones that catch an actual operational problem, and they're cheap to build from Query 2 and Query 3 alone.

Use raw columns for any threshold or trend calculation

Because the _DISPLAY columns are strings formatted for a human to read, they're the wrong source for a script that needs to compare a job's elapsed time against a numeric threshold, or chart throughput over several weeks. ELAPSED_SECONDS, INPUT_BYTES, OUTPUT_BYTES, INPUT_BYTES_PER_SEC, and OUTPUT_BYTES_PER_SEC are the numeric columns behind each of the display equivalents, and they're what belongs in a WHERE clause or a GROUP BY aggregate.

Reach for V$RMAN_OUTPUT only when investigating a specific job

Joining out to V$RMAN_OUTPUT for the full line-by-line log, as in Query 6, is the right move once a job in V$RMAN_BACKUP_JOB_DETAILS has already flagged as failed or unusually slow and the actual error text or step-by-step timing is needed. It is not the right default for routine polling — the job-summary view answers "is there a problem" cheaply; the output join answers "what exactly happened," and that's a heavier, more detailed read that only pays off once the summary has already pointed at a specific SESSION_KEY.

SQL queries and RMAN's own REPORT command answer different questions

RMAN ships its own REPORT and LIST commands, run from the RMAN client rather than SQL, for tasks like flagging files that currently need a backup or objects that have become obsolete under the retention policy. Those commands are the right tool when the question is about current backup coverage. V$RMAN_BACKUP_JOB_DETAILS answers a different question — the historical record of how jobs actually ran, over time — and its advantage is that it's a plain SQL view, so it plugs directly into whatever monitoring, reporting, or dashboarding tool already queries the database, without needing a separate RMAN client session.

Set a job-count expectation, then watch for gaps

Query 7's grouped counts are also useful the other direction: if a nightly ARCHIVELOG backup job is expected every day and the 30-day count comes back lower than expected, that's a missed-job signal worth chasing even when every job that did run shows COMPLETED. A job that never ran at all doesn't show up as a failure in this view — it shows up as a gap in the count.

When to Use This

• Building a daily or weekly RMAN health check that flags failed, warning, or unusually slow jobs automatically.
• Investigating a specific backup window that ran long, before deciding whether storage, network, or data-volume growth is the cause.
• Confirming that control file autobackups are actually happening job over job, not just assumed from a configuration setting.
• Trending backup throughput and compression ratio over weeks or months to catch a gradually degrading backup destination early.
• Auditing one pluggable database's backup job history separately from its siblings in a multitenant environment.
• Pulling the exact RMAN log text for a specific failed job, once the summary view has identified which SESSION_KEY to investigate.

Troubleshooting Common Issues

A job shows RUNNING WITH ERRORS and never seems to move to a finished state. Check the row again after some time has passed — this and RUNNING WITH WARNINGS are in-progress states, not terminal ones. If the row genuinely never resolves to one of the four finished STATUS values, that points at a job that's stuck rather than one that's merely slow.

The join to V$RMAN_OUTPUT in Query 6 returns no rows. Confirm all three of SESSION_KEY, SESSION_RECID, and SESSION_STAMP are being matched together — a query joining on SESSION_KEY alone can silently pull the wrong job's output, or none at all, if the other two columns don't also line up.

OUTPUT_DEVICE_TYPE shows * and it's not clear why. This is documented behavior, not an error — it means the job wrote to more than one device type in the same run, most commonly disk plus tape.

A job's INPUT_TYPE doesn't match what was actually run. This column is resolved through a documented preference order rather than a direct mapping, so a job that doesn't cleanly satisfy one specific type falls back through the list in order: DB FULL, RECVR AREA, DB INCR, DATAFILE FULL, DATAFILE INCR, ARCHIVELOG, CONTROLFILE, SPFILE.

Expected rows are missing in a multitenant database. Check CON_ID scoping first — a query run from the wrong container, or one that filters on the wrong CON_ID value, will appear to be missing jobs that are actually present under a different container ID.

References

• V$RMAN_BACKUP_JOB_DETAILS — Database Reference 19c - canonical column reference for the current release, including the documented STATUS values and the INPUT_TYPE preference order
• V$RMAN_BACKUP_JOB_DETAILS — Database Reference 11g Release 2 - confirms the same column set was already documented in 11g Release 2, well before the current release
• Reporting on RMAN Operations - further reading on building operational reports against RMAN's dynamic performance views