Status failure test failed listener refused the connection with the following error ora 12514 - Исправление ошибок и поиск оптимальных решений проблем

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor error occurs when the listener cannot find any matched service with the provided service name, preventing it from connecting to the database.The service name you used to connect to the database is either unavailable or incorrectly configured. Oracle Listener detects a mismatch between the service name you provided with connection descriptor configurations.The error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor is thrown when oracle fails to establish connection due to invalid service name provided in the configuration.

The Oracle service may be starting; please wait a moment before attempting to connect a second time. Check the listener’s existing information of services by running: lsnrctl services. Check that the SERVICE NAME parameter in the connect descriptor of the net service name used specifies a service that the listener is familiar with. If a connect identifier was used, make sure the service name specified is one the listener recognises. Look for any events in the listener.log file to resolve the error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.

The Problem

When you attempt to connect to a database, you will be given the host name, port, user name, password, and service name or Sid. When Oracle starts up, it will listen for connections on the same host name and port. The SID is the name of the Oracle instance that is currently running. The service name is an alias for the instance that allows you to connect to it. If there is a mismatch in the service name configuration, you will be unable to connect to the running Oracle database instance. The error message ORA-12514, TNS:listener does not currently know of service requested in connect descriptor will be displayed.

Host name : localhost
port      : 1521
service name : orcl
username  : hr
password  : hr

Error

Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
  (CONNECTION_ID=glCwDQzBSiScXNzmRhbuQg==)

Solution 1

The oracle server may be starting. wait for a moment and retry the connection. The network glitch may be occur. This will prevent to establish the connection to the oracle database. First make sure the database is running and no network issue with the database. If the host name is used to connect, try with IP address to connect with oracle database. Make sure the port configured is same and running port is the same. If you are using via application, the connection url should be configured as per the format.

jdbc:oracle:thin:@localhost:1521/servicename

Solution 2

Check the listen is running without any issue. If any issue in listen you can stop and start once. This will refresh the connection with the configured host and port. Listener might be hang due to multiple connections. Try connecting the listener once after restarting the listener. This will resolve the error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.

lsnrctl status

lsnrctl stop
lsnrctl start

Solution 3

Verify that the configured service name matches a valid service name in the Oracle database. The SQL query below will retrieve the configured service names from the Oracle database. If the provided service name in listener.ora differs from the database configuration, use the database’s service name. This will fix the error ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.

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

value
------
orclcdb

Solution 4

Verify the listener.ora file. The configuration should look like this. The Oracle instance configuration will be stored in the SID LIST LISTENER. In the database, this configuration will map GLOBAL DBNAME, SID NAME, and ORACLE HOME. The SID NAME should be the same as the service name from the previous query. The PROTOCAL, HOST, and PORT are defined by the LISTENER configuration. The LISTENER configuration uses this configuration to wait for the database connection to establish.

/u01/app/oracle/product/version/db_1/network/admin/listener.ora

OR

[ORACLE_HOME]/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orclcdb)
      (SID_NAME = orclcdb)
      (ORACLE_HOME = /u01/app/oracle/product/version/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orclcdb)

Solution 5

The tnsnames.ora file contains the service name configuration as well as the listener host and port. A sample tnsnames.ora file demonstrating service name configuration is provided below. In tnsnames.ora, check the service name. If the service name is not configured or if the configuration is incorrect, make the changes listed below.

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

OR

[ORACLE_HOME]/network/admin/tnsnames.ora

ORCLCDB=localhost:1521/orclcdb
ORCL=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Solution 6

Check your system environment configuration for the oracle database. The oracle database configuration will show the right database which is running. If multiple oracle database versions are installed in the server, it may conflict with the oracle database versions. Then environment setting will be in ~/.bash_profile, ~/.bashrc OR /etc/..bashrc.

export ORACLE_UNQNAME=orclcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/version/db_1
export ORACLE_SID=orclcdb
export PATH=/home/oracle/bin:/home/oracle/LDLIB:$ORACLE_HOME/bin:/usr/sbin:$PATH

Solution 7

Finally verify the configuration you provided while you try to connect with the database. If you misspelt the configuration, the error will be shown. Check the below configuration as per your oracle database configurations.

Host name : localhost
port      : 1521
service name : orcl
username  : hr
password  : hr

Источник

Содержание

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Problem
Error
Solution 1
Solution 2
Solution 3
Solution 4
/u01/app/oracle/product/version/db_1/network/admin/listener.ora
[ORACLE_HOME]/network/admin/listener.ora
Solution 5
/u01/app/oracle/product/version/db_1/network/admin/tnsnames.ora
[ORACLE_HOME]/network/admin/tnsnames.ora
Solution 6
Solution 7
ORA-12514 error in SQL Developer — post Oracle Database 18c XE install
Answers
12c — Connecting to PDB issue with listener
Answers

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

