无奈,只有发贴了,ora-12514错误。
发此贴纯属无奈,Google了,论坛搜索了,都没能解决问题,所以才发的帖,望版主谅解。
环境:服务端 linux as4+oracle9.2.0.4 ip:192.168.1.252 主机名:server.
客户端 win2003 oracle10gclient ip:192.168.1.254问题如下:
sqlplus 连接出现ora-12514错误:监听程序当前无法识别连接描述符中请求的服务。
使用net manager 和 net configuration assistant测试连接的时候都是成功连接的。服务端listener 监听正常(相关web服务使用也正常)
listener.ora 如下:
istener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora9/product/9.2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = xjty)
(ORACLE_HOME = /opt/ora9/product/9.2)
(SID_NAME = xjty)
)
)服务端tnsnames.ora如下:
nsnames.ora
# Generated by Oracle configuration tools.
xjty =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xjty)
(SID = xjty)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
客户端tnsnames.ora如下:
# tnsnames.ora Network Configuration File: F:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XJTY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xjty)
)
)
客户端上手动修改过tnsnames.ora加上(SID = xjty)结果一样
将HOST换成ip结果也一样,XJTY前面没有空格,tnsping正常,如下:F:\oracle\product\10.2.0\client_1\BIN>TNSPING.EXE gnnt
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-6月 -
2007 02:14:58
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的参数文件:
F:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = server.)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NA
ME = xjty)))
OK (30 毫秒)
F:\oracle\product\10.2.0\client_1\BIN>
郁闷死了,是不是SID事情?为什么用net manager配置的时候不能配置SID?而手动修改客户端tnsnames.ora以后再进net manager就发现
服务命名里面的xjty不见了,用net configuration assistant添加说已存在。
抓狂了,因为只是很少的一点应用只要能连接查询里面的一些数据就可以了,所以也来不及系统的看书也不一定能看懂。
麻烦各位达人帮忙,万分感谢。
发此贴纯属无奈,Google了,论坛搜索了,都没能解决问题,所以才发的帖,望版主谅解。
环境:服务端 linux as4+oracle9.2.0.4 ip:192.168.1.252 主机名:server.
客户端 win2003 oracle10gclient ip:192.168.1.254问题如下:
sqlplus 连接出现ora-12514错误:监听程序当前无法识别连接描述符中请求的服务。
使用net manager 和 net configuration assistant测试连接的时候都是成功连接的。服务端listener 监听正常(相关web服务使用也正常)
listener.ora 如下:
istener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora9/product/9.2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = xjty)
(ORACLE_HOME = /opt/ora9/product/9.2)
(SID_NAME = xjty)
)
)服务端tnsnames.ora如下:
nsnames.ora
# Generated by Oracle configuration tools.
xjty =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xjty)
(SID = xjty)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
客户端tnsnames.ora如下:
# tnsnames.ora Network Configuration File: F:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XJTY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xjty)
)
)
客户端上手动修改过tnsnames.ora加上(SID = xjty)结果一样
将HOST换成ip结果也一样,XJTY前面没有空格,tnsping正常,如下:F:\oracle\product\10.2.0\client_1\BIN>TNSPING.EXE gnnt
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-6月 -
2007 02:14:58
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的参数文件:
F:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = server.)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NA
ME = xjty)))
OK (30 毫秒)
F:\oracle\product\10.2.0\client_1\BIN>
郁闷死了,是不是SID事情?为什么用net manager配置的时候不能配置SID?而手动修改客户端tnsnames.ora以后再进net manager就发现
服务命名里面的xjty不见了,用net configuration assistant添加说已存在。
抓狂了,因为只是很少的一点应用只要能连接查询里面的一些数据就可以了,所以也来不及系统的看书也不一定能看懂。
麻烦各位达人帮忙,万分感谢。
客户端sqlnet.ora有tnsnames,如下,
# sqlnet.ora Network Configuration File: F:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.SQLNET.AUTHENTICATION_SERVICES= (NTS)NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)服务器上你说的那个如下:
server.:/users/rtsg $ sqlplus rtsg/rtsg@xjtySQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 25 17:14:06 2007Copyright (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 - ProductionSQL>
lsnrctl status 结果如下:svr-jl.:/users/rtsg $ lsnrctl statusLSNRCTL for Linux: Version 9.2.0.4.0 - Production on 25-JUN-2007 17:16:29Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 25-JUN-2007 08:07:38
Uptime 0 days 9 hr. 8 min. 51 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/ora9/product/9.2/network/admin/listener.ora
Listener Log File /opt/ora9/product/9.2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xjty" has 2 instance(s).
Instance "xjty", status UNKNOWN, has 1 handler(s) for this service...
Instance "xjty", status READY, has 1 handler(s) for this service...
Service "xjtyXDB" has 1 instance(s).
Instance "xjty", status READY, has 1 handler(s) for this service...
The command completed successfully
那个我没改gnnt就是xjty这个没错