博客 / 詳情

返回

Data Guard如何重建dg broker?

下面是個人結合文檔總結整理的重建dg broker的文檔,僅供參考,不一定適合所有環境或場景.

Step 1:停止broker(主庫&備庫)


SQL> SHOW PARAMETER DG_BROKER_START;
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
SQL> SHOW PARAMETER DG_BROKER_START;

Step 2: 檢查或配置監聽服務(主庫&備庫)


檢查主庫/備庫監聽文件listener.ora中的配置是否正確,如果只是重建或已經正確配置,直接跳到下一個步驟.

監聽文件listener.ora中必須添加一個靜態註冊的service_name為db_unique_name_DGMGRL.db_domain,其中db_domain是可選項.這個service_name會在
DGMGRL重啓數據庫的時候用到. 通過DGMGRL重啓數據庫時DMON進程會先將數據庫關閉,然後DGMGRL在通過靜態監聽中的service_name連接到數據庫,發送
啓動的命令。

例子(監聽增加內容):

(SID_DESC =
  (GLOBAL_DBNAME = gsp_DGMGRL)
  (SID_NAME = gsp)
  (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
)

重啓或重新加載監聽配置

lsnrctl reload <lisnter_name>
lsnrctl stop <lisnter_name>
lsnrctl start <lisnter_name>

Step 3: 刪除舊的broker配置文件(主庫&備庫)


SQL> show parameter dg_broker_config_file

ASM

ASMCMD> cd /<PATH>/
ASMCMD> rm <FILE_NAME>.dat
ASMCMD> rm <FILE_NAME>.dat

文件系統

rm /opt/oracle19c/product/dbs/dr1gsp.dat
rm /opt/oracle19c/product/dbs/dr2gsp.dat

Step 4： 啓用broker(主庫&備庫)


--in all instances in case of RAC
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

Step 5: 配置參數local_listener(主庫&備庫)


檢查參數local_listener

show parameter local_listener

主庫(Primary)

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST =dbtest01)(PORT = 15021))' scope=both;

備庫(Standby):

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST =dbtest02)(PORT = 15021))' scope=both;

這個步驟不是必須的,但是設置local_listener有下面一些好處:

  • 可以明確告知pmon進程「要向哪個IP/hostname: 端口的監聽器註冊數據庫服務」，解決「監聽器綁定特定 IP 但PMON隨機選地址導致註冊失敗」的核心問題;
  • 替代默認的1521端口註冊邏輯，適配非標準端口(如 15021）、多監聽器(如主庫/備庫不同監聽器)場景；
  • 支持多網卡/多IP環境，精準指定監聽器地址，避免註冊到無效網卡。

Step 6: 在主庫連接DGMGRL


DGMGRL> connect sys 
Password:
或
DGMGRL> CONNECT sys/<password>;

$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jul 17 09:52:04 2024
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 sys
Password:
Connected to "gsp"
Connected as SYSDBA.
DGMGRL>

Step 7: 在主庫創建配置(configuration)


語法如下:

CREATE CONFIGURATION '<CONFIGURATION_NAME>' AS PRIMARY DATABASE IS '<PRIMARY_DATABASE_NAME>' CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER>;

例子:

DGMGRL> create configuration 'dg_gsp' as primary database is 'gsp' connect identifier is gsp;
Configuration "dg_gsp" created with primary database "gsp"

錯誤案例1：

DGMGRL> create configuration 'dg_gsp' as primary database is 'gsp' connect identifier is gsp;
Error: 
ORA-16525: The Oracle Data Guard broker is not yet available.

原因分析：dg_broker_start參數值為FALSE.

錯誤案例2:

DGMGRL> create configuration 'dg_gsp' as primary database is 'test' connect identifier is test;
Error: ORA-16642: DB_UNIQUE_NAME mismatch

Failed.

原因分析,<PRIMARY_DATABASE_NAME>名字弄錯了.正確的寫法如下所示

CREATE CONFIGURATION '<CONFIGURATION_NAME>' AS PRIMARY DATABASE IS '<PRIMARY_DATABASE_NAME>' CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER>;

<PRIMARY_DATABASE_NAME>應該輸入DB_UNIQUE_NAME,可以通過下面SQL查詢DB_UNIQUE_NAME

select db_unique_name from v$database;

Step 8: 配置中加入備用數據庫(standby)


語法如下:

DGMGRL> ADD DATABASE '<STANDBY_DATABASE_NAME>' AS CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER> MAINTAINED AS PHYSICAL;

例子:

DGMGRL> add database 'gspro' as connect identifier is gspro maintained as physical;
Database "gspro" added
DGMGRL

錯誤案例1：

DGMGRL> add database 'gspro' as connect identifier is gspro maintained as physical;
Error: ORA-16525: The Oracle Data Guard broker is not yet available.

Failed.

原因分析：dg_broker_start參數值為FALSE.

錯誤案例2:

DGMGRL> add database 'gspro' as connect identifier is gspro maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
DGMGRL>

出現這個錯誤, 是因為之前已經手工設置了LOG_ARCHIVE_DEST_n參數, DG Broker要求"它自己全權管理redo傳輸"，因此建配置前必須把這些手工配置的參數(例如log_archive_dest_2)的值情況,否則就會報ORA-16698

主庫&備庫中執行下面SQL


SQL> alter system set log_archive_dest_2='' scope=both;

System altered.

SQL> alter system set log_archive_dest_1='' scope=both;

System altered.

SQL> 
SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL>

Step 9: 啓用配置


DGMGRL> ENABLE CONFIGURATION;
Enabled.

在啓用配置時，在主備庫執行tail命令觀察alert日誌信息。

tail -60f alert_<ORACLE_SID>.ora

檢查配置信息

錯誤案例1：

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_gsp

  Protection Mode: MaxPerformance
  Members:
  gsp   - Primary database
    Warning: ORA-16789: standby redo logs configured incorrectly

    gspro - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 5 seconds ago)

DGMGRL>

錯誤分析：
沒有配置standby redo logs,需要重新配置。

DGMGRL> DISABLE CONFIGURATION;
Disabled.
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;

Configuration - dg_gsp

  Protection Mode: MaxPerformance
  Members:
  gsp   - Primary database
    gspro - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 4 seconds ago)

DGMGRL>

Step 10: 檢查驗證

DGMGRL> show configuration;

DGMGRL> show configuration

Configuration - dg_gsp

  Protection Mode: MaxPerformance
  Members:
  gsp   - Primary database
    gspro - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 8 seconds ago)

DGMGRL> show database gsp

Database - gsp

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    gsp

Database Status:
SUCCESS

DGMGRL> show database gspro

Database - gspro

  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: 20.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    gsp

Database Status:
SUCCESS

DGMGRL>

參考資料：

  • Step By Step How to Recreate Dataguard Broker Configuration (Doc ID 808783.1)
  • (KB141840) Step By Step How to Recreate Dataguard Broker Configuration
  • (KB151794) 12c Create Dataguard Broker Configuration - DGMGRL
數據庫
user avatar
0 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.