潛水珽的異想世界

狀況:
C:\Documents and Settings\Administrator>sqlplus system/ora102@lnxrac1_emrep1

SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 7月 3 15:55:43 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS: 監聽器目前不知道連線描述區中要求的服務



解決方式:
這個問題分兩個部份來check,先判斷是Server端連線無法判斷?還是client端?
server端請check listener.ora以及listener.log還有利用lnnrctl來看有無相關的error訊息.

以下是我們的listener.ora的資訊:
[oracle@lnxrac1 admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/orahome/ora10g/db10g)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxrac1.ntuh.gov.tw)(PORT = 1521))
    )
  )

以下是我們的listener.log的資訊:
[oracle@lnxrac1 admin]$ cat /oracle/orahome/ora10g/db10g/network/log/listener.log
<省略部份>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
03-JUL-2008 15:47:42 * (CONNECT_DATA=(SERVICE_NAME=emrep1)(CID=(PROGRAM=C:\Program?Files\Quest?Software\Toad?for?Oracle\toad.exe)(HOST=SUBMARINE-NB)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.6.126)(PORT=4241)) * establish * emrep1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
03-JUL-2008 15:47:43 * (CONNECT_DATA=(SERVICE_NAME=emrep1)(CID=(PROGRAM=C:\Program?Files\Quest?Software\Toad?for?Oracle\toad.exe)(HOST=SUBMARINE-NB)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.6.126)(PORT=4242)) * establish * emrep1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
03-JUL-2008 15:48:02 * service_register * emrep1 * 0
03-JUL-2008 15:48:59 * (CONNECT_DATA=(SID=emrep1)(CID=(PROGRAM=perl)(HOST=lnxrac1.ntuh.gov.tw)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.20.81)(PORT=24441)) * establish * emrep1 * 0
03-JUL-2008 15:49:32 * service_update * emrep1 * 0


在server端是可以login oracle DB的:
[oracle@lnxrac1 admin]$ sqlplus system/ora102@emrep1

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Jul 3 16:03:40 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

反而是我的PC無法login,想比是我的PC上tnsnames.ora設定的問題.

以下是我PC上tnsnames.ora的設定:
lnxrac1_emrep1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxrac1.ntuh.gov.tw)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = emrep1)
    )
  )


我將service更改為完整的sid+domainname就解決了問題.
lnxrac1_emrep1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxrac1.ntuh.gov.tw)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = emrep1.ntuh.gov.tw)
    )
  )

這個問題的原因是因為client連線時server無法分辨真正要連線的SID Name(Service Name)所造成的。

END~