1. 适用场景本文适用于 Oracle 19c 单实例数据库搭建单实例 Physical Standby / Active Data Guard 场景。环境前提项目主库备库Oracle版本19.25.0.0.019.25.0.0.0架构单实例单实例部署方式OracleShellInstallOracleShellInstall当前状态Oracle 数据库已完整部署并正常运行仅安装 Oracle 软件不创建数据库DG初始化方式RMAN Backup-BasedRMAN Restore RecoverRedo传输ASYNC接收并应用Protection ModeMaximum PerformanceMaximum Performance最终状态READ WRITEREAD ONLY WITH APPLY示例环境统一使用虚拟地址主库 主机名oradb-primary IP192.168.100.11 备库 主机名oradb-standby IP192.168.100.12Oracle目录ORACLE_BASE/app/app/oracle ORACLE_HOME/app/app/oracle/product/19.3.0/db ORACLE_SIDimipzhwl 数据目录 /app/data 归档目录 /app/archivelog RMAN初始化目录 /app/backup/rman_dg_init数据库名称规划主库 DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwl 备库 DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwladg核心原则DB_NAME 主备必须一致 DB_UNIQUE_NAME 主备必须不同2. ADG总体搭建流程标准实施顺序1. 主库检查及DG前置参数 2. 创建Standby Redo Log 3. 准备密码文件 4. 主库RMAN初始化备份 5. 最后一轮归档备份 6. 备份文件传输及校验 7. 备库创建最小PFILE 8. 恢复Standby Controlfile 9. Restore Database 10. Recover Database 11. 创建正式Standby SPFILE 12. 配置主备Oracle Net 13. 配置主备DG参数 14. 启动MRP 15. 验证Redo传输及应用 16. 打开备库READ ONLY WITH APPLY 17. 执行ADG巡检 18. 使用测试表验证实时同步3. 主库前置检查登录主库su - oracle sqlplus / as sysdba确认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检查关键参数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;设置ALTER SYSTEM SET standby_file_managementAUTO SCOPEBOTH;4. 创建Standby Redo Log查询主库 Online Redoselect group#, thread#, bytes/1024/1024 size_mb, status from v$log order by group#;原则SRL大小 Online Redo大小 SRL数量 每个Thread的Online Redo组数 1例如主库8组 Online Redo 每组1024MB Thread 1则创建9组 Standby Redo Log 每组1024MB执行ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;按所需数量重复执行。检查select group#, thread#, bytes/1024/1024 size_mb, status from v$standby_log order by group#;5. 准备密码文件主库检查ls -l $ORACLE_HOME/dbs/orapw${ORACLE_SID}将主库密码文件复制至备库scp $ORACLE_HOME/dbs/orapwimipzhwl \ oracle192.168.100.12:$ORACLE_HOME/dbs/备库执行chown oracle:oinstall $ORACLE_HOME/dbs/orapwimipzhwl chmod 640 $ORACLE_HOME/dbs/orapwimipzhwl主备 SYS 密码文件必须保持一致。6. 主库RMAN初始化备份创建目录mkdir -p /app/backup/rman_dg_init chown oracle:oinstall /app/backup/rman_dg_init进入 RMANrman target /数据库备份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. 最后一轮归档备份数据库及控制文件备份完成后再进行一次日志切换避免备份期间产生的 redo 未包含在初始化备份中。确认当前在CDB$ROOT执行ALTER SYSTEM ARCHIVE LOG CURRENT;再进入 RMANBACKUP ARCHIVELOG ALL FORMAT /app/backup/rman_dg_init/arc_final_%d_%T_%s_%p.bkp;8. 备份文件传输及校验主库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 软件安装 未创建数据库 未创建CDB/PDB环境变量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创建目录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. 创建备库最小PFILE文件$ORACLE_HOME/dbs/initimipzhwl.ora内容*.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启动startup nomount pfile$ORACLE_HOME/dbs/initimipzhwl.ora;11. 恢复Standby Controlfile进入 RMANrman target /执行RESTORE STANDBY CONTROLFILE FROM /app/backup/rman_dg_init/standby_ctl_xxx.bkp;然后ALTER DATABASE MOUNT;12. Catalog并恢复数据库CatalogCATALOG START WITH /app/backup/rman_dg_init/ NOPROMPT;建议先RESTORE DATABASE PREVIEW;正式恢复RESTORE DATABASE;恢复 redoRECOVER DATABASE;如果根据备份链需要指定恢复点可采用RECOVER DATABASE UNTIL SCN 目标SCN;成功应看到media recovery complete检查select * from v$recover_file;正常no rows selected13. 创建正式Standby SPFILE推荐以主库参数为模板生成备库正式参数文件。主库CREATE PFILE/tmp/init_imipzhwl_primary.ora FROM SPFILE;复制到备库后调整以下核心参数*.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创建 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。验证tnsping IMIPZHWL tnsping IMIPZHWL_STBY然后分别测试 SYS 远程连接sqlplus sysIMIPZHWL_STBY as sysdba以及sqlplus sysIMIPZHWL as sysdba密码交互输入。15. 主库Data Guard参数主库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参数备库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. 启动Redo Apply备库ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;此时已经进入标准 Physical Standby 同步状态。18. 转为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;正常应为PHYSICAL STANDBY READ ONLY WITH APPLY说明已经进入 Active Data Guard Real-Time Query 状态。生产使用前需确认 Active Data Guard Option 授权。19. ADG核心巡检后续日常巡检不需要执行大量 SQL核心看以下几项即可。主库19.1 基本状态select db_unique_name, database_role, open_mode, protection_mode from v$database;应PRIMARY READ WRITE MAXIMUM PERFORMANCE19.2 备库传输状态select dest_id, status, target, destination, error, db_unique_name from v$archive_dest where dest_id in (1,2);重点看DEST_ID2 STATUSVALID TARGETSTANDBY ERROR为空备库19.3 数据库状态select db_unique_name, database_role, open_mode from v$database;应PHYSICAL STANDBY READ ONLY WITH APPLY19.4 MRP/RFS状态select process, status, thread#, sequence# from v$managed_standby where process in (MRP0,RFS) order by process;正常 MRPWAIT_FOR_LOG或者APPLYING_LOG19.5 接收及应用序列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快速判断标准同时满足以下条件即可基本判断正常主库 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 无记录不建议单独根据transport lag apply lag一个指标判断异常。21. 强制日志切换验证需要快速验证主备链路时主库必须在CDB$ROOT执行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#;序列推进即可证明 redo transport/apply 正常。22. 简单测试表验证ADG同步为了以后快速验证 ADG建议在某个业务 PDB 中保留一个简单测试表。例如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;能够查询到相同记录即可非常直观地验证主库写入 → Redo生成 → Redo传输 → Redo Apply → ADG只读查询整条链路正常。后续验证可以继续INSERT INTO ADGTEST (ID) VALUES (2); COMMIT;备库SELECT * FROM ADGTEST ORDER BY ID;23. 常见问题ORA-65040如果执行ALTER SYSTEM ARCHIVE LOG CURRENT;报operation not allowed from within a pluggable database说明当前在 PDB。切回ALTER SESSION SET CONTAINERCDB$ROOT;再执行。ORA-65093备库 CDB 启动时报错时检查*.enable_pluggable_databaseTRUEMRP0 WAIT_FOR_LOG这是正常状态表示当前Redo已经应用完成 正在等待新的日志不是故障。MRP0 APPLYING_LOG说明正在实时应用日志也是正常状态。v$archive_gap无记录说明当前没有归档缺口。24. 下一次搭建ADG速查版下一次遇到相同场景可以直接按下面执行【主库】 1. 确认ARCHIVELOG 2. 开启FORCE LOGGING 3. standby_file_managementAUTO 4. 创建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. 不创建数据库 16. 配置ORACLE_SID 17. 创建目录 18. 复制password file 19. 创建最小PFILE 20. startup nomount 21. restore standby controlfile 22. mount 23. catalog backup 24. restore database 25. recover database 26. 创建正式standby SPFILE 27. startup mount 【主备配置】 28. 配置listener/tnsnames 29. tnsping双向测试 30. SYS双向连接测试 31. 配置主库DG参数 32. 配置备库DG参数 【启动同步】 33. 备库启动MRP 34. 检查MRP/RFS 35. 检查received/applied 36. 检查archive gap 37. 检查主库DEST_2 【转ADG】 38. cancel MRP 39. open read only 40. restart MRP 41. 确认READ ONLY WITH APPLY 【最终验证】 42. 主库ADGTEST插入数据 43. COMMIT 44. 备库查询ADGTEST 45. 数据一致即完成 
长河君 · 2026/10/8 21:47:16 · 阅读 1.2k Oracle 19c 单机到单机 Active Data Guard 搭建与巡检操作手册
1. 适用场景本文适用于 Oracle 19c 单实例数据库搭建单实例 Physical Standby / Active Data Guard 场景。环境前提项目主库备库Oracle版本19.25.0.0.019.25.0.0.0架构单实例单实例部署方式OracleShellInstallOracleShellInstall当前状态Oracle 数据库已完整部署并正常运行仅安装 Oracle 软件不创建数据库DG初始化方式RMAN Backup-BasedRMAN Restore RecoverRedo传输ASYNC接收并应用Protection ModeMaximum PerformanceMaximum Performance最终状态READ WRITEREAD ONLY WITH APPLY示例环境统一使用虚拟地址主库 主机名oradb-primary IP192.168.100.11 备库 主机名oradb-standby IP192.168.100.12Oracle目录ORACLE_BASE/app/app/oracle ORACLE_HOME/app/app/oracle/product/19.3.0/db ORACLE_SIDimipzhwl 数据目录 /app/data 归档目录 /app/archivelog RMAN初始化目录 /app/backup/rman_dg_init数据库名称规划主库 DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwl 备库 DB_NAMEimipzhwl DB_UNIQUE_NAMEimipzhwladg核心原则DB_NAME 主备必须一致 DB_UNIQUE_NAME 主备必须不同2. ADG总体搭建流程标准实施顺序1. 主库检查及DG前置参数 2. 创建Standby Redo Log 3. 准备密码文件 4. 主库RMAN初始化备份 5. 最后一轮归档备份 6. 备份文件传输及校验 7. 备库创建最小PFILE 8. 恢复Standby Controlfile 9. Restore Database 10. Recover Database 11. 创建正式Standby SPFILE 12. 配置主备Oracle Net 13. 配置主备DG参数 14. 启动MRP 15. 验证Redo传输及应用 16. 打开备库READ ONLY WITH APPLY 17. 执行ADG巡检 18. 使用测试表验证实时同步3. 主库前置检查登录主库su - oracle sqlplus / as sysdba确认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检查关键参数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;设置ALTER SYSTEM SET standby_file_managementAUTO SCOPEBOTH;4. 创建Standby Redo Log查询主库 Online Redoselect group#, thread#, bytes/1024/1024 size_mb, status from v$log order by group#;原则SRL大小 Online Redo大小 SRL数量 每个Thread的Online Redo组数 1例如主库8组 Online Redo 每组1024MB Thread 1则创建9组 Standby Redo Log 每组1024MB执行ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;按所需数量重复执行。检查select group#, thread#, bytes/1024/1024 size_mb, status from v$standby_log order by group#;5. 准备密码文件主库检查ls -l $ORACLE_HOME/dbs/orapw${ORACLE_SID}将主库密码文件复制至备库scp $ORACLE_HOME/dbs/orapwimipzhwl \ oracle192.168.100.12:$ORACLE_HOME/dbs/备库执行chown oracle:oinstall $ORACLE_HOME/dbs/orapwimipzhwl chmod 640 $ORACLE_HOME/dbs/orapwimipzhwl主备 SYS 密码文件必须保持一致。6. 主库RMAN初始化备份创建目录mkdir -p /app/backup/rman_dg_init chown oracle:oinstall /app/backup/rman_dg_init进入 RMANrman target /数据库备份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. 最后一轮归档备份数据库及控制文件备份完成后再进行一次日志切换避免备份期间产生的 redo 未包含在初始化备份中。确认当前在CDB$ROOT执行ALTER SYSTEM ARCHIVE LOG CURRENT;再进入 RMANBACKUP ARCHIVELOG ALL FORMAT /app/backup/rman_dg_init/arc_final_%d_%T_%s_%p.bkp;8. 备份文件传输及校验主库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 软件安装 未创建数据库 未创建CDB/PDB环境变量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创建目录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. 创建备库最小PFILE文件$ORACLE_HOME/dbs/initimipzhwl.ora内容*.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启动startup nomount pfile$ORACLE_HOME/dbs/initimipzhwl.ora;11. 恢复Standby Controlfile进入 RMANrman target /执行RESTORE STANDBY CONTROLFILE FROM /app/backup/rman_dg_init/standby_ctl_xxx.bkp;然后ALTER DATABASE MOUNT;12. Catalog并恢复数据库CatalogCATALOG START WITH /app/backup/rman_dg_init/ NOPROMPT;建议先RESTORE DATABASE PREVIEW;正式恢复RESTORE DATABASE;恢复 redoRECOVER DATABASE;如果根据备份链需要指定恢复点可采用RECOVER DATABASE UNTIL SCN 目标SCN;成功应看到media recovery complete检查select * from v$recover_file;正常no rows selected13. 创建正式Standby SPFILE推荐以主库参数为模板生成备库正式参数文件。主库CREATE PFILE/tmp/init_imipzhwl_primary.ora FROM SPFILE;复制到备库后调整以下核心参数*.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创建 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。验证tnsping IMIPZHWL tnsping IMIPZHWL_STBY然后分别测试 SYS 远程连接sqlplus sysIMIPZHWL_STBY as sysdba以及sqlplus sysIMIPZHWL as sysdba密码交互输入。15. 主库Data Guard参数主库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参数备库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. 启动Redo Apply备库ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;此时已经进入标准 Physical Standby 同步状态。18. 转为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;正常应为PHYSICAL STANDBY READ ONLY WITH APPLY说明已经进入 Active Data Guard Real-Time Query 状态。生产使用前需确认 Active Data Guard Option 授权。19. ADG核心巡检后续日常巡检不需要执行大量 SQL核心看以下几项即可。主库19.1 基本状态select db_unique_name, database_role, open_mode, protection_mode from v$database;应PRIMARY READ WRITE MAXIMUM PERFORMANCE19.2 备库传输状态select dest_id, status, target, destination, error, db_unique_name from v$archive_dest where dest_id in (1,2);重点看DEST_ID2 STATUSVALID TARGETSTANDBY ERROR为空备库19.3 数据库状态select db_unique_name, database_role, open_mode from v$database;应PHYSICAL STANDBY READ ONLY WITH APPLY19.4 MRP/RFS状态select process, status, thread#, sequence# from v$managed_standby where process in (MRP0,RFS) order by process;正常 MRPWAIT_FOR_LOG或者APPLYING_LOG19.5 接收及应用序列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快速判断标准同时满足以下条件即可基本判断正常主库 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 无记录不建议单独根据transport lag apply lag一个指标判断异常。21. 强制日志切换验证需要快速验证主备链路时主库必须在CDB$ROOT执行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#;序列推进即可证明 redo transport/apply 正常。22. 简单测试表验证ADG同步为了以后快速验证 ADG建议在某个业务 PDB 中保留一个简单测试表。例如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;能够查询到相同记录即可非常直观地验证主库写入 → Redo生成 → Redo传输 → Redo Apply → ADG只读查询整条链路正常。后续验证可以继续INSERT INTO ADGTEST (ID) VALUES (2); COMMIT;备库SELECT * FROM ADGTEST ORDER BY ID;23. 常见问题ORA-65040如果执行ALTER SYSTEM ARCHIVE LOG CURRENT;报operation not allowed from within a pluggable database说明当前在 PDB。切回ALTER SESSION SET CONTAINERCDB$ROOT;再执行。ORA-65093备库 CDB 启动时报错时检查*.enable_pluggable_databaseTRUEMRP0 WAIT_FOR_LOG这是正常状态表示当前Redo已经应用完成 正在等待新的日志不是故障。MRP0 APPLYING_LOG说明正在实时应用日志也是正常状态。v$archive_gap无记录说明当前没有归档缺口。24. 下一次搭建ADG速查版下一次遇到相同场景可以直接按下面执行【主库】 1. 确认ARCHIVELOG 2. 开启FORCE LOGGING 3. standby_file_managementAUTO 4. 创建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. 不创建数据库 16. 配置ORACLE_SID 17. 创建目录 18. 复制password file 19. 创建最小PFILE 20. startup nomount 21. restore standby controlfile 22. mount 23. catalog backup 24. restore database 25. recover database 26. 创建正式standby SPFILE 27. startup mount 【主备配置】 28. 配置listener/tnsnames 29. tnsping双向测试 30. SYS双向连接测试 31. 配置主库DG参数 32. 配置备库DG参数 【启动同步】 33. 备库启动MRP 34. 检查MRP/RFS 35. 检查received/applied 36. 检查archive gap 37. 检查主库DEST_2 【转ADG】 38. cancel MRP 39. open read only 40. restart MRP 41. 确认READ ONLY WITH APPLY 【最终验证】 42. 主库ADGTEST插入数据 43. COMMIT 44. 备库查询ADGTEST 45. 数据一致即完成