Oracle DG Broker 进行 SwitchOver & Failover，Failover后恢复主从同步

Hehuyi_In

已于 2022-05-17 16:33:59 修改

阅读量3.2k

点赞数

分类专栏：灾备 Oracle 文章标签： oracle 数据库 database

于 2020-04-01 23:49:30 首次发布

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/105258110

版权

Oracle 同时被 2 个专栏收录

189 篇文章 33 订阅

订阅专栏

灾备

22 篇文章 3 订阅

订阅专栏

前一篇 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

一步一步配置DataGuard Broker - 墨天轮

Oracle DG 手动 SwitchOver & Failover，Failover后利用闪回恢复同步_Hehuyi_In的博客-CSDN博客

Switchover and Failover Operations

Oracle Data Guard Broker Concepts

Hehuyi_In

关注

0
点赞
踩
13

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle DG Broker 进行 SwitchOver & Failover，Failover后恢复主从同步

前一篇 https://blog.csdn.net/Hehuyi_In/article/details/94384431介绍了SwitchOver & Failover的概念、区别及利用sql操作的方法。可以看到利用sql来执行需要在主从库均进行操作，命令也多，较为复杂，本篇来看如何利用DG Broker来简化这些操作。一、DG Broker安装与配置1.DG Brok...
复制链接

扫一扫