檢查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;
瀏覽器開啟截圖：
相關文章
- 【TUNE_ORACLE】Oracle檢查點（四）檢查點對redo日誌的影響和redo日誌大小設定建議Oracle
- 12C關於CDB、PDB 日誌檔案redo log的總結
- 收集日誌檔案同步診斷資訊指令碼(lfsdiag.sql) (文件 ID 1064487.1)指令碼SQL
- Linux 查詢 日誌 相關命令Linux
- 關於Solidity指令碼相關環境配置及指令碼資料的查詢Solid指令碼
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- Linux 檔案系統與日誌分析的相關知識Linux
- lumen 日誌按天生成檔案
- 檢視Oracle的redo日誌切換頻率Oracle
- 系統日誌及資料庫相關資訊收集資料庫
- MySQL慢查詢日誌相關設定MySql
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- 檢視織夢CMS原始碼中的資料庫相關檔案原始碼資料庫
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- 將程式碼中的除錯資訊輸出到日誌檔案中除錯
- 檢視oracle的redo日誌組切換頻率Oracle
- 利用VS(Visual Studio)自帶的工具檢視DLL檔案相關資訊
- 【VMware VCF】使用 SoS 實用程式檢查 VCF 環境的執行狀態以及收集相關元件的日誌資訊。元件
- 【手摸手玩轉 OceanBase 160】日誌歸檔相關檢視介紹
- Springboot日誌相關Spring Boot
- 【VMware VCF】使用 SoS 實用程式檢查 VCF 環境的執行狀態以及收集相關元件的日誌資訊。Av元件
- Audio Kit 檢視檔案波形的相關原始碼，easy o原始碼
- VSCode：更舒服地檢視日誌檔案VSCode
- 從原始碼角度看CPU相關日誌原始碼
- Mysql慢查詢日誌檔案轉ExcelMySqlExcel
- 日誌系統相關
- MySQL重做日誌（redo log）MySql
- mysql關於redo事務日誌ib_logfile的理解MySql
- 『無為則無心』Python日誌 — 66、將日誌資訊儲存到檔案中Python
- 關於發展報告的相關檔案生成的源程式
- PowerDesigner: 利用sql指令碼檔案逆生成模型SQL指令碼模型
- 第22篇 生成proto檔案bat指令碼BAT指令碼
- Linux檔案內容檢視相關命令Linux
- DB2日誌相關DB2
- 用php生成HTML檔案的類PHPHTML
- Linux 生成複雜密碼並且檢查密碼強度Linux密碼
- MySQL redo與undo日誌解析MySql
- MySQL之事務和redo日誌MySql
- 拒絕蠻力，高效檢視Linux日誌檔案！Linux