ORA-12514 after a host rename, with tnsping succeeding
Two databases run on one host: SALES and HRDEV. listener.ora holds only the default LISTENER block on port 1521; both instances register dynamically. During a weekend window the server's hostname and IP address were changed. HRDEV was restarted during that window. SALES has been up for 41 days and was not restarted. LOCAL_LISTENER is not set on either instance. On Monday, tnsping sales_alias returns OK in 10 ms, but SQL*Plus returns ORA-12514: listener does not currently know of service requested in connect descriptor. lsnrctl status shows the listener running with services 'hrdev' and 'hrdevXDB' only. The SALES instance is OPEN and its alert log is clean.
1. Why does tnsping succeed while the actual connect fails?
Read the worked answer
tnsping does not connect to a database service. It opens a TCP connection to the address and port in the connect descriptor and performs a listener ping/version exchange. Success proves three things only: the alias resolves, the host is reachable, and a listener is listening on that port.
A database connection additionally requires that the listener has a registered service whose name matches the SERVICE_NAME in the descriptor, with a live handler. That mapping is what is missing: ORA-12514 means the listener received the request and found no service by that name.
So tnsping is not evidence against a registration problem - it is orthogonal to it. The discriminating tools are lsnrctl services (or lsnrctl status) and the instance-side registration view, not tnsping.
2. Give the root cause and the fix that avoids bouncing SALES.
Read the worked answer
Root cause: with no LOCAL_LISTENER, PMON registers to the default local address captured from the host at the time the instance started. The hostname/IP changed, but the SALES instance still holds the old address and its periodic registration refresh never reaches the listener on the new address. HRDEV works because it was restarted after the change and re-derived the address.
Fix without a restart:
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=newhost)(PORT=1521))' SCOPE=BOTH;
ALTER SYSTEM REGISTER;
ALTER SYSTEM REGISTER forces an immediate PMON refresh instead of waiting for the next interval. Verify with lsnrctl services and SELECT * FROM v$registration; (inst_id, service name, registered flag).
Setting LOCAL_LISTENER also fixes the next reboot, which matters - otherwise the problem returns silently on every future address change.
3. An on-call DBA proposes adding a static SID_LIST_LISTENER entry for SALES and restarting the listener. What are the consequences?
Read the worked answer
It works, but changes failure behaviour and costs information.
- Static registration requires the listener to be reloaded (
lsnrctl reload), which briefly drops the listener; existing connections survive, new ones may fail during the gap. - The entry must name the SID (
SID_NAME=SALES) and optionallyGLOBAL_DBNAME, which must match the client's SERVICE_NAME. Since SALES is a service name distinct from a SID here, a mismatch silently reproduces ORA-12514. - The listener then always advertises the service, including when the instance is down: clients get ORA-01034/ORA-27101 instead of the honest ORA-12514. Monitoring that keys on 12514 to mean 'database down' will break.
- Static services carry no dynamic service metrics: no load-balancing advice, no 'READY' handler state from PMON, no server-side connection pooling or DRCP info.
Static entries are the correct tool when the instance is NOMOUNT or MOUNT and cannot register - not for a running, open database that simply points at a stale address.