重庆思庄Oracle、Redhat认证学习论坛
标题: ORA-12514,TNS:listener does not currently know of service requested in conne... [打印本页]
作者: jiawang 时间: 2017-12-14 18:14
标题: ORA-12514,TNS:listener does not currently know of service requested in conne...
[root@strong ~]# su - oracle[oracle@strong ~]$ sqlplusscott/tiger@YADATA
SQL*Plus: Release 11.2.0.4.0 Production onThu Dec 14 01:33:39 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currentlyknow of service requested in connect
descriptor
Enter user-name:
1首先检查客户端的tnsnames.ora里面的host、port、service_name等是否正确
[root@strong admin]# su - oracle
[oracle@strong ~]$ cd $ORACLE_HOME
[oracle@strong db_1]$ cd network
[oracle@strong network]$ ll
总用量 36
drwxr-xr-x. 3 oracle dba 4096 12月 14 02:04 admin
drwxr-xr-x. 2 oracle dba 4096 6月 28 13:55 doc
drwxr-xr-x. 4 oracle dba 4096 6月 28 13:54 install
drwxr-xr-x. 2 oracle dba 4096 6月 28 13:56 jlib
drwxr-xr-x. 2 oracle dba 4096 6月 28 13:57 lib
drwxrwx---. 2 oracle dba 4096 6月 29 12:21 log
drwxr-xr-x. 2 oracle dba 4096 6月 28 13:56 mesg
drwxr-xr-x. 4 oracle dba 4096 6月 28 13:56 tools
drwxrwx---. 2 oracle dba 4096 6月 28 13:58 trace
[oracle@strong network]$ cd admin
[oracle@strong admin]$ ll
总用量 20
-rw-r--r--. 1 oracle dba 367 6月 29 11:10 listener17062911上午1043.bak
-rw-r--r--. 1 oracle dba 367 12月 14 02:03 listener.ora
drwxr-xr-x. 2 oracle dba 4096 6月 28 13:53 samples
-rw-r--r--. 1 oracle dba 381 12月 17 2012 shrept.lst
-rw-r-----. 1 oracle dba 325 12月 14 02:04 tnsnames.ora
[oracle@strong admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
YADATA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = strong)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YADATA)
)
)
2检查确认数据库实例启动情况，实例名。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
YADATA
SQL> select status from v$instance;
STATUS
------------
OPEN
查看监听状态
[oracle@strong admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-DEC-2017 02:12:22
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=strong)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-JUN-2017 14:09:58
Uptime 168 days 12 hr. 2 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/strong/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=strong)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "YADATA" has 1 instance(s).
Instance "YADATA", status READY, has 1 handler(s) for this service...
Service "YADATAXDB" has 1 instance(s).
Instance "YADATA", status READY, has 1 handler(s) for this service...
The command completed successfully
监听服务也正常，没有问题
检查 /u01/app/oracle/diag/tnslsnr/strong/listener/alert/log.xml下的日志，发现host_addr为192.168.133.131，实际IP地址不是这个。因为监听文件里面我使用的HOST=strong，所以赶紧检查/etc/hosts的配置
检查/etc/hosts的配置
[root@strong admin]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.133.131 strong
本机ip为192.168.133.144