The Problem

Error

Solution 1

Solution 2

Solution 3

Solution 4

/u01/app/oracle/product/version/db_1/network/admin/listener.ora

[ORACLE_HOME]/network/admin/listener.ora

Solution 5

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

[ORACLE_HOME]/network/admin/tnsnames.ora

Solution 6

/.bashrc OR /etc/..bashrc.

Solution 7

Источник

ORA-12514 error in SQL Developer — post Oracle Database 18c XE install

Post installation of 18c XE when going to SQL Developer to crate a new connection I receive the following error message when I go to test the connection.

Status : Failure -Test failed: Listener refused the connection with the following error:

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

ConnectionParameters:

Role: Sysdba

Connection Type: Basic

Hostname: localhost

Port: 1521

Service Name: XEPDB1

The SQL Developer Welcome Page under databases detected also says:

No TNS entries found in

Results of lsnrctl status:

LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 — Production on 28-JAN-2020 14:41:50

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Scott-Dell-XPS8930)(PORT=1521)))

STATUS of the LISTENER

Version TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 — Production

Start Date 28-JAN-2020 14:10:05

Uptime 0 days 0 hr. 31 min. 45 sec

Trace Level off

Security ON: Local OS Authentication

Default Service XE

Listener Parameter File C:Oracle18c_XEdbhomeXEnetworkadminlistener.ora

Listener Log File C:Oracle18c_XEdiagtnslsnrScott-Dell-XPS8930listeneralertlog.xml

Listening Endpoints Summary.

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.

The command completed successfully

Contents of tnsnames.ora

# tnsnames.ora Network Configuration File: C:Oracle18c_XEdbhomeXENETWORKADMINtnsnames.ora

# Generated by Oracle configuration tools.

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

Additionally, when I go check services I only find two services running:

Most forum posts tend to list 5 to 6 services in total.

Tried to manually run the Database Configuration assistant in Advanced Configuration on Step 9 receive:

«[DBT-50000] Unable to check for available memory»

Ran the Database Configuration assistant with the following parameter:

Process stopped with errors of:

1) DIM-00019:create service error. O/S Error: (OS 5) Access denied

2) ORA-12560: TNS protocol adapter error

Message was edited by: f13f7338-8747-4642-9299-5fe44e31267a

Answers

The database instance

contacts the Listener (using parameter LOCAL_LISTENER)
and registers its services (using parameters SERVICE_NAMES and DISPATCHERS) with the Listener

This fails. Typical reasons are firewalled Listener endpoint, and incorrect LOCAL_LISTENER parameter for connecting to the Listener’s endpoint.

Check if your PDB is actually open:

You mean OracleServiceXE is not running?

Start it, check if the instance XE appears in your listener and then open your PDB as I have posted before.

If OracleServiceXE is completely missing, this is a failed installation and the memory error suggests the known named pipes issue (caused by Anti Virus or inactive netbios). You will need to at least get to the point where your OracleServiceXE is running.

1) Is ORACLE_HOME or TNS_ADMIN set in the environment? Go to a command prompt, type «set». You will need to temporarily unset any such environment variables.

2) Are you using Windows 10 Home Edition? It is not supported and the installer is not properly checking for it in the Prerequisite check.

3) Are you using Server 2019? Same issue.

4) On your network adapter that you are using, check (enable) «Client for Microsoft Networks» and «File and Printer Sharing for Microsoft Networks».

5) Check to make sure Netbios is enabled on your network connection:

See this link for more info:

6) In some cases, antivirus has interfered with an installation. Try installing with AV disabled.

7) Try creating a local user and adding to local administrator group and then log in and install as that user.

Источник

12c — Connecting to PDB issue with listener

Oracle version: 12.1.0.2.0

Windows: 7 Ultimate

All I’ve set up a CDB and a PDB and am not able to connect to the PDB with either SQL*Plus or SQL Developer.

I’m getting the below from SQL Developer:

Status : Failure -Test failed: Listener refused the connection with the following error:

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

And the below from SQL*Plus:

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 27 18:24:47 2015

ORA-01017: invalid username/password; logon denied

I’ve been googling all day but not able to get anywhere. I’ve included TNSNAME.ora, SQLNET.ora and LISTENER.ora below. Please help.

# tnsnames.ora Network Configuration File: C:OracleOracle12cproduct12.1.0dbhome_1NETWORKADMINtnsnames.ora

# Generated by Oracle configuration tools.

