Error map
| Error | Meaning | Look at |
|---|---|---|
| ORA-12541: TNS:no listener | Nothing is listening on that host:port | Listener down, wrong port/host, firewall |
| ORA-12514: listener does not currently know of service requested | Listener up, service not registered | Database down, service name wrong, registration failed |
| ORA-12505: listener does not currently know of SID | Client uses SID, listener doesn't know it | Use service name, or static registration |
| ORA-12170 / ORA-12535: connect timeout | Packets dropped | Firewall, routing, security groups |
| ORA-12528: all appropriate instances are blocking new connections | Instance in restricted / mounting / shutting down | Instance state |
Step 1: on the database server
lsnrctl status # is it running, which endpoints, which services
lsnrctl services # handlers and service states
ps -ef | grep tnslsnr
Start it if needed: lsnrctl start (or srvctl start listener with Grid Infrastructure).
Check what address it's bound to:
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01.example.com)(PORT=1521)))
If it only listens on localhost or the wrong IP, fix listener.ora (HOST should be the server's hostname or 0.0.0.0 if policy allows) and restart.
Step 2: is the service registered?
-- as SYSDBA
SELECT name FROM v$services;
SELECT name, value FROM v$parameter WHERE name IN ('service_names', 'local_listener', 'remote_listener');
Force registration:
ALTER SYSTEM REGISTER;
If lsnrctl services still doesn't show it, the database is registering with the wrong listener address. Set LOCAL_LISTENER:
ALTER SYSTEM SET local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=db01.example.com)(PORT=1521))' SCOPE=BOTH;
ALTER SYSTEM REGISTER;
Common causes: a non-default port (registration defaults to 1521), a hostname change, or listener started after the database (registration happens within about a minute, or immediately with ALTER SYSTEM REGISTER).
Step 3: PDB services
In a CDB, applications connect to the PDB's service. If the PDB is closed, its service isn't registered:
SELECT name, open_mode FROM v$pdbs;
ALTER PLUGGABLE DATABASE salespdb OPEN;
ALTER PLUGGABLE DATABASE salespdb SAVE STATE; -- opens automatically after restart
Step 4: from the client
tnsping PRODSVC
sqlplus app_user@//db01.example.com:1521/salespdb.example.com
Using EZConnect (//host:port/service) removes tnsnames.ora from the problem. If EZConnect works but the alias doesn't, the client's tnsnames.ora (or TNS_ADMIN path) is wrong.
Network checks:
nc -zv db01.example.com 1521 # or: Test-NetConnection db01 -Port 1521 on Windows
RAC / SCAN notes
srvctl status scan_listener
srvctl status service -db PROD
srvctl start service -db PROD -service salessvc
ORA-12514 on RAC is often a service that isn't started on any instance (after failover or with -preferred instances down).
Fix table
| Finding | Fix |
|---|---|
| Listener not running | lsnrctl start; enable at boot (systemd unit or GI) |
| Listening on wrong address | Correct listener.ora, restart |
| Service missing | Start DB/PDB, ALTER SYSTEM REGISTER, set LOCAL_LISTENER |
| Client uses SID | Switch to service name; or add static SID_LIST_LISTENER entry |
| Timeout | Firewall/NSG/security group for 1521 (and redirect ports on Windows/shared servers) |
Tip
Keep a one-line EZConnect test command per database in the runbook. It's the fastest way to separate "database problem" from "client configuration problem".
Prevent it next time
- Listener and PDB
SAVE STATEconfigured to start automatically. - Monitoring that connects through the listener with the application's service name, not just checks the process.
LOCAL_LISTENERset explicitly on non-default ports.