【Oracle】TNS-12154 錯誤解析

楊奇龍發表於2011-11-14
Oracle
ORA-12514: TNS:監聽程式當前無法識別連線描述符中請求的服務
這個提示在一定程度上導致產生誤解，讓人認為資料庫服務名SERVICE NAME本身有問題，其實它指的是tnsnames.ora中配置的別名ALIAS所對應的SERVICE_NAME與在監聽程式listener的註冊的服務名中找不到匹配的服務名，當然如果只是配置了動態監聽，則可能一個也沒有（也可能一個也沒有，比如只配置了動態監聽）。
tnsnames.ora中的服務名SERVICE_NAME的可選值為靜態和動態註冊的服務名（SERVICE_NAME）,也即listener.ora中的GLOBAL_DBNAME和PARAMETER中的SERVICE_NAME，可以是任意支援的字元，但兩者必須與保持一致。而sqlplus中的連結字串，並不是SERVICE_NAME，而是一個SERVICE_NAME的ALIAS(在tnsname.ora中配置）。
lsnrctl status的結果：
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "yangdb" has 1 instance(s).
  Instance "yangdb", status UNKNOWN, has 1 handler(s) for this service...
listener.ora的內容：
 (SID_DESC =
          (GLOBAL_DBNAME = yangdb) --listener.ora檔案中註冊靜態監聽時的服務名，如上面的Service "yangdb" 
           (ORACLE_HOME = /opt/oracle/11.2.0/alifpre)
          (SID_NAME = yangdb)      --資料庫的例項名 
         )
tnsnames.ora的內容
yangdb =                           --別名Alias，可以任意填寫！sqlplus  USER/PWD@yangdb 用的就是這個別名
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.241 )(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = yangdb)      --可以任意填寫，但是必須和listener.ora中的GLOBAL_DBNAME(靜態監聽)   
    )                              --或者動態監聽時和lsnrctl status 中的"Service"的值一致 
  )
當然對於tnsnames.ora中的SERVICE_NAME 替換為SID的話，必須和lsnrctl status中的 Instance "yangdb" 一致！
使用service_name連線oracle 資料庫的方式見《service_name和service_names的關係》一文，下面討論一下我遇到的使用sid連線資料庫的情況：
服務端：
oracle@dba-host1:/opt/oracle/products/9.2.0/network/admin>cat listener.ora 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/products/9.2.0)
      (PROGRAM = extproc)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = dba-host1.dev.sd.aliyun.com)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
oracle@dba-host1:/opt/oracle/products/9.2.0/network/admin>cat tnsnames.ora
yang_TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.171)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = yang)
    )
  )
oracle@dba-host1:/home/oracle>lsnrctl status
LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 11-NOV-2011 02:06:41
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba-host1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba-host1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba-host1)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "ASOFT" has 1 instance(s).
  Instance "yang", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "yangXDB" has 1 instance(s).
  Instance "yang", status READY, has 1 handler(s) for this service...
The command completed successfully
在本地的tnsnames.ora檔案中service_name 指定 ASOFT 
yang_TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.171 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ASOFT)
    )
  )
或者知道SID為yang
TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.171 )(PORT = 1521))
    (CONNECT_DATA =
      (SID=yang)
    )
  )
都可以連線到資料庫，如果沒有按照上面的例子來做，就會遇到 TNS-12154
使用windows的客戶端連線
C:\Users\aaaa>sqlplus yang/yang@test
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 11 02:08:12 201
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
連線到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
yang@rac1>EXIT
從 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production 斷開
C:\Users\aaaa>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-710916/，如需轉載，請註明出處，否則將追究法律責任。

相關文章