1. 適用場(chǎng)景本文適用于 Oracle 19c 單實(shí)例數(shù)據(jù)庫(kù)搭建單實(shí)例 Physical Standby / Active Data Guard 場(chǎng)景。環(huán)境前提項(xiàng)目主庫(kù)備庫(kù)Oracle版本19.25.0.0.019.25.0.0.0架構(gòu)單實(shí)例單實(shí)例部署方式OracleShellInstallOracleShellInstall當(dāng)前狀態(tài)Oracle 數(shù)據(jù)庫(kù)已完整部署并正常運(yùn)行僅安裝 Oracle 軟件不創(chuàng)建數(shù)據(jù)庫(kù)DG初始化方式RMAN Backup-BasedRMAN Restore RecoverRedo傳輸ASYNC接收并應(yīng)用Protection ModeMaximum PerformanceMaximum Performance最終狀態(tài)READ WRITEREAD ONLY WITH APPLY示例環(huán)境統(tǒng)一使用虛擬地址主庫(kù) 主機(jī)名oradb-primary IP192.168.100.11 備庫(kù) 主機(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ù)庫(kù)名稱規(guī)劃主庫(kù) DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwl 備庫(kù) DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwladg核心原則DB_NAME 主備必須一致 DB_UNIQUE_NAME 主備必須不同2. ADG總體搭建流程標(biāo)準(zhǔn)實(shí)施順序1. 主庫(kù)檢查及DG前置參數(shù) 2. 創(chuàng)建Standby Redo Log 3. 準(zhǔn)備密碼文件 4. 主庫(kù)RMAN初始化備份 5. 最后一輪歸檔備份 6. 備份文件傳輸及校驗(yàn) 7. 備庫(kù)創(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. 打開(kāi)備庫(kù)READ ONLY WITH APPLY 17. 執(zhí)行ADG巡檢 18. 使用測(cè)試表驗(yàn)證實(shí)時(shí)同步3. 主庫(kù)前置檢查登錄主庫(kù)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如果未開(kāi)啟 FORCE LOGGINGALTER DATABASE FORCE LOGGING;設(shè)置ALTER SYSTEM SET standby_file_managementAUTO SCOPEBOTH;4. 創(chuàng)建Standby Redo Log查詢主庫(kù) 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例如主庫(kù)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)備密碼文件主庫(kù)檢查ls -l $ORACLE_HOME/dbs/orapw${ORACLE_SID}將主庫(kù)密碼文件復(fù)制至備庫(kù)scp $ORACLE_HOME/dbs/orapwimipzhwl \ oracle192.168.100.12:$ORACLE_HOME/dbs/備庫(kù)執(zhí)行chown oracle:oinstall $ORACLE_HOME/dbs/orapwimipzhwl chmod 640 $ORACLE_HOME/dbs/orapwimipzhwl主備 SYS 密碼文件必須保持一致。6. 主庫(kù)RMAN初始化備份創(chuàng)建目錄mkdir -p /app/backup/rman_dg_init chown oracle:oinstall /app/backup/rman_dg_init進(jìn)入 RMANrman target /數(shù)據(jù)庫(kù)備份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ù)庫(kù)及控制文件備份完成后再進(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)主庫(kù)cd /app/backup/rman_dg_init sha256sum *.bkp SHA256SUMS傳輸scp /app/backup/rman_dg_init/* \ oracle192.168.100.12:/app/backup/rman_dg_init/備庫(kù)cd /app/backup/rman_dg_init sha256sum -c SHA256SUMS所有文件必須顯示OK9. 備庫(kù)初始化備庫(kù)前提OracleShellInstall 已完成 Oracle 19.25 軟件安裝 未創(chuàng)建數(shù)據(jù)庫(kù) 未創(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)建備庫(kù)最小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ù)庫(kù)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推薦以主庫(kù)參數(shù)為模板生成備庫(kù)正式參數(shù)文件。主庫(kù)CREATE PFILE/tmp/init_imipzhwl_primary.ora FROM SPFILE;復(fù)制到備庫(kù)后調(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主庫(kù) 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) ) )備庫(kù)保持同樣的 TNS Alias。驗(yàn)證tnsping IMIPZHWL tnsping IMIPZHWL_STBY然后分別測(cè)試 SYS 遠(yuǎn)程連接sqlplus sysIMIPZHWL_STBY as sysdba以及sqlplus sysIMIPZHWL as sysdba密碼交互輸入。15. 主庫(kù)Data Guard參數(shù)主庫(kù)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. 備庫(kù)Data Guard參數(shù)備庫(kù)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備庫(kù)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如果需要備庫(kù)在線查詢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說(shuō)明已經(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)即可。主庫(kù)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 備庫(kù)傳輸狀態(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為空備庫(kù)19.3 數(shù)據(jù)庫(kù)狀態(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í)滿足以下條件即可基本判斷正常主庫(kù) PRIMARY READ WRITE DEST_2 VALID ERROR 空 備庫(kù) PHYSICAL STANDBY READ ONLY WITH APPLY MRP0 WAIT_FOR_LOG 或 APPLYING_LOG RFS 正常存在 received_seq 與 applied_seq 基本一致 v$archive_gap 無(wú)記錄不建議單獨(dú)根據(jù)transport lag apply lag一個(gè)指標(biāo)判斷異常。21. 強(qiáng)制日志切換驗(yàn)證需要快速驗(yàn)證主備鏈路時(shí)主庫(kù)必須在CDB$ROOT執(zhí)行ALTER SYSTEM ARCHIVE LOG CURRENT;然后觀察備庫(kù)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. 簡(jiǎn)單測(cè)試表驗(yàn)證ADG同步為了以后快速驗(yàn)證 ADG建議在某個(gè)業(yè)務(wù) PDB 中保留一個(gè)簡(jiǎn)單測(cè)試表。例如CREATE TABLE ADGTEST ( ID NUMBER PRIMARY KEY, TEST_TIME TIMESTAMP DEFAULT SYSTIMESTAMP );主庫(kù)插入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然后去備庫(kù)相同 PDBALTER SESSION SET NLS_TIMESTAMP_FORMATYYYY-MM-DD HH24:MI:SS.FF6; SELECT * FROM ADGTEST;能夠查詢到相同記錄即可非常直觀地驗(yàn)證主庫(kù)寫入 → Redo生成 → Redo傳輸 → Redo Apply → ADG只讀查詢整條鏈路正常。后續(xù)驗(yàn)證可以繼續(xù)INSERT INTO ADGTEST (ID) VALUES (2); COMMIT;備庫(kù)SELECT * FROM ADGTEST ORDER BY ID;23. 常見(jiàn)問(wèn)題ORA-65040如果執(zhí)行ALTER SYSTEM ARCHIVE LOG CURRENT;報(bào)operation not allowed from within a pluggable database說(shuō)明當(dāng)前在 PDB。切回ALTER SESSION SET CONTAINERCDB$ROOT;再執(zhí)行。ORA-65093備庫(kù) 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說(shuō)明正在實(shí)時(shí)應(yīng)用日志也是正常狀態(tài)。v$archive_gap無(wú)記錄說(shuō)明當(dāng)前沒(méi)有歸檔缺口。24. 下一次搭建ADG速查版下一次遇到相同場(chǎng)景可以直接按下面執(zhí)行【主庫(kù)】 1. 確認(rèn)ARCHIVELOG 2. 開(kāi)啟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. 傳輸備份文件 【備庫(kù)】 14. OracleShellInstall只安裝Oracle軟件 15. 不創(chuàng)建數(shù)據(jù)庫(kù) 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雙向測(cè)試 30. SYS雙向連接測(cè)試 31. 配置主庫(kù)DG參數(shù) 32. 配置備庫(kù)DG參數(shù) 【啟動(dòng)同步】 33. 備庫(kù)啟動(dòng)MRP 34. 檢查MRP/RFS 35. 檢查received/applied 36. 檢查archive gap 37. 檢查主庫(kù)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. 主庫(kù)ADGTEST插入數(shù)據(jù) 43. COMMIT 44. 備庫(kù)查詢ADGTEST 45. 數(shù)據(jù)一致即完成