liuziyi

/* Formatted on 2025/11/04 15:00:00 (QP5 v5.300) */
-- File Name : db_add_logfile_optimized.sql
-- Purpose : 優(yōu)化版Oracle重做日志組管理腳本
-- 支持功能：
-- 1. 支持日志大小單位（G/M/K），參數(shù)自動(dòng)校驗(yàn)
-- 2. 自動(dòng)補(bǔ)全路徑分隔符，校驗(yàn)ASM磁盤(pán)組/FS路徑存在性
-- 3. 減少視圖訪問(wèn)，提升性能
-- 4. 細(xì)化錯(cuò)誤處理，支持CDB/PDB環(huán)境
-- Usage : 
-- 1. 默認(rèn)執(zhí)行（2G/每線程6組/默認(rèn)路徑）：@db_add_logfile_optimized.sql
-- 2. 自定義執(zhí)行：@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 參數(shù)說(shuō)明：
PRO 1. 日志大小（支持G/M/K，如2G、500M，默認(rèn)2G）
PRO 2. 每線程日志組總數(shù)（默認(rèn)6）
PRO 3. 日志路徑（ASM:+DG/，F(xiàn)S:/u01/oradata/，默認(rèn)取現(xiàn)有日志路徑）
PRO ==============================================
/

DECLARE
    -- 1. 輸入?yún)?shù)與解析變量
    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;          -- 日志大小（字節(jié)）
    v_group_total NUMBER;          -- 每線程日志組總數(shù)
    v_path_type   VARCHAR2(20);    -- 路徑類型：ASM/FS/dev/ERROR
    v_full_path   VARCHAR2(200);   -- 完整路徑（補(bǔ)全分隔符后）
    v_log_mode    VARCHAR2(20);    -- 數(shù)據(jù)庫(kù)歸檔模式
    v_instance_id NUMBER;          -- 當(dāng)前實(shí)例ID
    v_is_cdb      BOOLEAN;         -- 是否為CDB環(huán)境
    v_valid       BOOLEAN := TRUE; -- 參數(shù)是否有效
    
    -- 集合類型：存儲(chǔ)日志成員與線程列表（減少視圖訪問(wèn)）
    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. 工具函數(shù)：參數(shù)解析與校驗(yàn)
    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('【參數(shù)錯(cuò)誤】日志大小格式非法！支持：數(shù)字+單位（如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. 工具函數(shù)：路徑處理
    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('【路徑錯(cuò)誤】ASM磁盤(pán)組不存在：'||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('【路徑錯(cuò)誤】文件系統(tǒng)路徑不存在：'||p_path);
            RETURN FALSE;
        WHEN UTL_FILE.ACCESS_DENIED THEN
            DBMS_OUTPUT.PUT_LINE('【權(quán)限錯(cuò)誤】無(wú)權(quán)限訪問(wèn)FS路徑：'||p_path);
            RETURN FALSE;
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('【路徑錯(cuò)誤】校驗(yàn)FS路徑異常：'||SQLERRM);
            RETURN FALSE;
    END;
    
    -- 4. 工具函數(shù)：性能優(yōu)化與等待
    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. 核心過(guò)程：添加日志組
    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，跳過(guò)創(chuàng)建');
            RETURN;
        END IF;
        
        v_sql := 'ALTER DATABASE ADD LOGFILE THREAD '||p_thread||' '''||v_member||''' SIZE '||v_log_size;
        DBMS_OUTPUT.PUT_LINE('【執(zhí)行】添加日志組：'||v_sql);
        EXECUTE IMMEDIATE v_sql;
        DBMS_OUTPUT.PUT_LINE('【成功】線程'||p_thread||'日志組'||p_group_num||'創(chuàng)建完成');
    EXCEPTION
        WHEN OTHERS THEN
            CASE SQLCODE
                WHEN -01658 THEN
                    DBMS_OUTPUT.PUT_LINE('【錯(cuò)誤】線程'||p_thread||'組'||p_group_num||'創(chuàng)建失敗（'||SQLCODE||'）：磁盤(pán)空間不足');
                    DBMS_OUTPUT.PUT_LINE('【建議】需至少'||ROUND(v_log_size/1024/1024,2)||'MB，檢查路徑'||v_full_path||'磁盤(pán)空間');
                WHEN -01031 THEN
                    DBMS_OUTPUT.PUT_LINE('【錯(cuò)誤】線程'||p_thread||'組'||p_group_num||'創(chuàng)建失敗（'||SQLCODE||'）：權(quán)限不足');
                    DBMS_OUTPUT.PUT_LINE('【建議】授予ALTER DATABASE權(quán)限：GRANT ALTER DATABASE TO 當(dāng)前用戶');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('【錯(cuò)誤】線程'||p_thread||'組'||p_group_num||'創(chuàng)建失敗（'||SQLCODE||'）：'||SQLERRM);
                END CASE;
            RAISE;
    END;
    
    -- 6. 核心過(guò)程：刪除小日志組
    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