上一篇文章我们简单介绍了一下 DG Broker 及简单配置，本篇文章先展示一下它切库到底有多方便，之后的文章再具体介绍管理功能。
1
配置静态监听是DG Broker 切库中非常重要的一个步骤，这里重点强调一下。且少静态监听切换过程中会报错，文章后面会讲，我们先按照要求来~~
修改主库监听
SID_LIST_LISTENER=
加入：
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME=test_pri_DGMGRL)
(ORACLE_HOME=/home/oracle/app/product/11.2.0/db_1)
(SID_NAME=test)) )
修改备库监听
SID_LIST_LISTENER=
加入：
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME=test_std_DGMGRL)
(ORACLE_HOME=/home/oracle/app/product/11.2.0/db_1)
(SID_NAME=test)))
格式为：db_unique_name + '_DGMGRL'
修改完成后需要lsnrctl reload 生效。
以主库为例，查看lsnrctl status 出现test_pri_dgmgrl
Services Summary...
Service "TEST_PRI" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "TEST_PRI_DGB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test_pri_dgmgrl" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "test_std" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
2
Switchover切换
使用SWITCHOVER命令切换主备库的角色，一条命令搞定，但是执行切换前，确认以下几点：
1) 主备库状态分别为TRANSPORT-ON 和 APPLY-ON。
查看配置状态：为success
DGMGRL> SHOW CONFIGURATION;
Configuration - test
Protection Mode: MaxPerformance
Databases:
TEST_PRI - Primary database
test_std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
查看主库状态：
DGMGRL> show database verbose 'TEST_PRI';
Database - TEST_PRI
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Properties:
DGConnectIdentifie= 'test_pri'
ObserverConnectIdentifier= ''
LogXptMode= 'ASYNC'
DelayMins= '0'
Binding= 'optional'
MaxFailure= '0'
MaxConnections = '1'
ReopenSecs= '120'
NetTimeout= '30'
RedoCompression= 'DISABLE'
LogShipping= 'ON'
PreferredApplyInstance= ''
ApplyInstanceTimeout= '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries= '(monitor)'
LogXptStatus= '(monitor)'
RecvQEntries= '(monitor)'
ApplyLagThreshold= '0'
TransportLagThreshold= '0'
TransportDisconnectedThreshold= '30'
SidName= 'test'
StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.22)(PORT=9539))(CONNECT_DATA=(SERVICE_NAME=TEST_PRI_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/home/oracle/arch'
AlternateLocation= ''
LogArchiveTrace= '0'
LogArchiveFormat= 'test_%t_%s_%r.arc'
TopWaitEvents= '(monitor)'
Database Status:
SUCCESS
查看备库状态：
DGMGRL> show database verbose 'test_std';
Database - test_std
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 37.00 KByte/s
Real Time Query: ON
Instance(s):
test
Properties:
DGConnectIdentifier = 'test_std'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '120'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout= '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/, '
LogFileNameConvert = '/, '
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'test'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.21)(PORT=9539))(CONNECT_DATA=(SERVICE_NAME=test_std_DGMGRL)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/home/oracle/arch/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
如果上述状态中出现警告和错误、请在切换前解决。
2) 确认监听静态注册dgmgrl服务
lsnrctl status确认dgmgrl服务已经注册成功。否则，在switchover时会遇到以下报错和警告：
切换到test_std备库后，需要手工启动旧主库的实例。
DGMGRL> switchover to 'test_std';
Performing switchover NOW, please wait...
Operation requires a connection to instance "test" on database "test_std"
Connecting to instance "test"...
Connected.
New primary database "test_std" is opening...
Operation requires startup of instance "test" on database "TEST_PRI"
Starting instance "test"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up instance "test" of database "TEST_PRI"
切换失败，因为没有静态监听无法连接到oracle，提示需要在test_pri上启动test实例完成switchover切换。
3) 确保主库上有standby redo log
4) 检查redo传输和应用服务的参数
传输参数比如LogXptMode, NetTimeout, StandbyArchiveLocation, and AlternateLocation。
应用参数比如DelayMins。
5) 确保临时表空间和文件在备库存在，和主库一致
6) 发起切换命令
DGMGRL> switchover to 'test_std';
Performing switchover NOW, please wait...
Operation requires a connection to instance "test" on database "test_std"
Connecting to instance "test"...
Connected.
New primary database "test_std" is opening...
Operation requires startup of instance "test" on database "TEST_PRI"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "test_std"
切换成功。
7) 确认切换后状态：
DGMGRL> SHOW CONFIGURATION;
Configuration - test
Protection Mode: MaxPerformance
Databases:
test_std - Primary database
TEST_PRI - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
8) 主库、物理备、逻辑备系统下的主备（物理）切换
主库和物理备库切换
当前TEST_PRI为主库：
DGMGRL> show configuration;
Configuration - test
Protection Mode: MaxPerformance
Databases:
TEST_PRI - Primary database
test_std - Physical standby database
test_his - Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
切换物理备库test_std为主库：
DGMGRL> switchover to 'test_std';
Performing switchover NOW, please wait...
Operation requires a connection to instance "test" on database "test_std"
Connecting to instance "test"...
Connected.
New primary database "test_std" is opening...
Operation requires startup of instance "test" on database "TEST_PRI"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "test_std"
确认，这时test_std为主库，逻辑备库test_his可用。
DGMGRL> show configuration;
Configuration - test
Protection Mode: MaxPerformance
Databases:
test_std - Primary database
TEST_PRI - Physical standby database
test_his - Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
如果将逻辑备库切换为主库，则DG系统内所有的物理备库和快照备库失效，必须重建。
3
手动failover切换
1) 开启数据库级别闪回
在所有主备库都开启数据库级别的闪回功能，这样在failover到物理备库后，可以将其他库修复重新加入DG，避免重建。
2) 连接到目标备库,查看配置状态
DGMGRL> connect sys@test_std;
Password:******
Connected.
DGMGRL> show configuration;
Configuration - test
Protection Mode: MaxPerformance
Databases:
TEST_PRI - Primary database
test_std - Physical standby database
test_his - Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
3) 执行failover命令
DGMGRL> FAILOVER TO 'test_std';
Performing failover NOW, please wait...
Failover succeeded, new primary is "test_std"
切换成功，新主库是test_std
4) 查看配置状态
DGMGRL> show configuration;
Configuration - test
Protection Mode: MaxPerformance
Databases:
test_std - Primary database
TEST_PRI - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
test_his - Logical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
原主库和逻辑备库状态都是disabled，需要修复。
5) 查看新主库状态
DGMGRL> show database 'test_std';
Database - test_std
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Database Status:
SUCCESS
6) 查看旧主库和逻辑备库状态
DGMGRL> show database 'TEST_PRI';
Database - TEST_PRI
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
test
Database Status:
ORA-16661: the standby database needs to be reinstated
DGMGRL> show database 'test_his';
Database - test_his
Role: LOGICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Apply Rate: (unknown)
Instance(s):
test
Database Status:
ORA-16661: the standby database needs to be reinstated
7) 修复旧主库
需要先将旧主库启动到mount状态。
然后发起修复命令
DGMGRL> REINSTATE DATABASE 'TEST_PRI';
Reinstating database "TEST_PRI", please wait...
Operation requires shutdown of instance "test" on database "TEST_PRI"
Shutting down instance "test"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "test" on database "TEST_PRI"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "TEST_PRI" ...
Reinstatement of database "TEST_PRI" succeeded
DGMGRL> show configuration;
Configuration - test
Protection Mode: MaxPerformance
Databases:
test_std - Primary database
TEST_PRI - Physical standby database
test_his - Logical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
8) 修复逻辑备库
需要先将逻辑备库启动到mount状态。
然后发起修复命令
DGMGRL> REINSTATE DATABASE 'test_his';
Reinstating database "test_his", please wait...
Operation requires shutdown of instance "test" on database "test_his"
Shutting down instance "test"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "test" on database "test_his"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "test_his" ...
Reinstatement of database "test_his" succeeded
DGMGRL> show configuration;
Configuration - test
Protection Mode: MaxPerformance
Databases:
test_std - Primary database
TEST_PRI - Physical standby database
test_his - Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
你看，不管是 switchover 、 failover 还是 failover 后修复 DG 成员，DG Broker 都是一条命令搞定，是不是真的很方便？不过有的同学就说了，我一条命令都不想敲~有木有办法可以自动切，不用我们管？这位同学，我非常欣赏你，你的懒惰是我们前进的动力，我们下回聊~
推荐阅读
欢迎分享
网易乐得DBA组负责网易乐得电商、网易邮箱、网易技术部数据库日常运维，负责数据库私有云平台的开发和维护，负责数据库及数据库中间件的开发和测试等，分享最前沿实用数据库干货，关注网易乐得DBA，精修数据库功底。
