Query to find RMAN backup status details

Query to find RMAN backup status details


Many times there requirement comes to DBA to check what is rman backup time or backup taking long time. As a DBA you are the responsible for the backup time and it should complete successfully in acceptable time frame.

You can capture the start time and end time from rman backup log as well. But if you want to compare it for last 20 days backup then you have to login to the server where backupfile exists. Need to open the every backup log file then check at the beginning for backup start time and at the end of file for backup end time. This is very tedious and time consuming procedure.

In this below query gives you the all required details of the rman backup performed on the database. Even it shows the input bytes - total database bytes backed up and output bytes - total backup size as well. 

You can also monitor the backup job by this query.

Below query is useful to find rman backup status of oracle database

set line 300
set pages 100

select name, open_mode from v$database;
col TIME_TAKEN_DISPLAY for a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'dd-mm-yyyy:hh24:mi:ss') start_time,
to_char(END_TIME,'dd-mm-yyyy:hh24:mi:ss')   end_time,
time_taken_display,OUTPUT_BYTES/1024/1024 "size in MB"
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

I hope you find it useful. Thanks.