Thursday, June 30, 2011

Find historical information about Oracle RMAN Jobs

In some cases, the V$ views supply information that is not available through use of the LIST and REPORT commands. You should periodically run the reports to obtain historical information about RMAN jobs. For example, you may want to know how many backup jobs have been issued, the status of each backup job (for example, whether it failed or completed), when a job started and finished, and what type of backup was performed.

The views V$RMAN_BACKUP_JOB_DETAILS and V$RMAN_BACKUP_SUBJOB_DETAILS provide details about RMAN backup jobs.

RMAN Reporting has been enhanced through V$VIEWS from version to version. Backup operations are more transparent and now DBA’s can easily embed queries to these views in SQL scripts, and generate historical reports. These reports help to analyze the issues and provide corrective action plan to prevent further failures.

The V$RMAN_OUTPUT view is an in-memory view and shows the output of all currently running and recently completed RMAN jobs.

The following query shows the output of a RMAN job

SQL> Select OUTPUT from V$RMAN_OUTPUT where SESSION_STAMP='698172913';

connected to target database: PINDBTS (DBID=3952868985)

executing command: SET SNAPSHOT
using target database control file instead of recovery catalog
snapshot control file name set to: /backup/dvtestrac01/rman/pindbts1/snapcf.f
new RMAN configuration parameters are successfully stored

allocated channel: d1
channel d1: sid=311 instance=pindbts1 devtype=DISK

sql statement: alter system archive log current

Starting backup at 21-SEP-09
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 21-SEP-09

---- (Some detail removed for brevity) ----

Finished backup at 21-SEP-09
released channel: d1

95 rows selected.

The V$RMAN_STATUS view shows the status of completed and running RMAN jobs. The running RMAN job information is stored in memory and once the job is completed the information is stored in the control file.

The following query shows the status of completed, completed with errors, failed and running RMAN jobs.


---------------- ----------------------------- ----------------------------- ----- -------------- ----- -----------------------
RMAN 09/20/2009 20:00:06 09/20/2009 20:01:21 COMPLETED
BACKUP 09/20/2009 20:00:09 09/20/2009 20:01:06 DB FULL COMPLETED
BACKUP 09/20/2009 20:01:11 09/20/2009 20:01:21 ARCHIVELOG COMPLETED
RMAN 09/20/2009 20:05:03 09/20/2009 20:05:28 COMPLETED WITH ERRORS
BACKUP 09/20/2009 20:05:16 09/20/2009 20:05:28 ARCHIVELOG FAILED
RMAN 09/20/2009 20:10:15 09/20/2009 20:10:47 RUNNING
BACKUP 09/20/2009 20:10:39 09/20/2009 20:10:47 ARCHIVELOG RUNNING

The V$BACKUP_FILES view simulates the LIST BACKUP and LIST COPY RMAN commands. Also this view provides the information used as the basis of the REPORT OBSOLETE command. Now you can get the exact physical size of the backup set from BS_BYTES column, as the BYTES column shows the size of the physical file.

Satishbabu Gunukula


  1. Hi thanks a lot for very useful info but here what is SESSION_STAMP # in v$RMAN_OUTPUT ?


  2. SESSION_STAMP is a timestamp of the session of a RMAN Job

    Satishbabu Gunukula

  3. The content looks real with valid information. Good Work
    gst login

  4. Manipur 6th, 7th, 8th, 9th , 10th Students Studying for Public Exam 2023 can be Challenging, and Requires a lot of Effort and Practice, Students will have to Master the Subjects. For this Purpose, After Learning the Subject Completely with the help of SCERT 6th, 7th, 8th, 9th , 10th Model Paper 2023, Mizoram 6th Class Question Paper 2023 Students Download Mizoram 6th, 7th, 8th, 9th , 10th important Questions 2023 PDF to Understand the Blueprint 2023 asks in the Public Exam 2023, Students will Find it Helpful to Practice with the help of Manipur Board 6th, 7th, 8th, 9th , 10th Question Paper 2023, All the Students can give Their best in the Examination with Good Preparation.