配置DataGuard Broker

手动进行DG switchover,步骤有些麻烦,是否有更简单的方式呢,可以试试dg broker。

Oracle DataGuard Broker分为Client Side和Server Side。

Client Side可以通过EM和DGMGRL两种工具对服务端进行管理和维护。

Server side会有一个配置文件和一个后台进程叫Data Guard Broker monitor process(DMON)。

DMON:它是一个用来管理Broker的后台进程,这个进程负责本地数据库与standby数据库的DMON进程进行通讯,当主库上接收到一个请求的时候,它会协调其他数据库上的DMON进程处理相应的请求,比如switchover。

同时会更新本地系统中的配置文件,并与standby数据库上的DMON进程进行通信,更新Standby上的配置文件。

主库、备库

配置DG_BROKER_START参数

1
2
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;

在监听文件中加入DGMGRL静态监听

https://img.bwcxtech.com/img/202303171345432.png

SERVICE_NAME=PRIMARYDB_DGMGRL 这个service_name参数,默认格式为db_unique_name_DGMGRL,如果这里采用默认格式,那么主备库的监听,就要配置静态监听。

主库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SID_LIST_LISTENER=
      (SID_LIST =
       (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
        (SID_NAME = orcl)
       )
       (SID_DESC =
        (GLOBAL_DBNAME=PRIMARYDB_DGMGRL)
        (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
        (SID_NAME =orcl)
       )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.67)(PORT = 1521))
    )
  )

备库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SID_LIST_LISTENER=
      (SID_LIST =
       (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
        (SID_NAME = orcl)
       )
       (SID_DESC =
         (GLOBAL_DBNAME=STANDBYDB_DGMGRL)
         (ORACLE_HOME =/data/oracle/product/11.2.0/db_1)
         (SID_NAME =orcl)
       )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
    )
  )

监听状态

1
2
lsnrctl reload
lsnrctl status

如果不采用静态监听的方式,那么就要用手工更改该参数的值

1
2
DGMGRL> edit database 'PRIMARYDB' set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primarydb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';
DGMGRL> edit database 'STANDBYDB' set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standbydb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';
1
$ dgmgrl sys/oracle

https://img.bwcxtech.com/img/202303171117655.png

1
DGMGRL> create configuration 'dgbroker' as primary database is 'PRIMARYDB' connect identifier is PRIMARYDB;

参数说明:

dgbroker是配置的名称,这里可以随便填

primary database is ‘PRIMARYDB’,这儿的PRIMARYDB是指database的db_unique_name

connect identifier is PRIMARYDB,这里的PRIMARYDB是指tnsname.ora连接到主库的service name.

https://img.bwcxtech.com/img/202303171117146.png

如果发现创建出错,或者其他原因可以执行

1
DGMGRL> remove configuration;
1
DGMGRL> add database 'STANDBYDB' as connect identifier is STANDBYDB maintained as physical;

参数说明:

add database ‘STANDBYDB’,这儿的STANDBYDB是指database的db_unique_name

as connect identifier is STANDBYDB,这里的STANDBYDB是指tnsname.ora连接到备库的service name.

1
2
3
DGMGRL> show configuration
DGMGRL> show database verbose 'PRIMARYDB'
DGMGRL> show database verbose 'STANDBYDB';

https://img.bwcxtech.com/img/202303200852789.png

1
DGMGRL> enable configuration

enable的过程很慢,而且状态出现error

主库查看broker详细信息

1
DGMGRL> show configuration verbose;

https://img.bwcxtech.com/img/202303171440456.png

备库查看broker详细信息

1
DGMGRL> show configuration verbose;

https://img.bwcxtech.com/img/202303171440258.png

查看备库详细信息

1
DGMGRL> show database verbose 'STANDBYDB';

https://img.bwcxtech.com/img/202303171440179.png

此时主备库alert日志均有报错 Fatal NI connect error 12514, connecting to:

https://img.bwcxtech.com/img/202303171442186.png

备库状态报告3个属性值与数据库设置不一致,重新设置

Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting

虽然看上去是一致的,但是就是需要重新设置一遍

1
2
3
DGMGRL> edit database 'STANDBYDB' set property ArchiveLagTarget=0;
DGMGRL> edit database 'STANDBYDB' set property LogArchiveMaxProcesses=4;
DGMGRL> edit database 'STANDBYDB' set property LogArchiveMinSucceedDest=1;

再次查看broker详细信息

1
DGMGRL> show configuration verbose;

https://img.bwcxtech.com/img/202303171446281.png

主备库都是success

1
DGMGRL> switchover to 'STANDBYDB'

https://img.bwcxtech.com/img/202303171448739.png

首先确认StaticConnectIdentifier的值

1
2
DGMGRL> show database 'PRIMARYDB' StaticConnectIdentifier;
DGMGRL> show database 'STANDBYDB' StaticConnectIdentifier;

https://img.bwcxtech.com/img/202303171503187.png

确认监听中已经静态注册上面的service name

再次测试仍有问题,可能与前面alert的Fatal NI connect error 12514报错有关,错误信息里的链接描述符均去请求一个 db_unique_name_DGB的服务

那么这个 db_unique_name_DGB的服务究竟是做什么用的呢?

{db_unique_name}_DGB.{db_domain}: This Service is used by the DMON-Processes to communicate between each other DMON是一个用来管理broker的后台进程,这个进程负责与本地数据库以及远程数据库的DMON进程进行通讯(与远端数据库的DMON进程进行通讯的时候使用的是一个动态注册的service name “db_unique_name_DGB.db_domain”)

既然是动态注册,那缘何注册失败呢? 文档 ID 365314.1给出了答案:Database Will Not Register With Listener configured on IP instead of Hostname

将主备的{db_unique_name}_DGB.{db_domain}静态entry删掉,host采用hostname,重启监听测试,switchover成功。

由此可见监听配置里还是采用hostname为好,通过本次事件也解惑了萦绕我心头很久的问题,很多时候建库完毕,使用工具创建动态注册的监听,监听状态里会有很多XDB之类的服务,而我改成静态监听之后(每次都用IP)却没有了之前的自动注册的服务,可见这就是根本原因:Database Will Not Register With Listener configured on IP instead of Hostname

https://img.bwcxtech.com/img/202303171616966.png

参考链接:

5分钟,彻底精通Oracle DG切换!

Data Guard配置Broker解决ORA-16664、ORA-16792