RMAN

Get size of failed backup per database for 1 month

SET LINESIZE 1000

SET PAGESIZE 1000

SELECT DB_NAME,SUM(OUTPUT_BYTES/1024/1024) SIZE_IN_MB  FROM  RC_BACKUP_SET_DETAILS A WHERE (SESSION_KEY IN (SELECT B.SESSION_KEY FROM RC_RMAN_BACKUP_JOB_DETAILS B WHERE STATUS = 'FAILED' AND B.START_TIME > (SYSDATE - 30) AND B.INPUT_TYPE='DB FULL')) GROUP BY A.DB_NAME;

Get size of failed backup per database per backupset for 1 month

SET LINESIZE 1000

SET PAGESIZE 1000

SELECT DB_NAME,BS_KEY,SUM(OUTPUT_BYTES/1024/1024) SIZE_IN_MB FROM  RC_BACKUP_SET_DETAILS A WHERE (SESSION_KEY IN (SELECT B.SESSION_KEY FROM RC_RMAN_BACKUP_JOB_DETAILS B WHERE STATUS = 'FAILED' AND B.START_TIME > (SYSDATE - 30) AND B.INPUT_TYPE='DB FULL')) GROUP BY A.DB_NAME,A.BS_KEY;

Get total size of failed backups per database

SET LINESIZE 1000

SET PAGESIZE 1000

SELECT DB_NAME,SUM(OUTPUT_BYTES/1024/1024) SIZE_IN_MB  FROM  RC_BACKUP_SET_DETAILS A WHERE (SESSION_KEY IN (SELECT B.SESSION_KEY FROM RC_RMAN_BACKUP_JOB_DETAILS B WHERE STATUS = 'FAILED' AND B.INPUT_TYPE='DB FULL')) GROUP BY A.DB_NAME;

Get total size of failed backup 

SET LINESIZE 1000

SET PAGESIZE 1000

SELECT SUM(OUTPUT_BYTES/1024/1024) SIZE_IN_MB  FROM  RC_BACKUP_SET_DETAILS A WHERE (SESSION_KEY IN (SELECT B.SESSION_KEY FROM RC_RMAN_BACKUP_JOB_DETAILS B WHERE STATUS = 'FAILED' AND B.INPUT_TYPE='DB FULL'));

SET PAGESIZE 1000

SET LINESIZE 120

COLUMN RECID FORMAT 99999

COL HANDLE FORMAT A50

COL COMMENTS FORMAT A50

WITH REC AS (SELECT RECID FROM RC_BACKUP_SET_DETAILS WHERE SESSION_KEY IN (SELECT B.SESSION_KEY FROM RC_RMAN_BACKUP_JOB_DETAILS B WHERE STATUS = 'FAILED' AND B.START_TIME > (SYSDATE - 30) AND B.INPUT_TYPE='DB FULL')) SELECT RECID,HANDLE,COMMENTS FROM RC_BACKUP_PIECE_DETAILS WHERE RECID IN (SELECT RECID FROM REC)