萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle Data Guard 邏輯 Standby 搭建全流程 (Physical 轉 Logical)

環境拓撲

角色IPSIDDB_UNIQUE_NAME備註
Primary192.168.1.11testtest
Physical Standby192.168.1.12dgtestdgtest既有
Logical Standby (目標)192.168.1.15logicdglogicdg由 Physical 轉換

前置條件

  • 確認物件類型與 SQL 支援度 (DBMS_LOGSTDBY.SKIP 排除不支援物件)
  • Primary 表需有 PK/唯一索引/非空唯一鍵以唯一識別行

步驟 1:Primary 修改參數並重啟

*.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 Standby 同步正常

-- Physical
SELECT sequence#, applied FROM v$archived_log;
-- Logical (現為 Physical)
SELECT sequence#, applied FROM v$archived_log;

確認 APPLIED = YES 且序號連續。

步驟 3:取消待轉換節點的 Redo Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

步驟 4:Primary 生成 LogMiner 字典

EXEC DBMS_LOGSTDBY.BUILD;

步驟 5:Physical → Logical 轉換

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;

驗證

SELECT * FROM v$logstdby_progress;
SELECT * FROM v$logstdby_state;

確認 APPLIED_SCN 持續推進。

關鍵提醒

  • 轉換前務必備份 Physical Standby
  • Logical Standby 不支援所有資料類型 (如 OBJECT TYPE, XMLTYPE 等)
  • DDL 自動複寫,但部分 DDL 需手動同步
  • 監控 DBA_LOGSTDBY_EVENTS 排查應用錯誤
copyright © 萬盛學電腦網 all rights reserved