检查REDO日志相关信息并生成HTML文件的脚本
生成HTML格式的文件
内容有：
检查数据库版本、REDO日志组情况，
最近20次日志切换频率检查--日志间的归档时间间隔, 这对查看数据库的IO繁忙时段
统计指定日期当天每小时的归档日志产生量--日期通过手动修改脚本中日期实现
统计最近10天每天的归档日志产生量
prompt Creating database report.
prompt This script must be run as a user with DBA privileges.
prompt This process can take several minutes to complete.
prompt need Specified date in---------archive log_MB in day
prompt
-- +----------------------------------------+
-- | SCRIPT SETTINGS |
-- +----------------------------------------+
set termout off
set echo off
set feedback off
set heading off
set verify off
set wrap on
set trimspool on
set serveroutput on
set escape on
set timing off
set pagesize 50000
set linesize 175
set long 2000000000
clear buffer computes columns breaks
define fileName=redolog_check
define versionNumber=5.3
COLUMN spool_time NEW_VALUE _spool_time NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDDhh24miss') spool_time FROM dual;
COLUMN dbname NEW_VALUE _dbname NOPRINT
SELECT name dbname FROM v$database;
set heading on
set markup html on spool on preformat off entmap on -
spool &FileName._&_dbname._&_spool_time..html
set markup html on entmap off
-- +----------------------------------------+
-- | - VERSION - |
-- +----------------------------------------+
prompt <a name="version"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Version</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN banner FORMAT a120 HEADING 'Banner'
SELECT * FROM v$version;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------+
-- | - ONLINE REDO LOGS - |
-- +----------------------------------------+
prompt <a name="online_redo_logs"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Online Redo Logs</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN instance_name_print FORMAT a95 HEADING 'Instance Name' ENTMAP off
COLUMN thread_number_print FORMAT a95 HEADING 'Thread Number' ENTMAP off
COLUMN groupno HEADING 'Group Number' ENTMAP off
COLUMN member HEADING 'Member' ENTMAP off
COLUMN redo_file_type FORMAT a75 HEADING 'Redo Type' ENTMAP off
COLUMN log_status FORMAT a75 HEADING 'Log Status' ENTMAP off
COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes' ENTMAP off
COLUMN archived FORMAT a75 HEADING 'Archived?' ENTMAP off
BREAK ON report ON instance_name_print ON thread_number_print
SELECT
'<div align="center"><font color="#336699"><b>' || i.instance_name || '</b></font></div>' instance_name_print
, '<div align="center">' || i.thread# || '</div>' thread_number_print
, f.group# groupno
, '<tt>' || f.member || '</tt>' member
, f.type redo_file_type
, DECODE( l.status
, 'CURRENT'
, '<div align="center"><b><font color="darkgreen">' || l.status || '</font></b></div>'
, '<div align="center"><b><font color="#990000">' || l.status || '</font></b></div>') log_status
, l.bytes bytes
, '<div align="center">' || l.archived || '</div>' archived
FROM gv$logfile f, gv$log l, gv$instance i
WHERE f.group# = l.group#
AND l.thread# = i.thread#
AND i.inst_id = f.inst_id
AND f.inst_id = l.inst_id
ORDER BY
i.instance_name
, f.group#
, f.member;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------+
-- | - redo log switch times- |
-- +----------------------------------------+
prompt <a name="datafile"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>redo log switch times</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN first_time HEADING 'first_time' ENTMAP off
COLUMN minutes HEADING 'minutes' ENTMAP off
COLUMN recid HEADING 'recid' ENTMAP off
select '<tt>' ||first_time|| '</tt>' as first_time,
'<div align="right">' ||minutes|| '</div>' as minutes,
'<div align="right">' ||recid|| '</div>' as recid
from
(select
to_char(first_time,'mm/dd hh24:mi:ss') first_time,
round(24 * 60 * (lead(first_time,1) over (order by first_time) - first_time ),2) minutes,
recid
from v$log_history v order by recid desc) a
where rownum<21;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------+
-- | - archive log_MB |
-- +----------------------------------------+
prompt <a name="datafile"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>archive log_MB</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN completion HEADING 'completion_time' ENTMAP off
COLUMN Count HEADING 'Count' ENTMAP off
COLUMN sum_MB HEADING 'sum_MB' ENTMAP off
select
'<tt>' ||completion|| '</tt>' as completion,
'<div align="right">' ||Count|| '</div>' as Count ,
'<div align="right">' ||sum_MB|| '</div>' as sum_MB
from
(select to_char(completion_time,'yyyy/mm/dd') as completion,
count(*) as Count,
trunc((sum(blocks * block_size))/1024/1024,2) as sum_MB
from v$archived_log
group by to_char(completion_time,'yyyy/mm/dd')
order by completion desc) a where rownum<11;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------+
-- | - archive log_MB in day---Specified date |
-- +----------------------------------------+
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>archive log_MB</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN completion HEADING 'completion_time' ENTMAP off
COLUMN sum_MB HEADING 'sum_MB' ENTMAP off
select '<tt>' ||a.completion|| '</tt>' as completion,
'<div align="right">' ||a.sum_MB|| '</div>' as sum_MB
from
(select
to_char(completion_time,'yyyy/mm/dd hh24') as completion,
trunc((sum(blocks * block_size))/1024/1024,2) as sum_MB
from v$archived_log
where to_char(completion_time,'yyyy/mm/dd')='2013/10/08'
group by to_char(completion_time,'yyyy/mm/dd hh24')
order by to_char(completion_time,'yyyy/mm/dd hh24')) a;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------+
-- | - END OF REPORT - |
-- +----------------------------------------+
SPOOL OFF
SET MARKUP HTML OFF
SET TERMOUT ON
prompt
prompt Output written to: &FileName._&_dbname._&_spool_time..html
EXIT;
