代码改变世界

Data Guard Broker遇到ORA-12541错误分析总结

2026-01-14 14:35  潇湘隐者  阅读(5)  评论(0)    收藏  举报

在Data Guard的DGMGRL中执行命令验证数据库或做DG切换(switchover)时,可能你会遇到错误ORA-12541,如下所示:

DGMGRL> validate database gsp

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    gsp:  Off

  Managed by Clusterware:
    gsp:  NO             
    Validating static connect identifier for the primary database gsp...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbtest01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=gsp_DGMGRL)(INSTANCE_NAME=gsp)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12541: TNS:no listener

Failed.
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL>

下面结合案例简单分析总结一下遇到ORA-12541的原因:

案例1

因为监听配置文件listener.ora中没有配置DGMGRL静态监听,如下所示

GSP =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.97)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_GSP =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = gsp)
      (ORACLE_HOME = /opt/oracle19c/product)
      (SID_NAME = gsp)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GSPPROD)
      (ORACLE_HOME = /opt/oracle19c/product)
      (SID_NAME = gsp)
    )
  )
 
SECURE_REGISTER_GSP = (IPC)
SECURE_CONTROL_GSP =(TCPS,IPC)
ADMIN_RESTRICTIONS_GSP = OFF
DIAG_ADR_ENABLED_GSP = OFF

修改listener.ora文件,增加配置静态监听条目，也就是StaticConnectIdentifier, 如下所示

GSP =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.97)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_GSP =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = gsp)
      (ORACLE_HOME = /opt/oracle19c/product)
      (SID_NAME = gsp)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GSPPROD)
      (ORACLE_HOME = /opt/oracle19c/product)
      (SID_NAME = gsp)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = gsp_DGMGRL)
      (ORACLE_HOME = /opt/oracle19c/product)
      (SID_NAME = gsp)
    )
  )
 
SECURE_REGISTER_GSP = (IPC)
SECURE_CONTROL_GSP =(TCPS,IPC)
ADMIN_RESTRICTIONS_GSP = OFF
DIAG_ADR_ENABLED_GSP = OFF

然后重新加载监听服务/重启监听服务,然后验证数据库此时此错误消失.

lsnrctl reload gsp 
lsnrctl stop/start gsp

另外,监听中的db_unique_name_DGMGRL.db_domain配置出错,例如拼写错误等, 甚至如果/etc/hosts中hostname与ip地址错误也会遇到这个错误.这个需要仔细检查.

案例2

DGMGRL> validate database gsp

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    gsp:  Off

  Managed by Clusterware:
    gsp:  NO             
    Validating static connect identifier for the primary database gsp...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbtest01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=gsp_DGMGRL)(INSTANCE_NAME=gsp)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12541: TNS:no listener

Failed.
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL>

即使listener.ora中配置正确, 依然报这个错误,如下所示,StaticConnectIdentifier中端口号为1521, 实际的监听端口号为15021, 执行下面命令修改

DGMGRL> show database 'gsp' StaticConnectIdentifier;
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbtest01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=gsp_DGMGRL)(INSTANCE_NAME=gsp)(SERVER=DEDICATED)))'
DGMGRL>
DGMGRL> edit database gsp set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbtest01)(PORT=15021))(CONNECT_DATA=(SERVICE_NAME=gsp_DGMGRL)(INSTANCE_NAME=gsp)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL>

出现这个的原因,官方文档[(KB833439) Database Will Not Register With Listener configured on IP instead of Hostname ORA-12514]中给出详细解释.

Listener has been configured with a raw IP address configured on one of the network interface(s) instead of the system hostname or you have used the (IP=FIRST) statement in the listener address list.


The listener has no database service registered. Database service(s) registration is performed by the PMON process.

The PMON process will attempt to register the database services, by default, with the listener running on port 1521, but it is not clearly defined to which IP address it attempts to connect to. In this situation PMON chooses an address different than the IP address on which the listener is bound (the address is chosen depending on system hostname configuration).

Since the listener was configured with a raw IP address, it will bind and listen only on that IP address. Connection attempts from PMON will miss this target and fail.

其实另外一个原因就是没有设置参数local_listener的缘故. 如果参数local_listener设置正确,pmon进程会使用正确的端口号注册监听服务.

参考资料

  1. (KB833439) Database Will Not Register With Listener configured on IP instead of Hostname ORA-12514