Oracle DBA必备工具：自动调整重做日志文件大小和数量

皮仪芳 · 3 天前

我们的文章会在微信公众号Oracle恢复实录和博客网站(www.htz.pw)同步更新，欢迎关注收藏。也欢迎大家转载，但请在文章开始处标注文章出处，谢谢！
由于博客中包含大量代码，建议通过网页浏览以获得更好的阅读体验。

背景介绍

在Oracle数据库运维过程中，合理配置重做日志（Redo Log）文件的大小和数量是非常重要的。不合适的重做日志配置可能会导致以下问题：

日志切换过于频繁，影响数据库性能
日志文件太小，导致归档速度跟不上
日志组数量不足，影响数据库可用性

今天给大家分享一个实用的脚本工具，可以帮助DBA快速调整重做日志文件的配置。
脚本功能介绍

这个脚本（db_add_logfile.sql）主要实现以下功能：

自动添加指定大小的重做日志文件组
支持指定每个线程的日志组数量
自动清理小于指定大小的旧日志文件
支持ASM和文件系统两种存储方式
支持RAC环境
支持归档和非归档模式

使用方法

脚本接受三个参数：

@db_add_logfile.sql [日志文件大小] [每线程日志组数量] [日志文件路径]

复制代码

参数说明：

参数1：日志文件大小（默认2G）
参数2：每个线程的日志组总数（默认7）
参数3：日志文件路径（默认使用现有日志组的路径）

使用示例：

@db_add_logfile.sql 2 8 +DATA_01

复制代码

脚本特点

智能路径处理
- 自动识别ASM和文件系统路径
- ASM环境下自动检查磁盘组是否存在
- 支持Windows和Linux文件系统路径格式
安全性保障
- 添加新日志组前检查路径有效性
- 删除旧日志组时自动检查状态
- 包含完善的错误处理机制
兼容性
- 支持Oracle RAC环境
- 支持归档和非归档模式
- 自动处理多线程情况

实现原理

路径类型判断

WHEN REGEXP_REPLACE (MEMBER, '[^\+]', '') = '+' THEN 'ASM'
WHEN SUBSTR (x.MEMBER, 1, REGEXP_INSTR (x.MEMBER, '[/]', 1, 2) - 1) = '/dev' THEN '/dev'
ELSE 'FS'

复制代码

日志组状态处理

WHILE i_group_status IN ('ACTIVE', 'CURRENT') LOOP
EXECUTE IMMEDIATE i_sql_arch2;
SELECT status INTO i_group_status FROM v$log
WHERE group# = i_delete_group.group#;
IF i_group_status != 'INACTIVE' THEN
DBMS_LOCK.SLEEP (2);
END IF;
END LOOP;

复制代码

完整的代码

当前发布时的版本如下，如要获得最新版本，请公众号回复db_add_logfile，或者直接连接我。