(ADDRESS = (PROTOCOL = TCP)(HOST = Gary-PC)(PORT = 1523))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

SQLNET.ORA (Without remarks)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

LISTENER.ORA (Tracing turned on):

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = Gary-PC)(PORT = 1523))

Answers

AS an aside, I am able to connect to the Container DB and then alter system set container=

ORA-12514 ALWAYS only occurs due to a problem on DB Server system.

One cause of this problem is when the Oracle database is down & needs to be started.

A remote client send a request to the Listener asking to be connected to a specific service.

If/when the listener does not know anything about that service, the listener responds with ORA-12514

post results from the following two OS commands:

Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

*This line contains valuable debugging details. So post this line & surrounding lines.*

for additional debugging suggestions read the URL below:

I have set up a listener (listenergarydb, on port 1523) as a listener specific for the DB. I can tnsping both container DB (GarysDB) and Pluggable (PDBGarys) and can connect to the container (GarysDB) via SQL*Plus and SQL Developer.

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 — Production on 27-JUN-2015 19:46:50

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

STATUS of the LISTENER

Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 — Production

Start Date 27-JUN-2015 12:50:42

Uptime 0 days 6 hr. 56 min. 9 sec

Trace Level admin

Security ON: Local OS Authentication

Listener Parameter File C:OracleOracle12cproduct12.1.0dbhome_1networkadminlistener.ora

Listener Log File C:OracleOracle12cproduct12.1.0dbhome_1logdiagtnslsnrGary-PClisteneralertlog.xml

Listener Trace File C:OracleOracle12cproduct12.1.0dbhome_1logdiagtnslsnrGary-PClistenertraceora_1488_1424.trc

Listening Endpoints Summary.

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.

The command completed successfully

C:UsersGary>lsnrctl status listenergarydb

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 — Production on 27-JUN-2015 19:47:07

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Gary-PC)(PORT=1523)))

STATUS of the LISTENER

Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 — Production

Start Date 27-JUN-2015 12:50:42

Uptime 0 days 6 hr. 56 min. 26 sec

Trace Level off

Security ON: Local OS Authentication

Listener Parameter File C:OracleOracle12cproduct12.1.0dbhome_1networkadminlistener.ora

Listener Log File C:OracleOracle12cproduct12.1.0dbhome_1logdiagtnslsnrGary-PClistenergarydbalertlog.xml

Listening Endpoints Summary.

Service «GarysDB» has 1 instance(s).

Instance «GarysDB», status UNKNOWN, has 1 handler(s) for this service.

The command completed successfully

C:UsersGary>lsnrctl status listener

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 — Production on 27-JUN-2015 19:47:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

STATUS of the LISTENER

Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 — Production

Start Date 27-JUN-2015 12:50:42

Uptime 0 days 6 hr. 56 min. 54 sec

Trace Level admin

Security ON: Local OS Authentication

Listener Parameter File C:OracleOracle12cproduct12.1.0dbhome_1networkadminlistener.ora

Listener Log File C:OracleOracle12cproduct12.1.0dbhome_1logdiagtnslsnrGary-PClisteneralertlog.xml

Listener Trace File C:OracleOracle12cproduct12.1.0dbhome_1logdiagtnslsnrGary-PClistenertraceora_1488_1424.trc

Listening Endpoints Summary.

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.

The command completed successfully

C:UsersGary>lsnrctl service listenergarydb

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 — Production on 27-JUN-2015 19:50:20

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Gary-PC)(PORT=1523)))

Service «GarysDB» has 1 instance(s).

Instance «GarysDB», status UNKNOWN, has 1 handler(s) for this service.

«DEDICATED» established:37 refused:0

The command completed successfully

C:UsersGary>lsnrctl service listener

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 — Production on 27-JUN-2015 19:52:08

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.

«DEDICATED» established:0 refused:0

The command completed successfully

I’ll make a few general comments.

Almost NEVER is more than 1 listener required or justified.

A single listener can listen on multiple ports; but almost NEVER is more than single port 1521 required or justified.

Having more than 1 listener or more than single port generally only adds confusion & complications; without any additional benefit.

Realize that listener is only involved to initially pass connection request from client to the database.

Listener has NO involvement with ongoing packet exchange between client & the database

No remote client can ever connect to any listener using LOCALHOST or 127.0.0.1

Listener will start and operate without error when no listener.ora file exists.

I strongly suggest that you move or remove the listener.ora file & then REBOOT and then report what works & what errors still exist

What does ‘set up’ mean to you?

If you can’t connect to a PDB it likely means the PDB is NOT open.

Again — that likely means the PDB is NOT open.

In 12c a STARTUP command does NOT open ANY PDBs at all. If you want PDBs open you either need to open them manually or create aa startup trigger that opens them.

