前一篇 Oracle DG 手动 SwitchOver & Failover,Failover后利用闪回恢复同步_Hehuyi_In的博客-CSDN博客介绍了SwitchOver & Failover的概念、区别及利用sql操作的方法。可以看到利用sql来执行需要在主从库均进行操作,命令也多,较为复杂,本篇来看如何利用DG Broker来简化这些操作。
注意:配置期间已有主从同步会中断,若当前从库已在使用,需要告知业务方,申请操作窗口
一、 DG Broker安装与配置
现有架构:以一主两备为例 | ||||
---|---|---|---|---|
数据库 IP | db_unique_name | 配置DG Broker时角色 | switchover后角色 | failover后角色 |
192.168.3.224 | prodcdb | 从库 | 主库 | 从库 |
192.168.3.225 | prodcdb_dg | 主库 | 从库 | 主库 |
192.168.3.226 | prodcdb_dg02 | 从库 | 从库 | 从库 |
1. DG Broker安装
DG Broker其实就是dgmgrl工具,安装Oracle数据库软件或完整的客户端都包含该工具,本文略。
2. 监听修改
按下面修改监听配置,如果不修改利用原有的监听也可以,后面会介绍这种方法
prodcdb_dg(主库)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pdb_fsfo)
(SID_NAME = prodcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = prodcdb)
(ORACLE_HOME = /data/prd/oracle/database/19.3.0.0/prod_cdb)
(SID_NAME = prodcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = prodcdb_dg_DGMGRL) # 新增部分,名称为db_unique_name_DGMGRL
(ORACLE_HOME = /data/prd/oracle/database/19.3.0.0/prod_cdb)
(SID_NAME = prodcdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.225)(PORT = 1521))
)
)
)
prodcdb(备库1)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pdb_fsfo)
(SID_NAME = prodcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = prodcdb)
(ORACLE_HOME = /data/prd/oracle/database/19.3.0.0/prod_cdb)
(SID_NAME = prodcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = prodcdb_DGMGRL) # 新增部分,名称为db_unique_name_DGMGRL
(ORACLE_HOME = /data/prd/oracle/database/19.3.0.0/prod_cdb)
(SID_NAME = prodcdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.224)(PORT = 1521))
)
)
)
prodcdb_dg02(备库2)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pdb_fsfo)
(SID_NAME = prodcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = prodcdb)
(ORACLE_HOME = /data/prd/oracle/database/19.3.0.0/prod_cdb)
(SID_NAME = prodcdb)
)
(SID_DESC =
(GLOBAL_DBNAME = prodcdb_dg02_DGMGRL) # 新增部分,名称为db_unique_name_DGMGRL
(ORACLE_HOME = /data/prd/oracle/database/19.3.0.0/prod_cdb)
(SID_NAME = prodcdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.226)(PORT = 1521))
)
)
)
reload 监听生效,reload后应该能看到 _DGMGRL相关信息
lsnrctl reload
lsnrctl status
3. 配置tnsnames.ora
三个节点均需配置
# add for DGMGRL
prodcdb_DGMGRL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.224)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodcdb_DGMGRL)
)
)
prodcdb_dg_DGMGRL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.225)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodcdb_dg_DGMGRL)
)
)
prodcdb_dg02_DGMGRL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.247)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodcdb_dg02_DGMGRL)
)
)
4. 清空远端LOG_ARCHIVE_DEST_n 信息
三个节点均需配置,这步开始,已有主从同步会中断。
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='';
如果不清空,后面添加从库时会报错
DGMGRL> add database prodcdb as connect identifier is prodcdb_DGMGRL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set Failed.
5. 修改dg_broker_config_file参数(可选)
可以用默认的路径,也可以自己指定(三个节点均需配置)。如果是RAC环境,把这个文件把到共享存储或者ASM中。
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /data/prd/oracle/database/19.3
.0.0/prod_cdb/dbs/dr1prodcdb.d
at
dg_broker_config_file2 string /data/prd/oracle/database/19.3
.0.0/prod_cdb/dbs/dr2prodcdb.d
at
6. 启用BROKER
三个节点均需配置
alter system set dg_broker_start=true;
7. 创建BROKER配置
任意节点执行即可,这里选择在主库(prodcdb_dg)执行
create configuration <配置名(随意)> as primary database is <db_unique_name> connect identifier is <TNS文件中对应名称>;
dgmgrl
DGMGRL> connect sys/"xxxx"
DGMGRL> create configuration prodcdb_dgmgrl as primary database is prodcdb_dg connect identifier is prodcdb_dg_DGMGRL;
8. 启用配置文件
DGMGRL>enable configuration
-- 查看配置文件
DGMGRL> show configuration
9. 添加两个从节点
add database <备库db_unique_name> as connect identifier is <TNS文件中对应名称> maintained as physical;
DGMGRL> add database prodcdb as connect identifier is prodcdb_DGMGRL;
DGMGRL> add database prodcdb_dg02 as connect identifier is prodcdb_dg02_DGMGRL;
10. enable两个从库
DGMGRL> enable database prodcdb;
DGMGRL> enable database prodcdb_dg02;
11. 查看配置及DB信息
至此配置完成,可以查看配置和从库信息
DGMGRL> show configuration
Configuration - prodcdb_dgmgrl
Protection Mode: MaxPerformance
Members:
prodcdb_dg - Primary database
prodcdb - Physical standby database
prodcdb_dg02 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 59 seconds ago)
DGMGRL> show database prodcdb
Database - prodcdb
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: 6.00 KByte/s
Real Time Query: ON
Instance(s):
prodcdb
Database Status:
SUCCESS
二、 利用DG Broker 进行 SwitchOver
- 首先检查下目标主库延迟
DGMGRL> show database prodcdb
Database - prodcdb
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: 6.00 KByte/s
Real Time Query: ON
Instance(s):
prodcdb
- 切换变得简单无比,就一条命令
DGMGRL> switchover to prodcdb;
DGMGRL> switchover to prodcdb;
Performing switchover NOW, please wait...
Operation requires a connection to database "prodcdb"
Connecting ...
Connected to "prodcdb"
Connected as SYSDBA.
New primary database "prodcdb" is opening...
Operation requires start up of instance "prodcdb" on database "prodcdb_dg"
Starting instance "prodcdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "prodcdb_dg"
Database mounted.
Database opened.
Connected to "prodcdb_dg"
Switchover succeeded, new primary is "prodcdb"
- 查看切换后配置信息
DGMGRL> show configuration
Configuration - prodcdb_dgmgrl
Protection Mode: MaxPerformance
Members:
prodcdb - Primary database
prodcdb_dg - Physical standby database
prodcdb_dg02 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 56 seconds ago)
发现SwitchOver已经成功完成了
- 查看切换后DB信息
DGMGRL> show database prodcdb_dg
Database - prodcdb_dg
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: 23.00 KByte/s
Real Time Query: ON
Instance(s):
prodcdb
Database Status:
SUCCESS
三、 利用DG Broker 进行 Failover
1. 模拟计划外切换场景
- 主库每秒执行一条insert语句
create table temp0315(a date);
begin
for i in 1 .. 1800 loop
insert into temp0315 values(sysdate);
commit;
DBMS_LOCK.SLEEP(1);
end loop;
end;
/
- insert过程中执行shutdown abort强行关闭该库
shutdown abort
- 尝试failover至从库
2. 开始failover
- 首先检查下目标主库延迟
DGMGRL> show database prodcdb_dg
Database - prodcdb_dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 59 seconds ago)
Apply Lag: 0 seconds (computed 59 seconds ago)
Average Apply Rate: 6.00 KByte/s
Real Time Query: ON
Instance(s):
prodcdb
Database Warning(s):
ORA-16857: member disconnected from redo source for longer than specified threshold
Database Status:
WARNING
- failover切换也变得简单无比,就一条命令
DGMGRL> failover to prodcdb_dg;
DGMGRL> failover to prodcdb_dg
Performing failover NOW, please wait...
Failover succeeded, new primary is "prodcdb_dg"
- 查看切换后配置信息
DGMGRL> show configuration
Configuration - prodcdb_dgmgrl
Protection Mode: MaxPerformance
Members:
prodcdb_dg - Primary database
Warning: ORA-16857: member disconnected from redo source for longer than specified threshold
prodcdb - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
prodcdb_dg02 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 93 seconds ago)
- 查看切换后DB信息
DGMGRL> show database prodcdb_dg02
Database - prodcdb_dg02
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: 5.00 KByte/s
Real Time Query: ON
Instance(s):
prodcdb
Database Status:
SUCCESS
补充:
如手工切换时failover有两类,使用DG Broker也有两种failover方法
- Complete Failover(默认):尝试先将所有重做日志应用到备库,最大程度地减少数据丢失。
- Immediate Failover :不再对备库进行日志应用,立刻进行切换。
DGMGRL> FAILOVER TO database-name;
# 或者
DGMGRL> FAILOVER TO database-name IMMEDIATE;
四、 Failover后恢复主从关系
为使REINSTATE命令成功执行,故障转移之前必须已在原主库(新备库)上启用闪回,并且必须有足够的闪回和归档日志。
1. 启动到mount状态
如果有开机自启动的,startup过程中会报错 ORA-16649: possible failover to another database prevents this database from being opened,会发现只能起到mount状态,没关系,继续后面的操作。
startup mount
2. 恢复主从关系
连接到代理配置中的任何数据库(要恢复的数据库除外)时,执行:
DGMGRL> reinstate database prodcdb;
Reinstating database "prodcdb", please wait...
Reinstatement of database "prodcdb" succeeded
如果能完成,它将变为新主库的备库。如果失败,其状态将更改为 ORA-16795: the standby database needs to be re-created。那就没办法,只能重搭主从了。
3. 查看切换后配置信息
DGMGRL> show configuration
Configuration - prodcdb_dgmgrl
Protection Mode: MaxPerformance
Members:
prodcdb_dg - Primary database
prodcdb - Physical standby database
prodcdb_dg02 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 47 seconds ago)
4. 查看切换后DB信息
DGMGRL> show database prodcdb
Database - prodcdb
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: 14.00 KByte/s
Real Time Query: ON
Instance(s):
prodcdb
Database Status:
SUCCESS
参考
https://blog.csdn.net/shiyu1157758655/article/details/55504026
Oracle DG 手动 SwitchOver & Failover,Failover后利用闪回恢复同步_Hehuyi_In的博客-CSDN博客