Oracle ORA-12154: TNS:could not resolve service name錯誤的解決方案

昨天一同事反映在使用Oracle9i時，在Net Assistant裡佈建服務名測試通過，但在使用SQL*PLUS等用戶端工具時老是串連不上。

·首先檢查服務名是否存在，探索服務名的確存在而且在Net Assistant中確實可以串連並測試通過。

·接下來用SQL*PLUS串連報錯，錯誤顯示：ORA-12154: TNS:could not resolve service name

·使用Oracle的tnsping命令測試報錯，錯誤顯示：TNS-03505: 未能分解名稱

本人百思不得其解，只能上網google，多番尋找之後終於找到了一篇文章，發現裡面的情況和我的一摸一樣。現摘錄如下：

(尊重原創：作者部落格地址為：http://blog.chinaunix.net/u1/44757/index.html)

以下內容為轉載部分：
----------------------------------------------------------------------------------------------------------------------------------------
ORA-12154: TNS:could not resolve service name

錯誤指出客戶不能尋找到tnsnames.ora檔案中所列舉的服務。

查錯誤碼顯示:

ORA-12154: TNS:could not resolve service name
Cause: Oracle Net could not locate the net service name specified in the tnsnames.ora configuration file.

Action: Perform these steps:
1.Verify that a tnsnames.ora file exists.(See Also: "Localized Configuration File Support" for configuration file location information)

2.Verify that there are not multiple copies of the tnsnames.ora file.

3.In the tnsnames.ora file, verify that the net service name specified in your connect string is mapped to a connect descriptor.

4.Verify that there are no duplicate copies of the sqlnet.ora file.

5.If you are using domain names, verify that your sqlnet.ora file contains a NAMES.DEFAULT_DOMAIN parameter. If this parameter does not exist, you must specify the domain name in your connect string.

6.If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out.

7.If you are connecting from a login dialog box, verify that you are not placing an "@" symbol before your connect net service name.

8.Activate client tracing and repeat the operation.

Cause: Oracle Net could not locate the database service name or net service name specified in the directory server.

Action: Perform these steps:
1.Verify that the database service or net service name entry exists in the directory that this computer was configured to use.
  (See Also: Chapter 8, "Setting Up Directory Server Usage" for directory setup instructions)

2.Verify that the sqlnet.ora file includes the following entry: NAMES.DIRECTORY_PATH=(ldap, other_naming_methods)
  根據文檔中的提示第三步3.（1.2沒問題了）檢查tnsnames.ora檔案中的服務名稱是否正確的配置，經過檢查(SERVICE_NAME = hatest)沒有問題
  然後按照文檔做第4.5步，到第6步時發現問題，sqlnet.ora檔案中引用了domain域(注:我們這邊沒使用域)
  [oracle@localhost admin]$ cat sqlnet.ora
  # SQLNET.ORA Network Configuration File: /home/oracle/OraHome1/network/admin/sqlnet.ora
  # Generated by Oracle configuration tools.
  NAMES.DEFAULT_DOMAIN = localdomain
  NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)--這個沒問題啦

  好了那還等什麼編輯該檔案把domain幹掉。編輯完後趕快tnsping hatest
  [oracle@localhost admin]$ tnsping hatest
  TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 26-APR-2007 16:33:39
  Copyright (c) 1997 Oracle Corporation.  All rights reserved.
  Used parameter files:
  /home/oracle/OraHome1/network/admin/sqlnet.ora
  Used TNSNAMES adapter to resolve the alias
  Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =  
  192.168.248.155)(PORT = 1521))) (CONNECT_DATA = (SID = hatest) (SERVER = DEDICATED)))
  OK (30 msec)

  沒問題通了。再串連資料庫

  [oracle@localhost admin]$ sqlplus hangan/hangan@hatest
  SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 26 16:33:49 2007
  Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
  Connected to:
  Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
  With the Partitioning, OLAP and Oracle Data Mining options
  JServer Release 9.2.0.4.0 - Production
 
  SQL> exit
----------------------------------------------------------------------------------------------------------------------------------------

看了上面的描述之後，我按照步驟一步步檢查，到了檢查sqlnet.ora檔案的時候，問題就出來了。原來這位同事的sqlnet.ora檔案中是這樣的：

# SQLNET.ORA Network Configuration File: C:/oracle/ora81/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = eapac.ericsson.se

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (ONAMES)

可以看到這裡只使用了ONAMES來尋找服務，但他的服務是配置在tnsnames.ora檔案中，當在SQL*PLUS中串連時，Oracle不是按tnsname的方式來尋找，當然找不到服務了。

問題找到了，將NAMES.DIRECTORY_PATH 改為：
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)

再測試連接通過，問題解決。

在此要感謝博文的博主，也感覺文中的DBA解決問題的思路很清晰，看來自己離一個合格的DBA還有很長的距離，繼續努力中。