See this Oracle-Base article for a sample trigger

Everything you have described so far suggests that you did a STARTUP but did NOT open any PDBs. So the listener won’t know of any PDBs to connect to. Post data showing the PDB you are trying to connect to is actually open.

Ok — that exception tells you what is wrong. You are using an invalid username or password. Use the proper credentials.

Источник

In previous articles I have explored various reasons why a client process might not be able to connect to an Oracle database. In Help! I can’t connect to my database … I presented an overview of the process. I followed that with articles going into more depth on the types of problems that can prevent the request from reaching the host server. Now I would like to look at problems that can arise once the request actually arrives at the listener, in particular “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.

For this demonstration I will generate the error then go through the standard analysis and solution. After that, I will explore some of the interesting factors that flow from this.

The Error – ORA-12514

Before we get into specifics of debugging ORA-12514, let’s create a test case and mention some general principles.

C:>sqlplus scott/tiger@tulsa

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 18 15:28:47 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

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


Enter user-name:

Fig. 1

Of course the very first thing anyone should do when confronted with an Oracle error is to check the official description. There are many sources on the web, but I like to start with ‘oerr’ utility on the server ..

[oracle@vmlnx01 admin]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The 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.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services
//  - 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.

Fig. 2

The error is pretty self-explanatory: “listener does not currently know of service requested in connect descriptor”. Of course, this could be because the client incorrectly specified the service name for the database, or because the listener really doesn’t know of a service that it should know.

The description returned by oerr also outlines a course of action to resolve the problem. By following this outline, we should come to know where the actual problem lies. Let’s fill in the details on each one in turn.

Wait and try again.

One of the features of Oracle database is the ability to register itself with the listener. This is referred to as “dynamic registration”. The listener does not have to be specifically configured to know about the database instance. The database will contact the listener every several seconds and register itself. The “wait and try again” solution is predicated on the assumption that we simply haven’t waited long enough for the database to register itself. Since the database instance performs dynamic registration every sixty seconds (if not more often), the “wait and try again” solution should only work if the error occurred immediately after database startup.

Check which services are known by the listener

Many people try to answer this question by looking at the listener configuration file, listener.ora. However, this is insufficient for one big reason: dynamic registration is not dependent on any listing in listener.ora. And as a result of that, for most cases it is not even necessary to have a listener.ora file at all.

The one reliable way to tell what instances and services the listener knows about is to query it directly, using the lsnrctl utility.

 
oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JAN-2016 15:03:12

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                18-JAN-2016 14:43:59
Uptime                    0 days 0 hr. 19 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle:tulsa$

Fig. 3

We see in figure 3, above, that the listener knows of two services, “tulsa”, and “tulsaXDB”, both associated with the instance “tulsa”. (The service “tulsaXDB” is for special use and is of no real concern for our present discussion.) Notice that the status is listed as “READY”. This is a definitive indication that the registration is dynamic. By contrast, look at the content of listener.ora:

 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Fig. 4

Notice in Fig. 4 that there is no mention of any service or SID at all, yet we know that the listener has registered service “tulsa” with SID “tulsa”.

At this point in your problem resolution, you must ask yourself if the listener has registered the database you are expecting. If not, you need to resolve that. If the expected database is not reported by the listener, then ORA-12514 is just an indication of a more fundamental problem, which I will discuss in more depth in a future article. In our current case, we want to connect to “tulsa”, and the listener knows about “tulsa”, so we should continue to the next possible problem.

Check the SERVICE_NAME parameter in the connect descriptor

At this point we know that the listener has a registration for “tulsa”. And didn’t we request a connection to “tulsa” in Fig. 1? Well, no, not really. We specified “tulsa” as the net service name, but that is not necessarily what gets passed in the connect descriptor.

The net service name in our connection request (see Fig. 1, line 1) is nothing more than an alias which gets resolved to the final connect descriptor. As described in my previous article Troubleshooting ORA-12154, there are several methods of resolving a net service name to a connect descriptor. In our case, we are relying on resolution via tnsnames.ora, so our request for “tulsa” was located in tnsnames, and information found there was used to construct the final connect descriptor.

Our tnsnames.ora entry for “tulsa” looks like this:

TULSA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = fubar)
    )
  )

Fig. 5

At Fig. 5, line 7, we see that the actual service name specified is “fubar”. Normally, one might expect the SERVICE_NAME specification to be the same as the alias, but this cannot be taken for granted and must be checked. If they do not match, you must ask yourself, “why?” There may be legitimate reasons, or it could be a simple typographical error, or it could be that the person who set it up really didn’t understand what they were doing. In the above case I deliberately made a mismatch for the express purpose of creating an error and making a point.

