暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Every Day of a DBA，第138期: Oracle 19c DG Broker _DGMGRL 配置与TNS故障

Every Day of a DBA，第138期: Oracle 19c DG Broker _DGMGRL 配置与TNS故障

原创 ByteHouse 2026-05-06
57

作者：bytehouse
Oracle ACE、PostgreSQL ACE
10+年数据库架构与运维实战经验
公众号：bytehouse
墨天轮专栏：bytehouse
CSDN：Young DBA

一、文档概述

本文档针对 Oracle 19c DG Broker 集群配置核心要求进行明确：集群内部握手、重做链路通信、元数据同步过程中，必须使用带 \_DGMGRL 后缀的专属服务名（主库：primary\_DGMGRL；备库：standby\_DGMGRL），否则会导致TNS解析失败、重做传输中断、DG配置异常等故障。具体核心要求如下：

  • 监听静态注册：必须配置GLOBAL\_DBNAME = 数据库名\_DGMGRL（主库：primary_DGMGRL；备库：standby_DGMGRL），否则Broker无法识别节点。

  • TNS配置：必须对应监听注册的服务名，添加带\_DGMGRL后缀的TNS条目，确保主备双向解析正常。

  • DGConnectIdentifier：建议配置为带\_DGMGRL后缀的全连接串（而非简写别名），减少对TNS文件的依赖，提升配置稳定性。

二、环境信息

项目 详情
数据库版本 Oracle 19.3.0.0.0
架构模式 主库（orcl-primary）+ 物理备库（orcl-standby）+ DG Broker管理
监听端口 1521（主备统一）
DG保护模式 MaxPerformance（最大性能模式）
故障涉及文件 tnsnames.ora、listener.ora、DG Broker日志、数据库告警日志

三、故障场景分析

场景一：TNS配置无_DGMGRL，DG状态WARNING

1. 查看主库tnsnames.ora配置

[oracle@orcl-primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-primary.lan)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-primary.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-primary.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-standby.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
[oracle@orcl-primary admin]$

2. TNSPING测试备库连接

[oracle@orcl-primary admin]$ tnsping standby

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 06-MAY-2026 10:01:04

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-standby.lan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby)))
OK (0 msec)
[oracle@orcl-primary admin]$

3. 查看DG状态

DGMGRL> show configuration;

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  primary - Primary database
    standby - Physical standby database 
      Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 6 seconds ago)

DGMGRL> show database verbose standby;

Database - standby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      28 minutes 57 seconds (computed 44 seconds ago)
  Apply Lag:          28 minutes 57 seconds (computed 44 seconds ago)
  Average Apply Rate: 18.00 KByte/s
  Active Apply Rate:  (unknown)
  Maximum Apply Rate: (unknown)
  Real Time Query:    ON
  Instance(s):
    orcl

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold
    ORA-16855: transport lag has exceeded specified threshold
    ORA-16857: member disconnected from redo source for longer than specified threshold

  Properties:
    DGConnectIdentifier             = 'standby'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'orcl-standby'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl-standby)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/standby/orcl/trace/alert_orcl.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/standby/orcl/trace/drcorcl.log

Database Status:
WARNING

DGMGRL>

4. 查看DG Broker日志

2026-05-06T10:01:37.964+08:00
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               dg_config                         Warning  ORA-16608: one or more members have warnings
  Primary Database            primary                           Success  ORA-0: normal, successful completion
  Physical Standby Database   standby                           Warning  ORA-16809: multiple warnings detected for the member
[oracle@orcl-primary admin]$

5. 故障原因分析

Oracle 19c DG Broker 必须配置带 \_DGMGRL 后缀的专属服务名（主库primary\_DGMGRL、备库standby\_DGMGRL），本次故障因TNS配置中未使用该后缀，导致DG同步延迟、出现警告信息。

场景二：TNS条目缺失，DG状态ERROR

1. 查看DG状态

DGMGRL> show configuration

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  primary - Primary database
    Error: ORA-16778: redo transport error for one or more members

    standby - Physical standby database 
      Error: ORA-12154: TNS:could not resolve the connect identifier specified

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 45 seconds ago)

DGMGRL> show database verbose primary;

Database - primary

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    orcl
      Error: ORA-16737: the redo transport service for member "standby" has an error

  Properties:
    DGConnectIdentifier             = 'primary'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'orcl-primary'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-primary.lan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/primary/orcl/trace/alert_orcl.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/primary/orcl/trace/drcorcl.log

