Listener Errors TNS-01155 and NL-00305 Using Oracle Gateways

刘泽宇

现象：
You are trying to use an Oracle Gateway and get an error on select -


SQL> select * from test_table@gateway_db_link;
ERROR:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from GATEWAY_DB_LINK

The listener log file will show the errors -



TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00305: the specified path name does not exist



原因：
The listener.ora file has an entry which uses 'SERVICE_NAME' instead of 'SID_NAME', for example -



(SID_DESC =
   (SERVICE_NAME = DG4MSQL)
   (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
)



处理方法：
1.  Either remove the 'SERVICE_NAME' entry or change to a SID_NAME -



(SID_DESC =
   (SID_NAME = DG4MSQL)
   (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
)

and stop and start the listener.

On some platforms the listener will not start when SERVICE_NAME is used instead of SID_NAME.



2.  This error is also caused by incorrect syntax in the tnsnames.ora file.  For example:

DG4ODBC_12C =
     (DESCRIPTION =
      (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>)
       (CONNECT_DATA=(SID=DG4ODBC12C))
      )
       (HS=OK)
     )

The close parentheses for ADDRESS_LIST is in the incorrect spot. It should be before the CONNECT_DATA portion of the entry.

Since ADDRESS_LIST is not required, remove it:

DG4ODBC_12C =
     (DESCRIPTION =
      (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>))
      (CONNECT_DATA=(SID=DG4ODBC12C))
      (HS=OK)
     )