We can check RMAN backup status in sql using the below sql
col STATUS format a9 col hrs format 999.99 select SESSION_KEY,SESSION_RECID,SESSION_STAMP, 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;
This is very helpful when you are operating in a DBCS(Database Cloud Service) environment with automated backup.
The status could be
RUNNING WITH WARNINGS
RUNNING WITH ERRORS
COMPLETED
COMPLETED WITH WARNINGS
COMPLETED WITH ERRORS
FAILED
The input_type determines the type of backup
Contains one of the following values. If the user command does not satisfy one of them, then preference is given in order, from top to bottom of the list.
DB FULL
RECVR AREA
DB INCR
DATAFILE FULL
DATAFILE INCR
ARCHIVELOG
CONTROLFILE
SPFILE
If you want to see the output of a particular backup, use the below query
To get the Backup job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by recid;
Hope you this article on How to check RMAN backup status in SQL and find it useful. Please do provide the feedback
Related Articles
RMAN Backup Commands : Check out the RMAN Backup commands in this post. This is going to be very helpful for the person who is involved in backup and recovery
Oracle RMAN interview questions : Oracle RMAN Interview Questions are must for Oracle DBA’s looking for a change. Oracle Backup and recovery is one of the essential duties of Oracle DBA
oracle dba interview questions for experienced professionals : check out awesome oracle dba interview questions and answers to succeed in any oracle database interviews. This will test your knowledge on various fronts
RMAN-06059 : Check out how to resolve the RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
RMAN-20004 : RMAN-20004 happens when a Database has been cloned and a connection made from the clone to Catalog using rman without changing the DBID of the clone,
How to delete the archive logs in Oracle: Visit this page to find out How to delete the archive logs in Oracle after backup, how to delete the expired archive log
https://docs.oracle.com/cd/B19306_01/backup.102/b14192/bku
How to correct Oracle RMAN-20004 Backup failure
Oracle Recovery Manager :RMAN cheatsheet