环境

#主备库 C:\Windows\System32\drivers\etc\hosts 文件
192.168.11.10  dg1
192.168.11.11  dg2
#环境
主库主机名：dg1 现有实例orcl
备库主机名：dg2 只安装软件

一，主库配置
–主库设置强制日志，保证所有的操作都记录到日志文件
–查看当前force\_logging的设置

#主库如果已开启归档，不需要停机
sqlplus / as sysdba
select force_logging from v$database;
select flashback_on from v$database;
alter database force logging; -- 开启强制日志模式
#######################################################
#如果没开归档
sqlplus / as sysdba
shudown immediate;
startup mount;
alter database archivelog; -- 开启归档模式
alter database force logging; -- 开启强制日志模式
#alter database flashback on;  -- 开启闪回，不是必须，推荐开启
#######################################################
#主库添加standby日志组
#查看日志文件大小 select bytes/1024/1024 from v$log;这里是50M
alter database add standby logfile group 10 ('D:\app\Administrator\oradata\orcl\standby_redo01.log') size 50m;
alter database add standby logfile group 11 ('D:\app\Administrator\oradata\orcl\standby_redo02.log') size 50m;
alter database add standby logfile group 12 ('D:\app\Administrator\oradata\orcl\standby_redo03.log') size 50m;
alter database add standby logfile group 13 ('D:\app\Administrator\oradata\orcl\standby_redo04.log') size 50m;
########################################################
#设置文件管理自动
alter system set standby_file_management=auto;

二、主备库网络设置

#主库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_DGMGRL)  #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#主库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
#备库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_stby_DGMGRL) #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#备库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
#主库监听reload
lsnrctl reload
#备库启动监听
lsnrctl start

三、备库配置

#创建一个临时参数文件如d:\pfile.txt内容如下
*.db_name='orcl'
#创建密码文件，或者从主库拷贝一个
orapwd file=D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\database\PWDorcl.ora password=oracle entries=10
#创建备库所需目录
mkdir D:\app\Administrator\oradata\orcl\
mkdir D:\app\Administrator\admin\orcl\adump\
mkdir D:\app\Administrator\fast_recovery_area\orcl\
#用ORADIM创建实例
oradim -new -sid orcl
#用临时参数文件启动
sqlplus / as sysdba
starup nomount pfile='d:\pfile.txt';

四、RMAN duplicate创建备库

#tnsping测试互通性
tnsping orcl
tnsping orcl_stby
#主库连接备库
sqlplus sys/oracle@stddb as sysdba
#备库连接主库
sqlplus sys/oracle@orcl as sysdba
#########################################################
#备库执行，连接主备库
rman target sys/oracle@orcl auxilary sys/oracle@orcl_stby
#创建dg备库，这里假设主备库路径相同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
  nofilenamecheck;
#########################################################
#如果主备库路径不同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
    set db_file_name_convert='orcl','orcl_stby'
    set log_file_name_convert='orcl','orcl_stby'
    set job_queue_processes='0'
  nofilenamecheck;
#开启ADG
sqlplus / as sysdba
alter database open read only;
alter database recover managed standby database disconnect from session;

五、配置DG BROKER

#主备库两边执行
alter system set dg_broker_start=true;
#主库连接dgmgrl
dgmgrl sys/oracle@orcl
#创建dg broker配置
create configuration dg_config as primary database is orcl connect identifier is orcl;
#添加备库到配置文件
add database orcl_stby as connect identifier is orcl_stby;
#启用配置
enable configuration;

############################################################################
#显示DG配置信息
show configuration
show configuration verbose
#显示主备库信息
show database orcl
show database orcl_stby
show database verbose orcl
show database verbose orcl_stby

六、一些测试

#测试Database Switchover
dgmgrl sys/oracle@orcl
switchover to orcl_stby;
show configuration
#切换回来
switchover to orcl;
show configuration
###########################################################################
#测试Database Failover，此时dg关系已经打破
dgmgrl sys/oracle@orcl
failover to orcl_stby;
#如果主库开启了flashback，执行以下语句自动重建主库
 reinstate database orcl;
