Data Guard如何重建dg broker?

下面是个人结合文档总结整理的重建dg broker的文档,仅供参考,不一定适合所有环境或场景.

Step 1:停止broker(主库&备库)

SQL> SHOW PARAMETER DG_BROKER_START;
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
SQL> SHOW PARAMETER DG_BROKER_START;

Step 2: 检查或配置监听服务(主库&备库)

检查主库/备库监听文件listener.ora中的配置是否正确,如果只是重建或已经正确配置,直接跳到下一个步骤.

监听文件listener.ora中必须添加一个静态注册的service_name为db_unique_name_DGMGRL.db_domain,其中db_domain是可选项.这个service_name会在
DGMGRL重启数据库的时候用到. 通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL在通过静态监听中的service_name连接到数据库,发送
启动的命令。

例子(监听增加内容):

(SID_DESC =
  (GLOBAL_DBNAME = gsp_DGMGRL)
  (SID_NAME = gsp)
  (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
) 

重启或重新加载监听配置

lsnrctl reload <lisnter_name>
lsnrctl stop <lisnter_name>
lsnrctl start <lisnter_name>

Step 3: 删除旧的broker配置文件(主库&备库)

SQL> show parameter dg_broker_config_file

ASM

ASMCMD> cd /<PATH>/
ASMCMD> rm <FILE_NAME>.dat
ASMCMD> rm <FILE_NAME>.dat

文件系统

rm /opt/oracle19c/product/dbs/dr1gsp.dat
rm /opt/oracle19c/product/dbs/dr2gsp.dat

Step 4： 启用broker(主库&备库)

--in all instances in case of RAC
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

Step 5: 配置参数local_listener(主库&备库)

检查参数local_listener

show parameter local_listener

主库(Primary)

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST =dbtest01)(PORT = 15021))' scope=both;

备库(Standby):

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST =dbtest02)(PORT = 15021))' scope=both;

这个步骤不是必须的,但是设置local_listener有下面一些好处:

• 可以明确告知pmon进程「要向哪个IP/hostname: 端口的监听器注册数据库服务」，解决「监听器绑定特定 IP 但PMON随机选地址导致注册失败」的核心问题;
• 替代默认的1521端口注册逻辑，适配非标准端口(如 15021）、多监听器(如主库/备库不同监听器)场景；
• 支持多网卡/多IP环境，精准指定监听器地址，避免注册到无效网卡。
  
  

Step 6: 在主库连接DGMGRL

DGMGRL> connect sys 
Password:
或
DGMGRL> CONNECT sys/<password>;

$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jul 17 09:52:04 2024
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected to "gsp"
Connected as SYSDBA.
DGMGRL> 

Step 7: 在主库创建配置(configuration)

语法如下:

CREATE CONFIGURATION '<CONFIGURATION_NAME>' AS PRIMARY DATABASE IS '<PRIMARY_DATABASE_NAME>' CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER>;

例子:

DGMGRL> create configuration 'dg_gsp' as primary database is 'gsp' connect identifier is gsp;
Configuration "dg_gsp" created with primary database "gsp"

错误案例1：

DGMGRL> create configuration 'dg_gsp' as primary database is 'gsp' connect identifier is gsp;
Error: 
ORA-16525: The Oracle Data Guard broker is not yet available.

原因分析：dg_broker_start参数值为FALSE.

错误案例2:

DGMGRL> create configuration 'dg_gsp' as primary database is 'test' connect identifier is test;
Error: ORA-16642: DB_UNIQUE_NAME mismatch

Failed.

原因分析,<PRIMARY_DATABASE_NAME>名字弄错了.正确的写法如下所示

CREATE CONFIGURATION '<CONFIGURATION_NAME>' AS PRIMARY DATABASE IS '<PRIMARY_DATABASE_NAME>' CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER>;

<PRIMARY_DATABASE_NAME>应该输入DB_UNIQUE_NAME,可以通过下面SQL查询DB_UNIQUE_NAME

select db_unique_name from v$database;

Step 8: 配置中加入备用数据库(standby)

语法如下:

DGMGRL> ADD DATABASE '<STANDBY_DATABASE_NAME>' AS CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER> MAINTAINED AS PHYSICAL;

例子:

DGMGRL> add database 'gspro' as connect identifier is gspro maintained as physical;
Database "gspro" added
DGMGRL

错误案例1：

DGMGRL> add database 'gspro' as connect identifier is gspro maintained as physical;
Error: ORA-16525: The Oracle Data Guard broker is not yet available.

Failed.

原因分析：dg_broker_start参数值为FALSE.

错误案例2:

DGMGRL> add database 'gspro' as connect identifier is gspro maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
DGMGRL> 

出现这个错误, 是因为之前已经手工设置了LOG_ARCHIVE_DEST_n参数, DG Broker要求"它自己全权管理redo传输"，因此建配置前必须把这些手工配置的参数(例如log_archive_dest_2)的值情况,否则就会报ORA-16698

主库&备库中执行下面SQL

SQL> alter system set log_archive_dest_2='' scope=both;

System altered.

SQL> alter system set log_archive_dest_1='' scope=both;

System altered.

SQL> 
SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL> 

Step 9: 启用配置

DGMGRL> ENABLE CONFIGURATION;
Enabled.

在启用配置时，在主备库执行tail命令观察alert日志信息。

tail -60f alert_<ORACLE_SID>.ora

检查配置信息

错误案例1：

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_gsp

  Protection Mode: MaxPerformance
  Members:
  gsp   - Primary database
    Warning: ORA-16789: standby redo logs configured incorrectly

    gspro - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 5 seconds ago)

DGMGRL>

错误分析：
没有配置standby redo logs,需要重新配置。

DGMGRL> DISABLE CONFIGURATION;
Disabled.
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;

Configuration - dg_gsp

  Protection Mode: MaxPerformance
  Members:
  gsp   - Primary database
    gspro - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 4 seconds ago)

DGMGRL>

Step 10: 检查验证

DGMGRL> show configuration;

DGMGRL> show configuration

Configuration - dg_gsp

  Protection Mode: MaxPerformance
  Members:
  gsp   - Primary database
    gspro - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 8 seconds ago)

DGMGRL> show database gsp

Database - gsp

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    gsp

Database Status:
SUCCESS

DGMGRL> show database gspro

Database - gspro

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 20.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    gsp

Database Status:
SUCCESS

DGMGRL>

参考资料：

• Step By Step How to Recreate Dataguard Broker Configuration (Doc ID 808783.1)
• (KB141840) Step By Step How to Recreate Dataguard Broker Configuration
• (KB151794) 12c Create Dataguard Broker Configuration - DGMGRL