在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