Oracle DBA必备工具：自动调整重做日志文件大小和数量
我们的文章会在微信公众号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, '^:\\.*$') 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 addlogfile 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 addlogfile 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
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]