17,382
社区成员
发帖
与我相关
我的任务
分享ORA-12514: TNS:listener does not currently know of service requested in connect
lsnrctl启动如下：
[oracle@powerlong4 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-FEB-2015 19:18:25
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/xxxx/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-FEB-2015 19:18:25
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$
[oracle@powerlong4 admin]$
[oracle@powerlong4 admin]$
[oracle@powerlong4 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-FEB-2015 19:18:37
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-FEB-2015 19:18:25
Uptime 0 days 0 hr. 0 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$
tnsnames.ora配置文件如下：
[oracle@powerlong4 admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SC_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.xxx.xxx.217)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = powerdes)
)
)
tnsping 是可以ping通的，如下所示：
[oracle@powerlong4 admin]$ tnsping SC_PD
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 02-FEB-2015 19:22:26
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = powerdes)))
OK (0 msec)
[oracle@powerlong4 admin]$
但是连接报错：
[oracle@powerlong4 admin]$ sqlplus "sys/sys@SC_PD";
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 2 19:21:04 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name: ^C
[oracle@powerlong4 admin]$
[oracle@powerlong4 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-FEB-2015 19:18:25
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/xxxx/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-FEB-2015 19:18:25
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$
[oracle@powerlong4 admin]$
[oracle@powerlong4 admin]$
[oracle@powerlong4 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-FEB-2015 19:18:37
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-FEB-2015 19:18:25
Uptime 0 days 0 hr. 0 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$
tnsnames.ora配置文件如下：
[oracle@powerlong4 admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SC_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.xxx.xxx.217)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = powerdes)
)
)
tnsping 是可以ping通的，如下所示：
[oracle@powerlong4 admin]$ tnsping SC_PD
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 02-FEB-2015 19:22:26
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = powerdes)))
OK (0 msec)
[oracle@powerlong4 admin]$
但是连接报错：
[oracle@powerlong4 admin]$ sqlplus "sys/sys@SC_PD";
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 2 19:21:04 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name: ^C
[oracle@powerlong4 admin]$
...全文
请发表友善的回复…
发表回复
xsssssc 2016-01-14
- 打赏
- 举报
http://zhidao.baidu.com/link?url=zvMF35ncwYBJk82GGqqcuQkQWus91tNKAExScdA12quTV4glopKqKu8ip4G6DwyalWHsChrDmO1F3IJtWp2RZq
csdnhadoop 2015-02-03
- 打赏
- 举报
那service_name要指定成什么样的呢，是否要和db_unquie_name保持一致？
小灰狼W 2015-02-03
- 打赏
- 举报
看看官方文档reference吧，里面描述得很清楚
DB_UNIQUE_NAME specifies a globally unique name for the database. Databases with the
same DB_NAME within the same DB_DOMAIN (for example, copies of a database created
for reporting or a physical standby) must have a unique DB_UNIQUE_NAME. Every
database's DB_UNIQUE_NAME must be unique within the enterprise.
DB_NAME specifies a database identifier of up to 8 characters. This parameter must be
specified and must correspond to the name specified in the CREATE DATABASE
statement.
If you have multiple databases, the value of this parameter should match the Oracle
instance identifier of each one to avoid confusion with other databases running on the
system. The value of DB_NAME should be the same in both the standby and production
initialization parameter files.
另外，db_name和db_unique_name和你上面的问题都没有关系，service_name只是给监听用的。上面是service_name指定错误
SERVICE_NAMES specifies one or more names by which clients can connect to the
instance. The instance registers its service names with the listener. When a client
requests a service, the listener determines which instances offer the requested service
and routes the client to the appropriate instance.
csdnhadoop 2015-02-03
- 打赏
- 举报
为什么启动的时候
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /home/oradata/powerdes, /home/
oradata/pwerdes
db_name string powerdes
db_unique_name string pdunq
global_names boolean FALSE
instance_name string powerdes
lock_name_space string
log_file_name_convert string /home/oradata/powerdes, /home/
oradata/powerdes
service_names string pdunq
SQL>
为什么我的db_name和db_unique_name不一样呢？
db_name string powerdes
db_unique_name string pdunq
csdnhadoop 2015-02-03
- 打赏
- 举报
为啥不用qq啊，斑竹？
csdnhadoop 2015-02-03
- 打赏
- 举报
C1和C2，差别就是在于service_name不一样，其它的一样啊。
我的疑惑是，oracle服务器上面，service_name有2个一个是powdes一个是pdunq，两个都好使。
为什么客户端，我用了2个service_name一个是powdes一个是pdunq，一个powdes好使一个pdunq不好使报错ORA-·12514：TNS:listener does not currently know of service erquested in connect descriptor。
我想弄明白这里面的区别啊斑竹。
如果按照你说的show paramter name为基准，多个以逗号连接，可我oracle服务器上只有一个powdes服务名，如下所示：
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string powdes
SQL>
按照道理，不管服务器端用sqlplus登录也好还是客户端用plsql登录也好，都应该只认唯一的service_name powdes才对，可是现在问题是，服务器端认powdes和pdunq，而客户端只认pdunq不认powdes，斑竹，这是为什么啊？
小灰狼W 2015-02-03
- 打赏
- 举报
说明c1配置错了。靠靠，我忙去了，解释半天又绕回去了
csdnhadoop 2015-02-03
- 打赏
- 举报
oracle服务器端：
我进去sql界面只有一个：
SQL> show parameter service_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string pdunq
SQL>
可是为什么我的tnsnames.ora里面却有2个，一个powdes，一个是pdunq呢？
S1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.2xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = powdes)
)
)
S2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.2xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdunq)
)
)
oracle服务器端，sqlplus登录 sqlplus user/pwd@S1可以登录成功， sqlplus user/pwd@S2也可以成功。
客户端的tnsname.ora：
C1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.2xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = powdes)
)
)
C2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.2xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdunq)
)
)
客户端用plsql连接，C2连接成功，C1连接失败，报错：ORA-·12514：TNS:listener does not currently know of service erquested in connect descriptor
斑竹，我这个问题 是什么问题啊？
小灰狼W 2015-02-03
- 打赏
- 举报
查看服务端的service_names是否有这个服务名
唉，同一个问题诶，理解一下试试看就知道了
csdnhadoop 2015-02-03
- 打赏
- 举报
我这里oracle服务器端tnsnames.ora里面有2个server_name，一个是用客户端连接好使，一个不好使报ORA-·12514：TNS:listener does not currently know of service erquested in connect descriptor
csdnhadoop 2015-02-03
- 打赏
- 举报
就是说客户端的tnsnames.ora里面的service_name只要保持与 服务器端的tnsname.ora的多个中的一个服务名一样就可以连上oracle服务器了，对吧？
小灰狼W 2015-02-03
- 打赏
- 举报
。。。如果tnsnames里面可以随便写，还要这个参数干嘛
服务端上可以自己设置service_names，客户端连接服务端，使用service_name的时候要配置成服务端中指定的名称，如果服务端指定了多个，那么客户端用其中一个即可
csdnhadoop 2015-02-03
- 打赏
- 举报
嗯，好的，那么在本机的话，是可以换任何名称，那么客户端的tnsnames.ora里面的service_name呢？怎么配置也是可以指定任何名称吗还是要指定一个统一的名称？
小灰狼W 2015-02-03
- 打赏
- 举报
不需要，可以任意指定名称和数量
小灰狼W 2015-02-02
- 打赏
- 举报
就是说，tnsnames里的service_name配置错了
发帖问吧，我平时不怎么用QQ。论坛里的话，我没空，还有别人可以帮你
把具体的日志错误什么的贴出来
csdnhadoop 2015-02-02
- 打赏
- 举报
方便加你qq吗？有oracle备份的事情请教下你。
我以为备份的是powerdes，结果恢复的时候，db_unique_name变成了pstxe，这是为什么呢？
csdnhadoop 2015-02-02
- 打赏
- 举报
实例已经打开啦，谢谢你啊。
我问题已经解决了，知道问题在哪里了，我的db_unique_name 不是powerdes是pstxe，所以才报错。
我把tnsnames.ora里面的service_name换成了pstxe就好了。
小灰狼W 2015-02-02
- 打赏
- 举报
sqlplus / as sysdba
select status from v$instance;
实例是否没有打开
csdnhadoop 2015-02-02
- 打赏
- 举报
对了，oracle版本11g，centos 64位系统。
