Ora 12514 как исправить

ORA-12514 means that the listener cannot find any matched service with yours, so it cannot establish the connection with the database for you. As a result, it responds ORA-12514 to alert the failed connection.

As a DBA, I have seen several kinds of ORA-12514 in different scenarios and solved them by different means. Here I divide scenarios that could throw ORA-12514 into several error patterns as below:

1. Common Situations
2. Restarting a Database
3. Switching over to Standby
4. Using a Database Link

Only one thing that can be sure in ORA-12514 is that the target listener is up and running. That is to say, the listener is reachable.

For unknown requested SID, the listener throws ORA-12505: TNS:listener does not currently know of SID given in connect. It may have different error patterns from this post.

ORA-12514 in Common Situations

Let’s see how we get ORA-12514. First of all, we have to make sure the listener on the database server is reachable by using tnsping.

C:Usersed>tnsping ora11g

OK, the remote listener is up and reachable. Please note that, the message of successful tnsping did not indicate that the service name is existing on the remote listener.

Next, we test the connection to the database by sqlplus (SQL*Plus).

C:Usersed>sqlplus hr/hr@ora11g
...
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Cause

When the connection came, the listener found no matched service name registered with it. Consequently, the listener had no idea which database should be used to service the connection. Here are some possible causes of ORA-12514:

1. No instance

The database was not available or say idle, no any dynamic services registered with the listener. That’s why we saw ORA-12514.

2. Mismatched service name

The service name of the connect descriptor in tnsnames.ora did not match the service name in the listener. That is to say, the listener does not currently know of service requested in connect descriptor.

3. Not registered

If the database instance is up and running, the service name may not registered with the listener at specific port as you thought. By default, the service port of listener is 1521. But sometimes, it could switch to another port, say 1522.

Please note that, ORA-12514 is an error threw by the listener specifically for the client side. The database is no way to know such problem.

Solutions

The solutions to ORA-12514 are to make sure the following things:

1. The database is available

If there’s no instance, then no service name will register with the listener. You should startup the instance, then LREG will automatically register the service in 60 seconds.

If you are sure that the database is up and running and still got ORA-12514, things may be complicated. Let’s keep looking for other solutions.

2. The service names are matched

The service name in the connect descriptor of client’s tnsnames.ora shall match the service registered in the listener. Sometimes, it’s just a typo problem.

In short, the service names in the following 3 parties should be matched with each other in order to solve ORA-12514.

• Connect descriptor.
• The listener.
• The database.

We talk about them respectively below.

Connect Descriptor

Let’s see an example of connect identifier. In which, the body is a connect descriptor.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
...
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

The question is, where is tnsnames.ora?

The Listener

The connect descriptor is easily checked, but how do we check the service names of the listener? See the following command:

