DG Broker即Data Guard Broker，是Oracle官方提供的一个用于维护DG的工具。我们可以通过dgmgrl这个命令行工具来使用DG Broker。
一、DgBroker 配置
1.1主库listener.ora 配置
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = misdb_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = misdb)
)
(SID_DESC=
(GLOBAL_DBNAME = misdb_DGB)
(SID_NAME=misdb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
)
1.2备库listener.ora 配置
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME = misdg_DGMGRL)
(SID_NAME=misdb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME = misdg_DGB)
(SID_NAME=misdb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
)
???? 注意： 如果不配置静态监听报如下错误：（misdg_DGMGR、misdg_DGB都需要配置）
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.137.108)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=misdg_DGB
1.3主备库端设置dg_broker_start
SQL> alter system set dg_broker_start = true scope=both ;
System altered.
[oracle@master dbhome_1]$ ps -ef |grep dmo
oracle 30995 1 0 21:17 ? 00:00:00 ora_dmon_misdb
1.4、创建配置文件
使用DGMGRL命令工具创建配置文件 设置主库：
dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> create configuration misdbdg as primary database is misdb connect identifier is misdb;
Configuration "misdbdg" created with primary database "misdb"
DGMGRL> show configuration;
Configuration - dg_misdb
Protection Mode: MaxPerformance
Databases:
misdb - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
(解释：其中 misdbdg是随便起的名字，misdb是主的数据库唯一名，identifier is是tnsnames.ora的网络服务名)
添加备库：
DGMGRL> add database misdg as connect identifier is misdg maintained as physical;
Database "misdg" added
identifier is 'misdg' 这里的 misdg 是你 tnsnames.ora 里面关于备库的那个名字
DGMGRL> enable configuration ;
DGMGRL> show configuration;
Configuration - dg_misdb
Protection Mode: MaxPerformance
Databases:
misdb - Primary database
misdg - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
1.5、激活配置文件
DGMGRL>enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - misdbdg
Protection Mode: MaxPerformance
Databases:
misdb - Primary database
misdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
REMOVE CONFIGURATION; 删除配置
dg_broker配置完成.
二、 利用DG Broker 进行 SwitchOver
2.1首先检查下目标主库延迟
DGMGRL> show database misdg
Database - misdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
misdb
Database Status:
SUCCESS
2.2切换switchover：
DGMGRL> switchover to misdb
Performing switchover NOW, please wait...
New primary database "misdb" is opening...
Operation requires startup of instance "misdb" on database "misdg"
Starting instance "misdb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "misdb"
说明： Dg Broker 就一条命令
2.3查看切换后配置信息
DGMGRL> show configuration;
Configuration - misdbdg
Protection Mode: MaxPerformance
Databases:
misdb - Primary database
misdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
2.4查看切换后db
DGMGRL> show database misdg
Database - misdb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
misdg
Database Status:
SUCCESS
???? 注意： SwitchOver已经成功完成了,只需要一条命令。
三、手动切换
3.1将主库切换为物理备库
查看主库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ WRITE PRIMARY SESSIONS ACTIVE YES ENABLED NONE
???? 注意： 需要检查SWITCHOVER_STATUS参数，如果值为"SESSION ACTIVE"或者"TO STANDBY", 则主数据库角色可以切换为备库角色。
查看主库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ WRITE PRIMARY SESSIONS ACTIVE YES ENABLED NONE
将其切换到备库，切换后，数据库会关闭
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 94049
Session ID: 150 Serial number: 3
???? 注意： 如果上一步的SWITCH_STATUS参数值为"TO STANDBY"，则 WITH SESSION SHUTDOWN 可以省略。 11.2.0.4版本及其以上版本不需要执行"SHUTDOWN ABORT"，因为数据库已经在命令中关闭了。
3.2将备库切换成主库并启动到 open
查看备库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY YES ENABLED NONE
???? 注意： 查看备库是否可以切换至主库（ SWITCHOVER_STATUS的值为TO PRIMARY或者为SESSIONS ACTIVE都可以切换至主库） 切换原备库至新主库
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
MOUNTED PRIMARY NOT ALLOWED YES ENABLED NONE
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ WRITE PRIMARY TO STANDBY YES ENABLED NONE
新的备库开启日志应用
SQL> startup
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size 2253544 bytes
Variable Size 956304664 bytes
Database Buffers 587202560 bytes
Redo Buffers 7544832 bytes
Database mounted.
Database opened.
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ ONLY PHYSICAL STANDBY TO PRIMARY YES ENABLED NONE
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
通过手动切换步骤，以下是一些注意事项： 检查 SWITCHOVER_STATUS：在进行切换前，确保 SWITCHOVER_STATUS 参数允许切换。 关闭和启动数据库：在切换过程中，确保数据库正确关闭和启动。 日志应用：切换完成后，确保新的备库开启日志应用。 至此 Dg broker 和手动切换已经完成。
四、总结
| 对比项 | Data Guard Broker 切换 | 手动切换 |
| 复杂度 | 低，自动化程度高 | 高，需要精通 Data Guard 的操作 |
| 出错概率 | 低，Broker 自动校验 | 高，依赖 DBA 的经验和操作准确性 |
| 适用场景 | 标准环境，期望快速、自动化切换 | 特殊需求或 Broker 不可用的情况 |
| 切换速度 | 快，流程简化 | 慢，需逐步手动执行每一步操作 |
| 维护成本 | 需额外配置 Broker，初期成本略高 | 无需额外组件，操作成本取决于复杂度 |