#如果没有开启flashback，删除重建主库，重新建立dg关系
############################################################################
#测试快照备库
dgmgrl sys/oracle@orcl
convert database orcl_stby to snapshot standby;
show configuration;
#快照转成正常备库
convert database orcl_stby to physical standby;
show configuration;

七、总结
优点在于除监听设置外主备库都不需要做过多的设置，备库临时参数文件只需要一个dbname，其余dg有关的参数dg broker会自动设置。
八、参考资料
ORACLE-BASE – Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2

今天检查数据库发现一套主库上面报大量RFS[22349]: Database mount ID mismatch之类的错误

[oracle@hisdb2 trace]$ tail -f alert_hisdb2.log 
LNS: Standby redo logfile selected for thread 2 sequence 133319 for destination LOG_ARCHIVE_DEST_2
Mon Nov 06 20:56:09 2023
RFS[27080]: Assigned to RFS process 15518
RFS[27080]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:57:09 2023
RFS[27081]: Assigned to RFS process 16299
RFS[27081]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:57:09 2023
RFS[27082]: Assigned to RFS process 16319
RFS[27082]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)

这种错误表示rfs进程在传输的时候检测到database mount id不匹配(也就是认为这个库不是主库该传输或者接受日志的),出现类似这样的错误,大概率是由于这个dg成员(主库和备库)中应该有不匹配的库,回想这个库近期的操作,突然想到对这个主库在虚拟化平台的备库做了一次克隆,然后吧克隆库激活作为测试库的操作.基于此种情况,先判断该库是否和上次克隆的库之间创建有会话连接

--主库
[oracle@hisdb2 trace]$ netstat -natp|grep 192.168.106.63
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 192.168.115.12:52785        192.168.106.63:1521         ESTABLISHED -                   
tcp        0      0 192.168.115.14:1521         192.168.106.63:21598        TIME_WAIT   - 

--克隆库
[oracle@HIS_DG ~]$ netstat -atpn|grep 192.168.115.12
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 ::ffff:192.168.106.63:1521  ::ffff:192.168.115.12:52785 ESTABLISHED 6126/oraclehisdb  

两个库之间确实存在会话,检查克隆库相关的dg配置

[oracle@HIS_DG ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 6 21:01:05 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter fal;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      HISDBDG
fal_server                           string      HISDB
SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> show parameter archive;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     1800
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST valid_for=(all_logfiles,a
                                                 ll_roles) db_unique_name=hisdb
                                                 dg
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      service=hisdb lgwr async valid
                                                 _for=(online_logfiles,primary_
                                                 role) db_unique_name=hisdb

该库的dg配置没有清除,当该库变为主库时,log_archive_dest_2刚好指向主库,从而使得主库上出现了类似RFS[22349]: Database mount ID mismatch的错误.处理方法是清除掉克隆库上面dg备库相关配置

SQL> alter system set log_archive_dest_2='';

System altered.

SQL> alter system set fal_server='';

System altered.

主库日志中未再出现类似错误

Mon Nov 06 20:48:08 2023
RFS[22349]: Assigned to RFS process 7383
RFS[22349]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
[oracle@hisdb1 trace]$ tail -f alert_hisdb1.log 
RFS[22352]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:58:09 2023
RFS[22353]: Assigned to RFS process 14958
RFS[22353]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:01:09 2023
RFS[22354]: Assigned to RFS process 18580
RFS[22354]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:01:09 2023
RFS[22355]: Assigned to RFS process 18598
RFS[22355]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:03:28 2023
Thread 1 advanced to log sequence 129769 (LGWR switch)
  Current log# 9 seq# 129769 mem# 0: +DATA/hisdb/onlinelog/group_9.276.976991877
  Current log# 9 seq# 129769 mem# 1: +FRA/hisdb/onlinelog/group_9.2334.976991877
Mon Nov 06 21:03:28 2023
Archived Log entry 524082 added for thread 1 sequence 129768 ID 0x70864b41 dest 1:
Mon Nov 06 21:03:28 2023
LNS: Standby redo logfile selected for thread 1 sequence 129769 for destination LOG_ARCHIVE_DEST_2

事后在MOS上有一篇文档供参考:Database mount ID mismatch ORA-16009: invalid redo transport destination (Doc ID 1450132.1)