架構拓撲
| 角色 | IP | SID | DB_UNIQUE_NAME | 備註 |
|---|
| Primary | 192.168.1.11 | test | test | 主庫 |
| Physical Standby | 192.168.1.12 | dgtest | dgtest | 物理備庫 |
| Logical Standby (目標) | 192.168.1.15 | logicdg | logicdg | 由 Physical 轉換 |
前置條件
- 確認對象類型與 SQL 語句支援邏輯備庫 (參考 `DBMS_LOGSTDBY` 文檔)
- 主庫表需有主鍵或唯一索引 (行唯一識別)
- Physical Standby 已同步正常 (無適用延遲)
步驟 1：修改 Primary 參數 (新增 LOGICAL 目標)
*.log_archive_config = 'dg_config=(test,dgtest,logicdg)'
*.log_archive_dest_2 = 'service=test12 arch valid_for=(online_logfiles,primary_role) db_unique_name=dgtest'
*.log_archive_dest_3 = 'service=test15 arch valid_for=(online_logfiles,primary_role) db_unique_name=logicdg'
*.log_archive_dest_state_2 = defer
*.log_archive_dest_state_3 = defer
*.fal_server = test11
*.fal_client = test12
*.standby_file_management = auto
*.db_file_name_convert = '/oracle/oradata/test','/oracle/oradata/dgtest/dgtest','/oracle/oradata/test','/oracle/oradata/logicdg'
*.log_file_name_convert = '/oracle/oradata/test','/oracle/oradata/dgtest/dgtest','/oracle/oradata/test','/oracle/oradata/logicdg'
步驟 2：驗證兩備庫同步狀態
-- Physical
SELECT sequence#, applied FROM v$archived_log;
-- SEQUENCE# APPLIED
-- 66 YES ... 72 YES
-- Logical (目前仍為 Physical)
SELECT sequence#, applied FROM v$archived_log;
-- 67 YES ... 72 YES
步驟 3：取消待轉換 Physical Standby 的 Redo Apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
步驟 4：Primary 生成數據字典 (邏輯備庫必需)
EXECUTE DBMS_LOGSTDBY.BUILD;
步驟 5：Physical 轉換為 Logical Standby
ALTER DATABASE RECOVER TO LOGICAL STANDBY logicdg;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
步驟 6：調整 Logical Standby 參數
ALTER SYSTEM SET log_archive_dest_1 = 'location=/oracle/oradata/logicdg/local-archive valid_for=(online_logfiles,all_roles) db_unique_name=logicdg';
ALTER SYSTEM SET log_archive_dest_5 = 'location=/oracle/oradata/logicdg/archive valid_for=(standby_logfiles,standby_role) db_unique_name=logicdg';
步驟 7：RESETLOGS 方式開啟
ALTER DATABASE OPEN RESETLOGS;
步驟 8：建立 Standby Redo Log 並啟動 SQL Apply
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/oracle/oradata/logicdg/standbyredo11.log' SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/oracle/oradata/logicdg/standbyredo12.log' SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/oracle/oradata/logicdg/standbyredo13.log' SIZE 100M;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
驗證邏輯備庫正常
-- 檢查 SQL Apply 進程
SELECT process, status, sequence# FROM v$managed_standby WHERE process LIKE 'LSP%';
-- 檢查適用延遲
SELECT name, value, datum_time FROM v$dataguard_stats WHERE name LIKE 'apply%';
關鍵差異：Physical vs Logical Standby
| 特性 | Physical Standby | Logical Standby |
|---|
| 同步機制 | Redo Apply (塊級) | SQL Apply (邏輯級) |
| 可讀性 | Active Data Guard (需授權) | 原生可讀寫 (報表、ETL) |
| 對象支援 | 全支援 | 部分不支援 (參考文檔) |
| 轉換方向 | → Logical (單向) | 不可轉回 Physical |
| 主鍵要求 | 不強制 | 強制 (行識別) |
常見問題排查
- `ORA-16191`：Primary 未執行 `DBMS_LOGSTDBY.BUILD`
- `ORA-16228`：缺少 Standby Redo Log
- SQL Apply 停止：查 `DBA_LOGSTDBY_EVENTS` 找錯誤 SQL，修正後 `ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;`
- 主庫 DDL 不同步：邏輯備庫不支援的 DDL 需手動在備庫執行
