René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

RMAN backup history script (Output as HTML)

The following script is provided by Shawn M. Ellinger. It creates a 10 day history of RMAN backups in HTML format.
set feedback off
set verify   off
set pages      0
set lines     90
set head     off

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;

select 'Ten Day Backup History for '||Upper('&&1')||' as of '||to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;

/* cleanup work if you see duplicate entries */
--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

create table &_DB.html
( fname varchar2(30),
  day1  varchar2(30),
  day2  varchar2(30),
  day3  varchar2(30),
  day4  varchar2(30),
  day5  varchar2(30),
  day6  varchar2(30),
  day7  varchar2(30),
  day8  varchar2(30),
  day9  varchar2(30),
  day10  varchar2(30),
  ttl   varchar2(30),
  last_bkup varchar2(30)
);



set serveroutput on size 1000000

declare

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

 d0 varchar2(30);
 d1 varchar2(30);
 d2 varchar2(30);
 d3 varchar2(30);
 d4 varchar2(30);
 d5 varchar2(30);
 d6 varchar2(30);
 d7 varchar2(30);
 d8 varchar2(30);
 d9 varchar2(30);

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

 c0 number;
 c1 number;
 c2 number;
 c3 number;
 c4 number;
 c5 number;
 c6 number;
 c7 number;
 c8 number;
 c9 number;

 b0 number := 0;
 b1 number := 0;
 b2 number := 0;
 b3 number := 0;
 b4 number := 0;
 b5 number := 0;
 b6 number := 0;
 b7 number := 0;
 b8 number := 0;
 b9 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'),
  to_char(dte-7, 'DY'), 
  to_char(dte-8, 'DY'),
  to_char(dte-9, 'DY') 
  into t0, t1, t2,t3,t4,t5,t6,t7,t8,t9
from dual;

 insert into &_DB.html values ('',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'),to_char(dte-7,'dd'),to_char(dte-8,'dd'),to_char(dte-9,'dd'),'','');
 insert into &_DB.html values ('Filename',t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,'Tot',' Last Bkup');
 insert into &_DB.html values ('--------------------------','---','---','---','---','---','---','---','---','---','---','---','-----------');
 
 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 &_DB..dfatt.DBINC_KEY = &_DBKEY
           and &_DB..df.DBINC_KEY= &_DBKEY
           and &_DB..ts.DBINC_KEY= &_DBKEY
           and ts.DROP_SCN is null
           and df.drop_time is null
           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,'<b>NO</b>','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,'<b>NO</b>','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,'<b>NO</b>','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,'<b>NO</b>','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,'<b>NO</b>','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,'<b>NO</b>','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,'<b>NO</b>','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;
    select decode(count(*),0,'<b>NO</b>','YES'), count(*) into d7, c7 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-7) and trunc(dte-5) and rownum = 1;
    select decode(count(*),0,'<b>NO</b>','YES'), count(*) into d8, c8 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-8) and trunc(dte-5) and rownum = 1;
    select decode(count(*),0,'<b>NO</b>','YES'), count(*) into d9, c9 from &_DB.BDF where r.file# = file# and COMPLETION_TIME between trunc(dte-9) and trunc(dte-5) and rownum = 1;
 

    if c1 = 0 then
      if min_complete > dte-1 then 
         d1 := '<font color="BLUE">N/A</font>';
      end if;
    end if;
    if c2 = 0 then
      if min_complete > dte-2 then 
         d2 := '<font color="BLUE">N/A</font>';
      end if;
    end if;    
    if c3 = 0 then
      if min_complete > dte-3 then 
         d3 := '<font color="BLUE">N/A</font>';
      end if;
    end if;    
    if c4 = 0 then
      if min_complete > dte-4 then 
         d4 := '<font color="BLUE">N/A</font>';
      end if;
    end if;    
    if c5 = 0 then
      if min_complete > dte-5 then 
         d5 := '<font color="BLUE">N/A</font>';
      end if;
    end if;    
    if c6 = 0 then
      if min_complete > dte-6 then 
         d6 := '<font color="BLUE">N/A</font>';
      end if;
    end if;    
    if c7 = 0 then
      if min_complete > dte-7 then 
         d7 := '<font color="BLUE">N/A</font>';
      end if;
    end if;    
    if c8 = 0 then
      if min_complete > dte-8 then 
         d8 := '<font color="BLUE">N/A</font>';
      end if;
    end if;    
    if c9 = 0 then
      if min_complete > dte-9 then 
         d9 := '<font color="BLUE">N/A</font>';
      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;

    insert into &_DB.html values (r.fname,d0,d1,d2,d3,d4,d5,d6,d7,d8,d9,to_char(c0+c1+c2+c3+c4+c5+c6+c7+c8+c9)||'/10',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;
    b7 := b7+c7;
    b8 := b8+c8;
    b9 := b9+c9;

    rows := rows+1;

  end loop;

 select max(completion_time) into ct from &_DB.BDF;
 insert into &_DB.html values ('--------------------------','---','---','---','---','---','---','---','---','---','---','---','-----------');
 insert into &_DB.html values ('datafiles backed up',to_char(b0),to_char(b1),to_char(b2),to_char(b3),to_char(b4),to_char(b5),to_char(b6),to_char(b7),to_char(b8),to_char(b9),'/'||rpad(to_char(rows),3),to_char(ct,'MM/DD HH24:MI'));
 insert into &_DB.html values ('percentage',to_char((b0/rows)*100,900),to_char((b1/rows)*100,900),to_char((b2/rows)*100,900),to_char((b3/rows)*100,900),to_char((b4/rows)*100,900),to_char((b5/rows)*100,900),to_char((b6/rows)*100,900),to_char((b7/rows)*100,900),to_char((b8/rows)*100,900),to_char((b9/rows)*100,900),to_char(((b0+b1+b2+b3+b4+b5+b6+b7+b8+b9)*100/rows/10), 900),to_char(mct,'MM/DD HH24:MI'));

end;
/

drop table &_DB.BDF;
--column filename format a24
--column day1 format a4
--column day2 format a4
--column day3 format a4
--column day4 format a4
--column day5 format a4
--column day6 format a4
--column day7 format a4
--column ttl  format a5
--column last_bkup format a13


select * from &_DB.html;
drop table &_DB.html;

@setdefs
set feedback on
set verify on

--exit;
In order to turn HTML output on in SQL*Plus, the script must be started like so:
sqlplus -l -s -m "html on entmap off" <username>/${PASSWD}@<rman_catalog> @rman_backup_history_html.sql P2BL44A >>$REPORTS/P2BL44A_backup.html

Links