Oracle 11g DataGuard主备架构搭建集群对单库一、环境介绍关键项主库备库数据库架构两节点Rac集群架构单库IP地址192.168.11.33/34192.168.11.38数据文件目录DATA(ASM磁盘组)/oradata/orcl控制文件目录DATA(ASM磁盘组)/oradata/orcl日志文件目录DATA(ASM磁盘组)/oradata/orcl归档文件目录ARCH(ASM磁盘组)/oradata/archOracle版本11.2.0.411.2.0.4OS版本Redhat 7.9 x64Redhat 7.9 x64主机名host33rac1/host34rac2host38实例名orcl1/orcl2orclDb_nameorclorclDb_unique_nameorclorcl_st前提1OS数据库软件都已经安装好2存储空间已经挂载完毕挂载点/oradata3提前将redo log的大小和组数规划好二、准备工作1、配置主机名数据库安装时已经规划并配置好 主库两节点主机名 节点一host33rac1 节点二host34rac2 备库主机名host382、/etc/hosts配置文件1主库上添加备库的主机名如下 主库两节点服务器上执行 su - root echo 192.168.11.38 host38 /etc/hosts 2备库上添加主库的主机名和VIP如下 备库务器上执行 su - root echo 192.168.11.33 host33rac1 /etc/hosts echo 192.168.11.34 host34rac2 /etc/hosts echo 192.168.11.35 host33rac1-vip /etc/hosts echo 192.168.11.36 host34rac2-vip /etc/hosts3、tnsnames.ora主备一样如下su - oracle cat $ORACLE_HOME/network/admin/tnsnames.ora EOF orcl_pt (DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST host33rac1-vip)(PORT 1521)) (ADDRESS (PROTOCOL TCP)(HOST host34rac2-vip)(PORT 1521)) (LOAD_BALANCE off) ) (CONNECT_DATA (SERVICE_NAME orcl) ) ) orcl_st (DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST host38)(PORT 1521)) ) (CONNECT_DATA (SID orcl) ) ) EOF 备注 1原本rac主库上想配置为orcl名称的但是orcl默认已经自带且使用scanip应该是用于scan listener监听使用负载均衡等功能因此维持orcl名称不变改为orcl_pt表示主库 验证 cat $ORACLE_HOME/network/admin/tnsnames.ora4、.bash_profile配置文件主备一样数据库安装时已经配置好5、密码文件主备一样将主库的copy过来主库节点一上执行 su - oracle scp $ORACLE_HOME/dbs/orapworcl1 oraclehost38:/u01/app/oracle/product/11.2.0/db_1/dbs 备注密码文件默认名称未orapw$ORACLE_SID,集群环境中节点一的密码文件名称为orapworcl1备库由于时单库环境需要调整密码文件名称 备库调整密码文件名称备库上执行 su - oracle mv /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl1 /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl6、主备参数文件配置1主库参数文件主库节点一主库执行 su - oracle sqlplus /as sysdba alter system set log_archive_dest_1locationARCH sid*; alter system set log_archive_configdg_config(orcl,orcl_st) sid*; alter system set log_archive_dest_2serviceorcl_st valid_for(online_logfiles,primary_role) db_unique_nameorcl_st sid*; alter system set standby_file_managementauto sid*; alter system set fal_clientorcl_pt sid*; alter system set fal_serverorcl_st sid*; alter system set db_file_name_convert/oradata/,DATA/ scopespfile sid*; alter system set log_file_name_convert/oradata/,DATA/ scopespfile sid*; alter system set log_archive_max_processes8 sid*; 备注 1上述这种方式如果主库原有环境已经开启归档和force logging则可以不停库搭建DG主备架构直接将搭建主备的关键参数通过alter system方式完成添加和修改 2log_file_name_convertdb_file_name_convert这两个参数只有当主库切换为备库时才起作用所以这里完全可以先直接修改至spfile中下次切换时自动生效 参数验证 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);备注log_file_name_convert和db_file_name_convert参数说明1以前由于存放数据文件的目录都是一样所以没有在意和理解这个参数。这一次asm到单库时duplicate报错突然发现第一参数指向备库目录第二个参数指向本库值得注意2如果主库时ASM磁盘的OMF管理文件的方式自动管理方式那么设置db_file_name_convert参数的时候必须得设置到具体目录否则会报错。导致db_file_convert参数无法正常解析与转换相应的数据文件。修改参数将解析目录写到datafile的真实路径。例如/oradata/orcl,data/orcl/datafile 类似这样data/orcl/datafile指向具体目录3本次案例中rac使用ASM磁盘组但是没有使用OMF文件管理方式创建的文件都是别名方式例如data/orcl/system.dbf类似这样因此设置的参数为’/oradata/‘,’DATA/,无需指向具体的目录。类似如下SQL select file_name from dba_data_Files; FILE_NAME -------------------------------------------------------------------------------- DATA/orcl/users01.dbf DATA/orcl/undotbs01.dbf DATA/orcl/sysaux01.dbf DATA/orcl/system01.dbf DATA/orcl/undotbs02.dbf SQL show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string DATA/orcl/control01.ctl, DATA/orcl/control02.ctl SQL select member from v$logfile; MEMBER -------------------------------------------------------------------------------- DATA/orcl/redo02.log DATA/orcl/redo01.log DATA/orcl/redo03.log DATA/orcl/redo04.log2备库参数文件备库上执行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_pt valid_for(online_logfiles,primary_role) db_unique_nameorcl *.db_file_name_convert DATA/,/oradata/ *.log_file_name_convert DATA/,/oradata/ *.standby_file_managementauto *.fal_clientorcl_st *.fal_serverorcl_pt *.log_archive_max_processes8 备注备库参数文件的基本内容可以通过主库生成和传输过来如下 主库上 sqlplus /as sysdba create pfile/home/oracle/initorcl.ora from spfile; exit scp /home/oracle/initorcl.ora oraclehost38:/u01/app/oracle/product/11.2.0/db_1/dbs 2、根据编辑的init参数文件生成spfile参数文件 sqlplus /as sysdba create spfile from pfile;7、主库必须开启归档同时开启force logging主库上必须提前规划好归档日志文件存放目录本实验环境为ARCH主库节点一上执行su - oracle 1immediate方式关闭数据库 srvctl stop database -d orcl -o immediate 备注必须immediate干净方式关闭数据库才可以开启归档immediate方式关闭主库的方法可以参考我的文章《Oracle单库环境下计划内启停数据库的步骤》 2启动一个节点的数据库实例并mount数据库 srvctl start instance -d orcl -i orcl1 -o mount 3开启归档和force logging sqlplus /as sysdba alter database archivelog ; alter database force logging; exit 4重启整个数据库 srvctl stop database -d orcl -o immediate srvctl start database -d orcl 5验证 sqlplus /as sysdba select FORCE_LOGGING,LOG_MODE 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)(HOSThost38)(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 thread 1 group 11 (DATA/orcl/st_redo11.log) size 50m; alter database add standby logfile thread 1 group 12 (DATA/orcl/st_redo12.log) size 50m; alter database add standby logfile thread 1 group 13 (DATA/orcl/st_redo13.log) size 50m; alter database add standby logfile thread 2 group 14 (DATA/orcl/st_redo14.log) size 50m; alter database add standby logfile thread 2 group 15 (DATA/orcl/st_redo15.log) size 50m; alter database add standby logfile thread 2 group 16 (DATA/orcl/st_redo16.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_pt auxiliary sys/oracleorcl_st nocatalog duplicate target database for standby from active database nofilenamecheck;备注1需要加上nocatalog关键字否则报错2主备库的连接都必须通过sys用户名/密码的方式指定否则报错3如果主库很大可以通过并发方式加快主备的搭建过程类似如下rman target sys/oracleorcl_pt 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_pt 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_pt 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_pt 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方式打开备库[oraclehost38 ~]$ 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.
