【DATAGUARD】Oracle19cDataGuardBroker

Oracle19c Data Guard Broker

---

描述

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

  DG_BROKER_CONFIG_FILE1 & DG_BROKER_CONFIG_FILE2
  

新特性

oracle19c

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

不推薦的功能

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

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

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

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

不再支持的特性

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

dg broker 配置

環(huán)境準(zhǔn)備

• 網(wǎng)絡(luò)配置

  

  創(chuàng)新互聯(lián)建站服務(wù)項(xiàng)目包括寧河網(wǎng)站建設(shè)、寧河網(wǎng)站制作、寧河網(wǎng)頁(yè)制作以及寧河網(wǎng)絡(luò)營(yíng)銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，寧河網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到寧河省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

--主備參考，主要注意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用戶,必須賦權(quán)sysdg權(quán)限，否則因無法寫入密碼文件而無法遠(yuǎn)程等。
 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ù)，主備庫(kù)
alter system set dg_broker_start=true;
--測(cè)試連接
[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

• 備端加入配置信息

--備端清除遠(yuǎn)程信息，不然報(bào)錯(cuò),后續(xù)切換時(shí)該參數(shù)自動(dòng)設(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ù)庫(kù)相信配置信息
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/';

• 啟動(dòng)配置

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ù)庫(kù)信息
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檢查信息

--驗(yàn)證主數(shù)據(jù)庫(kù)
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".
--備庫(kù)
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
--其他檢查語(yǔ)句
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"

• 切換后，恢復(fù)進(jìn)程自動(dòng)啟動(dòng)，新備庫(kù)為只讀模式

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
--啟動(dòng)pdb
alter pluggable database mypdb open;

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

---

快速切換配置參考

• 快速故障切換，不需要人工干預(yù)，通過broker工具自動(dòng)切換。

環(huán)境準(zhǔ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;

• 啟用 快速自動(dòng)故障轉(zhuǎn)移

--檢查切換目標(biāo)
DGMGRL> show database 'mydbdg' FastStartFailoverTarget;
  FastStartFailoverTarget = 'mydb19c'
--開啟
 enable fast_start failover;
--啟動(dòng)觀察
start observer;
--檢查數(shù)據(jù)庫(kù)信息
select name,FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT from v$database;