DB2 資料庫日誌歸檔管理：LOGRETAIN=ON 環境下的歸檔策略與 USEREXIT 自動化實戰

3.2 標準 USEREXIT 程式部署 (Linux/AIX)

# 1. 定位範例程式碼
ls /opt/ibm/db2/V11.5/bin/user_exit.rc
# 或
ls /opt/ibm/db2/V11.5/samples/c/user_exit.c

# 2. 編譯 (C 版本，效能最佳)
cd /opt/ibm/db2/V11.5/samples/c
xlc_r -o user_exit user_exit.c  # AIX
gcc -o user_exit user_exit.c    # Linux

# 3. 部署至實例 bin 目錄 (DB2 會自動尋找)
cp user_exit /home/db2inst1/sqllib/bin/
chmod 755 /home/db2inst1/sqllib/bin/user_exit
chown db2inst1:db2iadm1 /home/db2inst1/sqllib/bin/user_exit

3.3 核心配置參數

# 啟用歸檔 (二選一，LOGARCHMETH1 為現代語法)
db2 update db cfg for MYDB using LOGRETAIN ON
# 或
db2 update db cfg for MYDB using LOGARCHMETH1 "DISK:/archive/db2logs/MYDB"
db2 update db cfg for MYDB using LOGARCHMETH2 "VENDOR:/home/db2inst1/sqllib/bin/user_exit"

# 關鍵效能參數
db2 update db cfg for MYDB using NUM_LOGARCH_DBS 2        # 並行歸檔進程數
db2 update db cfg for MYDB using ARCHRETRYDELAY 30        # 重試間隔 (秒)
db2 update db cfg for MYDB using FAILARCHPATH "/archive/db2logs/MYDB/fail"  # 失敗暫存路徑

3.4 企業級 USEREXIT 腳本範例 (Shell + 功能完整)

#!/bin/ksh
# /home/db2inst1/sqllib/bin/user_exit
# DB2 呼叫慣例: user_exit <操作> <日誌檔名> <資料庫別名> <日誌路徑>
# 操作: ARCHIVE / RETRIEVE / DELETE / INIT / TERM

OPERATION=$1
LOGFILE=$2
DBALIAS=$3
LOGPATH=$4

ARCHIVE_ROOT="/archive/db2logs/${DBALIAS}"
FAIL_DIR="${ARCHIVE_ROOT}/failed"
COMPRESS="gzip -9"
ENCRYPT="gpg --encrypt --recipient dba-team@company.com"
MAX_RETRY=3
RETRY_DELAY=10

log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') [$OPERATION] $*" | tee -a /home/db2inst1/sqllib/db2dump/user_exit.log
}

archive_log() {
    local src="${LOGPATH}/${LOGFILE}"
    local dst="${ARCHIVE_ROOT}/${LOGFILE}.gz.gpg"
    local tmp_dst="${dst}.tmp"
    
    mkdir -p "${ARCHIVE_ROOT}"
    
    # 壓縮 + 加密 + 原子性寫入
    if ${COMPRESS} -c "${src}" | ${ENCRYPT} -o "${tmp_dst}"; then
        mv "${tmp_dst}" "${dst}"
        # 驗證完整性
        if gpg --decrypt "${dst}" | gzip -t; then
            log "SUCCESS: Archived ${LOGFILE} to ${dst}"
            return 0
        else
            log "ERROR: Integrity check failed for ${dst}"
            rm -f "${dst}"
            return 1
        fi
    else
        log "ERROR: Compression/Encryption failed for ${LOGFILE}"
        return 1
    fi
}

retrieve_log() {
    local src="${ARCHIVE_ROOT}/${LOGFILE}.gz.gpg"
    local dst="${LOGPATH}/${LOGFILE}"
    
    if [[ ! -f "${src}" ]]; then
        log "ERROR: Archive not found: ${src}"
        return 1
    }
    
    if gpg --decrypt "${src}" | gzip -dc > "${dst}"; then
        log "SUCCESS: Retrieved ${LOGFILE} from archive"
        return 0
    else
        log "ERROR: Decrypt/Decompress failed for ${LOGFILE}"
        rm -f "${dst}"
        return 1
    fi
}

case ${OPERATION} in
    ARCHIVE)
        retry=0
        while [ $retry -lt ${MAX_RETRY} ]; do
            archive_log && exit 0
            retry=$((retry+1))
            log "RETRY ${retry}/${MAX_RETRY} in ${RETRY_DELAY}s"
            sleep ${RETRY_DELAY}
        done
        # 失敗移至失敗目錄供人工處理
        mkdir -p "${FAIL_DIR}"
        cp "${LOGPATH}/${LOGFILE}" "${FAIL_DIR}/"
        log "FAILED: Moved to ${FAIL_DIR} for manual intervention"
        exit 1
        ;;
    RETRIEVE)
        retrieve_log
        exit $?
        ;;
    DELETE)
        # 歸檔保留策略由外部排程管理，此處通常不刪除歸檔檔
        log "INFO: DELETE operation received for ${LOGFILE} (no-op)"
        exit 0
        ;;
    INIT)
        log "INIT: User exit initialized for ${DBALIAS}"
        exit 0
        ;;
    TERM)
        log "TERM: User exit terminated for ${DBALIAS}"
        exit 0
        ;;
    *)
        log "ERROR: Unknown operation ${OPERATION}"
        exit 1
        ;;
esac

四、進階歸檔目標：TSM / Tivoli Storage Manager / Cloud Object Storage

# 使用 TSM API
db2 update db cfg for MYDB using LOGARCHMETH1 "TSM: {TSM_SERVER} {NODE_NAME} {PASSWORD}"

# 使用 VENDOR 庫 (如 NetBackup, CommVault, AWS S3, Azure Blob)
db2 update db cfg for MYDB using LOGARCHMETH1 "VENDOR:/opt/ibm/db2/V11.5/lib/libdb2vendor.so"

# 雙重歸檔 (本地磁碟 + 異地 TSM) - 強烈推薦生產環境
db2 update db cfg for MYDB using LOGARCHMETH1 "DISK:/archive/db2logs/MYDB"
db2 update db cfg for MYDB using LOGARCHMETH2 "TSM: tsm_server node_name password"

五、監控與告警體系

六、災難恢復演練驗證

1. 季度演練：從離線歸檔 (TSM/雲端) 恢復日誌至測試環境，執行 ROLLFORWARD 驗證完整性
2. 關鍵驗證點：日誌檔案連續性 (無缺口)、檢查點完整性、交易一致性、恢復時間 (RTO) 達標
3. 文檔記錄：演練日期、參與人員、恢復步驟、耗時、發現問題、改進措施

七、總結

DB2 日誌歸檔管理的核心原則：自動化優於手工、雙重歸檔優於單一、加密驗證優於裸傳、監控告警優於事後發現、定期演練優於臨時抱佛腳。USEREXIT 程式作為自動化核心，必須具備重試機制、完整性驗證、失敗隔離、詳細審計日誌。將歸檔基礎設施 (磁碟/TSM/雲端) 納入容量規劃與備份策略統一管理，確保在 RPO=0、RTO<分鐘的金融級要求下，日誌鏈永不斷裂，資料永不遺失。