I am trying to write a query to fetch the RMAN backup details in catalog database.
1st Query:
--------------
select DB_NAME,STATUS,TO_CHAR(END_TIME,'DAY') END_TIME_DAY,
TO_CHAR(END_TIME,'mm/dd/yy') END_TIME,
TIME_TAKEN_DISPLAY,
ROUND(SUM(OUTPUT_BYTES/1024/1024/1024),2) SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM RMAN.RC_RMAN_BACKUP_JOB_DETAILS
WHERE END_TIME > sysdate - 8
GROUP BY DB_NAME,STATUS,
TO_CHAR(END_TIME,'DAY') ,
TO_CHAR(END_TIME,'mm/dd/yy') ,
TIME_TAKEN_DISPLAY,
OUTPUT_DEVICE_TYPE
ORDER BY DB_NAME,
END_TIME_DAY,
END_TIME;
The above query gives all the backup output. But I need to filter it on Incr level 0 backup.
I wrote a query like below and it is taking long time to fetch details. Need help to tune the query.
select DB_NAME,SESSION_KEY,STATUS,TO_CHAR(END_TIME,'DAY') END_TIME_DAY,
TO_CHAR(END_TIME,'mm/dd/yy') END_TIME,
TIME_TAKEN_DISPLAY,
ROUND(SUM(OUTPUT_BYTES/1024/1024/1024),2) SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM RMAN.RC_RMAN_BACKUP_JOB_DETAILS A
WHERE
END_TIME > sysdate - 8
AND SESSION_KEY in (select distinct bsd.session_key
from RMAN.rc_backup_set bs
join RMAN.rc_database d
on d.dbid = bs.db_id
join RMAN.rc_backup_set_details bsd
on bsd.bs_key = bs.bs_key
where bs.backup_type in ('D','I')
and bs.incremental_level is not null
and bs.incremental_level = 0
and bs.start_time > sysdate -8)
GROUP BY DB_NAME,SESSION_KEY,STATUS,
TO_CHAR(END_TIME,'DAY') ,
TO_CHAR(END_TIME,'mm/dd/yy') ,
TIME_TAKEN_DISPLAY,
OUTPUT_DEVICE_TYPE
ORDER BY DB_NAME,
END_TIME_DAY,
END_TIME;
I want to fetch details for Incr Level 0 , level 1 and archivelog backup separately.