Of course, we could be looking at the wrong tnsnames.ora. Let’s run this to ground a bit more thoroughly.

First, let’s try a tnsping and see if the reported connect descriptor matches what we saw in our tnsnames.ora

C:>tnsping tulsa

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-JAN-2
016 15:57:51

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:apporacleproduct11.2.0client_1networkadminsqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vblnxsrv02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fubar)))
OK (30 msec)

C:>

Fig. 6

At line 14, we see the same bogus SERVICE_NAME = fubar, pretty much confirming that we were looking at the correct tnsnames.ora.

As one final nail in the coffin, we can look at the listener log on the database server. The plain text version of this log will be located at $ORACLE_BASE/diag/tnslsnr/<hostname>/listener/trace/listener.log. Looking at my log, I find the following entry:

18-JAN-2016 15:24:20 * (CONNECT_DATA=(SERVICE_NAME=fubar)(CID=(PROGRAM=C:apporacleproduct11.2.0client_1binsqlplus.exe)(HOST=STEVENS-NB-04)(USER=ed))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=64942)) * establish * fubar * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

Fig. 7

You may have to scroll the above display to the right, but you can clearly see in line 1 that the listener received a request to connect to “SERVICE_NAME=fubar”. The last item on the line logging the request is the return code, which in this case is “12514”. This line is followed immediately by a line explicitly reporting the 12514. At this level, it is reported as a TNS error instead of an ORA error but that distinction is merely a matter of where, within the call stack, the error is being reported. It is still the same error.

The fix

At this point it should be obvious that the root problem is an incorrect specification of SERVICE_NAME in our tnsnames.ora file. Let’s fix that:

TULSA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = tulsa)
    )
  )

Fig. 8

And prove our fix

C:>sqlplus scott/tiger@tulsa

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 18 16:22:34 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>

Fig. 9

Conclusion

In this article I have demonstrated the connection between what the client requests and what the listener expects. The next logical issue was mentioned in passing when we looked at the services registered with the listener. In my next article I will discuss the methods by which a database becomes registered with the listener.

Film at eleven …

Источник

В общем в одной подсети инстансы oracle с одинаковыми service name и портами жить могут.

в listener.ora добавляем информацию о новом подключении по IP и меняем порт

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2001))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2001))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 2001))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2001))
    )	
  )

В tnsnames.ora заносим информацию о новых экземплярах

SERVER01_DBORACLE_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)(PORT = 2001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dboracle)
    )
  )  
  
SERVER01_DBORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dboracle)
    )
  )    
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2001))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

LISTENER_DBORACLE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2001))

После перезапускаем службы листенера и самой БД.

