Oracle / 巡检监控
Oracle 归档日志容量及日志切换频率统计
统计近30天归档日志按日期和线程的容量与数量,并统计近7天每小时日志切换次数,用于评估归档日志生成速率和切换频率。
SQL#归档日志#日志切换#容量统计#Oracle监控
上生产前,先在测试库跑一遍,确认版本、权限和影响范围。涉及数据或配置变更时,先备份,备好回退方案。
脚本使用说明
这些脚本供学习和日常运维参考。执行前请自行验证脚本是否适合你的环境,做好必要备份,并承担执行风险。因使用脚本造成的损失,本站在法律允许的范围内不承担责任。依法应承担的责任仍由本站承担。
oracle-archive-log-capacity-and-switch-frequency-statistics.sql
环境与权限
需要具备对 gv$archived_log、v$log_history 动态性能视图的查询权限;确认归档日志已启用且 dest_id=1 的归档位置正常工作。
使用方式
- 下载脚本,在具备归档日志历史视图查询权限的环境启动 SQL*Plus。
使用方式 · bash
sqlplus -L /nolog- 按现场认证方式连接目标库,再执行下载的文件。
使用方式 · sql
@oracle-archive-log-capacity-and-switch-frequency-statistics.sql完整脚本
Oracle · sql
-- 按日期统计近30天归档日志容量与数量
SELECT archive_date,
ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS archive_gb,
COUNT(*) AS archive_count
FROM (
SELECT TRUNC(completion_time) AS archive_date,
thread#,
sequence#,
MAX(blocks * block_size) AS bytes
FROM gv$archived_log
WHERE completion_time >= SYSDATE - 30
AND archived = 'YES'
AND dest_id = 1
GROUP BY TRUNC(completion_time),
thread#,
sequence#
)
GROUP BY archive_date
ORDER BY archive_date DESC;
-- 按日期和线程统计近30天归档日志容量与数量
SELECT archive_date,
thread#,
ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS archive_gb,
COUNT(*) AS archive_count
FROM (
SELECT TRUNC(completion_time) AS archive_date,
thread#,
sequence#,
MAX(blocks * block_size) AS bytes
FROM gv$archived_log
WHERE completion_time >= SYSDATE - 30
AND archived = 'YES'
AND dest_id = 1
GROUP BY TRUNC(completion_time),
thread#,
sequence#
)
GROUP BY archive_date,
thread#
ORDER BY archive_date DESC,
thread#;
-- 设置 SQL*Plus 输出格式
SET LINESIZE 250
SET PAGESIZE 100
COLUMN DAY FORMAT A10
COLUMN THREAD# FORMAT 999
COLUMN H00 FORMAT 9999
COLUMN H01 FORMAT 9999
COLUMN H02 FORMAT 9999
COLUMN H03 FORMAT 9999
COLUMN H04 FORMAT 9999
COLUMN H05 FORMAT 9999
COLUMN H06 FORMAT 9999
COLUMN H07 FORMAT 9999
COLUMN H08 FORMAT 9999
COLUMN H09 FORMAT 9999
COLUMN H10 FORMAT 9999
COLUMN H11 FORMAT 9999
COLUMN H12 FORMAT 9999
COLUMN H13 FORMAT 9999
COLUMN H14 FORMAT 9999
COLUMN H15 FORMAT 9999
COLUMN H16 FORMAT 9999
COLUMN H17 FORMAT 9999
COLUMN H18 FORMAT 9999
COLUMN H19 FORMAT 9999
COLUMN H20 FORMAT 9999
COLUMN H21 FORMAT 9999
COLUMN H22 FORMAT 9999
COLUMN H23 FORMAT 9999
COLUMN TOTAL FORMAT 99999
-- 统计近七天按日期、线程和小时划分的日志切换次数
SELECT TO_CHAR(first_time, 'YYYY-MM-DD') AS day,
thread#,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) AS H00,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) AS H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) AS H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) AS H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) AS H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) AS H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) AS H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) AS H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) AS H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) AS H09,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) AS H10,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) AS H11,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) AS H12,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) AS H13,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) AS H14,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) AS H15,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) AS H16,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) AS H17,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) AS H18,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) AS H19,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) AS H20,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) AS H21,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) AS H22,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) AS H23,
COUNT(*) AS total
FROM v$log_history
WHERE first_time >= TRUNC(SYSDATE) - 6
GROUP BY TRUNC(first_time),
TO_CHAR(first_time, 'YYYY-MM-DD'),
thread#
ORDER BY TRUNC(first_time) DESC,
thread#;