Listener

How to Resolve ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

As a DBA, I saw ORA-12514 in different scenarios and solved them respectively by different means. Here I divide scenarios that could throw ORA-12514 into three parts for your error pattern:

  1. Common Situations
  2. When Restart a Database
  3. When Switchover to Standby

ORA-12514 in Common Situations

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

C:\Users\ed>tnsping compdb

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:24:04

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
OK (60 msec)

OK, the remote listener is up.

Next, we test the connection to the database.

C:\Users\ed>sqlplus hr/hr@compdb

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 22 20:11:20 2014

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

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 possible causes of ORA-12514:

  1. No instance
  2. The database was not available, you can just startup the instance to solve ORA-12514.

  3. Mismatched service name
  4. The service name was mismatched between the listener and the connect descriptor in tnsnames.ora.

  5. No registered
  6. If the database instance is up and running, the service name may not registered with the listener at port 1521.

Please note that, ORA-12514 is a client side error. The database is no way to know such problem.

Solution

The solution to ORA-12514 is to make sure:

  1. The database is available
  2. If there’s no instance, then no service name will register with the listener. You should startup the instance and then check the service name registered in the listener.

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

  5. The host and port are both right
  6. Sometime, we may go for the wrong destination, either host or port. As a result, we saw ORA-12514 because there’s no matched service name with that listener.

  7. Service registration problems
  8. Chances are, the instance may register with the wrong listener which does not service port 1521. Consequently, no service name is supported by that listener.

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

When you shutdown the instance, service was unregistered from the listener, no more service information of the database on the listener. Therefore, you can’t do any further to this database by this way.

To make this kind of connection work properly, you can add a static service registration to the listener for your database.

Let’s see a safer way to connect to the database if you are going to do critical jobs like shutdown or startup database.

Connect to the database through OS authentication

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

Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size             956304664 bytes
Database Buffers          587202560 bytes
Redo Buffers                7544832 bytes
Database mounted.
Database opened.

Can you tell the difference? By this way, you can never lose your connection during down time as long as the listener is up. No ORA-12514 interrupts you.

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

Failed.
Warning: You are no longer connected to ORACLE.

This kind of error pattern is the most confusing ORA-12514, 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 which 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 is the action that you have to take to complete the switchover. Please start up and mount the former “primary” database. After that, the data guard will automatically synchronize the data so as to complete the switchover.

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

Leave a Reply

Your email address will not be published. Required fields are marked *