前言
DG搭建
参考当前文档
https://www.modb.pro/db/1890265580175896576DG Broker配置
1、主节点更改listener.ora文件
需要添加的listener.ora配置如下：
(SID_DESC =
(GLOBAL_DBNAME = master_dgmgrl)
(ORACLE_HOME = /data/19.3.0/product)
(SID_NAME = master)
)具体信息如下：
Step1:-Creating a Service Listener in primary and standby side(Both Side)
On Primary Side
$ vi listener.ora
MASTER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = master.localdomain.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = master)
(ORACLE_HOME = /data/19.3.0/product)
(SID_NAME = master)
)
(SID_DESC =
(GLOBAL_DBNAME = master_dgmgrl)
(ORACLE_HOME = /data/19.3.0/product)
(SID_NAME = master)
)
)
Reload the listener：重新加载监听即可看到新的配置信息
$ lsnrctl reload
Service "master" has 2 instance(s).
Instance "master", status UNKNOWN, has 1 handler(s) for this service...
Instance "master", status READY, has 1 handler(s) for this service...
Service "masterXDB" has 1 instance(s).
Instance "master", status READY, has 1 handler(s) for this service...
Service "master_CFG" has 1 instance(s).
Instance "master", status READY, has 1 handler(s) for this service...
Service "master_dgmgrl" has 1 instance(s).
Instance "master", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
2、从节点更改listener.ora文件
(SID_DESC =
(GLOBAL_DBNAME = slave_dgmgrl)
(ORACLE_HOME = /data/19.3.0/product)
(SID_NAME = slave)
)具体信息如下：
On Standby Side:
$ vi listener.ora
SLAVE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.62)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = slave)
(ORACLE_HOME = /data/19.3.0/product)
(SID_NAME = slave)
)
(SID_DESC =
(GLOBAL_DBNAME = slave_dgmgrl)
(ORACLE_HOME = /data/19.3.0/product)
(SID_NAME = slave)
)
)
Reload the listener：重新加载监听即可看到新的配置信息
$ lsnrctl reload
Service "master_CFG" has 1 instance(s).
Instance "slave", status READY, has 1 handler(s) for this service...
Service "slave" has 2 instance(s).
Instance "slave", status UNKNOWN, has 1 handler(s) for this service...
Instance "slave", status READY, has 1 handler(s) for this service...
Service "slaveXDB" has 1 instance(s).
Instance "slave", status READY, has 1 handler(s) for this service...
Service "slave_dgmgrl" has 1 instance(s).
Instance "slave", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully3、主节点更改参数
# 开启broker
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both; -- 开启broker
# 注册监听配置信息
SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.61)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = master)))'; -- 配置监听参数
4、从节点更改参数
# 开启Broker
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both; -- 开启broker
# 注册监听配置信息
SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.62)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = slave)))'; -- 配置监听参数
5、主节点登录并创建DGMGRL
# 主节点登录
[oracle@master dbs]$ dgmgrl sys/admin@master
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Nov 12 00:39:44 2025
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.
Connected to "MASTER"
Connected as SYSDBA.
# 查看是否存在broker配置，正常情况下是不会存在Broker配置的，报错的信息
显示的就是没有broker配置，所以此处我们需要新建
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist -- 显示不存在
Configuration details cannot be determined by DGMGRL
# 新建broker
# 注意：在create语句中的单引号用于区分大小写，取决于db_unique_name值
# 第一个参数：‘dgbroker’ 代指当前配置的别名
# 第二个参数：‘master’ 是主节点的db_unqiue_name值
# 第三个参数：‘master’ 也就是最后的值是主节点db_unqiue_name值
DGMGRL> create configuration 'dgbroker' as primary database is 'master' connect identifier is master; -- 新建broker配置，注意‘dbbroker’是别名
Configuration "dgbroker" created with primary database "master"
# 查看配置
DGMGRL> show configuration; -- 校验配置，没有报错或警告即可
Configuration - dgbroker
Protection Mode: MaxPerformance
Members:
master - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED6、DGMGRL中添加从库信息
# 添加从库节点信息，这里的slave都是从节点的db_unique_name指代值
# 第一个参数：‘slave’ 是从节点的db_unqiue_name值
# 第二个参数：‘slave’ 是主节点的db_unqiue_name值
DGMGRL> Add database 'slave' as connect identifier is slave maintained as physical;
Database "slave" added
# 再次校验配置是否正确，没有错误或警告输出即可
DGMGRL> show configuration;
Configuration - dgbroker
Protection Mode: MaxPerformance
Members:
master - Primary database
slave - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED7、启动Broker
# 启动配置
DGMGRL> enable configuration;
Enabled.
# 查看具体配置信息，如果有报错则在此处会显示
DGMGRL> show configuration;
Configuration - dgbroker
Protection Mode: MaxPerformance
Members:
master - Primary database
slave - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 2 seconds ago) -- 此处值为SUCCESS则无报错8、查看配置信息
# 查看当前Broker参数配置
DGMGRL> show configuration verbose;
Configuration - dgbroker
Protection Mode: MaxPerformance
Members:
master - Primary database
slave - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'master_CFG'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS – 没有异常输出即可
# 查看从库是否正常
DGMGRL> show database slave;
Database - slave
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: 0 Byte/s
Real Time Query: ON
Instance(s):
slave
Database Status:
SUCCESS – 没有异常输出即可
# 查看从库的broker详细配置信息
DGMGRL> show database verbose 'slave';
Database - slave
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 0 Byte/s
Active Apply Rate: 17.00 KByte/s
Maximum Apply Rate: 17.00 KByte/s
Real Time Query: ON
Instance(s):
slave
Properties:
DGConnectIdentifier = 'slave'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'slave'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.60.62)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SLAVE_DGMGRL)(INSTANCE_NAME=slave)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /data/19.3.0/diag/rdbms/slave/slave/trace/alert_slave.log
Data Guard Broker log : /data/19.3.0/diag/rdbms/slave/slave/trace/drcslave.log
Database Status:
SUCCESS -- 没有异常输出即可
# 查看主库的broker详细配置信息
DGMGRL> show database verbose 'master';#
Database - master
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
master
Properties:
DGConnectIdentifier = 'master'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'master'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.60.61)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=MASTER_DGMGRL)(INSTANCE_NAME=master)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /data/19.3.0/diag/rdbms/master/master/trace/alert_master.log
Data Guard Broker log : /data/19.3.0/diag/rdbms/master/master/trace/drcmaster.log
Database Status:
SUCCESS -- 没有异常输出即可9、验证主从切换
# 验证主库是否可切换成从库
DGMGRL> validate database 'master';
Database Role: Primary database
Ready for Switchover: Yes -- 当前值为yes即可切换
Flashback Database Status:
master: Off
Managed by Clusterware:
master: NO
Validating static connect identifier for the primary database master...
The static connect identifier allows for a connection to database "master".
# 验证从库是否可切换成主库
注意：由于当前时间关系，没有对redo进行整改，标准作业流程下不能存在警告的字眼
DGMGRL> validate database 'slave';
Database Role: Physical standby database
Primary Database: master
Ready for Switchover: Yes -- 当前值为yes即可切换
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
master: Off
slave : Off
Managed by Clusterware:
master: NO
slave : NO
Validating static connect identifier for the primary database master...
The static connect identifier allows for a connection to database "master".
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(master) (slave)
1 3 2 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(slave) (master)
1 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on master
10、主从切换
# 主从执行切换
注意：
DGMGRL> switchover to 'slave';
Performing switchover NOW, please wait...
Operation requires a connection to database "slave"
Connecting ...
Connected to "SLAVE"
Connected as SYSDBA.
New primary database "slave" is opening...
Operation requires start up of instance "master" on database "master"
Starting instance "master"...
Connected to an idle instance.
ORACLE instance started.
Connected to "MASTER"
Database mounted.
Database opened.
Connected to "MASTER"
Switchover succeeded, new primary is "slave" -- 切换成功即可
11、验证同步即可
# 新主库创建数据执行同步
$ sqlplus / as sysdba
SQL>create table test (id number(10));
SQL>insert into test values(10);
SQL>commmit;
SQL>alter system switch logfile;
# 新从库执行数据校验
$ sqlplus / as sysdba
SQL>select * from test;
-----------
id
10「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