[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...

For a specific listener name, you should do this:

[oracle@test ~]$ lsnrctl status <listener_name>

The Database

If there’s no matched service name in the listener, we should check the service names of the instance by OS authentication. No credentials are needed.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show parameter service_names

In case that you have no way to query the database, you can make a guess. Usually but not always, the service name is the same as the database unique name (DB_UNIQUE_NAME). The database unique name is the same as the instance name ($ORACLE_SID) for a single-instance database.

If both service names in the client and database are matched, but the listener showed a different service name or «The listener supports no services». The database may register a different listener, let’s keep reading this post.

3. The host and port are both right

Sometime, we may go for the wrong destination of the listener, either host or port was incorrect. As a result, we saw ORA-12514 because there’s no matched service name with that listener. We should check the host and port in the connect descriptor of the TNS name.

Chances are, the correct hostname may be resolved as the wrong IP address by DNS. Consequently, connections took us to the wrong database server. This could happen when we switched from an old machine to a new database server. So we should focus on name resolution problem first.

For those ORA-12514 caused by name resolution, we can use the IP address instead of hostname to connect the database for clients to work around it. Additionally, DBA should also check both values of HOST and PORT in listener.ora.

During the troubleshooting on the name resolution, you might see TNS-12545: Connect failed because target host or object does not exist temporarily.

4. Instance registers it services with the right listener

The instance may register with the another listener which services whatever port other than 1521. Consequently, no service name is supported by that listener.

That’s why we always see ORA-12514 only in clients or TNS-12514 in the listener’s log, the database instance will never know such problem.

Now we have a question: What listener is the instance using? If the database is using a different listener other than the default one, we can check a parameter named LOCAL_LISTENER for sure:

SQL> show parameter local_listener

If the default listener at port 1521 in the same machine is used, the value could be empty. Otherwise, we will see the listener description like the above.

In a very rare case, the database uses a remote listener to register its service names. This is what Class of Secure Transports (COST) fights against.

For more troubleshooting beside ORA-12514, you may refer to Oracle 18c Net Services Administrator’s Guide: 15 Testing Connections.

ORA-12514 When Restart a Database

It’s pretty easy to explain why you got ORA-12514 when you restart a database. Please compare the following two cases.

Connect to the database through listener

[oracle@test ~]$ sqlplus /nolog
...
SQL> conn sys@orcl as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

We saw ORA-12514 after we tried to startup the stopped database. Here is the reason: When you shutdown the instance, you lost the connection from the database and the service was unregistered from the listener, no more service information of the database on the listener. Therefore, ORA-12514 notified that you can’t do any further to this database in this way.

To solve ORA-12514 thoroughly, you should add a static service registration to the listener for your database. Otherwise, you have to connect to the database by OS authentication.

Let’s see how to avoid ORA-12514 if you are going to do critical jobs like shutdown or startup database through OS authentication.

Connect to the database through OS authentication

You don’t have to provide the password through OS authentication.

[oracle@test ~]$ sqlplus /nolog
...
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Can you tell the difference? By this method, you can never lose your connection during down time no matter the listener is up or not. No ORA-12514 interrupts you.

By the way, I talked about how to connecting an idle, NOMOUNT or RESTRICT database from remote clients in another post. It may help you to clarify some concepts about ORA-12514.

ORA-12514 When Switchover to Standby

DGMGRL> switchover to standby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "primary" on database "standby"
Connecting to instance "primary"...
Connected.
New primary database "standby" is opening...
Operation requires startup of instance "primary" on database "primary"
Starting instance "primary"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

This kind of error pattern of ORA-12514 is the most confusing, because you might have no idea what’s going on according to the last message «Failed» in console. This is what’s going on:

1. The former standby is up and now playing the primary role.
2. The former primary is down and going to play the standby role, but the data guard broker is unable to mount the instance due to the lost contact. As a result, we saw ORA-12514 in DGMGRL.

That is to say, the switchover is incomplete, but it does not mean a failure. This is because the former primary database is down and the broker lost the contact that caused ORA-12514.

Here are the actions that you have to take in order to complete the switchover. Please start up and mount the former «primary» database manually. After that, the data guard will automatically synchronize the data so as to complete the switchover.

The preventive action to ORA-12514 in an incomplete switchover is to add a very special static service in listener.ora for data guard broker to use.

ORA-12514 When Using a Database Link

A database link is just like a client which is trying to connect the remote database.

SQL> conn hr/hr
Connected.
SQL> select first_name from employees@ora11g_hr;
select first_name from employees@ora11g_hr
                                 *
ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

If the database link has been reliable for a long time, you should check the availability of the remote database. The causes that we have talked about in the section of ORA-12514 in Common Situations are still sustained. They may be:

• The remote database is down. This is the most common cause of ORA-12514.
• The connect descriptor in the local tnsnames.ora has been changed.
• The service name of the remote database has been changed.
• Another listener is used for servicing the remote database.

Usually, this type of ORA-12514 does not complain about the database link, except that you defined the database link with its own connect descriptor.

For example, we can create a database link with a full connect descriptor like this:

SQL> create database link ora11g_hr connect to hr identified by hr using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ORCL)))';

As you can see, we did not use a connect identifier, instead, we use a full connect descriptor. Therefore, if there’s anything wrong with the connect descriptor, we have to drop the database link then create a new one for solving ORA-12514.

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:прослушиватель в данный момент не имеет данных о службе, запрашиваемой в дескрипторе соединения

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