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

User · Answer

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

User · Answer

Lots of answers here  but here comes a working example with code that you can copy and paste and test immediately   For me the error 12514 was solved after specifying the correct SERVICE NAME  You find that on the server in the file tnsnames ora which comes with 3 predefined service names  one of them is  XE      I installed the Oracle Express database OracleXE112 which already comes with some preinstalled demo tables   When you start the installer you are asked for a password  I entered  xxx  as password   not used in production  My server runs on the machine 192 168 1 158 On the server you must explicitely allow access for the process TNSLSNR exe in the Windows Firewall  This process listens on port 1521  OPTION A  For C    NET2 or  NET4  you can download ODAC11  from which you have to add Oracle DataAccess dll to your project  Additionally this DLL depends on  OraOps11w dll  oci dll  oraociei11 dll  130MB    msvcr80 dll   These DLLs must be in the same directory as the EXE or you must specify the DLL path in  HKEY LOCAL MACHINE SOFTWARE Oracle ODP NET 4 112 4 0 DllPath  On 64 bit machines write additionally to HKLM SOFTWARE Wow6432Node Oracle     OPTION B  If you have downloaded ODAC12 you need Oracle DataAccess dll  OraOps12w dll  oci dll  oraociei12 dll  160MB    oraons dll  msvcr100 dll  The Registry path is HKEY LOCAL MACHINE SOFTWARE Oracle ODP NET 4 121 2 0 DllPath OPTION C  If you don t want huge DLL s of more than 100 MB you should download ODP NET Managed12 x x x xxxxx zip in which you find Oracle ManagedDataAccess dll which is only 4 MB and is a pure managed DLL which works in 32 bit and 64 bit processes as well and depends on no other DLL and does not require any registry entries  The following C  code works for me without any configuration on the server side  just the default installation      using Oracle DataAccess Client  or using Oracle ManagedDataAccess Client         string oradb    Data Source  DESCRIPTION           ADDRESS LIST  ADDRESS  PROTOCOL TCP  HOST 192 168 1 158  PORT 1521             CONNECT DATA  SERVER DEDICATED  SERVICE NAME XE             User Id SYSTEM Password xxx     using  OracleConnection conn   new OracleConnection oradb          conn Open        using  OracleCommand cmd   new OracleCommand                  cmd Connection    conn          cmd CommandText    select TABLESPACE NAME from DBA DATA FILES            using  OracleDataReader dr   cmd ExecuteReader                          while  dr Read                                  listBox Items Add dr  TABLESPACE NAME                                      If the SERVICE NAME XE is wrong you get error 12514  The SERVICE NAME is optional  You can also leave it away

User · Answer

In my case  round brackets around the SERVICE NAME was missing in the tnsnames ora file    lt DBNAME gt       DESCRIPTION        ADDRESS LIST         ADDRESS    PROTOCOL TCP  HOST   nupark-cnvr-ora   PORT 1521              CONNECT DATA        SERVER   DEDICATED       SERVICE NAME    lt DBNAME gt     CLOSING ROUND BRACKET WAS MISSING HERE               LISTENER  lt DBNAME gt        ADDRESS    PROTOCOL   TCP  HOST   nupark-cnvr-ora  PORT   1521

User · Answer

I had also faced the same problem and spent 3 days to dig it out    This happens because of your wrong TNS service entry    First check  whether you are able to connect to standby database from  primary database using sql   sqlplus  sys orastand as sysdba  orastand is a standby database     If you are not able to connect then it is a problem with the service  Correct the entry of  service name in TNS file at primary end    Check standby database the same way  Make the changes here too if required    Make sure the log archive dest 2 parameter has the correct service name

User · Answer

tnslsnr is up but database is down   For oracle novice it is not obvious that database may be down while connections are accepted   I had to start up database manually like that  su - oracle export ORACLE SID XE sqlplus sys as sysdba   And then in sql console  startup   In my case i failed to startup but got another error message and found the source of a problem - i had to change host name and then database auto startup was functional again

User · Answer

Check to see the database is up  Log onto the server  set the ORACLE SID environment variable to your database SID  and run SQL Plus as a local connection

User · Answer

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    gt  lsnrctl stop  gt  lsnrctl start   Done

User · Answer

For thoses Who are using spring-boot and jdbc for connection  You have to be careful while writing jdbcUrl in application properties   With SID in Database connection -             source datasource jdbcUrl   jdbc oracle thin   HOST   PORT  SID  With Service name in db connection            globe datasource jdbcUrl   jdbc oracle thin     HOST   PORT  SERVICE  This worked for me

User · Answer

For those that may be running Oracle in a VM  like me  I saw this issue because my VM was running out of memory  which seems to have prevented OracleDB from starting up running correctly  Increasing my VM memory and restarting fixed the issue

