Oracle 11g DataGuard主备架构搭建（单库对单库）

Oracle 11g DataGuard主备架构搭建（单库对单库）

一、环境介绍

前提：
1）OS，数据库软件都已经安装好
2）存储空间已经挂载完毕，挂载点/oradata
3）提前将redo log的大小和组数规划好

二、准备工作

1、配置主机名

主库服务器
hostnamectl set-hostname host31
备库服务器
hostnamectl set-hostname host32

2、/etc/hosts配置文件（主备一样）

echo "192.168.11.31  host31" >>/etc/hosts
echo "192.168.11.32  host32" >>/etc/hosts

3、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.ora

4、.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_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; 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 EDITOR=vim
export LANG=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
stty erase ^H

5、密码文件（主备一样），将主库的copy过来

主库上执行
su - oracle
scp $ORACLE_HOME/dbs/orapworcl oracle@host32:/u01/app/oracle/product/11.2.0/db_1/dbs

6、主备参数文件配置

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_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=1184890880
*.undo_tablespace='UNDOTBS1'
####新增部分如下,实际写入参数文件中时，这一行注释需要去掉
*.log_archive_dest_1='location=/oradata/arch'
*.log_archive_config='dg_config=(orcl,orcl_st)'
*.log_archive_dest_2='service=orcl_st valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st'
*.db_file_name_convert='/oradata/orcl','/oradata/orcl'
*.log_file_name_convert='/oradata/orcl','/oradata/orcl'
*.standby_file_management=auto
*.fal_client='orcl'
*.fal_server='orcl_st'
*. standby_archive_dest='location=/oradata/arch'
*.log_archive_max_processes=8

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方式完成添加和修改：
2）log_file_name_convert，db_file_name_convert这两个参数只有当主库切换为备库时才起作用，所以，这里完全可以先直接修改至spfile中，下次切换时自动生效
alter system set log_archive_config='dg_config=(orcl,orcl_st)';
alter system set log_archive_dest_2='service=yun_orcl_st valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st';
alter system set standby_file_management=auto;
alter system set fal_client='orcl';
alter system set fal_server='orcl_st';
alter system set log_file_name_convert='/oradata/orcl','/oradata/orcl' scope=spfile; 
alter system set db_file_name_convert='/oradata/orcl','/oradata/orcl' scope=spfile;
alter system set log_archive_max_processes=8;
alter sysetm set log_archive_dest_1='location=/oradata/arch';

2）备库参数文件（备库上执行）

1、编辑init文件参数文件
su - oracle
vi $ORACLE_HOME/dbs/initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=1184890880
*.undo_tablespace='UNDOTBS1'
####上面这一段，与主库完全一样,实际写入参数文件中时，这一行注释需要去掉
####下面这一段是DG架构中备库非常关键的参数,实际写入参数文件中时，这一行注释需要去掉
*.log_archive_dest_1='location=/oradata/arch'
*.db_unique_name='orcl_st'
*.log_archive_config='dg_config=(orcl,orcl_st)'
*.log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.db_file_name_convert='/oradata/orcl','/oradata/orcl'
*.log_file_name_convert='/oradata/orcl','/oradata/orcl'
*.standby_file_management=auto
*.fal_client='orcl_st'
*.fal_server='orcl'
*.log_archive_max_processes=8

备注：备库参数文件的基本内容，可以通过主库生成和传输过来，如下
主库上
sqlplus "/as sysdba"
create pfile='/home/oracle/initorcl.ora' from  spfile;
exit
scp /home/oracle/initorcl.ora   oracle@host32:/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/arch

2）immediate方式停止主库

su - oracle
sqlplus "/as sysdba"
shutdown immediate;
备注：必须immediate干净方式关闭数据库，才可以开启归档，immediate方式关闭主库的方法，可以参考我的文章《Oracle单库环境下计划内启停数据库的步骤》

3）mount方式启动主库

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=(PROTOCOL=tcp)(HOST=host32)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

9、备库启动到nomount状态

备库上执行

1）创建一些必须的目录文件

su - oracle
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir /oradata/orcl
mkdir /oradata/arch

2）启动数据库到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日志组数量为主库的日志组+1，RAC例外，每个实例的日志组+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日志组一起创建出来（无需额外手工再次创建）
2）standby日志组的group号，建议从11开始，前面的1-10序号，保留给redo log日志组使用

三、正式搭建

1、主备搭建

主库上执行

su - oracle
rman target sys/oracle@orcl auxiliary sys/oracle@orcl_st nocatalog
duplicate target database for standby  from active database nofilenamecheck;

备注：
1）需要加上nocatalog关键字，否则报错
2）主备库的连接，都必须通过sys用户名/密码的方式指定，否则报错
3）如果主库很大，可以通过并发方式，加快主备的搭建过程，类似如下

rman target sys/oracle@orcl auxiliary sys/oracle@orcl_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/oracle@orcl auxiliary sys/oracle@orcl_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/oracle@orcl auxiliary sys/oracle@orcl_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/oracle@orcl auxiliary sys/oracle@orcl_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方式打开备库！！

[oracle@host32 ~]$ 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            OPEN

3、搭建完毕后，如果第一次 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.