Проверяем командой
lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-APR-2017 16:27:57

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=2001)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date                13-APR-2017 15:28:54
Uptime                    0 days 0 hr. 59 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:DataBasesOracleproduct12.2.0dbhome_1networkadminlistener.ora
Listener Log File         D:DataBasesOraclediagtnslsnrSERVER01listeneralertlog.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=2001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC2001ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.22)(PORT=2001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC2001ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=SERVER01)(PORT=5500))(Security=(my_wallet_directory=D:DATABASESORACLEadmindboraclexdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dboracle" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...
Service "dboracleXDB" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...

The command completed successfully

обязательно должна быть информация о

Service "dboracle" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...
Service "dboracleXDB" has 1 instance(s).
  Instance "dboracle", status READY, has 1 handler(s) for this service...

lsnrctl reload – переинициализировать листенер иногда помагает, когда инстанс не инициализовался листенером

Источник

This article is intended to help with troubleshooting SQL Developer connection
problems. It won’t tell you how to set up a connection because that is already well covered
online help (Start with the Getting Started topic and follow the link from there.)

The article is limited to the SQL Developer BASIC connection type. That is a simple
JDBC type 4 connection with no additional authentication or
security options, and no additional oracle client software.

What is a connection?

When you connect to an oracle instance using SQL Developer, you are
making a TCP/IP connection to a specific
host, on a specific port, and to a named instance
or service. These are the parameters you supply when creating a
basic connection in SQLDeveloper.

host
This can be a hostname or an IP address. If you use a hostname, including localhost, your workstation must be able to resolve it to an IP address.
port
This is the TCP port on the host which is listening for oracle connections. It is usually 1521, but not always.
SID
This the instance name of the database
service
This is the service name under which the database is available.
It is often the same as the SID, but may not be.

These details should be available from your DBA, or from when you created a database.

In the remainder of this article there is an assumption that you have
the correct details. There is always the possibility that you have the
wrong information or have mistyped something. If a test fails, re-check
your information before investigating other causes.

The database host and your workstation may be the same machine —
you may have SQL Developer installed on the database host,
or you may have an oracle database installed on your PC —
but you still need a TCP/IP connection. In this article I will use the
terms host and workstation to distinguish the two.

Connection Errors

This is not a comprehensive list of oracle errors, or even all errors relevant to SQL Developer connections.

See the Oracle Error messages reference manaual
for all error messages.

Client Side Errors
These are errors caused by issues on the workstation,
or somewhere between the workstation and the host. If you get either of these errors, follow the troubleshooting tips at Basic Network Connectivity Below
- Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection
- An error was encountered performing the requested operation:
  IO Error: The Network Adapter could not establish the connection
  
  Vendor code 17002
Host side errors
These are errors caused by issues on the host such as the
database service not running.
In each case you will need to contact the team responsible for the host.
- Status : Failure -Test failed: Listener refused the connection with the following error:
  ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.
  
  This means that the service you have specified is not running.
- Status : Failure -Test failed: Listener refused the connection with the following error:
  ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
  
  This means that the SID you have specified is not running.
- Status : Failure -Test failed: IO Error: Got minus one from a read call
  This is a non-specific error, but I have seen it when the remote database is shutting down.
- Status : Failure -Test failed: Listener refused the connection with the following error:
  ORA-12528, TNS:listener: all appropriate instances are blocking new connections
  
  This can mean that either the database is still starting up, or that the database is
  overloaded (too many connections.)
- Status : Failure -Test failed: ORA-01034: ORACLE not available
  ORA-27101: shared memory realm does not exist
  Linux Error: 2: No such file or directory
  
  This means that the database is down.
- Status : Failure -Test failed: ORA-01033: ORACLE initialization or shutdown in progress
  This means that the database is in the process of starting or stopping.
Other errors
These are not SQL Developer specific connection errors, but may mean you can’t connect.
- Status : Failure -Test failed: ORA-01017: invalid username/password; logon denied
  This is self-explanatory. Either your username or password is wrong.
- An error was encountered performing the requested operation:
  ORA-28000: the account is locked
  28000. 00000 — «the account is locked»
  *Cause: The user has entered wrong password consequently for maximum
  number of times specified by the user’s profile parameter
  FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
  *Action: Wait for PASSWORD_LOCK_TIME or contact DBA
  Vendor code 28000
  
  The account you are using is locked. You will need to contact your security administrator or DBA.

Basic Network Connectivity

These tests use a windows command window and windows tools. Tools on other platforms may produce different responses.

Can you ping the host?

Q:tests>ping mercure
Ping request could not find host mercure. Please check the name and try again.

This indicates that the host «mercure» isn’t known to your workstation. Typically this means that there is no DNS entry, or no HOSTS file entry.

Q:tests>ping hermes


Pinging hermes.ponder-stibbons.local [192.168.1.18] with 32 bytes of data:

Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 192.168.1.18:
    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

This indicates that the host «hermes» is known to your workstation, but the host itself is not accessible.
The host could be down, or there could be a DNS error (i.e. wrong IP address,) or your network configuration
means that your workstation cannot reach it.

Q:tests>ping mercury

Pinging mercury.ponder-stibbons.local [192.168.1.5] with 32 bytes of data:

Reply from 192.168.1.5: bytes=32 time<1ms TTL=64
Reply from 192.168.1.5: bytes=32 time=5ms TTL=64
Reply from 192.168.1.5: bytes=32 time<1ms TTL=64
Reply from 192.168.1.5: bytes=32 time<1ms TTL=64

Ping statistics for 192.168.1.5:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 5ms, Average = 1ms

This is a successful test, you can now proceed to the next stage.

Can you telnet to the host on the correct port?
```
Q:tests>telnet mercury 1522
Connecting To mercury...Could not open connection to the host, on port 1522: Connect failed
```
This could mean that the listener is not running, or that a firewall is blocking the port. The firewall could be
on the database host, on your workstation or anywhere in between.
```
Q:tests>telnet mercury 1521
```
If you get a blank screen here, you have successfully connected. Type ctrl-] to get the telnet command line and quit.
```
Welcome to Microsoft Telnet Client

Escape Character is 'CTRL+]'


Microsoft Telnet> quit

Q:tests>
```
If something other than an oracle listener is using port 1521 you may still get a blank screen, or you may see a welcome message.
This is unlikely if you have the correct host.

Can other oracle clients connect?

$ tnsping xe

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 19-OCT-2012 10:47:47

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:oracleproduct11.2.0dbhome_1networkadminsqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mercury.ponder-stibbons.local)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICAT
ED) (SERVICE_NAME = XE)))
OK (20 msec)

