【DATAGUARD】Oracle19c Data Guard Broker

Oracle19c Data Guard Broker

描述

• Data Guard broker是一个集中的框架，用于通过客户端连接到配置中的任何数据库来管理整个Data Guard配置
• DGMGRL无法创建备用（GUI可以这样做）。CLI主要用于配置和管理。
• 使用一个命令轻松切换/故障切换，从而最大限度地减少与计划内/计划外停机相关的总体停机时间
• broker不使用各种SQL*Plus语句管理主数据库和备用数据库，而是提供单一的统一配置
• 代理将其配置详细信息保存在平面文件中。这些文件存储在数据保护配置中的每个数据库节点上。此外，配置文件的两个副本始终存储在每个数据库上以备冗余。
• 下面的参数控制配置文件的存储位置。

  DG_BROKER_CONFIG_FILE1 & DG_BROKER_CONFIG_FILE2

新特性

oracle19c

• 动态更改fast-start failover目标，而无需禁用
• 在不影响当前环境下，可使用观察模式(observe-only mode)模拟测试fast-start failover工作方式
• Broker配置信息可以导出作为备份，当需要重建Broker时，可以使用导出的备份导入。
• 新命令可用于设置、修改和显示数据库、远同步实例或恢复设备中数据库初始化参数的值。使用这些命令设置的值将直接应用于数据库，而不会存储在代理配置文件中。

不推荐的功能

• 以下与数据库初始化参数相关联的属性在此版本中不推荐使用，在将来的版本中可能会被取消支持。这些属性将不再存储在代理配置文件中。

ArchiveLagTarget, DataGuardSyncLatency,LogArchiveMaxProcesses,xixLogArchiveMinSucceedDest, LogArchiveTrace,StandbyFileManagement,DbFileNameConvert, LogArchiveFormat, LogFileNameConvert

• 不一致属性在此版本中弃用。此属性将始终没有值。
• 以下与逻辑备用相关的属性将被重新计算，并可能在将来的版本中被取消支持:

LsbyMaxEventsRecorded, LsbyMaxServers,LsbyMaxSga, LsbyPreserveCommitOrder, LsbyRecordAppliedDdl,LsbyRecordSkipDdl,LsbyRecordSkipErrors, and LsbyParameter

不再支持的特性

• MaxConnections 参数不再支持

dg broker 配置

环境准备

• 网络配置

--主备参考，主要注意GLOBAL_DBNAME，db_unique_name+DGMGRLSID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = mydbdg)        (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)      (SID_NAME = mydbdg)    )    (SID_DESC =      (GLOBAL_DBNAME = mydbdg_DGMGRL)        (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)      (SID_NAME = mydbdg)    )    )

• 参数，用户环境

--主端执行即可--解锁dg用户,必须赋权sysdg权限，否则因无法写入密码文件而无法远程等。 select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;alter user sysdg identified by oracle account unlock;grant sysdg to sysdg; select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;--修改参数，主备库alter system set dg_broker_start=true;--测试连接[oracle@node216 admin]$ dgmgrl DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 18 15:14:07 2020Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Welcome to DGMGRL, type "help" for information.DGMGRL> connect sysdgPassword:Connected to "mydb19c"Connected as SYSDG.DGMGRL> connect sysdg@mydbdgPassword:Connected to "MYDBDG"Connected as SYSDG.DGMGRL>

开始配置

• 主端创建配置信息

create configuration 'mycdb' as primary database is 'mydb19c' connect identifier is mydb19c;--查看DGMGRL> show configuration;Configuration - mycdb  Protection Mode: MaxPerformance  Members:  mydb19c - Primary databaseFast-Start Failover:  DisabledConfiguration Status:DISABLED

• 备端加入配置信息

--备端清除远程信息，不然报错,后续切换时该参数自动设置 alter system set LOG_ARCHIVE_DEST_2='';  Add database 'mydbdg' as connect identifier is mydbdg maintained as physical;--检查配置信息DGMGRL> show configuration;Configuration - mycdb  Protection Mode: MaxPerformance  Members:  mydb19c - Primary database    mydbdg  - Physical standby database Fast-Start Failover:  DisabledConfiguration Status:DISABLED--查看数据库相信配置信息SHOW DATABASE VERBOSE 'South_Sales'--修改参考命令EDIT DATABASE 'South_Sales' SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';EDIT DATABASE 'South_Sales' SET PROPERTY 'StandbyArchiveLocation'='/archfs/arch/';

• 启动配置

