Oracle 11g DataGuard主备架构搭建单库对单库一、环境介绍关键项主库备库IP地址192.168.11.31192.168.11.32数据文件目录/oradata/orcl/oradata/orcl控制文件目录/oradata/orcl/oradata/orcl日志文件目录/oradata/orcl/oradata/orcl归档文件目录/oradata/arch/oradata/archOracle版本11.2.0.411.2.0.4OS版本Redhat 7.9 x64Redhat 7.9 x64主机名host31host32实例名orclorclDb_nameorclorclDb_unique_nameorclorcl_st前提1OS数据库软件都已经安装好2存储空间已经挂载完毕挂载点/oradata3提前将redo log的大小和组数规划好二、准备工作1、配置主机名主库服务器 hostnamectl set-hostname host31 备库服务器 hostnamectl set-hostname host322、/etc/hosts配置文件主备一样echo 192.168.11.31 host31 /etc/hosts echo 192.168.11.32 host32 /etc/hosts3、tnsnames.ora主备一样如下su - oracle cat $ORACLE_HOME/network/admin/tnsnames.ora EOF orcl (DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST host31)(PORT 1521)) ) (CONNECT_DATA (SID orcl) ) ) orcl_st (DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST host32)(PORT 1521)) ) (CONNECT_DATA (SID orcl) ) ) EOF 验证 cat $ORACLE_HOME/network/admin/tnsnames.ora4、.bash_profile配置文件主备一样安装数据库软件时已经配置好类似如下 su - oracle cat .bash_profile TMP/tmp; export TMP TMPDIR$TMP; export TMPDIR ORACLE_BASE/u01/app/oracle; export ORACLE_BASE ORACLE_HOME$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SIDorcl; export ORACLE_SID ORACLE_TERMxterm; export ORACLE_TERM PATH/usr/sbin:$PATH; export PATH PATH$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH; export PATH LD_LIBRARY_PATH$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH export EDITORvim export LANGen_US.UTF-8 export NLS_LANGAMERICAN_AMERICA.ZHS16GBK stty erase ^H5、密码文件主备一样将主库的copy过来主库上执行 su - oracle scp $ORACLE_HOME/dbs/orapworcl oraclehost32:/u01/app/oracle/product/11.2.0/db_1/dbs6、主备参数文件配置1主库参数文件主库上执行方法一直接编辑生成参数文件然后停止主库通过新编辑的参数文件生成spfile参数文件重启启动主库1、生成pfile文件 su - oracle sqlplus /as sysdba create pfile from spfile; 2、编辑生成参数文件 vi $ORACLE_HOME/dbs/initorcl.ora *.audit_file_dest/u01/app/oracle/admin/orcl/adump *.audit_traildb *.compatible11.2.0.4.0 *.control_files/oradata/orcl/control01.ctl,/oradata/orcl/control02.ctl *.db_block_size8192 *.db_domain *.db_nameorcl *.diagnostic_dest/u01/app/oracle *.dispatchers(PROTOCOLTCP) (SERVICEorclXDB) *.open_cursors300 *.pga_aggregate_target394264576 *.processes1500 *.remote_login_passwordfileEXCLUSIVE *.sessions1655 *.sga_target1184890880 *.undo_tablespaceUNDOTBS1 ####新增部分如下,实际写入参数文件中时这一行注释需要去掉 *.log_archive_dest_1location/oradata/arch *.log_archive_configdg_config(orcl,orcl_st) *.log_archive_dest_2serviceorcl_st valid_for(online_logfiles,primary_role) db_unique_nameorcl_st *.db_file_name_convert/oradata/orcl,/oradata/orcl *.log_file_name_convert/oradata/orcl,/oradata/orcl *.standby_file_managementauto *.fal_clientorcl *.fal_serverorcl_st *. standby_archive_destlocation/oradata/arch *.log_archive_max_processes8 3、通过编辑的参数文件生成spfile二进制参数文件 sqlplus /as sysdba shutdown immediate; //关闭主库生成最新的spfile文件然后通过spfile来启动数据库 mv $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora sqlplus /as sysdba create spfile from pfile; startup; //重新启动主库以最新的spfile文件启动 4、验证 show parameter spfile; set lines 500 pages 999 col value for a90 col name for a50 select name,value from v$parameter where name in(db_name,db_unique_name, log_archive_config, log_archive_dest_1,log_archive_dest_2, log_archive_max_processes, fal_server,fal_client, db_file_name_convert, log_file_name_convert, standby_file_management);方法二如果主库已经开启归档且已经开启强制日志force logging模式则主库可以不停库搭建DG方法如下1主库原有环境已经开启归档和force logging则可以不停库搭建DG主备架构直接将搭建主备的关键参数通过alter system方式完成添加和修改 2log_file_name_convertdb_file_name_convert这两个参数只有当主库切换为备库时才起作用所以这里完全可以先直接修改至spfile中下次切换时自动生效 alter system set log_archive_configdg_config(orcl,orcl_st); alter system set log_archive_dest_2serviceyun_orcl_st valid_for(online_logfiles,primary_role) db_unique_nameorcl_st; alter system set standby_file_managementauto; alter system set fal_clientorcl; alter system set fal_serverorcl_st; alter system set log_file_name_convert/oradata/orcl,/oradata/orcl scopespfile; alter system set db_file_name_convert/oradata/orcl,/oradata/orcl scopespfile; alter system set log_archive_max_processes8; alter sysetm set log_archive_dest_1location/oradata/arch;2备库参数文件备库上执行1、编辑init文件参数文件 su - oracle vi $ORACLE_HOME/dbs/initorcl.ora *.audit_file_dest/u01/app/oracle/admin/orcl/adump *.audit_traildb *.compatible11.2.0.4.0 *.control_files/oradata/orcl/control01.ctl,/oradata/orcl/control02.ctl *.db_block_size8192 *.db_domain *.db_nameorcl *.diagnostic_dest/u01/app/oracle *.dispatchers(PROTOCOLTCP) (SERVICEorclXDB) *.open_cursors300 *.pga_aggregate_target394264576 *.processes1500 *.remote_login_passwordfileEXCLUSIVE *.sessions1655 *.sga_target1184890880 *.undo_tablespaceUNDOTBS1 ####上面这一段与主库完全一样,实际写入参数文件中时这一行注释需要去掉 ####下面这一段是DG架构中备库非常关键的参数,实际写入参数文件中时这一行注释需要去掉 *.log_archive_dest_1location/oradata/arch *.db_unique_nameorcl_st *.log_archive_configdg_config(orcl,orcl_st) *.log_archive_dest_2serviceorcl valid_for(online_logfiles,primary_role) db_unique_nameorcl *.db_file_name_convert/oradata/orcl,/oradata/orcl *.log_file_name_convert/oradata/orcl,/oradata/orcl *.standby_file_managementauto *.fal_clientorcl_st *.fal_serverorcl *.log_archive_max_processes8 备注备库参数文件的基本内容可以通过主库生成和传输过来如下 主库上 sqlplus /as sysdba create pfile/home/oracle/initorcl.ora from spfile; exit scp /home/oracle/initorcl.ora oraclehost32:/u01/app/oracle/product/11.2.0/db_1/dbs 2、根据编辑的init参数文件生成spfile参数文件 sqlplus /as sysdba create spfile from pfile;7、主库必须开启归档同时开启force logging主库上执行1建立归档目录su - oracle mkdir /oradata/arch2immediate方式停止主库su - oracle sqlplus /as sysdba shutdown immediate; 备注必须immediate干净方式关闭数据库才可以开启归档immediate方式关闭主库的方法可以参考我的文章《Oracle单库环境下计划内启停数据库的步骤》3mount方式启动主库sqlplus /as sysdba startup mount;4开启归档和开启force logging强制记录日志模式alter database archivelog ; alter database force logging; 验证 select LOG_MODE,FORCE_LOGGING from v$database;8、备库必须建立静态注册监听文件如果为动态监听的状态为blocked实例nomount状态时后续rman无法直接连接备库。备库上执行 su - oracle cat $ORACLE_HOME/network/admin/listener.ora EOF SID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAME orcl) (ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1) (SID_NAME orcl) ) ) EOF 备库重启监听和验证 lsnrctl stop lsnrctl start lsnrctl status 状态类似如下 Listening Endpoints Summary... (DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOSThost32)(PORT1521))) Services Summary... Service orcl has 1 instance(s). Instance orcl, status UNKNOWN, has 1 handler(s) for this service... The command completed successfully9、备库启动到nomount状态备库上执行1创建一些必须的目录文件su - oracle mkdir -p /u01/app/oracle/admin/orcl/adump mkdir /oradata/orcl mkdir /oradata/arch2启动数据库到nomount状态su - oracle sqlplus /as sysdba startup nomount; 验证启动参数 show parameter spfile; //验证是否spfile文件启动 set lines 500 pages 999 col value for a90 col name for a50 select name,value from v$parameter where name in(db_name,db_unique_name, log_archive_config, log_archive_dest_1,log_archive_dest_2, log_archive_max_processes, fal_server,fal_client, db_file_name_convert, log_file_name_convert, standby_file_management);10、主库上创建standby redo log为了后续主备可以进行实时同步添加规则standby日志组数量为主库的日志组1RAC例外每个实例的日志组1主库上执行(实验环境主库上3组日志每个日志组大小50M查看方法select group#,thread#,bytes from v$log;) alter database add standby logfile group 11 (/oradata/orcl/st_redo11.log) size 50m; alter database add standby logfile group 12 (/oradata/orcl/st_redo12.log) size 50m; alter database add standby logfile group 13 (/oradata/orcl/st_redo13.log) size 50m; alter database add standby logfile group 14 (/oradata/orcl/st_redo14.log) size 50m; 验证 select group#,thread#,bytes from v$standby_log; 备注 1提前在主库上创建standby日志组from active database方式正式搭建时备库创建过程中可以自动将standby日志组一起创建出来无需额外手工再次创建 2standby日志组的group号建议从11开始前面的1-10序号保留给redo log日志组使用三、正式搭建1、主备搭建主库上执行su - oracle rman target sys/oracleorcl auxiliary sys/oracleorcl_st nocatalog duplicate target database for standby from active database nofilenamecheck;备注1需要加上nocatalog关键字否则报错2主备库的连接都必须通过sys用户名/密码的方式指定否则报错3如果主库很大可以通过并发方式加快主备的搭建过程类似如下rman target sys/oracleorcl auxiliary sys/oracleorcl_st nocatalog run{ allocate channel ch1 type disk; allocate channel ch2 type disk; allocate auxiliary channel ch3 type disk; allocate auxiliary channel ch4 type disk; duplicate target database for standby from active database nofilenamecheck; release channel ch1; release channel ch2; }4整个方式也可以在备库上执行类似如下su - oracle rman target sys/oracleorcl auxiliary sys/oracleorcl_st nocatalog duplicate target database for standby from active database nofilenamecheck;5主备搭建命令执行完毕后备库默认会启动到mount挂载状态2、备库验证备库上执行su - orace sqlplus /as sysdba select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; select group#,thread#,bytes from v$standby_log; select group#,thread#,bytes from v$log;3、备库上启动同步应用主库的redo日志两种日志应用的方式备库上执行1实时应用su - oracle sqlplus /as sysdba alter database open read only //启动备库只读打开状态然后应用主库的redo日志进行实时通过 ///搭建完毕后如果第一次 alter database open报错(file 1 was not restored from a sufficiently old backup)参考后面的处理方法 alter database recover managed standby database using current logfile disconnect from session; 备注实时日志应用方式主库上必须创建standby redo日志组才可以使用这种类型2主库切换日志时才应用即新归档日志过来时才应用这是默认选项su - oracle sqlplus /as sysdba //备库保持mount挂载状态主库切换日志时才应用同步即新归档日志过来时才应用这是默认选项 alter database recover managed standby database disconnect from session;至此Oracle主备架构搭建完毕4、可选备库上停止同步取消日志应用的命令如下备库上执行su - oracle sqlplus /as sysdba alter database recover managed standby database cancel;四、搭建总结1、from active database方式搭建主备1需要加上nocatalog关键字否则报错 2主备库的连接都必须通过sys用户名/密码的方式指定 rman target sys/oracleorcl auxiliary sys/oracleorcl_st nocatalog 否则报错类似如下 DBGSQL: TARGET begin :fhdbi : dbms_rcvcat.getDbid; end; DBGSQL: sqlcode 6550 DBGSQL: B :fhdbi 32767 RMAN-00571: RMAN-00569: ERROR MESSAGE STACK FOLLOWS RMAN-00571: RMAN-03002: failure of Duplicate Db command at 06/23/2026 11:28:45 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script ORA-06550: line 1, column 17: PLS-00201: identifier DBMS_RCVCAT.GETDBID must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored 3可以并发的方式类似如下 rman target sys/oracleorcl auxiliary sys/oracleorcl_st nocatalog run{ allocate channel ch1 type disk; allocate channel ch2 type disk; allocate auxiliary channel ch3 type disk; allocate auxiliary channel ch4 type disk; duplicate target database for standby from active database nofilenamecheck; release channel ch1; release channel ch2; }2、备库上默认只能以只读方式打开数据库备库上执行startup或者alter database open命令默认都是以open read only方式打开备库[oraclehost32 ~]$ sqlplus /as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 23 14:13:12 2026 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL startup ORACLE instance started. Total System Global Area 1185853440 bytes Fixed Size 2252664 bytes Variable Size 956301448 bytes Database Buffers 218103808 bytes Redo Buffers 9195520 bytes Database mounted. Database opened. SQL select open_mode,status from v$instance,v$database; OPEN_MODE STATUS -------------------- ------------ READ ONLY OPEN3、搭建完毕后如果第一次 alter database open报错file 1 was not restored from a sufficiently old backup参考处理方法如下SQL alter database open read only ; alter database open read only ERROR at line 1: ORA-10458: standby database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: /oradata/orcl/system01.dbf SQL alter database recover managed standby database disconnect from session; //这个命令会直接不打开数据库方式应用主库的归档日志会将数据库恢复为一致状态 Database altered. SQL alter database recover managed standby database cancel; Database altered. SQL alter database open read only; Database altered. SQL alter database recover managed standby database using current logfile disconnect from session; Database altered.