檢查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;
瀏覽器開啟截圖：
相關文章
- 檢查儲存結構-控制、REDO日誌、表空間使用情況、資料檔案等資訊指令碼--HTML指令碼HTML
- redo日誌組資訊查詢
- 重做日誌檔案的相關操作
- 重做日誌（redo log）相關總結
- 【REDO】重做日誌檔案（redo log files）管理（增，刪，改，查，切）
- 【TUNE_ORACLE】Oracle檢查點（四）檢查點對redo日誌的影響和redo日誌大小設定建議Oracle
- 檢視Oracle基礎配置資訊和效能相關資訊的指令碼Oracle指令碼
- alter session set events語句dump REDO日誌檔案頭資訊示例Session
- 檢查Linux系統日誌error和mysql錯誤日誌的指令碼薦LinuxErrorMySql指令碼
- 【redo】日誌檔案的丟失解決方法
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
- redo日誌檔案學習筆記（一)筆記
- 修改online redo日誌檔案大小
- 日誌檔案和歸檔日誌檔案的關係以及如何切換日誌
- 12C關於CDB、PDB 日誌檔案redo log的總結
- window下刪除過舊的日誌檔案指令碼(如jboss的日誌檔案 資料庫的trace檔案)指令碼資料庫
- PHP慢指令碼日誌和Mysql的慢查詢日誌PHP指令碼MySql
- 【恢復】Redo日誌檔案丟失的恢復
- Linux/Unix shell 指令碼清除歸檔日誌檔案Linux指令碼
- Linux 查詢 日誌 相關命令Linux
- 檢視oracle固定目錄下日誌和trace檔案大小指令碼Oracle指令碼
- lumen 日誌按天生成檔案
- 用指令碼一次性得到ORACLE資料庫相關配置資訊並儲存為檔案指令碼Oracle資料庫
- 通過Xtrabackup日誌來恢復檢查點檔案
- zt_日誌檔案相關等待_log file waitAI
- oracle cluster叢集相關日誌檢視Oracle
- python多程式檢查埠並寫日誌Python
- 使用java程式碼啟動generator生成相關的檔案Java
- MySQL慢查詢日誌相關設定MySql
- 一個刪除重做日誌檔案的參考指令碼指令碼
- Linux 檔案系統與日誌分析的相關知識Linux
- oracle聯機日誌檔案REDO LOGFILE簡述Oracle
- 收集日誌檔案同步診斷資訊指令碼(lfsdiag.sql) (文件 ID 1064487.1)指令碼SQL
- 用RMAN備份歸檔日誌時檢查歸檔日誌是否存在
- oracle 關於-日誌檔案Oracle
- 檢視Oracle的redo日誌切換頻率Oracle
- RMAN備份歸檔日誌指令碼指令碼
- 巧用shell生成資料庫檢查指令碼資料庫指令碼