Database Status:
ERROR

DGMGRL> show database verbose standby;

Database - standby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Active Apply Rate:  (unknown)
  Maximum Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    orcl

  Properties:
    DGConnectIdentifier             = 'standby'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'orcl-standby'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl-standby)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    (Failed to retrieve log file locations.)

Database Status:
DGM-17016: failed to retrieve status for database "standby"
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-16625: cannot reach member "standby"

DGMGRL>

2. 故障原因分析

Broker 配置中 DGConnectIdentifier = primary、DGConnectIdentifier = standby，直接使用简写网络服务名，强依赖本机tnsnames\.ora文件；一旦TNS条目缺失，会立即出现TNS解析失败（ORA-12154），这是本次故障的直接触发点。

3. 查看主库告警日志

===========================================================
2026-05-06T09:34:16.553695+08:00
db_recovery_file_dest_size of 8256 MB is 14.77% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2026-05-06T09:36:25.371094+08:00
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
2026-05-06T09:36:26.479448+08:00
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
2026-05-06T09:36:27.598920+08:00
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
2026-05-06T09:36:28.705014+08:00
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
 fal (PID:16739): Error 12154 received logging on to the standby
2026-05-06T09:36:29.809416+08:00
 fal (PID:16739): Error 12154 received logging on to the standby
2026-05-06T09:36:29.811430+08:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2026-05-06T09:36:29.958559+08:00
TT00 (PID:16258): Error 12154 received logging on to the standby
2026-05-06T09:36:29.958755+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt00_16258.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
TT00 (PID:16258): krsg_check_connection: Error 12154 connecting to standby 'standby'
2026-05-06T09:36:29.960804+08:00
TT04 (PID:16273): Error 12154 received logging on to the standby
2026-05-06T09:36:29.960978+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
TT04 (PID:16273): Error 12154 for LNO:2 to 'standby'
2026-05-06T09:36:29.963214+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:36:29.963333+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:36:31.558986+08:00
 fal (PID:16746): Error 12154 received logging on to the standby
 fal (PID:16746): Error 12154 received logging on to the standby
 fal (PID:16746): Error 12154 received logging on to the standby
2026-05-06T09:36:32.664502+08:00
 fal (PID:16746): Error 12154 received logging on to the standby
 fal (PID:16746): Error 12154 received logging on to the standby
 fal (PID:16746): Error 12154 received logging on to the standby
2026-05-06T09:36:33.772234+08:00
 fal (PID:16746): Error 12154 received logging on to the standby
 fal (PID:16746): Error 12154 received logging on to the standby
 fal (PID:16746): Error 12154 received logging on to the standby
2026-05-06T09:36:34.882370+08:00
 fal (PID:16746): Error 12154 received logging on to the standby
2026-05-06T09:36:34.991786+08:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2026-05-06T09:36:35.126196+08:00
TT00 (PID:16258): Error 12154 received logging on to the standby
2026-05-06T09:36:35.126354+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt00_16258.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
TT00 (PID:16258): krsg_check_connection: Error 12154 connecting to standby 'standby'
2026-05-06T09:36:35.126994+08:00
TT04 (PID:16273): Error 12154 received logging on to the standby
2026-05-06T09:36:35.127193+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
TT04 (PID:16273): Error 12154 for LNO:2 to 'standby'
2026-05-06T09:36:35.130575+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:36:35.130720+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:36:35.250575+08:00
 fal (PID:16746): Error 12154 received logging on to the standby
 fal (PID:16746): Error 12154 received logging on to the standby
2026-05-06T09:36:35.622315+08:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2026-05-06T09:36:35.755672+08:00
TT04 (PID:16273): Error 12154 received logging on to the standby
2026-05-06T09:36:35.755868+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
TT04 (PID:16273): Error 12154 for LNO:2 to 'standby'
2026-05-06T09:36:35.757943+08:00
TT00 (PID:16258): Error 12154 received logging on to the standby
2026-05-06T09:36:35.758110+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt00_16258.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
TT00 (PID:16258): krsg_check_connection: Error 12154 connecting to standby 'standby'
2026-05-06T09:36:35.758270+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:36:35.758385+08:00
Errors in file /u01/app/oracle/diag/rdbms/primary/orcl/trace/orcl_tt04_16273.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
[oracle@orcl-primary admin]$

日志显示，重做传输、归档拉取进程使用参数中的standby网络名，因无法完成TNS解析，直接导致进程断连，抛出ORA-12154错误。