DGMGRL> ENABLE CONFIGURATION;Enabled.DGMGRL> show configuration;Configuration - mycdb  Protection Mode: MaxPerformance  Members:  mydb19c - Primary database    mydbdg  - Physical standby database Fast-Start Failover:  DisabledConfiguration Status:SUCCESS   (status updated 6 seconds ago)DGMGRL> --检查数据库信息DGMGRL> show database 'mydbdg';Database - mydbdg  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: 3.00 KByte/s  Real Time Query:    ON  Instance(s):    mydbdgDatabase Status:SUCCESSDGMGRL> show database 'mydb19c';Database - mydb19c  Role:               PRIMARY  Intended State:     TRANSPORT-ON  Instance(s):    mydb19cDatabase Status:SUCCESS

主备切换

• dgmgrl检查信息

--验证主数据库DGMGRL> VALIDATE DATABASE 'mydb19c';  Database Role:    Primary database  Ready for Switchover:  Yes  Flashback Database Status:    mydb19c:  Off  Managed by Clusterware:    mydb19c:  NO                 Validating static connect identifier for the primary database mydb19c...    The static connect identifier allows for a connection to database "mydb19c".--备库DGMGRL> VALIDATE DATABASE 'mydbdg';  Database Role:     Physical standby database  Primary Database:  mydb19c  Ready for Switchover:  Yes  Ready for Failover:    Yes (Primary Running)  Flashback Database Status:    mydb19c:  Off    mydbdg :  Off  Managed by Clusterware:    mydb19c:  NO                 mydbdg :  NO                 Validating static connect identifier for the primary database mydb19c...    The static connect identifier allows for a connection to database "mydb19c".  Current Log File Groups Configuration:    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status                     (mydb19c)               (mydbdg)                                 1         3                       2                       Insufficient SRLs  Future Log File Groups Configuration:    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status                     (mydbdg)                (mydb19c)                                1         3                       0                       Insufficient SRLs    Warning: standby redo logs not configured for thread 1 on mydb19c  Transport-Related Property Settings:    Property                        mydb19c Value            mydbdg Value    NetTimeout                      30                       300--其他检查语句show database VERBOSE 'mydb19c';

• sql检查

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS--------------------TO STANDBY

• 切换

SWITCHOVER TO 'mydbdg';--参考日志DGMGRL> SWITCHOVER TO 'mydbdg';Performing switchover NOW, please wait...Operation requires a connection to database "mydbdg"Connecting ...Connected to "MYDBDG"Connected as SYSDG.New primary database "mydbdg" is opening...Operation requires start up of instance "mydb19c" on database "mydb19c"Starting instance "mydb19c"...Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to "mydb19c"Connected to an idle instance.ORACLE instance started.Connected to "mydb19c"Database mounted.Database opened.Connected to "mydb19c"Switchover succeeded, new primary is "mydbdg"

• 切换后，恢复进程自动启动，新备库为只读模式

select name,database_role,open_mode from v$database;SQL> NAME      DATABASE_ROLE    OPEN_MODE--------- ---------------- --------------------MYDB19C   PHYSICAL STANDBY READ ONLY WITH APPLYSQL> show pdbs    CON_ID CON_NAME              OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------     2 PDB$SEED              READ ONLY  NO     3 TESTPDB              MOUNTED     4 MYPDB              MOUNTED--启动pdbalter pluggable database mypdb open;

至此，dg broker 配置及切换成功

快速切换配置参考

• 快速故障切换，不需要人工干预，通过broker工具自动切换。

环境准备

--主备必须开启闪回区ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;--ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;SHUTDOWN IMMEDIATE;STARTUP MOUNT;SHOW PARAMETER UNDO;ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH;ALTER DATABASE ARCHIVELOG;ALTER SYSTEM SET db_recovery_file_dest_size=10g;ALTER SYSTEM SET db_recovery_file_dest=/backup/fra;ALTER DATABASE FLASHBACK ON;ALTER DATABASE OPEN;

启用

• 目的零数据丢失，修改为最大可用模式

DGMGRL> EDIT DATABASE 'mydb19c' SET PROPERTY 'LogXptMode'='SYNC';DGMGRL> EDIT DATABASE 'mydbdg' SET PROPERTY 'LogXptMode'='SYNC';DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

• 启用 快速自动故障转移

--检查切换目标DGMGRL> show database 'mydbdg' FastStartFailoverTarget;  FastStartFailoverTarget = 'mydb19c'--开启 enable fast_start failover;--启动观察start observer;--检查数据库信息select name,FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT from v$database;