Oracle Data Guard 環境主庫 Redo Log 大小調整完整操作手冊

Oracle Data Guard 環境主庫 Redo Log 大小調整完整操作手冊

Data Guard 環境下調整 Redo Log 大小涉及主庫與備庫的 Online Redo Log 與 Standby Redo Log 同步變更，操作順序錯誤易導致同步中斷。本文記錄從 50MB 調整至 60MB 的完整實操步驟，含 RAC 環境 Thread 考量。

環境說明

• 主庫：2 Thread（Thread 1: Group 1-4，Thread 2: Group 13-14）
• 備庫：Standby Redo Log Group 5-10
• 目標：所有 Redo Log 從 50MB 調整為 60MB
• 儲存：ASM (+DATA)

操作步驟總覽

1. 備庫：取消 MRP、新增 Standby Redo Log、刪除舊 Standby Redo Log
2. 主庫：新增 Online Redo Log、刪除舊 Online Redo Log
3. 備庫：設定 standby_file_management=manual、新增 Online Redo Log、清理/刪除舊 Log
4. 主庫：刪除舊 Standby Redo Log、新增新 Standby Redo Log
5. 備庫：啟動 MRP、恢復 standby_file_management=auto

詳細 SQL 指令

第一階段：備庫操作

-- 1. 取消 Managed Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- 2. 新增 Standby Redo Log (60MB x 6 groups)
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('+DATA/orcldg/onlinelog/standby_group_11.log') SIZE 60M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('+DATA/orcldg/onlinelog/standby_group_12.log') SIZE 60M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('+DATA/orcldg/onlinelog/standby_group_13.log') SIZE 60M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('+DATA/orcldg/onlinelog/standby_group_14.log') SIZE 60M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('+DATA/orcldg/onlinelog/standby_group_15.log') SIZE 60M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('+DATA/orcldg/onlinelog/standby_group_16.log') SIZE 60M;

-- 3. 刪除舊 Standby Redo Log (Group 5-10)
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;

第二階段：主庫操作

-- 1. 新增 Online Redo Log (Thread 1: Group 11-12, Thread 2: Group 13-14)
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ('+DATA/orcl/onlinelog/group_11.log') SIZE 60M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 12 ('+DATA/orcl/onlinelog/group_12.log') SIZE 60M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 13 ('+DATA/orcl/onlinelog/group_13.log') SIZE 60M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 14 ('+DATA/orcl/onlinelog/group_14.log') SIZE 60M;

-- 2. 刪除舊 Online Redo Log (Group 1-4)
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;

第三階段：備庫調整 Online Redo Log

-- 關鍵：先設為 manual，否則無法刪除/新增
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' SCOPE=BOTH;

-- 新增 Online Redo Log
ALTER DATABASE ADD LOGFILE GROUP 5 ('+DATA/orcldg/onlinelog/group_05.log') SIZE 60M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('+DATA/orcldg/onlinelog/group_06.log') SIZE 60M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ('+DATA/orcldg/onlinelog/group_07.log') SIZE 60M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ('+DATA/orcldg/onlinelog/group_08.log') SIZE 60M;

-- 若刪除報錯，可先 CLEAR
ALTER DATABASE CLEAR LOGFILE '+DATA/orcldg/onlinelog/group_2.272.862167353';

-- 刪除舊 Online Redo Log
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;

第四階段：主庫調整 Standby Redo Log

-- 刪除舊 Standby Redo Log
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;

-- 新增新 Standby Redo Log (REUSE 避免檔案已存在報錯)
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('+DATA/orcl/onlinelog/standby_group_05.log') SIZE 60M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('+DATA/orcl/onlinelog/standby_group_06.log') SIZE 60M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('+DATA/orcl/onlinelog/standby_group_07.log') SIZE 60M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('+DATA/orcl/onlinelog/standby_group_08.log') SIZE 60M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('+DATA/orcl/onlinelog/standby_group_09.log') SIZE 60M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('+DATA/orcl/onlinelog/standby_group_10.log') SIZE 60M REUSE;

第五階段：備庫恢復同步

-- 啟動 Managed Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

-- 恢復自動檔案管理
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;

關鍵參數檢查

若遇到檔案路徑轉換錯誤，檢查並修正：

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA/orcl/onlinelog,+DATA/orcldg/onlinelog' SCOPE=SPFILE;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA/orcl/datafile,+DATA/orcldg/datafile' SCOPE=SPFILE;

風險提示與最佳實踐

• 操作窗口：建議在業務低峰期執行，預留足夠維護時間
• 備份先行：操作前對 Controlfile、SPFILE 進行備份
• 同步驗證：每階段完成後查詢 v$log、v$standby_log 確認 Group 數量與大小
• RAC 注意：Thread 參數必須正確指定，避免跨 Instance 混淆
• REUSE 子句：ASM 環境下新增 Log 建議加 REUSE 避免檔案衝突