/* Formatted on 2025/11/04 15:00:00 (QP5 v5.300) */
-- File Name : db_add_logfile_optimized.sql
-- Purpose : 优化版Oracle重做日志组管理脚本
-- 支持功能：
-- 1. 支持日志大小单位（G/M/K），参数自动校验
-- 2. 自动补全路径分隔符，校验ASM磁盘组/FS路径存在性
-- 3. 减少视图访问，提升性能
-- 4. 细化错误处理，支持CDB/PDB环境
-- Usage :
-- 1. 默认执行（2G/每线程6组/默认路径）：@db_add_logfile_optimized.sql
-- 2. 自定义执行：@db_add_logfile_optimized.sql 500M 8 +DATA_01/
-- Date : 2025/11/04
SET LINES 300 PAGES 1000 HEADING ON VERIFY OFF SERVEROUTPUT ON SIZE UNLIMITED
PRO ==============================================
PRO Oracle重做日志组管理脚本
PRO 参数说明：
PRO 1. 日志大小（支持G/M/K，如2G、500M，默认2G）
PRO 2. 每线程日志组总数（默认6）
PRO 3. 日志路径（ASM:+DG/，FS:/u01/oradata/，默认取现有日志路径）
PRO ==============================================
/
DECLARE
-- 1. 输入参数与解析变量
p_size_str VARCHAR2(20) := NVL('&1', '2G');
p_group_cnt VARCHAR2(10) := NVL('&2', '6');
p_input_path VARCHAR2(200) := NVL('&3', 'DEFAULT');
v_log_size NUMBER; -- 日志大小（字节）
v_group_total NUMBER; -- 每线程日志组总数
v_path_type VARCHAR2(20); -- 路径类型：ASM/FS/dev/ERROR
v_full_path VARCHAR2(200); -- 完整路径（补全分隔符后）
v_log_mode VARCHAR2(20); -- 数据库归档模式
v_instance_id NUMBER; -- 当前实例ID
v_is_cdb BOOLEAN; -- 是否为CDB环境
v_valid BOOLEAN := TRUE; -- 参数是否有效
-- 集合类型：存储日志成员与线程列表（减少视图访问）
TYPE t_log_member IS TABLE OF VARCHAR2(200);
v_log_members t_log_member;
TYPE t_thread IS TABLE OF NUMBER;
v_threads t_thread;
-- 2. 工具函数：参数解析与校验
FUNCTION parse_size(p_size_str IN VARCHAR2) RETURN NUMBER IS
v_num NUMBER;
v_unit VARCHAR2(2);
BEGIN
IF NOT REGEXP_LIKE(p_size_str, '^[0-9]+\s*[GMKgmk]$') THEN
DBMS_OUTPUT.PUT_LINE('【参数错误】日志大小格式非法！支持：数字+单位（如2G、500M、1024K）');
RETURN NULL;
END IF;
v_num := TO_NUMBER(REGEXP_SUBSTR(p_size_str, '^[0-9]+'));
v_unit := UPPER(REGEXP_SUBSTR(p_size_str, '[GMK]$'));
CASE v_unit
WHEN 'G' THEN RETURN v_num * 1024 * 1024 * 1024;
WHEN 'M' THEN RETURN v_num * 1024 * 1024;
WHEN 'K' THEN RETURN v_num * 1024;
ELSE RETURN NULL;
END CASE;
END;
-- 3. 工具函数：路径处理
FUNCTION get_path_type(p_path IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_path = 'DEFAULT' THEN
RETURN 'DEFAULT';
ELSIF REGEXP_LIKE(p_path, '^\+[A-Za-z0-9_]+') THEN
RETURN 'ASM';
ELSIF REGEXP_LIKE(p_path, '^/dev/') THEN
RETURN 'dev';
ELSIF REGEXP_LIKE(p_path, '^[A-Za-z]:\\|^/') THEN
RETURN 'FS';
ELSE
RETURN 'ERROR';
END IF;
END;
FUNCTION complete_path_sep(p_path IN VARCHAR2, p_type IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_type = 'ASM' THEN
RETURN CASE WHEN SUBSTR(p_path, LENGTH(p_path)) != '/' THEN p_path || '/' ELSE p_path END;
ELSIF p_type = 'FS' THEN
IF REGEXP_LIKE(p_path, '^[A-Za-z]:\\') THEN
RETURN CASE WHEN SUBSTR(p_path, LENGTH(p_path)) != '\' THEN p_path || '\' ELSE p_path END;
ELSE
RETURN CASE WHEN SUBSTR(p_path, LENGTH(p_path)) != '/' THEN p_path || '/' ELSE p_path END;
END IF;
ELSE
RETURN p_path;
END IF;
END;
FUNCTION check_asm_dg(p_path IN VARCHAR2) RETURN BOOLEAN IS
v_dg_name VARCHAR2(30) := REGEXP_SUBSTR(p_path, '^\+([A-Za-z0-9_]+)', 1, 1, NULL, 1);
v_dg_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_dg_cnt FROM v$asm_diskgroup WHERE name = v_dg_name;
IF v_dg_cnt = 0 THEN
DBMS_OUTPUT.PUT_LINE('【路径错误】ASM磁盘组不存在：'||v_dg_name);
RETURN FALSE;
END IF;
RETURN TRUE;
END;
FUNCTION check_fs_path(p_path IN VARCHAR2) RETURN BOOLEAN IS
v_file UTL_FILE.FILE_TYPE;
v_loc VARCHAR2(200) := SUBSTR(p_path, 1, LENGTH(p_path)-1);
BEGIN
v_file := UTL_FILE.FOPEN(v_loc, 'tmp_dg_check.tmp', 'w', 1024);
UTL_FILE.FCLOSE(v_file);
UTL_FILE.FREMOVE(v_loc, 'tmp_dg_check.tmp');
RETURN TRUE;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('【路径错误】文件系统路径不存在：'||p_path);
RETURN FALSE;
WHEN UTL_FILE.ACCESS_DENIED THEN
DBMS_OUTPUT.PUT_LINE('【权限错误】无权限访问FS路径：'||p_path);
RETURN FALSE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('【路径错误】校验FS路径异常：'||SQLERRM);
RETURN FALSE;
END;
-- 4. 工具函数：性能优化与等待
PROCEDURE load_log_members IS
BEGIN
SELECT MEMBER BULK COLLECT INTO v_log_members FROM v$logfile;
END;
FUNCTION is_member_exists(p_member IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
FOR i IN 1..v_log_members.COUNT LOOP
IF v_log_members(i) = p_member THEN RETURN TRUE; END IF;
END LOOP;
RETURN FALSE;
END;
PROCEDURE wait_seconds(p_sec IN NUMBER) IS
v_start NUMBER := DBMS_UTILITY.GET_TIME;
BEGIN
WHILE (DBMS_UTILITY.GET_TIME - v_start) / 100 < p_sec LOOP NULL; END LOOP;
END;
-- 5. 核心过程：添加日志组
PROCEDURE add_logfile(p_thread IN NUMBER, p_group_num IN NUMBER) IS
v_sql VARCHAR2(500);
v_member VARCHAR2(300);
BEGIN
IF v_path_type = 'ASM' THEN
v_member := v_full_path || 'redo_t'||p_thread||'_g'||p_group_num||'.log';
ELSE
v_member := v_full_path || 'redo_t'||p_thread||'_g'||p_group_num||'.log';
END IF;
-- 检查文件名是否存在
IF is_member_exists(v_member) THEN
DBMS_OUTPUT.PUT_LINE('【提示】日志文件已存在：'||v_member，跳过创建');
RETURN;
END IF;
v_sql := 'ALTER DATABASE ADD LOGFILE THREAD '||p_thread||' '''||v_member||''' SIZE '||v_log_size;
DBMS_OUTPUT.PUT_LINE('【执行】添加日志组：'||v_sql);
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('【成功】线程'||p_thread||'日志组'||p_group_num||'创建完成');
EXCEPTION
WHEN OTHERS THEN
CASE SQLCODE
WHEN -01658 THEN
DBMS_OUTPUT.PUT_LINE('【错误】线程'||p_thread||'组'||p_group_num||'创建失败（'||SQLCODE||'）：磁盘空间不足');
DBMS_OUTPUT.PUT_LINE('【建议】需至少'||ROUND(v_log_size/1024/1024,2)||'MB，检查路径'||v_full_path||'磁盘空间');
WHEN -01031 THEN
DBMS_OUTPUT.PUT_LINE('【错误】线程'||p_thread||'组'||p_group_num||'创建失败（'||SQLCODE||'）：权限不足');
DBMS_OUTPUT.PUT_LINE('【建议】授予ALTER DATABASE权限：GRANT ALTER DATABASE TO 当前用户');
ELSE
DBMS_OUTPUT.PUT_LINE('【错误】线程'||p_thread||'组'||p_group_num||'创建失败（'||SQLCODE||'）：'||SQLERRM);
END CASE;
RAISE;
END;
-- 6. 核心过程：删除小日志组
PROCEDURE drop_small_logfile(p_thread IN NUMBER) IS
CURSOR c_small_log IS
SELECT group# FROM v$log
WHERE thread# = p_thread AND bytes < v_log_size;
v_group_num NUMBER;
v_status VARCHAR2(20);
v_sql VARCHAR2(200);
BEGIN
OPEN c_small_log;
FETCH c_small_log INTO v_group_num;
WHILE c_small_log%FOUND LOOP
SELECT status INTO v_status FROM v$log WHERE group# = v_group_num;
DBMS_OUTPUT.PUT_LINE('【处理】线程'||p_thread
浙公网安备 33010602011771号