/* Formatted on 2025/6/9 23:00:19 (QP5 v5.300) */
-- File Name : db_add_logfile.sql
-- Purpose : Add redo log file groups to the database, specify log group number,
-- specify log file size, auto delete log files smaller than specified size
-- @db_add_logfile.sql (press enter)
-- @db_add_logfile.sql 2 8 +DATA_01
-- Date : 2016/05/19
-- weixin:18081072613
-- http://www.htz.pw
-- 20161129 Added path input, if path is not specified, use the path of existing log groups.
-- 20161129 Added support for non-archive mode and RAC, in non-archive mode, only display message for current REDO.
-- 20250609 Enhanced functionality, added error handling
-- 20250610 重构整个代码，并修复asm磁盘组路径传入忽略+的导致的报错，增加磁盘组是否存在的检查
SET LINES 200
SET PAGES 1000 HEADING ON VERIFY OFF SERVEROUTPUT ON
PRO
PRO Parameter 1:
PRO log file size (default 2g)
PRO
PRO Parameter 2:
PRO total group number by thread (default 7)
PRO
PRO Parameter 3:
PRO log file path name(for +DG_DATA) (default old path name)
PRO
PRO
DECLARE
path_type VARCHAR2 (200);
path_name VARCHAR2 (200);
i_group_current NUMBER;
i_logfile_size NUMBER;
i_group_number NUMBER;
i_log_number NUMBER;
i_group_name NUMBER;
i_instance_id NUMBER;
i_sql VARCHAR2 (200);
i_sql_arch VARCHAR2 (200) := 'alter system switch logfile';
i_sql_arch2 VARCHAR2 (200) := 'alter system archive log current';
i_group_status VARCHAR2 (200);
i_logfile_name VARCHAR2 (200);
i_log_mode VARCHAR2 (200);
BEGIN
DECLARE
PROCEDURE drop_logfile_group (p_group_no IN NUMBER)
IS
v_sql VARCHAR2 (200);
BEGIN
v_sql := 'alter database drop logfile group ' || p_group_no;
DBMS_OUTPUT.put_line (v_sql);
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Warning: Unknown error occurred when dropping log group '
|| p_group_no
|| ': '
|| SQLERRM);
DBMS_OUTPUT.put_line ('Continue processing next log group...');
END;
BEGIN
SELECT TO_NUMBER (NVL ('&1', 2) * 1024 * 1024 * 1024)
INTO i_logfile_size
FROM DUAL;
SELECT TO_NUMBER (NVL ('&2', 6)) INTO i_group_number FROM DUAL;
SELECT NVL ('&3', 'DEFAULT') INTO path_name FROM DUAL;
SELECT log_mode INTO i_log_mode FROM v$database;
SELECT instance_number INTO i_instance_id FROM v$instance;
IF path_name = 'DEFAULT'
THEN
SELECT CASE
WHEN REGEXP_REPLACE (MEMBER, '[^\+]', '') = '+'
THEN
'ASM'
WHEN SUBSTR (x.MEMBER,
1,
REGEXP_INSTR (x.MEMBER,
'[/]',
1,
2)
- 1) = '/dev'
THEN
'/dev'
ELSE
'FS'
END
AS path_type,
CASE
WHEN REGEXP_REPLACE (MEMBER, '[^\+]', '') = '+'
THEN
SUBSTR (x.MEMBER,
1,
REGEXP_INSTR (x.MEMBER,
'[/]',
1,
1)
- 1)
ELSE
SUBSTR (
x.MEMBER,
1,
REGEXP_INSTR (x.MEMBER,
'[/]',
1,
REGEXP_COUNT (x.MEMBER, '[/]')))
END
AS name
INTO path_type, path_name
FROM v$logfile x
WHERE ROWNUM = 1;
ELSE
SELECT CASE
WHEN REGEXP_REPLACE (path_name, '[^\+]', '') = '+'
THEN
'ASM'
WHEN SUBSTR (path_name,
1,
REGEXP_INSTR (path_name,
'[/]',
1,
2)
- 1) = '/dev'
THEN
'/dev'
WHEN (REGEXP_LIKE(path_name, '^[A-Za-z]:\\.*$') OR
REGEXP_LIKE(path_name, '^/.*$'))
THEN
'FS'
ELSE
'ERROR'
END
AS path_type
INTO path_type
FROM DUAL x
WHERE ROWNUM = 1;
END IF;
IF path_type = 'ERROR' THEN
DBMS_OUTPUT.put_line('Error: The specified filesystem path does not exist or has invalid format');
RETURN;
END IF;
-- 如果是ASM路径,检查磁盘组是否存在
IF path_type = 'ASM' THEN
DECLARE
v_diskgroup_exists NUMBER;
v_diskgroup_name VARCHAR2(30);
BEGIN
-- 提取磁盘组名称 (去掉+号)
v_diskgroup_name := SUBSTR(path_name, 2, INSTR(path_name, '/') - 2);
-- 检查磁盘组是否存在
SELECT COUNT(*)
INTO v_diskgroup_exists
FROM v$asm_diskgroup
WHERE name = v_diskgroup_name;
IF v_diskgroup_exists = 0 THEN
DBMS_OUTPUT.put_line('Error: ASM diskgroup ' || v_diskgroup_name || ' does not exist');
RETURN;
END IF;
END;
END IF;
SELECT (MAX (group#) + 1)
INTO i_group_name
FROM v$logfile;
FOR c_thread IN (SELECT DISTINCT thread#
FROM v$log)
LOOP
SELECT COUNT (*)
INTO i_group_current
FROM v$log
WHERE thread# = c_thread.thread# AND bytes >= i_logfile_size;
WHILE i_group_current < i_group_number
LOOP
IF path_type = 'ASM'
THEN
i_sql :=
'alter database add logfile thread '
|| c_thread.thread#
|| ' '
|| CHR (39)
|| path_name
|| CHR (39)
|| ' size '
|| i_logfile_size;
ELSIF path_type = 'FS'
THEN
i_logfile_name :=
path_name || 'redo' || i_group_name || '.log';
SELECT COUNT (*)
INTO i_log_number
FROM v$logfile
WHERE MEMBER = i_logfile_name;
WHILE i_log_number > 0
LOOP
i_group_name := i_group_name + 1;
i_logfile_name :=
path_name || 'redo' || i_group_name || '.log';
SELECT COUNT (*)
INTO i_log_number
FROM v$logfile
WHERE MEMBER = i_logfile_name;
END LOOP;
i_sql :=
'alter database add logfile thread '
|| c_thread.thread#
|| ' '
|| CHR (39)
|| path_name
|| 'redo'
|| i_group_name
|| '.log'
|| CHR (39)
|| ' size '
|| i_logfile_size;
END IF;
DBMS_OUTPUT.put_line (i_sql);
EXECUTE IMMEDIATE i_sql;
i_group_current := i_group_current + 1;
i_group_name := i_group_name + 1;
END LOOP;
FOR i_delete_group
IN (SELECT group#, THREAD#
FROM v$log
WHERE thread# = c_thread.thread#
AND BYTES < i_logfile_size)
LOOP
SELECT status
INTO i_group_status
FROM v$log
WHERE group# = i_delete_group.group#;
IF i_log_mode = 'ARCHIVELOG'
THEN
WHILE i_group_status IN ('ACTIVE', 'CURRENT')
LOOP
EXECUTE IMMEDIATE i_sql_arch2;
SELECT status
INTO i_group_status
FROM v$log
WHERE group# = i_delete_group.group#;
IF i_group_status != 'INACTIVE'
THEN
DBMS_LOCK.SLEEP (2);
END IF;
END LOOP;
i_sql :=
'alter database drop logfile group '
|| i_delete_group.group#;
DBMS_OUTPUT.put_line (i_sql);
EXECUTE IMMEDIATE i_sql;
ELSE
IF i_instance_id = c_thread.thread#
THEN
WHILE i_group_status IN ('ACTIVE', 'CURRENT')
LOOP
EXECUTE IMMEDIATE i_sql_arch;
SELECT status
INTO i_group_status
FROM v$log
WHERE group# = i_delete_group.group#;
IF i_group_status != 'INACTIVE'
THEN
DBMS_LOCK.SLEEP (2);
END IF;
END LOOP;
drop_logfile_group (i_delete_group.group#);
ELSE
IF i_group_status IN ('ACTIVE', 'CURRENT')
THEN
DBMS_OUTPUT.put_line (
'manual exec : alter database drop logfile group '
|| i_delete_group.group#
|| ' on '
|| c_thread.thread#);
ELSE
drop_logfile_group (i_delete_group.group#);
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
END;
END;
/

复制代码

使用注意事项

执行脚本前需要具备足够的权限
建议在业务低峰期执行
ASM环境下需要确保磁盘组有足够空间
RAC环境中注意观察所有节点的日志切换情况

更新历史

2025/06/10：重构代码，修复ASM磁盘组路径问题，增加磁盘组存在性检查
2025/06/09：增强功能，添加错误处理
2016/11/29：增加路径输入支持，增加非归档模式和RAC支持
2016/05/19：初始版本发布

总结

这个脚本工具可以帮助DBA快速调整Oracle数据库的重做日志配置，提高数据库的可维护性和性能。它的智能判断和安全机制可以让DBA放心使用，是一个值得收藏的运维工具。
以上就是这个Oracle DBA工具的详细介绍。如果您在使用过程中遇到任何问题，欢迎留言交流！
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

账号		自动登录	找回密码
密码			立即注册

Oracle DBA必备工具：自动调整重做日志文件大小和数量

相关帖子

浏览过的版块

签约作者

Oracle DBA必备工具：自动调整重做日志文件大小和数量

相关帖子

相关推荐

浏览过的版块

签约作者