TODO:

Other connection types connections (TNS,LDAP, Advanced, Local/Bequeath)
Oracle client software interactions
diagnostic tools

Update History

Date	Amendment
19 Oct 2012	Initial version

Источник

ORA-12514 means that the listener cannot find any matched service with yours, so it cannot establish the connection with the database for you. As a result, it responds ORA-12514 to alert the failed connection.

As a DBA, I have seen several kinds of ORA-12514 in different scenarios and solved them by different means. Here I divide scenarios that could throw ORA-12514 into several error patterns as below:

Common Situations
Restarting a Database
Switching over to Standby
Using a Database Link

Only one thing that can be sure in ORA-12514 is that the target listener is up and running. That is to say, the listener is reachable.

For unknown requested SID, the listener throws ORA-12505: TNS:listener does not currently know of SID given in connect. It may have different error patterns from this post.

ORA-12514 in Common Situations

Let’s see how we get ORA-12514. First of all, we have to make sure the listener on the database server is reachable by using tnsping.

C:Usersed>tnsping ora11g


  TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:24:04
  Copyright (c) 1997, 2013, Oracle.  All rights reserved.
  Used parameter files:
 C:oracleappclientedproduct12.1.0client_1networkadminsqlnet.ora
   Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
 OK (60 msec)

OK, the remote listener is up and reachable. Please note that, the message of successful tnsping did not indicate that the service name is existing on the remote listener.

Next, we test the connection to the database by sqlplus (SQL*Plus).

C:Usersed>sqlplus hr/hr@ora11g ... ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


   Enter user-name:

Cause

When the connection came, the listener found no matched service name registered with it. Consequently, the listener had no idea which database should be used to service the connection. Here are some possible causes of ORA-12514:

1. No instance

The database was not available or say idle, no any dynamic services registered with the listener. That’s why we saw ORA-12514.

2. Mismatched service name

The service name of the connect descriptor in tnsnames.ora did not match the service name in the listener. That is to say, the listener does not currently know of service requested in connect descriptor.

3. Not registered

If the database instance is up and running, the service name may not registered with the listener at specific port as you thought. By default, the service port of listener is 1521. But sometimes, it could switch to another port, say 1522.

Please note that, ORA-12514 is an error threw by the listener specifically for the client side. The database is no way to know such problem.

Solutions

The solutions to ORA-12514 are to make sure the following things:

1. The database is available

If there’s no instance, then no service name will register with the listener. You should startup the instance, then LREG will automatically register the service in 60 seconds.

If you are sure that the database is up and running and still got ORA-12514, things may be complicated. Let’s keep looking for other solutions.

2. The service names are matched

The service name in the connect descriptor of client’s tnsnames.ora shall match the service registered in the listener. Sometimes, it’s just a typo problem.

In short, the service names in the following 3 parties should be matched with each other in order to solve ORA-12514.

Service Names Shall Match for Solving ORA-12514

Connect descriptor.
The listener.
The database.

We talk about them respectively below.

Connect Descriptor

Let’s see an example of connect identifier. In which, the body is a connect descriptor.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora ... ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) )

The question is, where is tnsnames.ora?

The Listener

The connect descriptor is easily checked, but how do we check the service names of the listener? See the following command:

[oracle@test ~]$ lsnrctl status ... Services Summary... Service "ORCL" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service...

For a specific listener name, you should do this:

[oracle@test ~]$ lsnrctl status <listener_name>

The Database

If there’s no matched service name in the listener, we should check the service names of the instance by OS authentication. No credentials are needed.

[oracle@test ~]$ sqlplus / as sysdba ... SQL> show parameter service_names


  NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 service_names                        string      ORCL

In case that you have no way to query the database, you can make a guess. Usually but not always, the service name is the same as the database unique name (DB_UNIQUE_NAME). The database unique name is the same as the instance name ($ORACLE_SID) for a single-instance database.

If both service names in the client and database are matched, but the listener showed a different service name or «The listener supports no services». The database may register a different listener, let’s keep reading this post.

3. The host and port are both right

Sometime, we may go for the wrong destination of the listener, either host or port was incorrect. As a result, we saw ORA-12514 because there’s no matched service name with that listener. We should check the host and port in the connect descriptor of the TNS name.

Chances are, the correct hostname may be resolved as the wrong IP address by DNS. Consequently, connections took us to the wrong database server. This could happen when we switched from an old machine to a new database server. So we should focus on name resolution problem first.

For those ORA-12514 caused by name resolution, we can use the IP address instead of hostname to connect the database for clients to work around it. Additionally, DBA should also check both values of HOST and PORT in listener.ora.

