umzzil nng

Posted on Sep 14 Originally published at oraerror.com

Oracle ORA-12560 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12560: TNS Protocol Adapter Error — Causes, Fixes & Prevention

ORA-12560 is one of the most common Oracle errors that DBAs encounter, occurring when the Oracle client fails to initialize or communicate through the network protocol adapter. This error typically means the Oracle service is not running, environment variables are misconfigured, or the listener is down. Whether you're on Windows or Linux, resolving this quickly is critical to restoring database connectivity.

Top 3 Causes and Fixes

Cause 1: Oracle Service / Instance Is Not Running

The most frequent culprit. If the Oracle database instance is down, no client can connect.

Fix on Windows (CMD as Administrator):

-- Start the Oracle service
net start OracleServiceORCL

-- Verify service status
sc query OracleServiceORCL

Fix on Linux (as oracle user):

-- Connect locally and start the database
sqlplus / as sysdba

-- Check instance status
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

-- Start the database if it's down
STARTUP;

Cause 2: ORACLE_SID or ORACLE_HOME Environment Variables Are Missing or Wrong

Oracle uses ORACLE_SID and ORACLE_HOME to identify which instance to connect to. If these are unset or pointing to the wrong location, the protocol adapter cannot initialize properly.

Verify and confirm from inside the database:

-- Check current instance and host details
SELECT INSTANCE_NAME,
       HOST_NAME,
       VERSION,
       STATUS
FROM V$INSTANCE;

-- Check key parameters related to identity
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME IN ('db_name', 'instance_name', 'service_names', 'local_listener');

-- Confirm session environment context
SELECT SYS_CONTEXT('USERENV', 'DB_NAME')     AS DB_NAME,
       SYS_CONTEXT('USERENV', 'SERVER_HOST')  AS SERVER_HOST,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS')   AS CLIENT_IP
FROM DUAL;

On Linux, ensure your .bash_profile includes:

export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

Cause 3: Oracle Listener Is Down or Misconfigured

Remote connections always require an active listener. If lsnrctl status shows the listener is not running, or listener.ora has incorrect HOST/PORT values, ORA-12560 will appear.

Fix — restart listener and force service registration:

-- After starting the listener (lsnrctl start), force DB registration
ALTER SYSTEM REGISTER;

-- Verify registered services inside the DB
SELECT NAME, NETWORK_NAME, CREATION_DATE
FROM V$SERVICES
ORDER BY NAME;

-- Check diagnostic trace path for deeper investigation
SELECT VALUE
FROM V$DIAG_INFO
WHERE NAME = 'Diag Trace';

Run tnsping ORCL from the client to confirm the listener is reachable before attempting a full connection.

Quick Fix Checklist

Is the Oracle service running? → Start it via net start (Windows) or STARTUP in SQL*Plus (Linux).
Are environment variables set? → Check ORACLE_SID, ORACLE_HOME, and PATH.
Is the listener up? → Run lsnrctl start and then ALTER SYSTEM REGISTER;.
Is the firewall blocking port 1521? → Open port 1521 or your custom listener port.
Run tnsping to isolate whether the issue is network or instance-level.

Prevention Tips

1. Enable Automatic Startup
Configure Oracle services and the listener to start automatically after server reboots. On Linux, set the SID entry to Y in /etc/oratab and register dbstart/dbshut with systemd. On Windows, set the Oracle service startup type to Automatic.

2. Implement Health-Check Monitoring

-- Schedule this query via a cron job or monitoring tool (OEM, Zabbix, etc.)
SELECT INSTANCE_NAME,
       HOST_NAME,
       STATUS,
       DATABASE_STATUS,
       TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') AS STARTUP_TIME
FROM V$INSTANCE;

Set up automated alerts if this query fails to return OPEN status, so you can act before users are impacted.

Related Errors

ORA-12541 — TNS: no listener (listener not started)
ORA-12154 — TNS: could not resolve connect identifier (bad tnsnames.ora)
ORA-01034 — ORACLE not available (instance not open)
ORA-12170 — TNS: connect timeout (network/firewall issue)

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community