【DATAGUARD】Oracle19c Data Guard Broker

Oracle19c Data Guard Broker

描述

• Data Guard broker是一個集中的框架，用于通過客戶端連接到配置中的任何數(shù)據(jù)庫來管理整個Data Guard配置
• DGMGRL無法創(chuàng)建備用（GUI可以這樣做）。CLI主要用于配置和管理。
• 使用一個命令輕松切換/故障切換，從而最大限度地減少與計劃內/計劃外停機相關的總體停機時間
• broker不使用各種SQL*Plus語句管理主數(shù)據(jù)庫和備用數(shù)據(jù)庫，而是提供單一的統(tǒng)一配置
• 代理將其配置詳細信息保存在平面文件中。這些文件存儲在數(shù)據(jù)保護配置中的每個數(shù)據(jù)庫節(jié)點上。此外，配置文件的兩個副本始終存儲在每個數(shù)據(jù)庫上以備冗余。
• 下面的參數(shù)控制配置文件的存儲位置。

  DG_BROKER_CONFIG_FILE1 & DG_BROKER_CONFIG_FILE2
  

新特性

oracle19c

• 動態(tài)更改fast-start failover目標，而無需禁用
• 在不影響當前環(huán)境下，可使用觀察模式(observe-only mode)模擬測試fast-start failover工作方式
• Broker配置信息可以導出作為備份，當需要重建Broker時，可以使用導出的備份導入。
• 新命令可用于設置、修改和顯示數(shù)據(jù)庫、遠同步實例或恢復設備中數(shù)據(jù)庫初始化參數(shù)的值。使用這些命令設置的值將直接應用于數(shù)據(jù)庫，而不會存儲在代理配置文件中。

不推薦的功能

• 以下與數(shù)據(jù)庫初始化參數(shù)相關聯(lián)的屬性在此版本中不推薦使用，在將來的版本中可能會被取消支持。這些屬性將不再存儲在代理配置文件中。

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

• 不一致屬性在此版本中棄用。此屬性將始終沒有值。
• 以下與邏輯備用相關的屬性將被重新計算，并可能在將來的版本中被取消支持:

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

不再支持的特性

• MaxConnections 參數(shù)不再支持

dg broker 配置

環(huán)境準備

• 網(wǎng)絡配置

--主備參考，主要注意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)
    )  
  )

• 參數(shù)，用戶環(huán)境

--主端執(zhí)行即可
--解鎖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;
--修改參數(shù)，主備庫
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>

開始配置

• 主端創(chuàng)建配置信息

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

• 備端加入配置信息

--備端清除遠程信息，不然報錯,后續(xù)切換時該參數(shù)自動設置
 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
--查看數(shù)據(jù)庫相信配置信息
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> 
--檢查數(shù)據(jù)庫信息
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檢查信息

--驗證主數(shù)據(jù)庫
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工具自動切換。

環(huán)境準備

--主備必須開啟閃回區(qū)
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;

啟用

• 目的零數(shù)據(jù)丟失，修改為最大可用模式

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;
--檢查數(shù)據(jù)庫信息
select name,FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT from v$database;