During the troubleshooting on the name resolution, you might see TNS-12545: Connect failed because target host or object does not exist temporarily.

4. Instance registers it services with the right listener

The instance may register with the another listener which services whatever port other than 1521. Consequently, no service name is supported by that listener.

That’s why we always see ORA-12514 only in clients or TNS-12514 in the listener’s log, the database instance will never know such problem.

Now we have a question: What listener is the instance using? If the database is using a different listener other than the default one, we can check a parameter named LOCAL_LISTENER for sure:

SQL> show parameter local_listener


  NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=o
                                                  racle-11g-server)(PORT=1522))

If the default listener at port 1521 in the same machine is used, the value could be empty. Otherwise, we will see the listener description like the above.

In a very rare case, the database uses a remote listener to register its service names. This is what Class of Secure Transports (COST) fights against.

For more troubleshooting beside ORA-12514, you may refer to Oracle 18c Net Services Administrator’s Guide: 15 Testing Connections.

ORA-12514 When Restart a Database

It’s pretty easy to explain why you got ORA-12514 when you restart a database. Please compare the following two cases.

Connect to the database through listener

[oracle@test ~]$ sqlplus /nolog ... SQL> conn sys@orcl as sysdba Enter password: Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

We saw ORA-12514 after we tried to startup the stopped database. Here is the reason: When you shutdown the instance, you lost the connection from the database and the service was unregistered from the listener, no more service information of the database on the listener. Therefore, ORA-12514 notified that you can’t do any further to this database in this way.

To solve ORA-12514 thoroughly, you should add a static service registration to the listener for your database. Otherwise, you have to connect to the database by OS authentication.

Let’s see how to avoid ORA-12514 if you are going to do critical jobs like shutdown or startup database through OS authentication.

Connect to the database through OS authentication

You don’t have to provide the password through OS authentication.

[oracle@test ~]$ sqlplus /nolog ... SQL> conn / as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started.


  Total System Global Area 1553305600 bytes
 Fixed Size                  2253544 bytes
 Variable Size             956304664 bytes
 Database Buffers          587202560 bytes
 Redo Buffers                7544832 bytes
 Database mounted.
 Database opened.

Can you tell the difference? By this method, you can never lose your connection during down time no matter the listener is up or not. No ORA-12514 interrupts you.

By the way, I talked about how to connecting an idle, NOMOUNT or RESTRICT database from remote clients in another post. It may help you to clarify some concepts about ORA-12514.

ORA-12514 When Switchover to Standby

DGMGRL> switchover to standby; Performing switchover NOW, please wait... Operation requires a connection to instance "primary" on database "standby" Connecting to instance "primary"... Connected. New primary database "standby" is opening... Operation requires startup of instance "primary" on database "primary" Starting instance "primary"... Unable to connect to database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


  Failed.
 Warning: You are no longer connected to ORACLE.

This kind of error pattern of ORA-12514 is the most confusing, because you might have no idea what’s going on according to the last message «Failed» in console. This is what’s going on:

The former standby is up and now playing the primary role.
The former primary is down and going to play the standby role, but the data guard broker is unable to mount the instance due to the lost contact. As a result, we saw ORA-12514 in DGMGRL.

That is to say, the switchover is incomplete, but it does not mean a failure. This is because the former primary database is down and the broker lost the contact that caused ORA-12514.

Here are the actions that you have to take in order to complete the switchover. Please start up and mount the former «primary» database manually. After that, the data guard will automatically synchronize the data so as to complete the switchover.

The preventive action to ORA-12514 in an incomplete switchover is to add a very special static service in listener.ora for data guard broker to use.

ORA-12514 When Using a Database Link

A database link is just like a client which is trying to connect the remote database.

SQL> conn hr/hr Connected. SQL> select first_name from employees@ora11g_hr; select first_name from employees@ora11g_hr * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

If the database link has been reliable for a long time, you should check the availability of the remote database. The causes that we have talked about in the section of ORA-12514 in Common Situations are still sustained. They may be:

The remote database is down. This is the most common cause of ORA-12514.
The connect descriptor in the local tnsnames.ora has been changed.
The service name of the remote database has been changed.
Another listener is used for servicing the remote database.

Usually, this type of ORA-12514 does not complain about the database link, except that you defined the database link with its own connect descriptor.

For example, we can create a database link with a full connect descriptor like this:

SQL> create database link ora11g_hr connect to hr identified by hr using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-11g-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ORCL)))';


  Database link created.

As you can see, we did not use a connect identifier, instead, we use a full connect descriptor. Therefore, if there’s anything wrong with the connect descriptor, we have to drop the database link then create a new one for solving ORA-12514.

Источник