Checking Database backups are one of the main focus areas of a DBA. Time to time, DBA needs to check database backup status and see if its completed, failed, running etc. Also, DBA must be able to get the backup start time, end time and even the backup size for reference purpose. The below query gives answers to all the backup details in oracle
Query to check database backup status
sql">set linesize 500 col BACKUP_SIZE for a20 SELECT INPUT_TYPE "BACKUP_TYPE", --NVL(INPUT_BYTES/(1024*1024),0)"INPUT_BYTES(MB)", --NVL(OUTPUT_BYTES/(1024*1024),0) "OUTPUT_BYTES(MB)", STATUS, TO_CHAR(START_TIME,'MM/DD/YYYY:hh24:mi:ss') as START_TIME, TO_CHAR(END_TIME,'MM/DD/YYYY:hh24:mi:ss') as END_TIME, TRUNC((ELAPSED_SECONDS/60),2) "ELAPSED_TIME(Min)", --ROUND(COMPRESSION_RATIO,3)"COMPRESSION_RATIO", --ROUND(INPUT_BYTES_PER_SEC/(1024*1024),2) "INPUT_BYTES_PER_SEC(MB)", --ROUND(OUTPUT_BYTES_PER_SEC/(1024*1024),2) "OUTPUT_BYTES_PER_SEC(MB)", --INPUT_BYTES_DISPLAY "INPUT_BYTES_DISPLAY", OUTPUT_BYTES_DISPLAY "BACKUP_SIZE", OUTPUT_DEVICE_TYPE "OUTPUT_DEVICE" --INPUT_BYTES_PER_SEC_DISPLAY "INPUT_BYTES_PER_SEC_DIS", --OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT_BYTES_PER_SEC_DIS" FROM V$RMAN_BACKUP_JOB_DETAILS where start_time > SYSDATE -10 and INPUT_TYPE != 'ARCHIVELOG' ORDER BY END_TIME DESC /
Query to check archive Backup status
In the 3rd last line and INPUT_TYPE != ‘ARCHIVELOG’, just remove ‘!’ to get archivelog backup details
