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.

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

Leave a Reply to Balaji Cancel reply