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

Question

We have an application running locally where we're experiencing the following error:

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

I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try connecting, which failed with the same error as above. I used this syntax for SQLPlus:

sqlplus username/password@addressname[or host name]

We have verified that:

• the TNS Listener on the server is running.
• Oracle itself on the server is running.

We don't know of any changes that were made to this environment. Anything else we can test?

Solution 1

I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle:

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

Once I updated tnsnames.ora to:

TEST =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = *<servicenamefromDB>*)
    )
)

then I ran:

sqlplus user@TEST

Success! The listener is basically telling you that whatever service_name you are using isn't a valid service according to the DB.

(*I was running sqlplus from Win7 client workstation to remote DB and blame the DBAs ;) *)

Solution 2

I know this is an old question, but still unanswered. It took me a day of research, but I found the simplest solution, at least in my case (Oracle 11.2 on Windows 2008 R2) and wanted to share.

The error, if looked at directly, indicates that the listener does not recognize the service name. But where does it keep service names? In %ORACLE_HOME%\NETWORK\ADMIN\listener.ora

The "SID_LIST" is just that, a list of SIDs and service names paired up in a format you can copy or lookup.

I added the problem Service Name, then in Windows "Services" control panel, I did a "Restart" on the Oracle listener service. Now all is well.

---

For example, your listener.ora file might initially look like:

# listener.ora Network Configuration File: C:\app\oracle_user\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

... And to make it recognize a service name of orcl, you might change it to:

# listener.ora Network Configuration File: C:\app\oracle_user\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle_user\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC = 
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = C:\app\oracle_user\product\12.1.0\dbhome_1)
        (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Solution 3

In my circumstances the error was due to the fact the listener did not have the db's service registered. I solved this by registering the services. Example:

My descriptor in tnsnames.ora:

LOCALDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LOCALDB)
    )
  )

So, I proceed to register the service in the listener.ora manually:

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = LOCALDB)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (SID_NAME = LOCALDB)
    )

Finally, restart the listener by command:

> lsnrctl stop
> lsnrctl start

Done!

Solution 4

I had this issue at Windows server 2008 R2 and Oracle 11g

go to Net Manager > Listener > select database services form the combox > "Global Database Name" must be same as "SID" and "Oracle Home Directory" must be correct.

If you don't have any entry for database services, create one and set correct global database , sid and oracle home.

Solution 5

Starting the OracleServiceXXX from the services.msc worked for me in Windows.

View more solutions

Comments

[Grzegorz W]

what is TNSPing command (with params) that you used?

[David Aldridge]

when you say "running locally" you mean that the application is connecting to a database on the same host? Also, what are the contents of your sqlnet.ora file? what versions are reported for sqlplus and tnsping, and are you sure that they're in the same ORACLE_HOME?

[Jens Schauder]

try restarting the database. Since they supposed to inform the Listener about their existence on startup this might fix your problem.

[DCookie]

ALTER SYSTEM REGISTER is less drastic than restarting the database.

[ravibeli]

After researching enough found the right solution here shekhargulati.com/2019/01/22/…

[JosephDoggie]

It is possibly as simple as a typo in the host name or other connection properties (that is what happened to me). Of course, other comments and answers are valid and detail more complex situations.

[Hossein]

Thanks alot, you solved my problem ;) But where is the tnsnames.ora located?

[Brad Rippe]

On win7, %ORACLE_HOME%\NETWORK\ADMIN\tnsnames.ora

[Sh4d0wsPlyr]

This was exactly my problem. Our database is hosted on a VM which was down while I was attempting to use another program that used the DP. After seeing this thread I realized it was probably down.

[isabelle martz]

How am I supposed to query the DB if I'm not able to even connect to it??

[Brad Rippe]

Can you ssh to the DB server directly and run sqlplus from there?

[dan]

We had a similar issue with our connection string causing this error. We were connecting using Oracle's JDBC thin driver with the connection string: jdbc:oracle:thin:@//localhost:1521/orcl. The corrected connection string to eliminate this error was: jdbc:oracle:thin:@localhost:1521.

[Ostap]

this is old, but it doesn't make much sense to add your IP to /etc/hosts. OP is missing SERVICE_NAME, other stuff is quite unrelated

[Ivan Chau]

In my case, though Startup Type is set to Automatic, it does not start when machine is boot. After manually start service "OracleServiceXE", it works for Oracle Database 11g Express to connect to DB and APEX(Oracle Application Express) webpage.

[vapcguy]

Need to update this answer with the syntax of how listener.ora stores the names. I don't even have a listener.ora file. I'm also on a client workstation running SQL Developer and trying to just create a Database Link when I get the error. I don't have an Oracle Listener service to restart.

[Joseph Argenio]

vapcguy, you need to be on the database server. it sounds like you are on the client

[robot_alien]

Thanks a lot, from your solution I found the trick, in my case, it was the anti-virus that was blocking the program, hence was not able to obtain a connection to the Oracle db.

[Sergio Marcelo C Figueiredo]

I have not added my IP to /etc/host. I just corrected it. As you can see in this topic, there are many valid (with upvote) answers and varieties to solve this question. If that answer did not help you, why downvote? This answer may still help someone as it helped me.

[hello_earth]

it depends whether this would work - i think this way of connecting, judging on what others said, is bypassing the listener entirely, using the computer host name instead of SID - this way of connecting causes problems in third-party clients, - i think it also works only when the EZCONNECT is specified in sqlnet.ora: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

[hello_earth]

this might not be the case when multiple clients/servers are, or used to be, installed on the same machine. (tnsping outputs the location of the directory it uses) - in my case, listener.ora in that directory contained information pertaining to an old database instance that i had uninstalled - quick and dirty way was to copy the entire contents of listener.ora from my current installation of Oracle Express, to that other directory that listener seems to be checking (i think i changed it through a registry or something, and it has precedence over ORACLE_HOME (?))

[street hawk]

In windows, if you are using Oracle Release 12.x, then make sure the service OracleServiceORCL is running. If this service is not started, then also you will receive the same error code.

[Morgeth888]

ORA-00942: table or view does not exist

[Ganesh Jadhav]

@isabellemartz stackoverflow.com/questions/10786782/…

[jpacareu]

here worked for me after reviewing every single answer

[Kevin]

After following Sepideh's instructions involving the Net Manager, I noticed that my listeners.ora file had been updated to contain a new SID_LIST entry. I have edited this answer to include a before-and-after example of the syntax, for the benefit of readers that can't use the Net Manager, for whatever reason.

[Jose Manuel Gomez Alvarez]

I am sorry to agree with hello_earth, the rightest answer is listener.ora, although the answer is still useful.

[CodeSlave]

How can I find this out?

[Nicolas]

This was wrong on my system: "Global Database Name" must be same as "SID" - this isn't intuitive at all, if these values have to be identical, why are these two separate parameters?? -.-

[ravibeli]

I found right solution here shekhargulati.com/2019/01/22/…

[Jamie]

I made it here, but there was a final block that stated error ORA-01219 (pluggable database not open) that kept me from opening the tables. They opened when logging into the EE database with the sys/pwd@ipaddress:port. Untikl then SQL Developer would not allow the tables to open until that account ran "startup;" for that instance.

[Klaws]

For me, turned out that SID= instead of Service_Name= in the connect string did the job. Despite having some documentation which tells me to use Service_Name= to specify the SID...well, whatever. Thank you!