69 lines
1.6 KiB
SQL
69 lines
1.6 KiB
SQL
-- gives an historical overview of the past backups (both backupset and backup copy), for which at least a part exists on disk for all databases
|
|
-- the reported sizes and timings are for the entire (original) set and may not reflect the real status
|
|
--
|
|
-- needs to be executed against the rman catalog schema
|
|
|
|
set linesize 300
|
|
set pages 50000
|
|
|
|
column time_taken_display format a15
|
|
column GB format 999G999D99
|
|
column db_unique_name format a15
|
|
column start_time format a18
|
|
column end_time format a18
|
|
|
|
break on db_unique_name skip 1 on dbid on output_device_type on report
|
|
|
|
compute sum of GB on output_device_type
|
|
compute sum of GB on report
|
|
|
|
with
|
|
rman_history as
|
|
( select
|
|
distinct
|
|
rs.session_key,
|
|
rs.db_key,
|
|
rs.site_key
|
|
from
|
|
rc_rman_status rs
|
|
)
|
|
select
|
|
site.db_unique_name,
|
|
db.dbid,
|
|
jobd.session_key,
|
|
jobd.output_device_type,
|
|
to_char(jobd.start_time,'DD/MM/YYYY HH24:MI') start_time ,
|
|
to_char(jobd.end_time,'DD/MM/YYYY HH24:MI') end_time,
|
|
jobd.time_taken_display,
|
|
jobd.input_type,
|
|
jobd.status,
|
|
(jobd.output_bytes/1024/1024/1024) GB
|
|
from
|
|
rman_history rh,
|
|
rc_site site,
|
|
rc_database db,
|
|
rc_rman_backup_job_details jobd
|
|
where
|
|
site.site_key = rh.site_key
|
|
and db.db_key = rh.db_key
|
|
and rh.session_key = jobd.session_key
|
|
and jobd.session_key in
|
|
( select
|
|
session_key
|
|
from
|
|
rc_backup_set_details
|
|
union all
|
|
select
|
|
session_key
|
|
from
|
|
rc_backup_copy_details
|
|
)
|
|
order by
|
|
site.db_unique_name,
|
|
jobd.output_device_type,
|
|
jobd.start_time
|
|
;
|
|
|
|
clear breaks;
|
|
clear computes;
|