xp系统
以前装过9i,卸载后装的10g,10g我卸载换目录装了好几次了，还是不行，很郁闷在sqlplus中一切正常但在程序中用jdbc连接报错，一直没解决
错误信息如下:
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
//localhost/XE
以前装过9i,卸载后装的10g,10g我卸载换目录装了好几次了，还是不行，很郁闷在sqlplus中一切正常但在程序中用jdbc连接报错，一直没解决
错误信息如下:
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
//localhost/XE
tnsping 一下看看
This error occurs when you try to connect to a database.
Cause: If you used Oracle Net Easy Configuration or Oracle Net Assistant to create the Net Service Name entry, and you used the default option (Service Name) on the newly created Net Service Name, then the parameter SERVICE_NAME is added to the TNSNAMES.ORA as a subclause to the CONNECT_DATA section in the Net Service Name entry. This replaces the (SID=SIDname) subclause in previous releases of the database, for example, Oracle Database8i (8.1.x).
Action: Implement the TNSNAMES.ORA file as follows:
Use the GLOBAL_DBNAME parameter in the LISTENER.ORA for each SID that you want to identify as a separate service. Use the value of this parameter as the value of the SERVICE_NAME parameter. You need to activate any changes that you make to LISTENER.ORA for this purpose by stopping and restarting the listener process.Use the values of the parameters that exist in the INIT.ORA, namely SERVICE_NAMES and DB_DOMAIN, to determine the value of the SERVICE_NAME that you must use in TNSNAMES.ORA. The valid construction of this value is SERVICE_NAMES.DB_DOMAIN with the period separating the two INIT.ORA values. If your SERVICE_NAMES is BIKES and your DB_DOMAIN is COM, then your SERVICE_NAME is BIKES.COM.If there is no DB_DOMAIN parameter set in your INIT.ORA, or if there is no GLOBAL_DBNAME in the LISTENER.ORA, then you can use the SERVICE_NAMES from the INIT.ORA in your TNSNAMES.ORA for the SERVICE_NAME parameter.For example, if INIT.ORA contains SERVICE_NAMES = "TEST817" and db_domain is not set, then the TNSNAMES.ORA entry is: CONNECT_DATA =(SERVICE_NAME = "TEST817")).If you have multiple values specified in the SERVICE_NAMES parameter in the init.ora, then you can use one of them. If SERVICE_NAMES is not set, then you can use DB_NAME.DB_DOMAIN parameters from the INIT.ORA file.If SERVICE_NAMES and DB_DOMAIN is not set in the INIT.ORA and there is no GLOBAL_DBNAME in the LISTENER.ORA, then your SERVICE_NAME in TNSNAMES.ORA file is DB_NAME.
1.问题描述：在客户端使用shutdown immediate关闭远程数据库后，无法startup，提示错误为：ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务。操作如下：
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\IBM>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 9月 12 10:20:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
请输入用户名: sys/password@s210 as sysdba
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
SQL> conn sys/password@s210 as sysdba
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
2.检查服务器端：
Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 9月 12 10:40:02 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
2.1使用操作系统验证方式可以登录
请输入用户名: / as sysdba
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
2.2使用连接符无法登录
SQL> conn sys/password@orcl as sysdba
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
警告: 您不再连接到 ORACLE。
2.3检查监听，发现未设置对服务名的监听（Oracle10g默认安装后不自动设置监听）。
2.3.1修改前为：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
)
)LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hdsb-dell)(PORT = 1521))
)
)2.3.2修改后为：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(SID_NAME = ORCL)
)
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
)
)LISTENER =(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hdsb-dell)(PORT = 1521)) ))2.4重启监听：SQL> hostMicrosoft Windows [版本 5.2.3790](C) 版权所有 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-9月 -2007 10:4
1:56
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
命令执行成功
C:\Documents and Settings\Administrator>lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-9月 -2007 10:4
2:00
Copyright (c) 1991, 2005, Oracle. All rights reserved.
启动tnslsnr: 请稍候...TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production系统参数文件为d:\oracle\product\10.2.0\db_1\network\admin\listener.ora写入d:\oracle\product\10.2.0\db_1\network\log\listener.log的日志信息监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hdsb-dell)(PORT=1521)))正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------别名 LISTENER版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production启动日期 12-9月 -2007 10:42:02正常运行时间 0 天 0 小时 0 分 1 秒跟踪级别 off安全性 ON: Local OS AuthenticationSNMP OFF监听程序参数文件 d:\oracle\product\10.2.0\db_1\network\admin\listener.ora监听程序日志文件 d:\oracle\product\10.2.0\db_1\network\log\listener.log监听端点概要...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hdsb-dell)(PORT=1521)))服务摘要..服务 "PLSExtProc" 包含 1 个例程。例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...服务 "orcl" 包含 1 个例程。例程 "ORCL", 状态 UNKNOWN, 包含此服务的 1 个处理程序...命令执行成功C:\Documents and Settings\Administrator>exit2.5再次测试：SQL> conn sys/password@orcl as sysdba已连接。SQL>3.再测试客户端的启动关闭：C:\Documents and Settings\IBM>sqlplus "sys/password@s210 as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 9月 12 10:45:27 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 104857852 bytesDatabase Buffers 960495616 bytesRedo Buffers 7135232 bytes数据库装载完毕。数据库已经打开。SQL>
那些方法都没用，我最后郁闷的再防火墙里发现oracle的监听端口变成了2577
停掉，重启，端口又变了
现在想知道如何让端口固定下来？请知道的朋友帮下忙噢
大家能谈自己的实际经验最好，如果是帮lz找到了答案，粘一个相应的网址就可以了，确实不必一下子粘了那么一大段过来了，确实比较耽误大家浏览。我这里谈谈这个问题的看法首先，你的sqlplus一切正常，你是sqlplus是怎么连接的 是 直接 sqlplus "用户名/密码" 还是sqlplus "用户名/密码@tnsname"，如果是前一种，是不需要listener的，所以和jdbc的错误就没有任何瓜葛了，通过你的错误信息，TNS:listener does not currently know of service requested in connect descriptor
这里应该不是listener的问题，至少listener已经交互过了，不过是根据你给listener的连接信息，listener没有找到与你描述的连接相对应的service。所以由此可见listener是一点罪过都没有的。推断到这里，已经没有足够的信息继续了。你检查一下你的jdbc的连接url是怎么写的，或者直接贴上了，大家一起看，jdbc是不需要经过tnsname的，jdbc的url实际上可以被翻译成连接的描述。所以这里要你检查tnsname配置的，也是一点意义没有的。把你的jdbc url发上来看看。
最后这一句话纠正一下，我这里指的jdbc是一般的thin连接方式，如果是oci的方式，是肥客户端，是会依赖oracle的客户端的，也就需要tnsname了，不过不管哪种方式吧，现在首先应该探究的是你的jdbc的url是如何写的。