dg_broker 配置参考

原创许玉冲 2024-07-03

146

1,配置文件放置到共享存储：

alter system set dg_broker_start=false scope=both sid='*';
alter system set dg_broker_config_file1='+data/sborcl/dr1rac.dat' sid='*';
alter system set dg_broker_config_file2='+data/sborcl/dr2rac.dat' sid='*';
alter system set dg_broker_start=true scope=both sid='*';

2，配置dg_broker_

 dgmgrl sys/oracle
 create configuration dg_orcl as primary database is orcl connect identifier is orcl;
 add database sborcl as connect identifier is sborcl;
 add database orcl03 as connect identifier is orcl03;
 show configuration;
 enable configuration;
 show configuration;

3，查看配置结果

[oracle@acstest01 ~]$  dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jul 3 22:13:17 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL>  show configuration;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl   - Primary database
    sborcl - Physical standby database 
    orcl03 - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

--查看日志传输拓扑：SQL> set linesize 1000;
col database format a20;
col connect_identifier format a20;
col dataguard_role format a20;
col redo_source format a20;

select INST_ID,database, connect_identifier, dataguard_role, redo_source from gv_$dg_broker_config; SQL> SQL> SQL> SQL> SQL> SQL> 

   INST_ID DATABASE		CONNECT_IDENTIFIER   DATAGUARD_ROLE	  REDO_SOURCE
---------- -------------------- -------------------- -------------------- --------------------
	 2 orcl 		orcl		     PRIMARY		  -N/A-
	 2 sborcl		sborcl		     PHYSICAL STANDBY	  orcl
	 2 orcl03		orcl03		     PHYSICAL STANDBY	  sborcl
	 1 orcl 		orcl		     PRIMARY		  -N/A-
	 1 sborcl		sborcl		     PHYSICAL STANDBY	  orcl
	 1 orcl03		orcl03		     PHYSICAL STANDBY	  sborcl

6 rows selected.

4，修改相关日志传输配置（级联备库配置）

edit database orcl set property redoroutes='(LOCAL : sborcl SYNC)';
edit database sborcl set property redoroutes='(orcl : orcl03 ASYNC)';
show configuration;
DGMGRL> show configuration;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl   - Primary database
    sborcl - Physical standby database 
      orcl03 - Physical standby database (receiving current redo)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 56 seconds ago)

5，switchover操作需要配置相关静态监听：

To enable DGMGRL to restart instances during the course of broker operations, a static service must be registered with the local listener of each instance. A static service registration is also required to enable the observer to restart instances as part of automatic reinstatement of the old primary database after a fast-start failover has occurred. A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.
By default, the broker assumes a static service name of db_unique_name_DGMGRL.db_domain and expects the listener has been started with the following content in the listener.ora file。
Alternatively, you can use a different static service name. If you do, be sure to modify the StaticConnectIdentifier instance-specific property to reflect the static service that has been registered.
To ensure that the connect identifier has been set up correctly, use the VALIDATE STATIC CONNECT IDENTIFIER command.

######静态监听注册，否则switch over 需要手动启动新备数据库。
--service_name为db_unique_name_DGMGRL

sid_list_listener=
  (sid_list=
     (sid_desc=
     (global_dbname= db_nuique_name_dgmgrl)
     (oracle_home=/u01/app/oracle/product/1x.x.0/db_1)
     (sid_name=<instance name>)
    )
  )

直接switchover切换测试：

DGMGRL> switchover to orcl03;   <----执行切换命令！
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl03"
Connecting ...
Connected to "ORCL03"
Connected as SYSDBA.
New primary database "orcl03" is opening...
Oracle Clusterware is restarting database "orcl" ...
Connected to "ORCL"
Switchover succeeded, new primary is "orcl03"
DGMGRL> show configuration;  <----查看切换结果

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl03 - Primary database
    orcl   - Physical standby database 
    sborcl - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 73 seconds ago)

DGMGRL>

@未配置静态监听，切换可以报错！

DGMGRL> switchover to orcl;
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl"
Connecting ...
Connected to "ORCL"
Connected as SYSDBA.
New primary database "orcl" is opening...
Operation requires start up of instance "orcl03" on database "orcl03"
Starting instance "orcl03"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.57.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL03_DGMGRL)(INSTANCE_NAME=orcl03)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
[W000 2024-07-03T22:23:19.836+08:00] Failed to attach to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.57.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL03_DGMGRL)(INSTANCE_NAME=orcl03)(SERVER=DEDICATED))).

Please complete the following steps to finish switchover:
	start up instance "orcl03" of database "orcl03"

要去访问一个已经关闭掉的数据库时，Broker默认会使用LOCAL_LISTENER里的信息和关键字"_DGMGRL“，来生成一个默认的StaticConnectIdentifier值，不建议用户自行去修改这个参数，因为这个值会被Broker自动更新。
StaticConnectIdentifier
如果用户手动修改了这个参数，那么Broker会认为用户从现在开始以后都会自行修改，如果实例从一个不同的host启动时，broker则无法连接到对应关闭的实例上。
同样的道理，当LOCAL_LISTENER参数被用户修改以后，则它以后也不会自动更新了。另外如果数据库被加入到DG Broker以后，用户再对LOCAL_LISTENER进行修改，则原有的StaticConnectIdentifier参数并不会同步更新来对应最新的LOCAL_LISTENER值。
如果确实有修改LOCAL_LISTENER参数的需要，则Broker配置也需要手动来操作，恢复StaticConnectIdentifier的自动更新机制：
对于standby库使用DGMGRL CLI移除数据库然后重新添加
对于primary库使用DGMGRL CLI移除整个配置然后重新创建

@如果数据库有集群软件管理，可以不用配置静态监听：

DGMGRL> switchover to sborcl
Performing switchover NOW, please wait...
Operation requires a connection to database "sborcl"
Connecting ...
Connected to "sborcl"
Connected as SYSDBA.
New primary database "sborcl" is opening...
Oracle Clusterware is restarting database "orcl" ...   《《《------ 数据库启动关闭，有集群软件管理。不需要_DGMGRL静态监听。
Connected to "ORCL"
Switchover succeeded, new primary is "sborcl"

@静态监听验证测试：

you can use a different static service name. If you do, be sure to modify the StaticConnectIdentifier instance-specific property to reflect the static service that has been registered.
To ensure that the connect identifier has been set up correctly, use the VALIDATE STATIC CONNECT IDENTIFIER command.

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR orcl03;
Oracle Clusterware is not configured on database "orcl03".
Connecting to database "orcl03" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.57.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL03_DGMGRL)(INSTANCE_NAME=orcl03)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.57.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL03_DGMGRL)(INSTANCE_NAME=orcl03)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOr orcl;
Oracle Clusterware on database "orcl" is available for database restart.

dg_broker

最后修改时间：2024-07-05 13:36:42

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

dg_broker 配置参考

评论