RMAN backup history script

set feedback     off
set verify       off
set serveroutput on size 1000000

column db    new_value _DB noprint;
column dbkey new_value _DBKEY noprint;

select 'rman'||'&&1' db from dual;
select dbinc_key dbkey from &_DB..rc_database;


/* cleanup work if you see duplicate entries due to multiple incarnations */
--delete from &_DB..dbinc where dbinc_key != (select dbinc_key from &_DB..rc_database);
--delete from &_DB..dfatt where dbinc_key != (select dbinc_key from &_DB..rc_database);
--delete from &_DB..bdf   where dbinc_key != (select dbinc_key from &_DB..rc_database);
--delete from &_DB..df    where dbinc_key != (select dbinc_key from &_DB..rc_database);
--delete from &_DB..al    where dbinc_key != (select dbinc_key from &_DB..rc_database);
--delete from &_DB..tsatt where dbinc_key != (select dbinc_key from &_DB..rc_database);
--delete from &_DB..ts    where dbinc_key != (select dbinc_key from &_DB..rc_database);


-- was forced to create a new table due to the index on the 
-- original table cause invalid packages when dropping

create table &_DB.BDF nologging as select file#, COMPLETION_TIME, create_scn from &_DB..BDF where DBINC_KEY = &_DBKEY;
--prompt Table Created

create index &_db._idx on &_DB.BDF(file#,COMPLETION_TIME);
--prompt Index Created


declare

 dte date := sysdate-0;  /* start day:  0 = today */

 d0 varchar2(10);
 d1 varchar2(10);
 d2 varchar2(10);
 d3 varchar2(10);
 d4 varchar2(10);
 d5 varchar2(10);
 d6 varchar2(10);

 t0 varchar2(10);
 t1 varchar2(10);
 t2 varchar2(10);
 t3 varchar2(10);
 t4 varchar2(10);
 t5 varchar2(10);
 t6 varchar2(10);

 c0 number;
 c1 number;
 c2 number;
 c3 number;
 c4 number;
 c5 number;
 c6 number;

 b0 number := 0;
 b1 number := 0;
 b2 number := 0;
 b3 number := 0;
 b4 number := 0;
 b5 number := 0;
 b6 number := 0;

 rows number := 0;

 ct  date;   
 mct date := sysdate+100;   
 min_complete date;

 dbinc number := 0;

begin

  select 
    to_char(dte-0, 'DY'),
    to_char(dte-1, 'DY'), 
    to_char(dte-2, 'DY'), 
    to_char(dte-3, 'DY'), 
    to_char(dte-4, 'DY'), 
    to_char(dte-5, 'DY'), 
    to_char(dte-6, 'DY') into t0, t1, t2,t3,t4,t5,t6 
  from dual;

  sys.dbms_output.put_line('.                           '||to_char(dte,'dd')||'   '||to_char(dte-1,'dd')||'   '||to_char(dte-2,'dd')||'   '||to_char(dte-3,'dd')||'   '||to_char(dte-4,'dd')||'   '||to_char(dte-5,'dd')||'   '||to_char(dte-6,'dd'));
  sys.dbms_output.put_line('Filename                  '||' '||t0||'  '||t1||'  '||t2||'  '||t3||'  '||t4||'  '||t5||'  '||t6||'  Tot   Last Bkup');
  sys.dbms_output.put_line('-------------------------- ---  ---  ---  ---  ---  ---  ---  ---  -----------');
  
  for r in (select dfatt.file#, substr(dfatt.fname,instr(dfatt.fname,'/',-1)+1) fname, dfatt.CREATE_SCN 
            from &_DB..dfatt, &_DB..df, &_DB..ts
            where END_CKP_KEY is null
            and START_CKP_KEY = (select max(df2.START_CKP_KEY) 
                                 from &_DB..dfatt df2 
                                 where df2.file# = dfatt.file#
                                 and &_DB..dfatt.DBINC_KEY = &_DBKEY)
            and dfatt.file# = df.file#
            and df.ts# = ts.ts#
            and ts.TS_NAME != 'TEMP'  /* exclude TEMP */
            and ts.DROP_SCN is null
            and df.drop_time is null
            and &_DB..dfatt.DBINC_KEY = &_DBKEY
            and &_DB..df.DBINC_KEY= &_DBKEY
            and &_DB..ts.DBINC_KEY= &_DBKEY
            order by TS_NAME,fname)
  loop
    
    -- get the min completion time to see if the datafile was added recently
    -- if datafile was added today there will be no completion_time so need to use sysdate+1
    select NVL(min(COMPLETION_TIME), SYSDATE+1) into min_complete 
    from &_DB.BDF 
    where r.file# = file# 
    and create_scn = r.create_scn;

    select decode(count(*),0,'  NO ',' YES '), count(*) into d0, c0 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-0) and trunc(dte+1) and rownum = 1;
    select decode(count(*),0,'  NO ',' YES '), count(*) into d1, c1 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-1) and trunc(dte+0) and rownum = 1;
    select decode(count(*),0,'  NO ',' YES '), count(*) into d2, c2 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-2) and trunc(dte-1) and rownum = 1;
    select decode(count(*),0,'  NO ',' YES '), count(*) into d3, c3 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-3) and trunc(dte-2) and rownum = 1;
    select decode(count(*),0,'  NO ',' YES '), count(*) into d4, c4 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-4) and trunc(dte-3) and rownum = 1;
    select decode(count(*),0,'  NO ',' YES '), count(*) into d5, c5 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-5) and trunc(dte-4) and rownum = 1;
    select decode(count(*),0,'  NO ',' YES '), count(*) into d6, c6 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-6) and trunc(dte-5) and rownum = 1;


    if c1 = 0 then
      if min_complete > dte-2 then 
         d1 := '  -  ';
      end if;
    end if;    
      if c2 = 0 then
      if min_complete > dte-2 then 
         d2 := '  -  ';
      end if;
    end if;    
    if c3 = 0 then
      if min_complete > dte-3 then 
         d3 := '  -  ';
      end if;
    end if;    
    if c4 = 0 then
      if min_complete > dte-4 then 
         d4 := '  -  ';
      end if;
    end if;    
    if c5 = 0 then
      if min_complete > dte-5 then 
         d5 := '  -  ';
      end if;
    end if;    
    if c6 = 0 then
      if min_complete > dte-6 then 
         d6 := '  -  ';
      end if;
    end if;    

 
    select max(completion_time) into ct from &_DB.BDF where r.file# = file#;

    if (ct < mct) then
       mct := ct;
    end if;

    sys.dbms_output.put_line(rpad(r.fname,25)||' '||d0||d1||d2||d3||d4||d5||d6||' '||to_char(c0+c1+c2+c3+c4+c5+c6)||'/7'||'  '||to_char(ct,'MM/DD HH24:MI'));
 
    b0 := b0+c0;
    b1 := b1+c1;
    b2 := b2+c2;
    b3 := b3+c3;
    b4 := b4+c4;
    b5 := b5+c5;
    b6 := b6+c6;

    rows := rows+1;

  end loop;

  select max(completion_time) into ct from &_DB.BDF;
  sys.dbms_output.put_line('-------------------------- ---  ---  ---  ---  ---  ---  ---  ---- -----------');
  sys.dbms_output.put_line(rpad('datafiles backed up',24)||' '||to_char(b0,9000)||to_char(b1,9000)||to_char(b2,9000)||to_char(b3,9000)||to_char(b4,9000)||to_char(b5,9000)||to_char(b6,9000)||'  /'||rpad(to_char(rows),3)||' '||to_char(ct,'MM/DD HH24:MI'));
  sys.dbms_output.put_line(rpad('percentage',24)||' '||to_char((b0/rows)*100,9000)||to_char((b1/rows)*100,9000)||to_char((b2/rows)*100,9000)||to_char((b3/rows)*100,9000)||to_char((b4/rows)*100,9000)||to_char((b5/rows)*100,9000)||to_char((b6/rows)*100,9000)||' '||to_char(((b0+b1+b2+b3+b4+b5+b6)*100/rows/7), 9000)||' '||to_char(mct,'MM/DD HH24:MI'));
 
