配置dataguard broker并主从切换
环境:
OS:Centos 7
DB:11.2.0.4
建议:
observer不要部署在主从库上，部署到另外一台机器上，若实在没有资源，可以部署到从库上面。
因为部署在主库上的话,主库机器宕机了,observer进程也没有了。
1.搭建dataguard环境
步骤省略
2.配置相应参数
主库
alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slnngk.dat' scope=both;
alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slnngk.dat' scope=both;
备库
alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr1slavea.dat' scope=both;
alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/db_1/dbs/dr2slavea.dat' scope=both;
文件名称无关紧要,可以随便取,一般放dbs目录下,若是asm的需要放共享磁盘上.
启用BROKER(主备库均执行)
alter system set dg_broker_start=true scope=both;
3.主从看需要配置好tns
[oracle@dbslave01 admin]$ more tnsnames.ora
tnsslnngk =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.180)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = slnngk)
)
)
tnsslavea =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.181)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = slavea)
)
)
4.主库添加静态监听
vi /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = slnngk_DGMGRL)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME =slnngk)
)
(SID_DESC =
(GLOBAL_DBNAME = slnngk)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME =slnngk)
)
)
然后重启动监听器
[oracle@dbmaster admin]$ lsnrctl stop
[oracle@dbmaster admin]$ lsnrctl start
[oracle@dbmaster admin]$ lsnrctl status
5.备库添加静态监听器
vi /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = slavea)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME =slavea)
)
(SID_DESC =
(GLOBAL_DBNAME = slavea_DGMGRL)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME =slavea)
)
)
[oracle@dbslave01 admin]$ lsnrctl stop
[oracle@dbslave01 admin]$ lsnrctl start
[oracle@dbslave01 admin]$ lsnrctl status
如不想配置静态监听的话,可以手工修改配置
DGMGRL> edit database slnngk set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.180)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit database slavea set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.181)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
6.创建并启用配置文件(主库上执行)
主库上执行即可
[oracle@dg admin]$ dgmgrl sys/oracle@tnsslnngk; ##这里的tnsslnngk是连接主库的tns,也可以连接备库
添加主库
DGMGRL> create configuration slnngktest as primary database is 'slnngk' connect identifier is 'tnsslnngk';
Configuration "slnngktest" created with primary database "slnngk"
slnngktest:配置别名,可以随意取
slnngk:主库的db_unique_name
tnsslnngk:连接主库的tns
#启用配置文件(这个时候生成主库的配置文件dr1slnngk.dat,dr2slnngk.dat)
DGMGRL> enable configuration;
Enabled.
这个时候可以看到主库的日志输出:
RSM0 started with pid=32, OS id=4157
ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='slnngk';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='slnngk';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='slavea','slnngk' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='slavea','slnngk' SCOPE=SPFILE;
#添加备库(也是在主库上执行,这个时候会生成备库的元数据文件,同时会应用之前没有应用的归档)
DGMGRL> add database 'slavea' as connect identifier is 'tnsslavea';
DGMGRL> enable database 'slavea';
slavea:备库的db_unique_name
tnsslaveb:连接备库的tns
这个时候可以看到备库的输出日志:
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='slavea';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='slavea';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='slnngk','slavea' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='slnngk','slavea' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='tnsslnngk' SCOPE=BOTH;
7.查看配置信息
DGMGRL> show configuration
Configuration - slnngktest
Protection Mode: MaxPerformance
Databases:
slnngk - Primary database
slavea - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
8.查看主备库配置详细信息
主库
DGMGRL> show database verbose slnngk;
Database - slnngk
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
slnngk
Properties:
DGConnectIdentifier = 'tnsslnngk'
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 = 'slavea, slnngk'
LogFileNameConvert = 'slavea, slnngk'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'slnngk'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbmaster)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk_DGMGRL)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
备库
DGMGRL> show database verbose slavea
Database - slavea
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: 0 Byte/s
Real Time Query: ON
Instance(s):
slavea
Properties:
DGConnectIdentifier = 'tnsslavea'
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, slavea'
LogFileNameConvert = 'slnngk, slavea'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'slavea'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbslave01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea_DGMGRL)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/archive_log/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
--这里需要注意的一点是:
SERVICE_NAME=slnngk_DGMGRL 这个service_name参数，默认格式为db_unique_name_DGMGRL,如果这里采用默认格式，那么主备库的监听，就要配置静态监听.
下面进行添加主备静态监听,同时需要在两台机器上配置hosts文件,因为主备的配置HOST=dbmaster,HOST=dbslave01 需要进行解析
将如下两项加入到/etc/hosts文件中
192.168.56.180 dbmaster
192.168.56.181 dbslave01
若是不想修改hosts文件的话,可以修改配置,写死ip,如下
DGMGRL> edit database slnngk set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.180)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slnngk_DGMGRL)(INSTANCE_NAME=slnngk)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit database slavea set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.181)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=slavea_DGMGRL)(INSTANCE_NAME=slavea)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
9.switchover切换测试(切换操作在主从可以上都可以的)
[oracle@dbmaster ~]$ 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> connect sys/oracle
Connected.
在当前的主库上执行,将主库角色切换成从库,to后面是新主库
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> 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 "slavea" on database "slavea"
Starting instance "slavea"...
Unable to connect to database
ORA-12545: Connect failed because target host or object does not exist
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up instance "slavea" of database "slavea"
这里需要手工启动备库
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
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.
Database opened.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG
9 rows selected.
10.说明
a.dgbroker管理后,从库启动后自动会启用应用进程,而不需要手工启动
以上的配置是没有开启Fast-Start Failover,开启的话参考如下连接地址:
https://www.cnblogs.com/hxlasky/p/15194374.html
--The End --
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
2019-08-20 ssdb双主部署