Audit RMAN Backup Job History with V$RMAN_BACKUP_JOB_DETAILS

calendar Sep 22, 2026 / · 11 min read · RMAN Reporting RMAN Backup History RMAN Backup Job Monitoring Backup Auditing V$ Dynamic Views v$rman_backup_job_details v$rman_output  ·
Share on: linkedin copy

Audit RMAN Backup Job History with V$RMAN_BACKUP_JOB_DETAILS

Purpose

V$RMAN_BACKUP_JOB_DETAILS summarizes RMAN backup work at the job level. Oracle describes it as the view that "displays details about backup jobs." One row covers one job: when it started, when it ended, how much input it read, how much output it wrote, and its status. That summary suits a recurring health check, because the question is usually "did last night's job run clean, and how does it compare to the job before it?" and not whether one datafile backed up correctly.

A job's row carries three columns, SESSION_KEY, SESSION_RECID, and SESSION_STAMP. Oracle documents them as the columns that "together" identify the job's output in V$RMAN_OUTPUT, the view that holds the text RMAN printed. The STATUS column has six documented values, covering jobs that are still running and jobs that have finished. A query built to catch trouble must account for more than FAILED.

INPUT_TYPE labels the kind of job: a full database backup, an incremental, an archived-log backup, and others. When a job's command does not fit one label exactly, Oracle documents that the label is chosen by a fixed order of preference. This post gives eight queries for recent history, failed or slow jobs, throughput, compression, autobackup, the raw RMAN log of one job, and container scoping in a multitenant database.

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

Run this first on any instance. It returns the backup jobs the view holds, newest first. The _DISPLAY columns do the unit conversion, so TIME_TAKEN_DISPLAY is a string in the <nn>h:<nn>m:<nn>s format and INPUT_BYTES_DISPLAY is a size such as 42G instead of a raw byte count. It is a quick way to see whether last night's job ran and roughly how long it took.

Query 2: isolating trouble

Filtering on STATUS != 'COMPLETED' returns every row that is not a plain clean finish. That is FAILED, COMPLETED WITH WARNINGS, COMPLETED WITH ERRORS, and the two RUNNING WITH... values. A check that tests only STATUS = 'FAILED' misses the other five values, four of which report a warning or an error.

Query 3: the slow-job leaderboard

Sorting on ELAPSED_SECONDS shows the jobs worth a look even when STATUS says COMPLETED. A job that finished but took far longer than usual is a sign that something changed. Possible causes include data growth, busier storage, or a loaded backup destination, and the query alone does not tell you which. FETCH FIRST 10 ROWS ONLY keeps the output short 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 the read rate and the write rate of the job. COMPRESSION_RATIO is documented only as "Compression ratio," so compare the value against a job whose input and output sizes you know before you rely on its direction. Reading these columns for the same kind of job over several weeks shows whether the rates are drifting.

Query 5: autobackup confirmation

AUTOBACKUP_DONE is a YES or NO value that tells you whether a control file autobackup was done as part of the job. AUTOBACKUP_COUNT is the number of autobackups the job performed. This checks job by job that the control file backup is happening, instead of assuming it from a configuration setting.

Query 6: pulling the raw log for one job

The query joins V$RMAN_OUTPUT to the job row on all three session columns. V$RMAN_OUTPUT also has SESSION_KEY, SESSION_RECID, and SESSION_STAMP, and its OUTPUT column holds the text RMAN reported. Its RECID column is the record ID of the matching V$RMAN_STATUS row, so ordering by it keeps the lines of one status row together. Replace 145 with the SESSION_KEY from Query 1 or Query 2.

Query 7: trend counts by type and outcome

Grouping by INPUT_TYPE and STATUS over a rolling window turns job rows into a trend. It shows how many full backups ran clean this month and how many archived-log backups ended with warnings. A weekly or monthly backup report can be built from this shape of query.

Query 8: multitenant scoping with CON_ID

CON_ID is the container the row belongs to. Oracle documents 0 for rows about the entire CDB or for a non-CDB, 1 for rows about the root only, and any other number n for the applicable container ID. Filtering on one CON_ID isolates the rows for that container. The value 3 in the query is an example; look up the container ID in your own CDB.

