Oracle ошибка ora 12514

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor error occurs when the listener cannot find any matched service with the provided service name, preventing it from connecting to the database.The service name you used to connect to the database is either unavailable or incorrectly configured. Oracle Listener detects a mismatch between the service name you provided with connection descriptor configurations.The error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor is thrown when oracle fails to establish connection due to invalid service name provided in the configuration.

The Oracle service may be starting; please wait a moment before attempting to connect a second time. Check the listener’s existing information of services by running: lsnrctl services. Check that the SERVICE NAME parameter in the connect descriptor of the net service name used specifies a service that the listener is familiar with. If a connect identifier was used, make sure the service name specified is one the listener recognises. Look for any events in the listener.log file to resolve the error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.

The Problem

When you attempt to connect to a database, you will be given the host name, port, user name, password, and service name or Sid. When Oracle starts up, it will listen for connections on the same host name and port. The SID is the name of the Oracle instance that is currently running. The service name is an alias for the instance that allows you to connect to it. If there is a mismatch in the service name configuration, you will be unable to connect to the running Oracle database instance. The error message ORA-12514, TNS:listener does not currently know of service requested in connect descriptor will be displayed.

Host name : localhost
port      : 1521
service name : orcl
username  : hr
password  : hr

Error

Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
  (CONNECTION_ID=glCwDQzBSiScXNzmRhbuQg==)

Solution 1

The oracle server may be starting. wait for a moment and retry the connection. The network glitch may be occur. This will prevent to establish the connection to the oracle database. First make sure the database is running and no network issue with the database. If the host name is used to connect, try with IP address to connect with oracle database. Make sure the port configured is same and running port is the same. If you are using via application, the connection url should be configured as per the format.

jdbc:oracle:thin:@localhost:1521/servicename

Solution 2

Check the listen is running without any issue. If any issue in listen you can stop and start once. This will refresh the connection with the configured host and port. Listener might be hang due to multiple connections. Try connecting the listener once after restarting the listener. This will resolve the error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.

lsnrctl status

lsnrctl stop
lsnrctl start

Solution 3

Verify that the configured service name matches a valid service name in the Oracle database. The SQL query below will retrieve the configured service names from the Oracle database. If the provided service name in listener.ora differs from the database configuration, use the database’s service name. This will fix the error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.

select value from v$parameter where name='service_names'

value
------
orclcdb

Solution 4

Verify the listener.ora file. The configuration should look like this. The Oracle instance configuration will be stored in the SID LIST LISTENER. In the database, this configuration will map GLOBAL DBNAME, SID NAME, and ORACLE HOME. The SID NAME should be the same as the service name from the previous query. The PROTOCAL, HOST, and PORT are defined by the LISTENER configuration. The LISTENER configuration uses this configuration to wait for the database connection to establish.

/u01/app/oracle/product/version/db_1/network/admin/listener.ora

OR

[ORACLE_HOME]/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orclcdb)
      (SID_NAME = orclcdb)
      (ORACLE_HOME = /u01/app/oracle/product/version/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orclcdb)

Solution 5

The tnsnames.ora file contains the service name configuration as well as the listener host and port. A sample tnsnames.ora file demonstrating service name configuration is provided below. In tnsnames.ora, check the service name. If the service name is not configured or if the configuration is incorrect, make the changes listed below.

/u01/app/oracle/product/version/db_1/network/admin/tnsnames.ora

OR

[ORACLE_HOME]/network/admin/tnsnames.ora

ORCLCDB=localhost:1521/orclcdb
ORCL=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Solution 6

Check your system environment configuration for the oracle database. The oracle database configuration will show the right database which is running. If multiple oracle database versions are installed in the server, it may conflict with the oracle database versions. Then environment setting will be in ~/.bash_profile, ~/.bashrc OR /etc/..bashrc.

export ORACLE_UNQNAME=orclcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/version/db_1
export ORACLE_SID=orclcdb
export PATH=/home/oracle/bin:/home/oracle/LDLIB:$ORACLE_HOME/bin:/usr/sbin:$PATH

Solution 7

Finally verify the configuration you provided while you try to connect with the database. If you misspelt the configuration, the error will be shown. Check the below configuration as per your oracle database configurations.

Host name : localhost
port      : 1521
service name : orcl
username  : hr
password  : hr

[{«Product»:{«code»:»SSSHCT»,»label»:»Rational RequisitePro»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Database: Oracle»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»7.0;7.0.0.1;7.0.0.2;7.0.0.3;7.0.0.4;7.0.0.5;7.0.0.6;7.0.0.7;7.0.1;7.0.1.1;7.0.1.2;7.0.1.3;7.0.1.4;7.0.1.5;7.0.1.6;7.1;7.1.0.1;7.1.0.2;7.1.0.3″,»Edition»:»»,»Line of Business»:{«code»:»LOB45″,»label»:»Automation»}}]

null

C:Windowssystem32>sqlplus sys/pass@connect_identifier as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on ***

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Присоединен к:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
База данных закрыта.
База данных размонтирована.
Экземпляр ORACLE завершен.
SQL> startup nomount
ORA-12514: TNS:прослушиватель в данный момент не имеет данных о службе, запрашиваемой в дескрипторе соединения