First of all, make sure the database is started. If it is not, you’ll get this message (In 10g release 2).
C:>sc query OracleServiceXE SERVICE_NAME: OracleServiceXE TYPE : 10 WIN32_OWN_PROCESS STATE : 1 STOPPED (NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN) WIN32_EXIT_CODE : 1077 (0x435) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0 C:>sqlplus myuser@xe SQL*Plus: Release 10.2.0.1.0 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
If the database is running, probably the database hasn’t registered yet with the listener.
This occurs when the database or listener just starts up.
Normally this problem should be solved by waiting a minute or so.
If you are using dynamic registration (using the local_listener database parameter), you can try to register the database service with the listener using the following command (from Oracle 9i onwards):
show parameter local listener -- if the above is empty alter system set local_listener='(address=(protocol=tcp)(host=localhost)(port=1521))'; alter system register;
If this does not work or you’re using static registration, make sure the SERVICE_NAME entry used in the connection string (TNSNAMES.ORA, NAMES, OID, …) matches a valid service know by the listener.
C:>tnsping ora920 TNS Ping Utility for 32-bit Windows: Version 184.108.40.206.0 - Production Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: c:\oracle\ora920\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DEV01)(PORT = 2491))) (CONNECT_DATA = (SERVICE_NAME = UNKNOWN) (SERVER = DEDICATED))) OK (20 msec)
As one can see, this is the connection information stored in a tnsnames.ora file:
ORA920.EU.DBMOTIVE.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DEV01)(PORT = 2491)) ) (CONNECT_DATA = (SERVICE_NAME = UNKNOWN) (SERVER = DEDICATED) ) )
However, the SERVICE_NAME UNKNOWN is not known by the listener at the database server side.
In order to test the known services by a listener, we can issue following command at the database server side:
C:>lsnrctl services LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production Copyright (c) 1991, 2004, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DEV01)(PORT=1521))) Services Summary... Service "ORA10G.eu.dbmotive.com" has 1 instance(s). Instance "ORA10G", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "ORA920.eu.dbmotive.com" has 2 instance(s). Instance "ORA920", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Instance "ORA920", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:2 refused:0 state:ready LOCAL SERVER The command completed successfully
Know services are ORA10G and ORA920.
Changing the SERVICE_NAME in our tnsnames.ora to a known service by the listener (ORA920.EU.DBMOTIVE.COM) solved the problem.