4. 查看DG Broker日志

2026-05-06T09:34:01.157+08:00                      DMON: SCI harvested (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-primary.lan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))
Broker Configuration:             dg_config
  Oracle Release:                 19.0.0.0.0
  Oracle Version:                 19.3.0.0.0
  Metadata Version:               4.1 / UID=0x0f2a3442 / Seq.MIV=0.6 / blksz.grain=4096.8
  Protection Mode:                Maximum Performance
  Fast-Start Failover (FSFO): DISABLED, flags=0x0, version=0
  Name                            Member Type                Handle      Enabled
  primary                         Primary Database           0x01010000  YES
  standby                         Physical Standby Database  0x02010000  YES
2026-05-06T09:34:05.244+08:00
Failed to connect to remote database standby. Error is ORA-1034
Failed to send message to member standby. Error code is ORA-1034.
Configuration Validation Results:
      Member standby: ORA-01034: ORACLE not available
2026-05-06T09:35:02.068+08:00
Redo transport problem detected: redo transport to database standby has the following error:
  ORA-01034: ORACLE not available
Failed to connect to remote database standby. Error is ORA-1034
Failed to send message to member standby. Error code is ORA-1034.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               dg_config                         Warning  ORA-16607: one or more members have failed
  Primary Database            primary                             Error  ORA-16778: redo transport error for one or more members
  Physical Standby Database   standby                             Error  ORA-01034: ORACLE not available
2026-05-06T09:36:03.492+08:00
Redo transport problem detected: redo transport to database standby has the following error:
  ORA-01034: ORACLE not available
Failed to connect to remote database standby. Error is ORA-12154
Failed to send message to member standby. Error code is ORA-12154.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               dg_config                         Warning  ORA-16607: one or more members have failed
  Primary Database            primary                             Error  ORA-16778: redo transport error for one or more members
  Physical Standby Database   standby                             Error  ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:36:39.128+08:00
EDIT DATABASE standby SET PROPERTY ActualApplyInstance = orcl
Failed to connect to remote database standby. Error is ORA-12154
Metadata Resync failed. Status = ORA-12154
Apply Instance for database standby is orcl
EDIT DATABASE standby SET PROPERTY ActualApplyInstance = orcl completed successfully
Failed to send message to member standby. Error code is ORA-16883.
2026-05-06T09:37:04.419+08:00
Redo transport problem detected: redo transport to database standby has the following error:
  ORA-12154: TNS:could not resolve the connect identifier specified
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               dg_config                         Warning  ORA-16607: one or more members have failed
  Primary Database            primary                             Error  ORA-16778: redo transport error for one or more members
  Physical Standby Database   standby                             Error  ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:37:42.024+08:00
Redo transport problem detected: redo transport to database standby has the following error:
  ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:38:05.859+08:00
Redo transport problem detected: redo transport to database standby has the following error:
  ORA-12154: TNS:could not resolve the connect identifier specified
Failed to connect to remote database standby. Error is ORA-12154
Failed to send message to member standby. Error code is ORA-12154.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               dg_config                         Warning  ORA-16607: one or more members have failed
  Primary Database            primary                             Error  ORA-16778: redo transport error for one or more members
  Physical Standby Database   standby                             Error  ORA-12154: TNS:could not resolve the connect identifier specified
2026-05-06T09:39:07.300+08:00
Redo transport problem detected: redo transport to database standby has the following error:
  ORA-12154: TNS:could not resolve the connect identifier specified
Failed to connect to remote database standby. Error is ORA-12154
Failed to send message to member standby. Error code is ORA-12154.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               dg_config                         Warning  ORA-16607: one or more members have failed
  Primary Database            primary                             Error  ORA-16778: redo transport error for one or more members
  Physical Standby Database   standby                             Error  ORA-12154: TNS:could not resolve the connect identifier specified
[oracle@orcl-primary admin]$

Broker 无法远程连接备库，无法采集应用状态和同步延迟信息，导致DG配置标记为ERROR。

四、故障修复与验证测试

4.1 修复方案

1. 配置listener.ora（主库）

[oracle@orcl-primary admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-primary.lan)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
      (GLOBAL_DBNAME = primary_DGMGRL)
      (ENVS = "TNS_ADMIN=/u01/app/oracle/product/19.3.0/dbhome_1/network/admin")
    )
  )

ADMIN_RESTRICTIONS_LISTENER = ON
[oracle@orcl-primary admin]$

