现有dgbroker管理的dg下添加一台从库且互相切换

环境:

OS:Centos 7

DB:11.2.0.4

说明:

当前环境一主一从,主从都采用dbbroker管理,现在新添加一台从库,组成1主2从的架构
当前架构:
slnngk->slava
重新部署后:
slnngk->slava
slnngk->slavb

1.主库添加到归档到新从库的参数
alter system set log_archive_config='dg_config=(slnngk,slavea,slaveb)' scope=both; ##填写主备库的db_unique_name
alter system set log_archive_dest_3= 'service=tnsslaveb async valid_for=(online_logfiles,primary_role) db_unique_name=slaveb' scope=both; ##这里service填写配置的备库2的tns,db_unique_name填写备库2的db_unique_name.
alter system set log_archive_dest_state_3=enable scope=both; ##启用归档路径2

2.新从库采用网络复制的方式进行初始化
步骤省略

3.创建spfile启动
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initslaveb.ora';

SQL> startup mount
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size 2257352 bytes
Variable Size 704646712 bytes
Database Buffers 2415919104 bytes
Redo Buffers 17203200 bytes
Database mounted.

3.新从库切换到应用日志模式
alter database recover managed standby database using current logfile disconnect from session;

4.新从库修改dgbroker参数
alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slaveb.dat';
alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slaveb.dat';
alter system set dg_broker_start=true scope=both;

5.主库上操作添加新从库
[oracle@dbmaster ~]$ dgmgrl
DGMGRL> connect sys/oracle
Connected.
DGMGRL> add database 'slaveb' as connect identifier is 'tnsslaveb';
DGMGRL> enable database 'slaveb';

6.新从库添加静态监听
vi /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = slaveb)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME =slaveb)
)
(SID_DESC =
(GLOBAL_DBNAME = slaveb_DGMGRL)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME =slaveb)
)
)

然后重启监听
[oracle@dbslave02 admin]$ lsnrctl stop
[oracle@dbslave02 admin]$ lsnrctl start

7.主库上查看配置
DGMGRL> show configuration;

Configuration - slnngktest

Protection Mode: MaxPerformance
Databases:
slnngk - Primary database
slavea - Physical standby database
slaveb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

8.主库上查看新添加的备库信息
DGMGRL> show database verbose slaveb;

Database - slaveb

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: OFF
Instance(s):
slaveb

Properties:
DGConnectIdentifier = 'tnsslaveb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'slnngk, slaveb'
LogFileNameConvert = 'slnngk, slaveb'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'slaveb'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbslave02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slaveb_DGMGRL)(INSTANCE_NAME=slaveb)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/archive_log/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

9.修改host为ip地址
DGMGRL> edit database slaveb set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.182)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slaveb_DGMGRL)(INSTANCE_NAME=slaveb)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

10.新备库打开数据库

SQL> alter database open;

Database altered.

11.切换测试
主库切换为slavea
DGMGRL> switchover to slavea
Performing switchover NOW, please wait...
Operation requires a connection to instance "slavea" on database "slavea"
Connecting to instance "slavea"...
Connected.
New primary database "slavea" is opening...
Operation requires startup of instance "slnngk" on database "slnngk"
Starting instance "slnngk"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "slavea"

DGMGRL> show configuration;

Configuration - slnngktest

Protection Mode: MaxPerformance
Databases:
slavea - Primary database
slnngk - Physical standby database
slaveb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

主库切换为slaveb
DGMGRL> switchover to slaveb
Performing switchover NOW, please wait...
Operation requires a connection to instance "slaveb" on database "slaveb"
Connecting to instance "slaveb"...
Connected.
New primary database "slaveb" is opening...
Operation requires startup of instance "slavea" on database "slavea"
Starting instance "slavea"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "slaveb"
DGMGRL> show configuration;

Configuration - slnngktest

Protection Mode: MaxPerformance
Databases:
slaveb - Primary database
slnngk - Physical standby database
slavea - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

主库切换为slnngk
DGMGRL> swtichover to slnngk
Unrecognized command "swtichover", try "help"
DGMGRL> switchover to slnngk
Performing switchover NOW, please wait...
Operation requires a connection to instance "slnngk" on database "slnngk"
Connecting to instance "slnngk"...
Connected.
New primary database "slnngk" is opening...
Operation requires startup of instance "slaveb" on database "slaveb"
Starting instance "slaveb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "slnngk"
DGMGRL> show configuration;