Recently i was asked to generate a report that shows all the databases that were backed up via RMAN and they wanted a color code when a backup failed or wasn’t completed successfully.
That posed a challenge and took some research, but finally generated a report. Below is a sample report and how to color code on afailed backup, but can be used on any sql report:
SET MARKUP HTML ON SPOOL ON
SET TERMOUT OFF
SET PAGESIZE 1000
SET LINESIZE 300
SET TRIMOUT ON
SET TERMOUT ON
set pages 999
set feedback off
break on db_name page
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
spool backup_report
SET MARKUP HTML ON SPOOL ON
SET TERMOUT OFF
SET PAGESIZE 1000
SET LINESIZE 300
SET TRIMOUT ON
SET TERMOUT ON
set pages 999
set feedback off
break on db_name page
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
spool backup_report
SET MARKUP HTML ENTMAP OFF
PROMPT <H2><center><b> RMAN Backup details </b></center></H2>
SET MARKUP HTML ENTMAP ON
SET VERIFY off
SET lines 132 pages 9999 feedback off
COLUMN start_time format date heading ‘Start Date’
COLUMN end_time format date heading ‘End Date’
COLUMN input_bytes format 999,999,999,999 heading ‘Input Bytes’
COLUMN output_bytes format 999,999,999,999 heading ‘Output Bytes’
COLUMN cstatus format a40 heading ‘Status’
COLUMN object_type format a10 heading ‘Backup Type’
COLUMN time_taken_display format a40 heading ‘Time Taken’
COLUMN cstatus ENTMAP OFF
select db_name, object_type,start_time,end_time,input_bytes, output_bytes,
(case
when status like ‘%WARN%’ then ‘<font color=”green”>COMPLETED</font>’ <==== Note color default for non-failed jobs
when status like ‘COMPLETED’ then ‘<font color=”green”>COMPLETED</font>’
ELSE ‘ <font color=”red”>’||status||'</font>’ <=== Note – falls through code and any noncompleted status uses this color
END) cstatus
from <RMAN CATALOG>.rc_rman_status
where object_type = ‘DB FULL’
and operation not in (‘RESTORE VALIDATE’)
order by db_name,start_time Asc;
SET MARKUP HTML ENTMAP OFF
spool off
Hope this helps with your reports and can be used for any type of report that requires color coding.
–Mike
Mike Gangler's Musings 