备库listener.ora配置参照主库，将GLOBAL\_DBNAME改为standby\_DGMGRL。

2. 修改tnsnames.ora（主备均需配置）

添加带\_DGMGRL后缀的TNS条目，确保主备双向解析：

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-primary.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary_DGMGRL)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-standby.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby_DGMGRL)
    )
  )

配置完成后，重启监听和DG Broker服务，DG状态恢复正常。

4.2 测试验证（分场景）

4.2.1 测试一：TNS标准配置验证（修复后）

测试项目 测试操作 预期结果 实际结果 测试结论
TNS解析验证 主库：tnsping standby；备库：tnsping primary 解析成功，返回“OK”，耗时0ms 符合预期，解析正常 TNS配置正确
DG状态验证 dgmgrl执行show configuration 全局状态为SUCCESS，无ERROR/WARNING 符合预期，DG状态恢复正常 DG通信恢复
重做传输验证 主库执行日志切换：alter system switch logfile；备库查看日志应用：select status from v$managed_standby; 主库日志正常归档，备库MRP进程状态为APPLYING_LOG 符合预期，重做传输、应用正常 重做链路恢复
延迟指标验证 dgmgrl执行show database verbose standby，查看传输/应用延迟 延迟逐步降低，最终归零 符合预期，延迟正常归零 同步状态正常

4.2.2 测试二：错误配置回归测试（验证故障复现）

测试场景 错误配置操作 预期故障现象 实际故障现象 测试结论
场景A：TNS无_DGMGRL后缀 将TNS条目改为普通服务名（primary、standby） DG状态变为WARNING，出现延迟类告警 与场景一故障现象一致 无_DGMGRL后缀会导致同步延迟
场景B：TNS条目缺失 删除tnsnames.ora中的primary、standby条目 DG状态变为ERROR，抛出ORA-12154 与场景二故障现象一致 TNS条目缺失会导致通信中断
场景C：DGConnectIdentifier不匹配 TNS用primary_DGMGRL，DGConnectIdentifier设为primary DG状态WARNING，同步延迟超标 符合预期，通信效率下降 两者必须完全一致

五、19c DG Broker 版本特性说明

Oracle 19c 中 V$MANAGED_STANDBY 视图已被废弃（deprecated），官方不再推荐使用，虽然目前仍可查询，但未来版本会被彻底移除。

SQL> select process, status, thread#, sequence# from v$managed_standby;

PROCESS   STATUS	  THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH	  CLOSING		1	  10
DGRD	  ALLOCATED		0	   0
DGRD	  ALLOCATED		0	   0
ARCH	  CONNECTED		0	   0
ARCH	  CONNECTED		0	   0
ARCH	  CONNECTED		0	   0
MRP0	  WAIT_FOR_LOG		1	  11
RFS	  IDLE			1	   0
RFS	  IDLE			1	  11
RFS	  IDLE			0	   0

10 rows selected.

SQL>

Oracle 从 12.2 版本开始，提供 V$DATAGUARD\_PROCESS 视图替代旧的 V$MANAGED\_STANDBY，用于监控DataGuard相关进程状态：

SQL> SELECT ROLE, ACTION, THREAD#, SEQUENCE# FROM V$DATAGUARD_PROCESS;
  
ROLE			 ACTION 	 THREAD#  SEQUENCE#
------------------------ ------------ ---------- ----------
log writer		 IDLE		       0	  0
redo transport monitor	 IDLE		       0	  0
gap manager		 IDLE		       0	  0
redo transport timer	 IDLE		       0	  0
archive local		 IDLE		       0	  0
archive redo		 IDLE		       0	  0
archive redo		 IDLE		       0	  0
archive redo		 IDLE		       0	  0
broker instance slave	 IDLE		       0	  0
broker monitor		 IDLE		       0	  0
broker net slave	 IDLE		       0	  0

ROLE			 ACTION 	 THREAD#  SEQUENCE#
------------------------ ------------ ---------- ----------
broker worker		 IDLE		       0	  0
RFS ping		 IDLE		       1	 11
RFS async		 IDLE		       1	 11
RFS archive		 IDLE		       0	  0
managed recovery	 IDLE		       0	  0
recovery logmerger	 WAIT_FOR_LOG	       1	 11
recovery apply slave	 IDLE		       0	  0
recovery apply slave	 IDLE		       0	  0

19 rows selected.

SQL>
实操看我的
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论