| 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
See also the non-html backup script.
|