/* 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
