oracle-databaseoracle11gsqlplusora-12514tnsping

Reputation: 7135

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

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?

Upvotes: 341

Answers (30)

Raphael Bravim

Reputation: 1

Sorry, but none of the aswners worked for me. After many tries I discovered the problem was FULL DISK, that's right.

try this:

df -h

then if you see anything 100%, try to clean to release space, in my case, the "listener.log" file was with 2.1G. Then I did:

sudo sh -c "echo -n > /var/log/dbs/oracle/diag/tnslsnr/<your_db>/listener/trace/listener.log"

Upvotes: 0

Ishildur Baggins

Reputation: 147

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

Upvotes: 10

Akhila Kota

Reputation: 11

I got the same error in spring boot at first i gave the url as spring.datasource.url=jdbc:oracle:thin:@localhost:1521:TEST_DB

but give the url as

spring.datasource.url=jdbc:oracle:thin:@localhost:1521/ORCL

this solved my problem

Upvotes: 1

Capricorn1

Reputation: 849

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 were 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.

Upvotes: 14

em_bo

Reputation: 692

This is a tricky Oracle error, as it can have lots of possible causes. It seems to mean that something in this chain of events is failing:

Look up the service name in tnsnames.ora
Connect to the host in the tnsnames entry
Reach the listener on that host
Tell the listener that you want to connect to the service specified in the tnsnames entry

Anything that throws that off can cause this error, from a missing bracket in tnsnames.ora, to a connecting to the wrong database host.

If tnsping works, that rules some things out, as it means that we can look up the entry in tnsnames, connect to the host, and contact the listener ... so we know, for instance, that the host at least exists, and there is an Oracle listener running there; but the listener is saying the service name is not known. That still leaves a lot of possibilities like:

service name in tnsnames.ora is just wrong
database instance is not running and/or not registered with the listener
we're connecting to the wrong Oracle host
we're connecting to the wrong listener (wrong port) on the correct Oracle host
firewall/networking issue (apparently it's possible that a firewall could allow tnsping packets through but block other client applications from connecting)

I've seen an ORA-12514 with tiny typos in tnsnames.ora ... like incorrect indenting. But I suspect tnsping wouldn't succeed if that is the issue.

When I encountered this most recently, it was due to the 3rd bullet-point above; we were connecting to an Oracle database host -- just not the right host; we had to ensure that the hostname in the tnsnames entry was for the Oracle database server that was hosting this particular database service.

Connecting to the Oracle host and running lsnrctl status can help check that the service is known to this listener. Remember that lsnrctl by default checks the default listener name. You may need to check listener.ora to see if there are other names listeners in this database instance.

Upvotes: 1

rahul Bhavar

Reputation: 21

I had just to replace my connection string

from:

jdbc:oracle:thin:@localhost:1521:xe

To:

jdbc:oracle:thin:@localhost:1521:orcl

Upvotes: 2

JanBrus

Reputation: 1520

For Dbeaver users: try selecting "SID" instead of "Service name" in connection settings.

Upvotes: 7

Rohit Gaikwad

Reputation: 3914

In my case for Linux environment, the oracle file at ORACLE_HOME/bin was highlighted in "Red" color with different permissions as below:

I changed the permissions of this file as below:

1) Stop Oracle -> sudo systemctl stop oracle.service
2) Change the permission of oracle file at ORACLE_HOME/bin directory as "sudo chmod 777 oracle"
3) Start Oracle -> sudo systemctl start oracle.service

Then after this change, I checked the status of listener using lsnrctl status.Here, I can see the db instances loaded successfully.

However, I can connect using sqldeveloper only, with sqlplus command line I'm getting ORA-12547: TNS Lost Contact error. So, this can a quick workaround to use sqldeveloper.

Note: Take a backup of oracle file before changing the permissions.

Upvotes: 0

x3hree

Reputation: 91

The problem can be in the incorrect URL.

For example, I'm using Oracle database (inside VM) with Spring framework and having this issue.

I had in my application.properties file:

spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/orcl12c

But the db version was defferent:

spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/orclcdb

The correct URL can be found in the tnsnames.ora file (this file would be available where the Oracle server, so if you using VM, you should look for this file inside your host VM). For example for Oracle in the VirtualBox the command to see this file is:

nano /u01/app/oracle/product/version/db_1/network/admin/tnsnames.ora

Upvotes: 0

Bronek

Reputation: 11245

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.

Upvotes: 0

surajmall

Reputation: 196

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 :)

Upvotes: 14

Hossein

Reputation: 26004

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.

Upvotes: 5

Mosab Sasi

Reputation: 1130

In my case, round brackets around the SERVICE_NAME was missing in the tnsnames.ora file.

<DBNAME> =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL=TCP)(HOST = nupark-cnvr-ora )(PORT=1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = <DBNAME> ***CLOSING ROUND BRACKET WAS MISSING HERE***
    )
  )

LISTENER_<DBNAME> =

  (ADDRESS = (PROTOCOL = TCP)(HOST = nupark-cnvr-ora)(PORT = 1521))

Upvotes: 1

Benas

Reputation: 2332

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:

    <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" />

My server version was "Oracle 11.2 Express", but solution should work on other versions too.

Upvotes: 0

user3778101

Reputation: 31

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.

Upvotes: 3

Raman B

Reputation: 339

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.

Upvotes: 0

user3132194

Reputation: 2567

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.

Upvotes: 0

Sir. Hedgehog

Reputation: 1300

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

Upvotes: 0

wishman

Reputation: 774

Restarting the VM worked for me

Upvotes: 0

Joseph Argenio

Reputation: 760

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))
    )
  )

Upvotes: 72

manix

Reputation: 14747

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!

Upvotes: 30

Laura Liparulo

Reputation: 2907

I got the same error because the remote SID specified was wrong:

 > 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.

Upvotes: 1

Elmue

Reputation: 8178

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.

Upvotes: 2

Steef

Reputation: 615

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

Upvotes: 0

isabelle martz

Reputation: 171

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.

Upvotes: 3

th3uiguy

Reputation: 1899

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.

Upvotes: 2

Sergio Figueiredo

Reputation: 2007

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.

Upvotes: 3

Pete Brumm

Reputation: 1696

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.

Upvotes: 1

Sepideh

Reputation: 149

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.

Upvotes: 14

Brad Rippe

Reputation: 3515

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 ;) *)

Upvotes: 282

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

Answers (30)

Related Questions