ORACLE 12C DG BROKER配置切换以及FAST FAILOVER介绍

【背景】

通常情况下习惯使用sqlplus命令对数据库primary以及dataguard进行switchover、failover.虽然oracle很早在10g时候就推出dg broker命令行进行快速切换，由dgmgrl对数据库状态检查、延迟检查、是否可以切换进行封装命令输出，所以可以很快捷简单检查整个主从配置和切换，个人觉得dg broker报错排除之类不是太友好，,另外dataguard切换2条命令，dgmgrl封装成一条命令，整体切换相对简单，使用dgmgrl需要配置静态监听、standby log、延迟等要求比较多，另外broker可以配合em操作，实现web化操作数据库切换。总的来dg broker操作简单，配置相对复杂(对于sqlplus进行切换来说)下面跟大家分享下dg broker以及12c一些新的变化。

【环境准备说明】

首先搭建好dataguard且配置standby log，并开启实时应用日志(standby log size必须与redo size保持y一致)

dg_broker_config_file1以及dg_broker_config_file2对应路径必须先存在(不存在需要先创建，否则启动dg_broker_start提示ORA-16604: Data Guard broker configuration file inaccessible)

开启dg_broker_start为TRUE(默认是FALSE)--主备都执行

【主库】

SQL> show parameter broker

dg_broker_config_file1 string +DATA/PRIMARY/dgb_conf

ig1.ora

dg_broker_config_file2 string +DATA/PRIMARY/dgb_conf

ig2.ora

dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=true sid='*';

System altered.

【从库】

SQL> show parameter broker

dg_broker_config_file1 string+DATA/STANDBY/dgb_conf

ig1.ora

dg_broker_config_file2 string+DATA/STANDBY/dgb_conf

ig2.ora

dg_broker_start booleanFALSE

SQL> alter system set dg_broker_start=true sid='*';

System altered.

4、配置好静态监听--主备库都需要配置

备注:Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance.

--监听例子,主备库都增加，然后reload listener

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PRIMARY)

(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)

(SID_NAME= pridb1)

)

)

--配置tns别名或者使用具体具体连接串，测试通过.

【创建管理配置信息】

在主库:

注释:从12c开始可以不使用sys用户，使用sysdg用户来操作即可

$dgmgrl sysdg --连接dgmgrl

创建一个配置并增加主库：--注意单引号是区分大小写，根据你DB_UNIQUE_NAME来配置

DGMGRL>CREATE CONFIGURATION 'orcl12c' AS PRIMARY DATABASE IS 'RPIMARY' CONNECT IDENTIFIER ISRPIMARY;

Configuration "orcl12c" created with primary database "RPIMARY"

查看配置：

DGMGRL> show configuration;

Configuration - orclwm12c

Protection Mode: MaxPerformance

Members:

RPIMARY- Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

增加备库:

DGMGRL> add database 'STANDBY' as connect identifier isSTANDBY;

Database "STANDBY" added

查看配置:

DGMGRL> show configuration;

Configuration - orcl12c

Protection Mode: MaxPerformance

Members:

PRIMARY - Primary database

STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

启用当前配置:

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration - orclwm12c

Protection Mode: MaxPerformance

Members:

PRIMARY- Primary database

STANDBY- Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 28 seconds ago)

查看配置详细信息:--有任何告警都需要处理

DGMGRL> show configuration verbose;

Configuration - orcl12c

Protection Mode: MaxPerformance

Members:

PRIMARY - Primary database

STANDBY- Physical standby database

Properties:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

TraceLevel = 'USER'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

ObserverReconnect = '0'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

ObserverOverride = 'FALSE'

ExternalDestination1 = ''

ExternalDestination2 = ''

PrimaryLostWriteAction = 'CONTINUE'

ConfigurationWideServiceName = 'orcl_CFG'

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

--至此配置成功

【通过dgmgrl查看主备实例详细】

备注:前面使用单引号增加database,后面查询一律要使用.

主库信息:

DGMGRL> show database 'PRIMARY';

