一、DG
Broker配置
1.
DG当前测试环境配置
|
Database
Name |
MSDB |
MSDB |
|
Database
Unique Name |
msdb |
smdb |
|
Net
Service Name |
msdb |
smdb |
|
Version |
11.2.0.4.0
for x86_64 |
11.2.0.4.0
for x86_64 |
|
PSU |
200114 |
200114 |
select name, database_role db_role, controlfile_type cf_type, open_mode,
protection_mode, dataguard_broker dg_broker, guard_status, force_logging from
v$database;
主库：
备库:
2.
DG Broker主备库配置
DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径，存在默认路径。
DG_BROKER_START参数设置实例启动的时候是否自动启动Broker，需要开启。
alter system set dg_broker_start=true;
数据库的archive_dest_n 参数会由DG
BROKER自动进行管理，无需人工干预。
3.
配置主备库listener和tns
此步骤为主备库实例配置静态监听及broker使用的额外静态监听
主库listener.ora：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = msdb)
(ORACLE_HOME
= /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =
msdb)
)
(SID_DESC =
(GLOBAL_DBNAME
= msdb_DGMGRL)
(ORACLE_HOME
= /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =
msdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = shsnc1)(PORT = 1521))
(ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
备库listener.ora：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = smdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = smdb)
)
(SID_DESC =
(GLOBAL_DBNAME = smdb_DGMGRL)
(ORACLE_HOME
= /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =
smdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = shsnc2)(PORT = 1521))
(ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
重启监听生效：
lsnrctl reload
配置主备库tns
主备库tnsnames.ora：
MSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST
= shsnc1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = msdb)
)
)
SMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST
= shsnc2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = smdb)
)
)
4.
创建DataGuard
Broker配置
dgmgrl /
DGMGRL> create configuration 'dg_msdb' as primary database
is 'msdb' connect identifier is msdb;
Configuration "dg_msdb" created with primary database
"msdb"
DGMGRL> add database 'smdb' as connect identifier is
'smdb' maintained as physical;
Database "smdb" added
DGMGRL> show configuration;
Configuration - dg_msdb
Protection Mode: MaxPerformance
Databases:
msdb - Primary database
smdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - dg_msdb
Protection Mode: MaxPerformance
Databases:
msdb - Primary database
smdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
5.
查看主备库broker状态
主库：
备库：
DGMGRL> show database smdb
Database - smdb
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):
smdb
Database Status:
SUCCESS
DGMGRL> show database msdb
Database - msdb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
msdb
Database Status:
SUCCESS
二、配置高可用服务名及触发器
1.
配置primary角色启动的service
客户端应用程序连接串，使用DG_PROD网络服务来连接数据库，采用这种方式来避免DG发生切换时应用程序修改配置文件。
在主库创建主服务：
exec DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME => 'DG_PROD',NETWORK_NAME
=> 'DG_PROD',FAILOVER_METHOD => 'BASIC',FAILOVER_TYPE =>
'SELECT',FAILOVER_RETRIES => 200,FAILOVER_DELAY => 1);
在主库和备库tnsnames.ora中添加DG_PROD网络服务名:
DG_PROD =
(DESCRIPTION_LIST =
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST =shsnc1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DG_PROD)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = shsnc2)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = DG_PROD)
)
)
)
在主库创建触发器代码会自动同步到备库，触发器创建完成后需要手动触发一次才能在listener中注册DG_PROD服务（可通过switchover或重启数据库的方式触发）：
create trigger manage_services after startup on database
declare
role varchar2(30);
omode varchar2(30);
begin
select database_role into role
from v$database;
select open_mode into omode from
v$database;
if role='PRIMARY' then
dbms_service.start_service
('DG_PROD');
elsif role='PHYSICAL STANDBY' then
if omode like 'READ ONLY%' then
dbms_service.start_service
('DG_RTQ');
end if;
elsif role='LOGICAL STANDBY' then
dbms_service.start_service
('DG_LSBY');
elsif role='SNAPSHOT STANDBY' then
dbms_service.start_service
('DG_SNAP');
end if;
end;
/
三、switchover及failover切换测试
1.
switchover
dgmgrl sys/oracle
DGMGRL> switchover
to smdb
Performing switchover NOW, please wait...
Operation requires a connection to instance "smdb" on database
"smdb"
Connecting to instance "smdb"...
Connected.
New primary database "smdb" is opening...
Operation requires startup of instance "msdb" on database
"msdb"
Starting instance "msdb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "smdb"
DGMGRL> show database msdb
Database - msdb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
msdb
Database Status:
SUCCESS
DGMGRL> show database smdb
Database - smdb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
smdb
Database Status:
SUCCESS
检查switchover后主备库状态：
smdb成为主库：
msdb成为备库：
6.
failover
failover命令：
failover to database-name [immediate];
在主库发生故障时需要手动failover，将主库切换到备库。此时原主库需要人为介入进行故障诊断和修复，修复后在DG Broker中执行reinstate database [ db_name] 命令可以重新将原主库转换为备库恢复为新的主备关系，若需要还原为初始环境执行switchover即可。
reinstate
database [ db_name] 启用该功能修复，需要数据库开启闪回。
在原主库极端故障无法修复的情况下，需要重新建立DG环境。此时主库是启用了DG
broker的，修复后的备库需要执行enable database [ db_name] 加入broker环境。
7.
failover时连接测试
当前主备库状态
主库：
备库：
通过DG_PROD连接数据库
sqlplus system/oracle@DG_PROD
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 16 15:45:19 2020
Copyright (c) 1982, 2013, Oracle.
All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name
string
db_file_name_convert
string
db_name
string msdb
db_unique_name
string msdb
global_names
boolean FALSE
instance_name
string msdb
lock_name_space
string
log_file_name_convert
string
processor_group_name
string
service_names
string DG_PROD
此时将msdb主库shutdown abort , dg borker failover 到smdb，查看该连接状态。
[oracle@shsnc1 admin]$ sqlplus system/oracle@DG_PROD
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 16 16:35:55 2020
Copyright (c) 1982, 2013, Oracle.
All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name
string
db_file_name_convert
string
db_name
string msdb
db_unique_name string msdb
global_names
boolean FALSE
instance_name
string msdb
lock_name_space
string
log_file_name_convert
string
processor_group_name
string
service_names
string DG_PROD
SQL>
SQL> show parameter name
NAME TYPE
VALUE
------------------------------------ -----------
------------------------------
cell_offloadgroup_name
string
db_file_name_convert
string
/u01/app/oracle/oradata/msdb,
/u01/app/oracle/oradata/smdb
db_name
string msdb
db_unique_name string smdb
global_names
boolean FALSE
instance_name string smdb
lock_name_space
string
log_file_name_convert string /u01/app/oracle/oradata/msdb,
/u01/app/oracle/oradata/smdb
processor_group_name
string
service_names
string DG_PROD
使用该方法配置，在主库故障failover到备库时连接查询正常。在failover的过程进行中可能会出现连接中断的情况，建议在应用连接池或中间件中配置自动重连机制。
四、附录
1.
fast_start failover相关配置
fast_start
failover功能仅作参考，生产环境中不建议开启该功能
1.
保证主从数据库的闪回数据库功能以及强制归档都打开
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FOR
------------------ ---
YES YES
2.
开启fast-start failover
DGMGRL> enable fast_start failover;
Enabled.
c.
启动观察器（observer）
由于observer的启动会一直占用session 窗口的，所以建议写成脚本挂后台
在备库上创建脚本，具体如下
创建一个名叫observer.sql的脚本，把下面的命令放进去，然后执行该脚本即可。
nohup dgmgrl sys/oracle "start
observer">>observer.log 2>&1 &
d.
查看fast-start failover 状态
e.
验证自动切换，msdb主库模拟异常关闭
shutdown abort;
查看smdb状态,自动切换为主库。
8.
DG Broker常用命令
dgmgrl username/passwd --进入命令行
show configuration --查看当前DG borker配置文件
show database db_name --查看数据库状态信息
show database verbose db_name --查看数据库详细配置信息
switchover to db_name --正常切换主备数据库
failover to db_name --故障时切换
reinstate database db_name --将故障的原主库作为备库加入BROKER，需开启闪回
help --查看帮助