User · Answer

I had the same problem  For me  just writing   sqlplus myusername mypassword localhost   did the trick  doing so makes it connect to the default service name  I guess

User · Answer

I got the same error because the remote SID specified was wrong     gt  sqlplus  DATASOURCE USERNAME  DATASOURCE PASSWORD  DB SERVER URL  REMOTE SID    I queried the system database   select   from global name   and found my remote SID   XE     Then I could connect without any problem

User · Answer

This really should be a comment to Brad Rippe s answer  but alas  not enough rep  That answer got me 90  of the way there  In my case  the installation and configuration of the databases put entries in the tnsnames ora file for the databases I was running  First  I was able to connect to the database by setting the environment variables  Windows    set ORACLE SID mydatabase set ORACLE HOME C  Oracle product 11 2 0 dbhome 1   and then connecting using  sqlplus   as sysdba   Next  running the command from Brad Rippe s answer   select value from v parameter where name  service names     showed that the names didn t match exactly  The entries as created using Oracle s Database Configuration Assistant where originally   MYDATABASE      DESCRIPTION        ADDRESS    PROTOCOL   TCP  HOST   mylaptop mydomain com  PORT   1521        CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   mydatabase mydomain com               The service name from the query was just mydatabase rather than mydatabase mydomain com  I edited the tnsnames ora file to just the base name without the domain portion so they looked like this   MYDATABASE      DESCRIPTION        ADDRESS    PROTOCOL   TCP  HOST   mylaptop mydomain com  PORT   1521        CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   mydatabase               I restarted the TNS Listener service  I often use lsnrctl stop and lsnrctl start from an administrator command window  or Windows Powershell  instead of the Services control panel  but both work   After that  I was able to connect

User · Answer

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     lt validhost gt    PORT     lt validport gt                CONNECT DATA          SERVER   DEDICATED         SERVICE NAME     lt servicenamefromDB gt              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

User · Answer

The problem was that my connection string url contained database name instead of SID  Replacing database name with oracle database connection SID solved this problem   To know your oracle SID s you can browse tnsnames ora file   XE was the actual SID  so this is how my tomcat connection string looks like now        lt Resource        name  jdbc my db conn         auth  Container         type  javax sql DataSource         driverClassName  oracle jdbc driver OracleDriver         url  jdbc oracle thin    127 0 0 1 1521 XE         username  test user         password  test    gt    My server version was  Oracle 11 2 Express   but solution should work on other versions too

User · Answer

I resolved this issue in my linux enviroment updating the IP of my machine in  etc hosts file   You can verify your network IP  inet end   with    ifconfig   See if your IP matches with  etc hosts file    cat  etc hosts   Edit your  etc hosts file  if nedded    sudo gedit  etc hosts   Bye

User · Answer

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

User · Answer

Restarting the VM worked for me

User · Answer

In my case the database had ran out of disk space    Which caused it to not respond    Once I cleared up that issue everything worked again

User · Answer

For me this was caused by using a dynamic ipadress using installation  I reinstalled Oracle using a static ipadress and then everything was fine

User · Answer

My issue was resolved by replacing the SID  in URL with  service name  and correct host

User · Answer

I had a case that I used DBMS where I had to fulfill a db connection form  I put SID into the Database field and in the dropdown  next to the field  I had had  Service Name  value instead of  SID  value   normally I don t use Oracle database so I ve not been aware of the difference  That was the reason I got the error message

User · Answer

what worked for me was really simple  I just needed to initiate the service manually in the  Windows Services   services msc in cmd trompt   my service name is  OracleServiceXXXXX

User · Answer

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

User · Answer

This error can occur when an application makes a new connection for every database interaction or the connections are not closed properly  One of the free tools to monitor and confirm this is  Oracle Sql developer  although this is not the only tool you can use to monitor DB sessions    you can download the tool from oracle site Sql Developer  here is a screenshot of how to monitor you sessions   if you see many sessions piling up for your application user during when you see the ORA-12514 error then it s a good indication that you may have connection pool problem

User · Answer

I have implemented below workaround to resolve this issue    I have set the ORACLE HOME using command prompt  right click cmd exe and Run as System administrator   Used below command  set oracle home  path to the oracle home  Go to All programs --  Oracle -ora home1 --  Configuration migration tools --  Net Manager --  Listener Select Database Services from dropdown   Both Global database name and SID are set to the same  ORCL in my case   Set Oracle Home Directory    Oracle Net Manager window example from oracle documentation     Click on File and save network configuration

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

Examples related to oracle

Examples related to oracle11g

Examples related to sqlplus

Examples related to ora-12514

Examples related to tnsping