ORA-12514,TNS:listener does not currently know of service requested in conne...

jiawang

[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

登录/注册后可看大图

郑全

确实,这个错误平时很常见.