end;
/

drop table &_DB.BDF;

set feedback on
set verify   on

.                           16   15   14   13   12   11   10
Filename                   WED  TUE  MON  SUN  SAT  FRI  THU  Tot    Last Bkup
-------------------------- ---  ---  ---  ---  ---  ---  ---  ---  -----------
P2BL36A_BANLIST_D_1.dbf    YES  YES  YES   NO  YES  YES  YES  6/7  08/16 08:25
P2BL36A_BANLIST_I_1.dbf    YES  YES  YES   NO  YES  YES  YES  6/7  08/16 09:22
P2BL36A_BILL_D_1.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 07:07
P2BL36A_BILL_D_2.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 07:49
P2BL36A_BILL_D_3.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 08:02
P2BL36A_BILL_D_4.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 07:39
P2BL36A_BILL_D_5.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 07:52
P2BL36A_BILL_D_6.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 07:56
P2BL36A_BILL_D_7.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 08:05
P2BL36A_BILL_D_8.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 07:40
P2BL36A_BILL_I_1.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 09:51
P2BL36A_BILL_I_10.dbf      YES  YES  YES   NO  YES  YES  YES  6/7  08/16 08:41
P2BL36A_BILL_I_11.dbf      YES  YES  YES   NO  YES  YES  YES  6/7  08/16 08:41
P2BL36A_BILL_I_12.dbf      YES  YES  YES   NO  YES  YES  YES  6/7  08/16 10:48
P2BL36A_BILL_I_2.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 09:38
P2BL36A_BILL_I_3.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 10:44
P2BL36A_BILL_I_4.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 09:47
P2BL36A_BILL_I_5.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 09:35
P2BL36A_BILL_I_6.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 10:46
P2BL36A_BILL_I_9.dbf       YES  YES  YES   NO  YES  YES  YES  6/7  08/16 08:25
.
. Snip
.
P2BL36A_undo2_6.dbf        YES  YES  YES   NO  YES  YES  YES  6/7  08/16 07:35
P2BL36A_undo2_7.dbf        YES  YES  YES   NO  YES  YES  YES  6/7  08/16 10:44
P2BL36A_undo2_8.dbf        YES  YES  YES   NO  YES  YES  YES  6/7  08/16 10:45
-------------------------- ---  ---  ---  ---  ---  ---  ---  ---- -----------
datafiles backed up        440  440  440  000  440  440  440  /440 08/16 11:23
percentage                 100  100  100  000  100  100  100   086 08/16 07:07