ERROR - ORA-12514

huizzz

大家好

当我在客户端有ie,用isql *plus 链接数据库时，提示如下错误，请帮忙分析下，是什么原因，谢谢
Oracle 主机 Solaris 10
Oracle 版本 10.2
客户端 ： windows pc

ERROR - ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

tnsnames.ora 文件内容
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = zch08view19)(PORT = 1521)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = sdh)
(PRESENTATION = RO)
)
)

listener.ora 文件内容

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = sdh)
(ORACLE_HOME = /u01/oracle/product/10gr2)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zch08view19)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

监听启动过程

zch08view19% lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 17-5月 -2010 04:41:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zch08view19)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 17-5月 -2010 04:24:15
Uptime 0 days 0 hr. 17 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/product/10gr2/network/admin/listener.ora
Listener Log File /u01/oracle/product/10gr2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zch08view19)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "sdh" has 1 instance(s).
Instance "sdh", status UNKNOWN, has 1 handler(s) for this service...
Service "sdh.test.com" has 1 instance(s).
Instance "sdh", status READY, has 1 handler(s) for this service...
Service "sdhXDB.test.com" has 1 instance(s).
Instance "sdh", status READY, has 1 handler(s) for this service...
Service "sdh_XPT.test.com" has 1 instance(s).
Instance "sdh", status READY, has 1 handler(s) for this service...
The command completed successfully

ilsyx

最好把你在 sqlplus 里面连接的命令都一并贴出来。从报错信息分析,tnsname.ora 文件的原因。
netmgr 添加个服务命名

renxiao2003

sqlplus username/passwd@sdh这样连接呢？

huizzz

下面是在 pc 段sqlplus 的报错，在服务器段可以正确sqlplus 连接，请帮忙分析下，我感觉可能也是tnsnames.ora 的问题，但不知道怎么改，或是别的方面的问题，请高手帮看下，谢谢

PC 端 sqlplus 的输出

D:\Profiles\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 5月 17 23:03:55 2010

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

SQL> connect system/oracle@sdh
ERROR:
ORA-28547: 连接服务器失败, 可能是 Oracle Net 管理错误


SQL> exit

D:\Profiles\>sqlplus system/oracle@sdh

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 5月 17 23:04:42 2010

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

ERROR:
ORA-28547: 连接服务器失败, 可能是 Oracle Net 管理错误




请输入用户名:

服务器端 sqlplus 的输出

zch08view19% sqlplus system/oracle

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 5月 17 10:43:18 2010

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


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

SQL> select * from v$instance;

flutter

tnsnames.ora里的HOST尽量使用IP地址

tnsping你的服务名看通不通。

yuio654

(PROGRAM = extproc)

去掉

ilsyx

把下面的内容贴到你tnsname.ora文件试试

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = zch08view19)(PORT = 1521)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

sdh =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = zch08view19)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sdh)
    )
  )

huizzz

我已经按照fultter的提示 把 tnsnames.ora 的HOST 改为ip 地址，也按照 yuio654 的建议把 (PROGRAM =

extproc) 去掉。最后尝试按照ilsyx  的方法把 tnsnames.ora 改为如下，但三种方法都没有解决问题，请大家再帮我分析下，谢谢

tnsnames.ora 文件内容

# tnsnames.ora Network Configuration File: /u01/oracle/product/10gr2/network/adm
in/tnsnames.ora
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.193.174.10(PORT = 1521)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

sdh =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.193.174.10(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sdh)
    )
  )

最后我把listener.ora 中的 host 也改为IP 地址，但也是不行，listener.ora 文件内容如下

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = sdh)
      (ORACLE_HOME = /u01/oracle/product/10gr2)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.193.174.10(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

pc 端 sqlplus 报错如下

D:\Profiles\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 5月 18 17:23:47 201

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

SQL> connect system/oracle@sdh
ERROR:
ORA-28547: 连接服务器失败, 可能是 Oracle Net 管理错误


SQL>

lsnrctl 状态如下

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.193.174.10(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date                18-5月 -2010 04:50:00
Uptime                    0 days 0 hr. 13 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/10gr2/network/admin/listener.ora
Listener Log File         /u01/oracle/product/10gr2/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.193.174.10(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "sdh" has 1 instance(s).
  Instance "sdh", status UNKNOWN, has 1 handler(s) for this service...
Service "sdh.test.com" has 1 instance(s).
  Instance "sdh", status READY, has 1 handler(s) for this service...
Service "sdhXDB.test.com" has 1 instance(s).
  Instance "sdh", status READY, has 1 handler(s) for this service...
Service "sdh_XPT.test.com" has 1 instance(s).
  Instance "sdh", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

doni

telnet 10.193.174.10 1521
看一下是否被防火墙挡住了

huizzz

应该不是防火墙的问题，我在同一网段，内网的PC 端登陆的