Database - PRIMARY

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

pridb1

pridb2

Database Status:

SUCCESS

备库信息：

DGMGRL> show database 'STANDBY';

Database - STANDBY

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: 9.00 KByte/s

Real Time Query: ON

Instance(s):

stdb1 (apply instance)

stdb2

Database Status:

SUCCESS

--当前备库状态是节点1实时应用日志,比sqlplus输出详细且更清晰些.且备库同步都正常.

【通过dgmgrl来进行switchover切换】

1、通过dgmgrl来验证是否能否进行切换--类似switchover_status from v$database;

验证主库是否可以进行切换:

DGMGRL> validate database 'PRIMARY';

Database Role: Primary database

Ready for Switchover: Yes--表示可以切换

Flashback Database Status:

PRIMARY: Off

Managed by Clusterware:

PRIMARY: YES

验证备库是否可以进行切换:

DGMGRL> validate database 'STANDBY';

Database Role: Physical standby database

Primary Database:STANDBY

Ready for Switchover: Yes --表示可以switchover

Ready for Failover: Yes (Primary Running)---表示可以failover并提示主库

Flashback Database Status:

PRIMARY: Off

STANDBY: On

Managed by Clusterware:

PRIMARY: YES

STANDBY: YES

2、通过dgmgrl来进行switchover切换

备注:把primary切换standby，standby切换primary,使用dgmgrl切换不用手动去关闭节点以及启动实例，自动完成相应关闭节点与启动实例。操作简单很多，类似rman duplicate远程启动与关闭数据库实例，通过静态监听去访问.

DGMGRL> switchover to 'STANDBY';

Performing switchover NOW, please wait...

Operation requires a connection to database "STANDBY"

Connecting ...

Connected to "STANDBY"

Connected as SYSDG.

New primary database "STANDBY" is opening...

Oracle Clusterware is restarting database "PRMARY" ...

Switchover succeeded, new primary is "STANDBY"

3、验证switchover后数据库情况

DGMGRL> show configuration;

Configuration - orcl12c

Protection Mode: MaxPerformance

Members:

STANDBY- Primary database --standby变成主库

PRIMARY- Physical standby database --primary变成备库

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 56 seconds ago)

查看备库同步情况：

DGMGRL> show database 'PRIMARY';

Database -PRIMARY

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: 1.61 MByte/s

Real Time Query: ON

Instance(s):

pridb1 (apply instance)

pridb2

Database Status:

SUCCESS

--自此主备切换完成，是不是很方便且速度.

【dg broker常见问题】

1、ORA-16857: member disconnected from redo source for longer than specified threshold

原因:通常是主库redo与备库standby redo 大小不一致或者group number不匹配.

2、ORA-02097: parameter cannot be modified because specified value is invalid，ORA-16604: Data Guard broker configuration file inaccessible

原因:通常是dg_broker_config_file2和dg_broker_config_file1配置路径不存在，导致创建文件，创建好路径即可.

3、Error: ORA-16587: ambiguous object specified to Data Guard broker

原因:通过log_archive_dest_n里面配置db_unique_name名字重复导致，改成正确的即可.

4、如果是sysdg用户，账号必须解锁的.

5、ORA-16853: apply lag has exceeded specified threshold

DGMGRL> show configuration;

Configuration - orcl12c

Protection Mode: MaxPerformance

Members:

PRIMARY - Primary database

STANDBY - Physical standby database

Warning: ORA-16853:apply lag has exceeded specified threshold

原因：解决备库是否开启实时应用日志、检查主备库redo,standby redo大小是否一直以及mrp是否正常启动.

【介绍fast failover】

备注:在12c之前，observer只能启动一个，通过守护进程启动且必须要启动在备库上(如果启动在主库，出现主库网络不通或者其他原因，导致fast failover功能不可用),12.2 fast failover高可用优化，启动3个observer，一个master，2个backup,且变成系统进程(不在通过&方式启动).关于具体配置测试，后续会详细介绍。