How to check RMAN backup status and timings

This script will be run in the target( registered ) database, not in the catalog ( Repository ) database.

Login as sysdba and issue the following script:

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

SQL> col STATUS format a9
SQL> col hrs format 999.99
SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .00
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .01
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

Above script will give you RMAN backup status along with start and stop timing.

OR

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 18        29         1          9115569    19258880   47.33

Above script will give you SID, Total Work, Sofar & % of completion.

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME  HOURS
----------- ------------- ---------- ------------------- ------------------- --------
 137764     DB FULL       COMPLETED  06-06-2017 02:00:32 06-06-2017 04:12:13 2.19
 137770     ARCHIVELOG    COMPLETED  06-06-2017 04:00:29 06-06-2017 04:01:05 .01
 137778     ARCHIVELOG    COMPLETED  06-06-2017 06:00:27 06-06-2017 06:00:35 .00
 137782     ARCHIVELOG    COMPLETED  06-06-2017 08:00:32 06-06-2017 08:03:36 .05
 137786     ARCHIVELOG    COMPLETED  06-06-2017 10:00:30 06-06-2017 10:02:03 .03
 137790     ARCHIVELOG    COMPLETED  06-06-2017 12:00:30 06-06-2017 12:02:34 .03
 137794     ARCHIVELOG    COMPLETED  06-06-2017 14:00:30 06-06-2017 14:02:58 .04
 . .. ...

*****

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have an easy life ahead.

12 thoughts on “How to check RMAN backup status and timings

  1. Hi Jethwa,

    How to find the reason for Rman database backup failure in oracle 11g

    • Hello Balaji,
      Thank you for writing…

      While executing RMAN backup ( whether manually or by scripting with crontab scheduler ) RMAN will generate backup logs, you can verify its backup logs to verify status of RMAN backups.

      Additionally,
      You can query to V$RMAN_STATUS dictionary view for completed job information.
      Or
      For current job information, kindly query to V$RMAN_STATUS dictionary view.

      Ex:
      I have simulated the same scenario by instantiating RMAN backup and interrupt it by Ctrl + C at the same time, Error log will be written on V$RMAN_OUTPUT dictionary view is as follows:

      SQL> select OUTPUT from V$RMAN_OUTPUT;
      OUTPUT
      ————————————————————————————————————————
      connected to target database: ORCL (DBID=1138782461)

      Starting backup at 03-NOV-14
      using target database control file instead of recovery catalog
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=44 device type=DISK
      channel ORA_DISK_1: starting full datafile backup set
      channel ORA_DISK_1: specifying datafile(s) in backup set
      input datafile file number=00001 name=/u02/app/oracle/oradata/orcl/system01.dbf
      input datafile file number=00002 name=/u02/app/oracle/oradata/orcl/sysaux01.dbf
      input datafile file number=00003 name=/u02/app/oracle/oradata/orcl/undotbs01.dbf
      input datafile file number=00005 name=/u02/app/oracle/oradata/orcl/example01.dbf
      input datafile file number=00004 name=/u02/app/oracle/oradata/orcl/users01.dbf
      channel ORA_DISK_1: starting piece 1 at 03-NOV-14
      Finished backup at 03-NOV-14
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03099: job cancelled at user request
      –x–

      Hope this would be clear to you.
      In case any query, feel free to ask, stay tune. 🙂

  2. Using the first query I got the backup details for only last 38 days or so but my requirement is for last 6 months(180 days). Can somebody guide me how to get it.

  3. How to identify failed rman backup job pieces? Is there any command or script to identify failed backup pieces from backup pieces of successfully completed job.

    We have an environment where we are taking rman full,incremental and archivelog backups at regular interval. But some backup jobs get failed after some time and their backup pieces remains there only. Here I want to identify which backup piece belongs to failed backup jobs so that I can clear them, to make room for future backups.

Leave a Reply