How do I fix Oracle ORA-12154 and ORA-12514 TNS connection errors?

tnsping PRODDB (full clients) shows how an alias resolves.

ORA-12514: the listener is up but doesn't know the service (step 3)

The client reached the listener, but no database registered the service name you asked for.

lsnrctl status      # or: lsnrctl services

Compare the listed services with what you're connecting to:

• SID vs. service name. …:1521/ORCL uses a service name. Older connect strings use SID=ORCL. JDBC thin syntax differs: @host:1521:ORCL is a SID, @//host:1521/service is a service name.

• Multitenant (CDB/PDB). Applications should connect to the PDB's service (orclpdb1), not the container's (ORCLCDB). A PDB that isn't open (ALTER PLUGGABLE DATABASE orclpdb1 OPEN; … SAVE STATE;) doesn't register its service.

• The instance didn't register with this listener. If the listener doesn't run on the default port, or there are several, set LOCAL_LISTENER in the database and force registration:

  ALTER SYSTEM SET local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1522))';
  ALTER SYSTEM REGISTER;
  

• Just after startup, registration can take up to a minute. It's temporary.

ORA-12541: nothing is listening at that host and port (step 2)

• The listener isn't running: lsnrctl start.
• It listens on a different port or interface. Check listener.ora and lsnrctl status (for example bound to localhost only, when you connect from another machine).
• The client resolved the wrong host or port (see 12154).

ORA-12170 / ORA-12535: timeout (network)

The TCP connection never completed: a firewall, security group, wrong IP or routing problem. Test plain connectivity first:

nc -vz db01.example.com 1521

In cloud setups, check security lists and NSGs on both sides. With Oracle RAC and SCAN listeners, clients may be redirected to node VIPs that the network also has to allow.

A connect-string sanity checklist

host:port/service_name        ← EZConnect, service name (preferred)
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=orclpdb1)))   ← full descriptor, no tnsnames needed

Autonomous Database and other TLS endpoints need a wallet (or TLS without a wallet, where supported) and PROTOCOL=TCPS on port 1522. Missing wallet configuration usually shows up as ORA-12154 or an SSL error, not an authentication error.

Checklist

1. 12154: the client can't resolve the alias. Check TNS_ADMIN and tnsnames.ora, or use EZConnect.
2. 12514: the listener lacks that service. Compare with lsnrctl services, use the PDB service name, open the PDB, check LOCAL_LISTENER.
3. 12541: no listener at that host and port. Start it or fix the address.
4. 12170: a network or firewall problem. Test with nc first.

What is New in Oracle’s Eighth Generation SPARC Systems

In this article, we will see what the newly launched Eighth Generation of Oracle SPARC platform has to offer.