The database unique name is the same as the instance name ( $ORACLE_SID) for a single-instance database. Usually but not always, the service name is the same as the database unique name ( DB_UNIQUE_NAME). In case that you have no way to query the database, you can make a guess. If there's no matched service name in the listener, we should check the service names of the instance by OS authentication. Instance "ORCL", status READY, has 1 handler(s) for this service.įor a specific listener name, you should do this: ~]$ lsnrctl status The Database The connect descriptor is easily checked, but how do we check the service names of the listener? See the following command: ~]$ lsnrctl status
The question is: where is tnsnames.ora in Windows platform? It is usually at TNS_ADMIN, an environment variable contains the location to the network configuration files in Windows platforms. (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521)) ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora In which, the body is a connect descriptor. Let's see an example of connect identifier. Service Names Shall Match for Solving ORA-12514 In short, the service names in the following 3 parties should be matched with each other in order to solve ORA-12514. The service name in the connect descriptor of client's tnsnames.ora shall match the service registered in the listener. If you are sure that the database is up and running and still got ORA-12514, things may be complicated. You should startup the instance, then LREG will register the service in 60 seconds. If there's no instance, then no service name will register with the listener. The solutions to ORA-12514 are to make sure the following things: 1. The database is no way to know such problem. Please note that, ORA-12514 is an error threw by the listener specifically for the client side.
But sometimes, it could switch to another port, say 1522. By default, the service port of listener is 1521. If the database instance is up and running, the service name may not registered with the listener at specific port as you thought. That is to say, the listener does not currently know of service requested in connect descriptor. The service name of the connect descriptor in tnsnames.ora did not match the service name in the listener. The database was not available or say idle, no any dynamic services registered with the listener. Here are some possible causes of ORA-12514: 1. Consequently, the listener had no idea which database should be used to service the connection. When the connection came, the listener found no matched service name registered with it. C:\Users\ed>sqlplus TNS:listener does not currently know of service requested in connect Next, we test the connection to the database by sqlplus ( SQL*Plus). Please note that, the message of successful tnsping did not indicate that the service name is existing on the remote listener.
OK, the remote listener is up and reachable. Used TNSNAMES adapter to resolve the aliasĪttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) All rights reserved.Ĭ:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora First of all, we have to make sure the listener on the database server is reachable by using tnsping. It may have different error patterns from this post. That is to say, the listener is reachable.įor unknown requested SID, the listener throws ORA-12505: TNS:listener does not currently know of SID given in connect. Only one thing that can be sure in ORA-12514 is that the target listener is up and running. Here I divide scenarios that could throw ORA-12514 into several error patterns as below: As a result, it responds ORA-12514 to alert the failed connection.Īs a DBA, I have seen several kinds of ORA-12514 in different scenarios and solved them by different means. ORA-12514 means that the listener cannot find any matched service with yours, so it cannot establish the connection with the database for you.