vi select_backups_from_target_DB.sql
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9999999999999999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;
--To Check backup by size
select ctime "Date"
, decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type
, bsize "Size MB",
tag
from (select bp.completion_time ctime
, backup_type
, round(sum(bp.bytes/1024/1024),2) bsize,bp.tag
from gv$backup_set bs, gv$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bp.status = 'A'
group by bp.completion_time, backup_type,tag)
order by 1, 2;
vi select_backup_archivelog_details_from_target_DB.sql
select btype_key,first_change#, thread#, sequence#, THREAD#, SEQUENCE#
from V$BACKUP_ARCHIVELOG_DETAILS
where btype_key in
(select recid from V$BACKUP_PIECE where tag='&tag_backup_piece')
order by first_change# desc;
Running the scrip select_backups_from_target_DB.sql
SQL> @select_backups_from_target_DB.sql
Enter value for number_of_days: 5
old 32: where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
new 32: where j.start_time > trunc(sysdate)-5
SESSION SESSION OUTPUT ELAPSED TIME OUT
RECID STAMP START_TIME END_TIME MBYTES STATUS INPUT_TYPE DOW SECONDS TAKEN CF DF I0 I1 L INST
------- ------------ ------------------- ------------------- ----------------- ---------- ------------- --------- ---------- ---------- ------ ------ ------ ------ ------ -----
1 955306573 2017-09-21 18:57:24 2017-09-21 18:59:56 4625 COMPLETED DATAFILE FULL Wednesday 152 00:02:32 9 0 0 0 0
Date BACKUP_TYPE Size MB TAG
--------- ----------- ---------- --------------------------------
21-SEP-17 Full 17.2 TAG20170921T185733
21-SEP-17 Full 17.2 TAG20170921T185852
21-SEP-17 Full 17.2 TAG20170921T185901
21-SEP-17 Full 17.2 TAG20170921T185911
21-SEP-17 Full 17.2 TAG20170921T185929
21-SEP-17 Full 17.2 TAG20170921T185937
21-SEP-17 Full 17.2 TAG20170921T185942
21-SEP-17 Full 17.2 TAG20170921T185951
21-SEP-17 Full 17.2 TAG20170921T185955
9 rows selected.
Running the scrip @select_backup_archivelog_details_from_target_DB.sql
SQL> @select_backup_archivelog_details_from_target_DB.sql
BTYPE_KEY FIRST_CHANGE# THREAD# SEQUENCE# THREAD# SEQUENCE#
---------- ------------- ---------- ---------- ---------- ----------
3 6732028 1 104 1 104
3 6731930 1 103 1 103
3 6731806 1 102 1 102
3 6731794 1 101 1 101
3 6731265 1 100 1 100
3 6729789 1 99 1 99
6 rows selected.
for Oracle Database Administrators
