René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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.
|