This is a step-by-step troubleshooting guide for error:

ERROR:

ORA-12514 / TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

This error can occur when connecting directly to the database or when selecting across a database link (dblink). The troubleshooting steps outlined below are applicable to either situation.

As per Oracle documentation:

ORA-12514: 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.

Troubleshooting Tips

The error indicates that the SERVICE_NAME tried to access by the client is not found with the targeted listener.

A few Preliminary checks would be:
a. Check whether the hostname and port specified in the client are correct for the database service. If when the client tries to access the wrong listener for the database service can result in this error.
b. Check the database is up and running.
c. In the case of a dblink, consider the origin or source database as the client and the target database as the destination server.

Assuming the following is the client side TNSNAMES.ORA settings:

[Net_alias] =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname1.domain)(PORT = [port #]))
    )
    (CONNECT_DATA =
      (SERVER=SHARED)
      (SERVICE_NAME = (service_name1.DB_Domain)
    )
  )

Step 1: First do a tnsping on the TNS service name to confirm the availability of the intended listener:

D:>tnsping [Net_alias]

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 19-JUL-2007 08:29:05
Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:
D:oracleproduct10.2.0db_1NETWORKADMINsqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = hostname1.domain)(PORT = [port #])))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = service_name1.DB_Domain)))
OK (60 msec)

Step 2: If the previous step has failed, then go through the corresponding Trouble Shooting Guide.

After confirming the intended listener is reachable, now check the listener services output to confirm the database service is registered to it.

LSNRCTL> services listener
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "[service_name2.DB_Domain]" has 1 instance(s).
  Instance "[instance_name2]", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "[service_name1.DB_Domain]" has 1 instance(s).
  Instance "[instance_name1]", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

Step 3: In the listener services output, you should see the SERVICE_NAME value ‘‘ as a database service been registered to the listener. It is necessary that the value for SERVICE_NAME in the connect string matches the value for “Service” that is registered with the listener. If you don’t see the database service registered to the listener, confirm the database is running and available. Once the database service is registered with the intended listener, then you should be able to connect to the database from the remote machine.

Step 4: Log in to the database or instance that is not reachable through the listener and check the LOCAL_LISTENER setting to confirm it is correctly specified:

show parameter LOCAL_LISTENER;

If using a non-default port for the listener, it should be set to that listener address like:

LOCAL_LISTENER = '(ADDRESS=(PROTOCOL=TCP)(HOST=hostname1.domain)(PORT=[port #]))'

If it is NOT set, issue this statement:

alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=hostname1.domain])(PORT=[port #]))" scope=both sid='[instance_name]';