Mit folgendem SQL-Statement kann vom RMAN-Repository das Backup-Volumen vergangener Monate gelistet werden:
SELECT TO_CHAR(completion_time, ‚YYYY-MON‘) completion_time, type, round(sum(bytes)/1024/1024/1024/1024) TB
FROM
(
SELECT
CASE
WHEN p.backup_type=’L‘ THEN ‚ARCHIVELOG‘
WHEN p.backup_type=’D‘ THEN ‚ONLINE_FULL‘
WHEN p.backup_type=’I‘ AND p.incremental_level=0 THEN ‚LEVEL0′
WHEN p.backup_type=’I‘ AND p.incremental_level=1 THEN ‚LEVEL1′
END type,
TRUNC(p.completion_time) completion_time,
p.bytes
FROM rc_backup_piece p
WHERE p.status=’A‘
AND p.completion_time > add_months(trunc(sysdate,’mm‘),-2)
and p.completion_time < add_months(trunc(sysdate, ‚mm‘),-1)
UNION ALL
SELECT ‚DATAFILECOPY‘ type, TRUNC(completion_time), output_bytes FROM rc_backup_copy_details
where completion_time > add_months(trunc(sysdate,’mm‘),-2)
and completion_time < add_months(trunc(sysdate, ‚mm‘),-1)
)
group by TO_CHAR(completion_time, ‚YYYY-MON‘), type
order by 1 asc,2,3
;
COMPLETION_TIME TYPE GB HOURS
—————– ———— ———- ———-
2017-JAN ARCHIVELOG 28274 4367
2017-JAN LEVEL0 8068 134
2017-JAN LEVEL1 823 60
2017-JAN ONLINE_FULL 41512 107