【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+DGMGRL
SID_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 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sysdg
Password:
Connected to "mydb19c"
Connected as SYSDG.
DGMGRL> connect sysdg@mydbdg
Password:
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 database
Fast-Start Failover:  Disabled
Configuration 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:  Disabled
Configuration 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:  Disabled
Configuration 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):
    mydbdg
Database Status:
SUCCESS
DGMGRL> show database 'mydb19c';
Database - mydb19c
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    mydb19c
Database 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 APPLY
SQL> show pdbs
    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 TESTPDB              MOUNTED
     4 MYPDB              MOUNTED
--启动pdb
alter 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;