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';

OUTPUT
-----------------------------------------------------------------
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.

SQL> select OPERATION, START_TIME, END_TIME, OBJECT_TYPE, STATUS from v$RMAN_STATUS order by START_TIME;

OPERATION START_TIME END_TIME OBJECT_TYPE STATUS
---------------- ----------------------------- ----------------------------- ----- -------------- ----- -----------------------
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.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

3 comments:

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

    thanks
    chandu
    http://www.chandu208.blogspot.com/

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

    Regards
    Satishbabu Gunukula
    http://www.oracleracexpert.com

    ReplyDelete