1. 適用場景本文適用于 Oracle 19c 單實(shí)例數(shù)據(jù)庫搭建單實(shí)例 Physical Standby / Active Data Guard 場景。環(huán)境前提項(xiàng)目主庫備庫Oracle版本19.25.0.0.019.25.0.0.0架構(gòu)單實(shí)例單實(shí)例部署方式OracleShellInstallOracleShellInstall當(dāng)前狀態(tài)Oracle 數(shù)據(jù)庫已完整部署并正常運(yùn)行僅安裝 Oracle 軟件不創(chuàng)建數(shù)據(jù)庫DG初始化方式RMAN Backup-BasedRMAN Restore RecoverRedo傳輸ASYNC接收并應(yīng)用Protection ModeMaximum PerformanceMaximum Performance最終狀態(tài)READ WRITEREAD ONLY WITH APPLY示例環(huán)境統(tǒng)一使用虛擬地址主庫 主機(jī)名oradb-primary IP192.168.100.11 備庫 主機(jī)名oradb-standby IP192.168.100.12Oracle目錄ORACLE_BASE/app/app/oracle ORACLE_HOME/app/app/oracle/product/19.3.0/db ORACLE_SIDimipzhwl 數(shù)據(jù)目錄 /app/data 歸檔目錄 /app/archivelog RMAN初始化目錄 /app/backup/rman_dg_init數(shù)據(jù)庫名稱規(guī)劃主庫 DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwl 備庫 DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwladg核心原則DB_NAME 主備必須一致 DB_UNIQUE_NAME 主備必須不同2. ADG總體搭建流程標(biāo)準(zhǔn)實(shí)施順序1. 主庫檢查及DG前置參數(shù) 2. 創(chuàng)建Standby Redo Log 3. 準(zhǔn)備密碼文件 4. 主庫RMAN初始化備份 5. 最后一輪歸檔備份 6. 備份文件傳輸及校驗(yàn) 7. 備庫創(chuàng)建最小PFILE 8. 恢復(fù)Standby Controlfile 9. Restore Database 10. Recover Database 11. 創(chuàng)建正式Standby SPFILE 12. 配置主備Oracle Net 13. 配置主備DG參數(shù) 14. 啟動(dòng)MRP 15. 驗(yàn)證Redo傳輸及應(yīng)用 16. 打開備庫READ ONLY WITH APPLY 17. 執(zhí)行ADG巡檢 18. 使用測試表驗(yàn)證實(shí)時(shí)同步3. 主庫前置檢查登錄主庫su - oracle sqlplus / as sysdba確認(rèn)set lines 200 select db_unique_name, database_role, open_mode, log_mode, force_logging from v$database;要求DATABASE_ROLE PRIMARY OPEN_MODE READ WRITE LOG_MODE ARCHIVELOG檢查關(guān)鍵參數(shù)show parameter db_name show parameter db_unique_name show parameter db_create_file_dest show parameter standby_file_management show parameter remote_login_passwordfile建議remote_login_passwordfile EXCLUSIVE standby_file_management AUTO如果未開啟 FORCE LOGGINGALTER DATABASE FORCE LOGGING;設(shè)置ALTER SYSTEM SET standby_file_managementAUTO SCOPEBOTH;4. 創(chuàng)建Standby Redo Log查詢主庫 Online Redoselect group#, thread#, bytes/1024/1024 size_mb, status from v$log order by group#;原則SRL大小 Online Redo大小 SRL數(shù)量 每個(gè)Thread的Online Redo組數(shù) 1例如主庫8組 Online Redo 每組1024MB Thread 1則創(chuàng)建9組 Standby Redo Log 每組1024MB執(zhí)行ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;按所需數(shù)量重復(fù)執(zhí)行。檢查select group#, thread#, bytes/1024/1024 size_mb, status from v$standby_log order by group#;5. 準(zhǔn)備密碼文件主庫檢查ls -l $ORACLE_HOME/dbs/orapw${ORACLE_SID}將主庫密碼文件復(fù)制至備庫scp $ORACLE_HOME/dbs/orapwimipzhwl \ oracle192.168.100.12:$ORACLE_HOME/dbs/備庫執(zhí)行chown oracle:oinstall $ORACLE_HOME/dbs/orapwimipzhwl chmod 640 $ORACLE_HOME/dbs/orapwimipzhwl主備 SYS 密碼文件必須保持一致。6. 主庫RMAN初始化備份創(chuàng)建目錄mkdir -p /app/backup/rman_dg_init chown oracle:oinstall /app/backup/rman_dg_init進(jìn)入 RMANrman target /數(shù)據(jù)庫備份BACKUP DATABASE FORMAT /app/backup/rman_dg_init/db_%d_%T_%s_%p.bkp;歸檔備份BACKUP ARCHIVELOG ALL FORMAT /app/backup/rman_dg_init/arc_%d_%T_%s_%p.bkp;Standby ControlfileBACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT /app/backup/rman_dg_init/standby_ctl_%d_%T_%s_%p.bkp;SPFILEBACKUP SPFILE FORMAT /app/backup/rman_dg_init/spfile_%d_%T_%s_%p.bkp;7. 最后一輪歸檔備份數(shù)據(jù)庫及控制文件備份完成后再進(jìn)行一次日志切換避免備份期間產(chǎn)生的 redo 未包含在初始化備份中。確認(rèn)當(dāng)前在CDB$ROOT執(zhí)行ALTER SYSTEM ARCHIVE LOG CURRENT;再進(jìn)入 RMANBACKUP ARCHIVELOG ALL FORMAT /app/backup/rman_dg_init/arc_final_%d_%T_%s_%p.bkp;8. 備份文件傳輸及校驗(yàn)主庫cd /app/backup/rman_dg_init sha256sum *.bkp SHA256SUMS傳輸scp /app/backup/rman_dg_init/* \ oracle192.168.100.12:/app/backup/rman_dg_init/備庫cd /app/backup/rman_dg_init sha256sum -c SHA256SUMS所有文件必須顯示OK9. 備庫初始化備庫前提OracleShellInstall 已完成 Oracle 19.25 軟件安裝 未創(chuàng)建數(shù)據(jù)庫 未創(chuàng)建CDB/PDB環(huán)境變量export ORACLE_BASE/app/app/oracle export ORACLE_HOME/app/app/oracle/product/19.3.0/db export ORACLE_SIDimipzhwl export PATH$ORACLE_HOME/bin:$PATH創(chuàng)建目錄mkdir -p /app/data/IMIPZHWL mkdir -p /app/data/IMIPZHWL/pdbseed mkdir -p /app/data/IMIPZHWL/onlinelog mkdir -p /app/archivelog mkdir -p /app/app/oracle/admin/imipzhwl/adump chown -R oracle:oinstall /app/data chown -R oracle:oinstall /app/archivelog chown -R oracle:oinstall /app/app/oracle/admin10. 創(chuàng)建備庫最小PFILE文件$ORACLE_HOME/dbs/initimipzhwl.ora內(nèi)容*.db_nameimipzhwl *.db_unique_nameimipzhwladg *.enable_pluggable_databaseTRUE *.db_create_file_dest/app/data *.control_files/app/data/IMIPZHWL/control01.ctl, /app/data/IMIPZHWL/control02.ctl *.log_archive_dest_1LOCATION/app/archivelog *.standby_file_managementAUTO啟動(dòng)startup nomount pfile$ORACLE_HOME/dbs/initimipzhwl.ora;11. 恢復(fù)Standby Controlfile進(jìn)入 RMANrman target /執(zhí)行RESTORE STANDBY CONTROLFILE FROM /app/backup/rman_dg_init/standby_ctl_xxx.bkp;然后ALTER DATABASE MOUNT;12. Catalog并恢復(fù)數(shù)據(jù)庫CatalogCATALOG START WITH /app/backup/rman_dg_init/ NOPROMPT;建議先RESTORE DATABASE PREVIEW;正式恢復(fù)RESTORE DATABASE;恢復(fù) redoRECOVER DATABASE;如果根據(jù)備份鏈需要指定恢復(fù)點(diǎn)可采用RECOVER DATABASE UNTIL SCN 目標(biāo)SCN;成功應(yīng)看到media recovery complete檢查select * from v$recover_file;正常no rows selected13. 創(chuàng)建正式Standby SPFILE推薦以主庫參數(shù)為模板生成備庫正式參數(shù)文件。主庫CREATE PFILE/tmp/init_imipzhwl_primary.ora FROM SPFILE;復(fù)制到備庫后調(diào)整以下核心參數(shù)*.db_unique_nameimipzhwladg *.service_namesimipzhwladg *.local_listener (ADDRESS(PROTOCOLTCP)(HOST192.168.100.12)(PORT1521)) *.log_archive_config DG_CONFIG(imipzhwl,imipzhwladg) *.log_archive_dest_1 LOCATION/app/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEimipzhwladg *.log_archive_dest_2 SERVICEIMIPZHWL ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEimipzhwl *.log_archive_dest_state_1ENABLE *.log_archive_dest_state_2ENABLE *.fal_serverIMIPZHWL *.standby_file_managementAUTO創(chuàng)建 SPFILECREATE SPFILE /app/app/oracle/product/19.3.0/db/dbs/spfileimipzhwl.ora FROM PFILE /app/app/oracle/product/19.3.0/db/dbs/initimipzhwl_stby.ora;然后shutdown immediate; startup mount;14. 配置Oracle Net主庫 tnsnames.oraIMIPZHWL (DESCRIPTION (ADDRESS (PROTOCOL TCP) (HOST 192.168.100.11) (PORT 1521) ) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME imipzhwl) ) ) IMIPZHWL_STBY (DESCRIPTION (ADDRESS (PROTOCOL TCP) (HOST 192.168.100.12) (PORT 1521) ) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME imipzhwladg) ) )備庫保持同樣的 TNS Alias。驗(yàn)證tnsping IMIPZHWL tnsping IMIPZHWL_STBY然后分別測試 SYS 遠(yuǎn)程連接sqlplus sysIMIPZHWL_STBY as sysdba以及sqlplus sysIMIPZHWL as sysdba密碼交互輸入。15. 主庫Data Guard參數(shù)主庫ALTER SYSTEM SET log_archive_config DG_CONFIG(imipzhwl,imipzhwladg) SCOPEBOTH;ALTER SYSTEM SET log_archive_dest_1 LOCATION/app/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEimipzhwl SCOPEBOTH;ALTER SYSTEM SET log_archive_dest_2 SERVICEIMIPZHWL_STBY ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEimipzhwladg SCOPEBOTH;ALTER SYSTEM SET log_archive_dest_state_1ENABLE SCOPEBOTH; ALTER SYSTEM SET log_archive_dest_state_2ENABLE SCOPEBOTH;ALTER SYSTEM SET fal_serverIMIPZHWL_STBY SCOPEBOTH;ALTER SYSTEM SET standby_file_managementAUTO SCOPEBOTH;16. 備庫Data Guard參數(shù)備庫ALTER SYSTEM SET log_archive_config DG_CONFIG(imipzhwl,imipzhwladg) SCOPEBOTH;ALTER SYSTEM SET log_archive_dest_1 LOCATION/app/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEimipzhwladg SCOPEBOTH;ALTER SYSTEM SET log_archive_dest_2 SERVICEIMIPZHWL ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEimipzhwl SCOPEBOTH;ALTER SYSTEM SET log_archive_dest_state_1ENABLE SCOPEBOTH; ALTER SYSTEM SET log_archive_dest_state_2ENABLE SCOPEBOTH;ALTER SYSTEM SET fal_serverIMIPZHWL SCOPEBOTH;ALTER SYSTEM SET standby_file_managementAUTO SCOPEBOTH;17. 啟動(dòng)Redo Apply備庫ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;此時(shí)已經(jīng)進(jìn)入標(biāo)準(zhǔn) Physical Standby 同步狀態(tài)。18. 轉(zhuǎn)為Active Data Guard如果需要備庫在線查詢ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;ALTER DATABASE OPEN READ ONLY;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;檢查select database_role, open_mode from v$database;正常應(yīng)為PHYSICAL STANDBY READ ONLY WITH APPLY說明已經(jīng)進(jìn)入 Active Data Guard Real-Time Query 狀態(tài)。生產(chǎn)使用前需確認(rèn) Active Data Guard Option 授權(quán)。19. ADG核心巡檢后續(xù)日常巡檢不需要執(zhí)行大量 SQL核心看以下幾項(xiàng)即可。主庫19.1 基本狀態(tài)select db_unique_name, database_role, open_mode, protection_mode from v$database;應(yīng)PRIMARY READ WRITE MAXIMUM PERFORMANCE19.2 備庫傳輸狀態(tài)select dest_id, status, target, destination, error, db_unique_name from v$archive_dest where dest_id in (1,2);重點(diǎn)看DEST_ID2 STATUSVALID TARGETSTANDBY ERROR為空備庫19.3 數(shù)據(jù)庫狀態(tài)select db_unique_name, database_role, open_mode from v$database;應(yīng)PHYSICAL STANDBY READ ONLY WITH APPLY19.4 MRP/RFS狀態(tài)select process, status, thread#, sequence# from v$managed_standby where process in (MRP0,RFS) order by process;正常 MRPWAIT_FOR_LOG或者APPLYING_LOG19.5 接收及應(yīng)用序列select thread#, max(sequence#) received_seq, max(case when appliedYES then sequence# end) applied_seq from v$archived_log group by thread#;正常received_seq ≈ applied_seq19.6 Archive Gapselect * from v$archive_gap;正常no rows selected20. ADG快速判斷標(biāo)準(zhǔn)同時(shí)滿足以下條件即可基本判斷正常主庫 PRIMARY READ WRITE DEST_2 VALID ERROR 空 備庫 PHYSICAL STANDBY READ ONLY WITH APPLY MRP0 WAIT_FOR_LOG 或 APPLYING_LOG RFS 正常存在 received_seq 與 applied_seq 基本一致 v$archive_gap 無記錄不建議單獨(dú)根據(jù)transport lag apply lag一個(gè)指標(biāo)判斷異常。21. 強(qiáng)制日志切換驗(yàn)證需要快速驗(yàn)證主備鏈路時(shí)主庫必須在CDB$ROOT執(zhí)行ALTER SYSTEM ARCHIVE LOG CURRENT;然后觀察備庫select process, status, thread#, sequence# from v$managed_standby where process in (MRP0,RFS);以及select thread#, max(sequence#) received_seq, max(case when appliedYES then sequence# end) applied_seq from v$archived_log group by thread#;序列推進(jìn)即可證明 redo transport/apply 正常。22. 簡單測試表驗(yàn)證ADG同步為了以后快速驗(yàn)證 ADG建議在某個(gè)業(yè)務(wù) PDB 中保留一個(gè)簡單測試表。例如CREATE TABLE ADGTEST ( ID NUMBER PRIMARY KEY, TEST_TIME TIMESTAMP DEFAULT SYSTIMESTAMP );主庫插入INSERT INTO ADGTEST (ID) VALUES (1); COMMIT;查詢ALTER SESSION SET NLS_TIMESTAMP_FORMATYYYY-MM-DD HH24:MI:SS.FF6; SELECT * FROM ADGTEST;例如ID TEST_TIME 1 2026-10-06 17:18:08.294606然后去備庫相同 PDBALTER SESSION SET NLS_TIMESTAMP_FORMATYYYY-MM-DD HH24:MI:SS.FF6; SELECT * FROM ADGTEST;能夠查詢到相同記錄即可非常直觀地驗(yàn)證主庫寫入 → Redo生成 → Redo傳輸 → Redo Apply → ADG只讀查詢整條鏈路正常。后續(xù)驗(yàn)證可以繼續(xù)INSERT INTO ADGTEST (ID) VALUES (2); COMMIT;備庫SELECT * FROM ADGTEST ORDER BY ID;23. 常見問題ORA-65040如果執(zhí)行ALTER SYSTEM ARCHIVE LOG CURRENT;報(bào)operation not allowed from within a pluggable database說明當(dāng)前在 PDB。切回ALTER SESSION SET CONTAINERCDB$ROOT;再執(zhí)行。ORA-65093備庫 CDB 啟動(dòng)時(shí)報(bào)錯(cuò)時(shí)檢查*.enable_pluggable_databaseTRUEMRP0 WAIT_FOR_LOG這是正常狀態(tài)表示當(dāng)前Redo已經(jīng)應(yīng)用完成 正在等待新的日志不是故障。MRP0 APPLYING_LOG說明正在實(shí)時(shí)應(yīng)用日志也是正常狀態(tài)。v$archive_gap無記錄說明當(dāng)前沒有歸檔缺口。24. 下一次搭建ADG速查版下一次遇到相同場景可以直接按下面執(zhí)行【主庫】 1. 確認(rèn)ARCHIVELOG 2. 開啟FORCE LOGGING 3. standby_file_managementAUTO 4. 創(chuàng)建SRL 5. 檢查password file 6. RMAN BACKUP DATABASE 7. RMAN BACKUP ARCHIVELOG 8. BACKUP STANDBY CONTROLFILE 9. BACKUP SPFILE 10. ARCHIVE LOG CURRENT 11. 再備份一次ARCHIVELOG 12. SHA256 13. 傳輸備份文件 【備庫】 14. OracleShellInstall只安裝Oracle軟件 15. 不創(chuàng)建數(shù)據(jù)庫 16. 配置ORACLE_SID 17. 創(chuàng)建目錄 18. 復(fù)制password file 19. 創(chuàng)建最小PFILE 20. startup nomount 21. restore standby controlfile 22. mount 23. catalog backup 24. restore database 25. recover database 26. 創(chuàng)建正式standby SPFILE 27. startup mount 【主備配置】 28. 配置listener/tnsnames 29. tnsping雙向測試 30. SYS雙向連接測試 31. 配置主庫DG參數(shù) 32. 配置備庫DG參數(shù) 【啟動(dòng)同步】 33. 備庫啟動(dòng)MRP 34. 檢查MRP/RFS 35. 檢查received/applied 36. 檢查archive gap 37. 檢查主庫DEST_2 【轉(zhuǎn)ADG】 38. cancel MRP 39. open read only 40. restart MRP 41. 確認(rèn)READ ONLY WITH APPLY 【最終驗(yàn)證】 42. 主庫ADGTEST插入數(shù)據(jù) 43. COMMIT 44. 備庫查詢ADGTEST 45. 數(shù)據(jù)一致即完成