Oracle Data Guard 物理 Standby 資料庫建立全記錄

環境規劃

1. Listener.ora 雙向配置

-- 主節點
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=A1)(ORACLE_HOME=...)(SID_NAME=A1)))
LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521))(ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY)))

-- 備節點（GLOBAL_DBNAME 必須與主庫一致，便於主庫連線）
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=A1)(ORACLE_HOME=...)(SID_NAME=A1)))
LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.20)(PORT=1521))(ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY)))

2. TNSNAMES.ORA 雙邊一致

A1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=A1)))
A2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.20)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=A2)))

3. 主庫啟用強制歸檔與歸檔模式

ALTER DATABASE FORCE LOGGING;
ARCHIVE LOG LIST;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ARCHIVE LOG LIST;

4. 主庫建立密碼檔並複製至備庫

ORAPWD FILE=$ORACLE_HOME/dbs/orapwa1.ora PASSWORD=xyc ENTRIES=10 FORCE=Y IGNORECASE=Y
SCP $ORACLE_HOME/dbs/orapwa1.ora A2:$ORACLE_HOME/dbs/orapwa2.ora

5. 主庫建立 Standby Redo Log（建議多路徑鏡像）

ALTER DATABASE ADD STANDBY LOGFILE
GROUP 4 ('/u01/app/oracle/oradata/a1/standby_redo04a.log','/u01/app/oracle/oradata/a1/standby_redo04b.log') SIZE 50M,
GROUP 5 ('/u01/app/oracle/oradata/a1/standby_redo05a.log','/u01/app/oracle/oradata/a1/standby_redo05b.log') SIZE 50M,
GROUP 6 ('/u01/app/oracle/oradata/a1/standby_redo06a.log','/u01/app/oracle/oradata/a1/standby_redo06b.log') SIZE 50M,
GROUP 7 ('/u01/app/oracle/oradata/a1/standby_redo07a.log','/u01/app/oracle/oradata/a1/standby_redo07b.log') SIZE 50M;

6. 主庫 PFILE 關鍵參數

*.DB_UNIQUE_NAME=A1
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(A1,A2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch/a1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=A1'
*.LOG_ARCHIVE_DEST_2='SERVICE=A2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=A2'
*.FAL_CLIENT=A1
*.FAL_SERVER=A2
*.STANDBY_FILE_MANAGEMENT=AUTO

7. 備庫 PFILE 關鍵參數（檔名轉換關鍵）

*.DB_UNIQUE_NAME=A2
*.DB_FILE_NAME_CONVERT='/oradata/a1','/oradata/a2'
*.LOG_FILE_NAME_CONVERT='/oradata/a1','/oradata/a2'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch/a2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=A2'
*.LOG_ARCHIVE_DEST_2='SERVICE=A1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=A1'
*.FAL_CLIENT=A2
*.FAL_SERVER=A1
*.STANDBY_FILE_MANAGEMENT=AUTO

8. RMAN 備份主庫並恢復至備庫

-- 主庫
RMAN TARGET /
BACKUP DATABASE FORMAT '/u01/oradata/dbfull_%U';
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oradata/a2/stbycontrol.ctl';
-- 複製控制檔至備庫雙路徑

-- 備庫
STARTUP NOMOUNT PFILE='init_a2.ora'
RMAN TARGET /
RESTORE DATABASE;

9. 啟動驗證

-- 主庫
STARTUP PFILE='init_a1.ora'
ALTER SYSTEM SWITCH LOGFILE;

-- 備庫
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- 稍後
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY 1;  -- APPLIED=YES 同步正常

後續可依需求配置 Data Guard Broker 實現自動化故障切換與集中管理。