Ошибка 12514 oracle

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

answered Jun 16, 2020 at 8:18

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

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

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

answered Apr 1, 2013 at 0:21

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.

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.

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.

answered Apr 14, 2016 at 18:14

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.

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.

answered Oct 13, 2017 at 10:55

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

answered Mar 10, 2020 at 17:59

I had just to replace my connection string

from:

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

To:

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

answered Dec 3, 2016 at 7:45

answered Mar 20, 2019 at 3:35

answered Aug 23, 2019 at 13:47

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.

answered Nov 11, 2019 at 9:38

I have implemented below workaround to resolve this issue.

1. I have set the ORACLE_HOME using command prompt
   (right click cmd.exe and Run as System administrator).

2. Used below command

   set oracle_home="path to the oracle home"

3. Go to All programs —> Oracle -ora home1 —> Configuration migration tools —> Net Manager —> Listener

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

1. Click on File and save network configuration.

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.

answered Dec 5, 2019 at 12:07

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.

answered Jul 28, 2020 at 16:02

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

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.

answered Aug 11, 2021 at 13:49

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.

answered Apr 7 at 18:27

DPB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.100)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = dps)
    )
  )

I have used above in tnsnames but still gives me the above error.

How to resolve this error?
IS SERVICE_NAME & SID Similar in tnsnames?

Updated 10-Apr-18 20:04pm

Hello Member,

The error ORA-12514 means that a listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

The possible resolutions for this error are

• Check which services are currently known by the listener by executing:

  lsnrctl services <listener name>

• Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
• If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener
• Check for an event in the listener.log file

This answer[^] on oracle forums should help you understand the difference between the SID and the SERVICE_NAME.

Regards,

In order to solve the problem follow the following steps;

>close the sql developer or sql plus window.
>Open Run by pressing (WIndowsButton + R ).
>type in the text field ‘services.msc’ and run it.
>A new window appears
>In that Services window scroll down and find ‘OracleOraDb11g_homeTNSListner’,start it if it is not.
>and also start ‘OracleServiesORCL’,
>Open sql developer and try to establish the connection,If this does not connect Try to create one more connection in sql developer.
>If even this dosent help, try to establish a connection after sometime you will be able to establish a connection.

try to restart the listener with command prompt in your file bin in product in oracle:

lsnrctl stop listener
lsnrctl start listener

use below if you have SID instead service

berlin =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCP)
(PROTOCOL = TCP)
(HOST = hun)
(PORT = 1521)
)
)
(CONNECT_DATA = (SID = kraus))
)