Key Points

  • STATUS has six documented values. They are RUNNING WITH WARNINGS, RUNNING WITH ERRORS, COMPLETED, COMPLETED WITH WARNINGS, COMPLETED WITH ERRORS, and FAILED. A filter on FAILED alone passes over the other five.
  • INPUT_TYPE follows a preference order. If the user command does not fit one label, the documented order is DB FULL, RECVR AREA, DB INCR, DATAFILE FULL, DATAFILE INCR, ARCHIVELOG, CONTROLFILE, SPFILE.
  • OUTPUT_DEVICE_TYPE can be DISK, SBT, or *. Oracle states that * indicates more than one device, and that in most cases the value will be DISK or SBT.
  • Use all three session columns for the V$RMAN_OUTPUT join. Oracle documents SESSION_KEY, SESSION_RECID, and SESSION_STAMP together as the identifier of a job's output, so the query joins on all three.
  • The _DISPLAY columns are for reading. They are VARCHAR2 strings converted to a format such as nM, nG, nT, or nP, and a time in h:m:s format. For a threshold or a trend calculation, use the numeric columns INPUT_BYTES, OUTPUT_BYTES, ELAPSED_SECONDS, INPUT_BYTES_PER_SEC, and OUTPUT_BYTES_PER_SEC.
  • CON_ID matters in a multitenant database. 0 is the whole CDB or a non-CDB, 1 is the root only, and any other value is a specific container.
  • BACKED_BY_OSB is about Oracle Secure Backup. YES means the backup was done to Oracle Secure Backup. Otherwise, Oracle says, the backup went to another third-party tape library.
  • The CON_ID column is part of the current reference. It appears in the Database Reference for release 19c and for release 26. Check the reference for your own release before you rely on it on an older version.

Insights and Best Practices

Build the failed and slow checks first

Two checks give the most signal for the least work. One is STATUS outside COMPLETED. The other is ELAPSED_SECONDS far outside the normal range for that kind of job. Both come from Query 2 and Query 3. A dashboard with throughput, compression, and autobackup columns can follow once those two checks run on a schedule.

Use the numeric columns for any threshold

The _DISPLAY columns are strings made for people. A script that compares a job's time with a limit, or charts throughput over several weeks, should read ELAPSED_SECONDS, INPUT_BYTES, OUTPUT_BYTES, INPUT_BYTES_PER_SEC, and OUTPUT_BYTES_PER_SEC. Those numeric columns belong in a WHERE clause or a GROUP BY aggregate.

Join to V$RMAN_OUTPUT only for a specific job

Query 6 returns every line RMAN printed for the job, so use it after the summary has pointed at a failed or slow SESSION_KEY. It is not a good default for routine polling. The summary view answers "is there a problem," and the output join answers "what did RMAN print for this job."

Know the neighbouring views

V$RMAN_STATUS is also a job-level view. Oracle describes it as displaying "the finished and on-going RMAN jobs," with progress and status for the jobs still running. It covers RMAN jobs in general, while V$RMAN_BACKUP_JOB_DETAILS is the view for backup jobs, with job totals such as INPUT_BYTES, OUTPUT_BYTES, and COMPRESSION_RATIO. Oracle also notes that in-progress jobs in V$RMAN_STATUS are kept only in memory, while finished jobs are stored in the control file. If you need to watch a job that is running now, start from V$RMAN_STATUS.

SQL queries and the RMAN REPORT command answer different questions

RMAN has a REPORT command that you run from the RMAN client. REPORT NEED BACKUP lists the data files that need a new backup, and REPORT OBSOLETE lists full backups, data file copies, and archived redo log files in the repository that can be deleted because they are no longer needed. Those commands answer questions about current backup coverage. V$RMAN_BACKUP_JOB_DETAILS answers a different question, which is how past jobs ran. It is a plain SQL view, so any tool that already queries the database can read it without an RMAN client session.

Set a job-count expectation, then watch for gaps

Query 7 also works in the other direction. If a nightly ARCHIVELOG job is expected every day and the 30-day count is lower than expected, a job did not run, even when every job that did run shows COMPLETED. A job that never started does not appear as a failure in this view. It appears as a missing row, so only a count against an expected schedule will show it.

When to Use This Check

  • Building a daily or weekly RMAN health check that flags failed, warning, or slow jobs.
  • Looking into a backup window that ran long, before deciding whether storage, network, or data growth is the cause.
  • Confirming that control file autobackups happen job by job.
  • Tracking throughput and compression ratio over weeks or months for the same kind of job.
  • Reviewing the backup job history of one container in a multitenant database.
  • Pulling the RMAN log text of one failed job once the summary has identified its SESSION_KEY.

Troubleshooting Common Issues

A job shows RUNNING WITH ERRORS for a long time. The name of this value, and of RUNNING WITH WARNINGS, shows that the job had not finished when the row was read. Query the view again later. If the same row never moves to one of the four finished values, check the RMAN session itself, and read its text with Query 6 and the V$RMAN_STATUS view.

The join to V$RMAN_OUTPUT in Query 6 returns no rows. Confirm that all three of SESSION_KEY, SESSION_RECID, and SESSION_STAMP are in the join, as Oracle documents them as the combined identifier. Then confirm the SESSION_KEY you typed is one that Query 1 returned.

OUTPUT_DEVICE_TYPE shows *. This is documented behaviour and not an error. It means the job used more than one device.

A job's INPUT_TYPE does not match the command that was run. The column takes one label from the fixed list, chosen by the preference order in the Key Points, so a job can show a label that differs from the command text you remember.

Expected rows are missing in a multitenant database. Check which container you are connected to and which CON_ID value the query filters on. Rows with a different CON_ID do not appear in a query that filters on one value.

References

Posts in this series