I do a mix of differential and cumulative incremental backups. Then I report on them with code I found around the web somewhere:
select
to_char(j.start_time, 'Dy hh24:mi:ss') start_time,
decode(j.input_type,'DB INCR',decode(i0,0,'Incr Lvl 1','Incr Lvl 0'),initcap(j.input_type)) backup_type,
j.time_taken_display,
round(j.elapsed_seconds/60,2) elapsed_min,
(j.output_bytes/1024/1024/1024) output_gigabytes,
initcap(j.status) backup_status,
x.cf,
x.i0 + x.i1 dfiles,
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(next_day(sysdate-6,'SUNDAY'))
order by j.start_time
That produces output like this:
| START_TIME BACKUP_TYPE TIME_TAKEN MINS | OUTPUT_GB | STATUS | CF DFILES L OUTPUT_INSTANCE |
------------ ------------- ----------- ------- ------------- ----------- -- ------ - ----------------
Wed 09:20:19 Incr Lvl 1 00:34:27 34.45 116.54296875 Completed 1 13 13
| Thu 00:00:15 Archivelog 00:23:17 23.28 30.15625 | Completed 0 0 2 |
Thu 09:35:31 Incr Lvl 1 00:45:42 45.7 128.427734375 Completed 1 13 11 1
which *almost* meets my requirements.
The problem is that the Wednesday Incremental Level 1 was a differential backup, and the Thursday morning one at 9:35AM was a cumulative incremental. Both are being reported as "incremental" (because they are), but I can't find a flag I can use to spot the difference between cumulative/differential backups. I can't find it exposed in any of the RMAN 'list backup' outputs, nor in any of the V$ views I'm familiar with.
Any one got a hint where to look for it? (Or, even better, some actual code that works?