DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12505 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12505: TNS Listener Does Not Currently Know of SID Given in Connect Descriptor

ORA-12505 is one of the most common Oracle connectivity errors, occurring when the TNS listener receives a connection request containing a SID (System Identifier) that it does not recognize. This typically means the database instance is not running, not yet registered with the listener, or the SID in the connection string is simply incorrect. Understanding the root cause quickly is essential for minimizing downtime in production environments.

Top 3 Causes and Fixes

Cause 1: Database Instance Is Not Running or Not Yet Registered

The most frequent cause. The listener only knows about a SID after the instance is fully open and PMON has dynamically registered it.

Diagnosis:

-- Check instance status from SQL*Plus (connect as SYSDBA locally)
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS
FROM V$INSTANCE;
Enter fullscreen mode Exit fullscreen mode

Fix — Open the database if it's in MOUNT state:

ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Fix — Force dynamic registration with the listener:

ALTER SYSTEM REGISTER;
Enter fullscreen mode Exit fullscreen mode

After running ALTER SYSTEM REGISTER, wait up to 60 seconds and verify with:

-- Run from OS shell, not SQL*Plus
-- lsnrctl status
-- Look for the SID under "Services Summary"
Enter fullscreen mode Exit fullscreen mode

Cause 2: Wrong SID in Connection String or tnsnames.ora

A typo or incorrect SID in tnsnames.ora, JDBC URL, or application config will always produce ORA-12505. Additionally, using SID-style connections in environments configured for SERVICE_NAME will cause this error.

Verify the correct SID and service name:

-- Confirm the real SID and service name on the server
SELECT INSTANCE_NAME FROM V$INSTANCE;
SELECT NAME FROM V$DATABASE;
SELECT NAME, NETWORK_NAME FROM V$SERVICES;
Enter fullscreen mode Exit fullscreen mode

Correct tnsnames.ora using SERVICE_NAME (recommended):

-- Preferred: SERVICE_NAME style
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL.example.com)
    )
  )
Enter fullscreen mode Exit fullscreen mode

JDBC URL — use the slash format for SERVICE_NAME:

-- Avoid this (SID style, prone to ORA-12505):
-- jdbc:oracle:thin:@mydbhost:1521:ORCL

-- Use this instead (SERVICE_NAME style):
-- jdbc:oracle:thin:@//mydbhost:1521/ORCL.example.com
Enter fullscreen mode Exit fullscreen mode

Cause 3: Missing Static Registration in listener.ora

In environments where dynamic registration is not available or reliable (older Oracle versions, RAC, or firewall-restricted setups), the SID must be statically registered in listener.ora. Without it, the listener will never recognize the SID.

Add static registration to listener.ora:

-- listener.ora — add this block then run: lsnrctl reload
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )
Enter fullscreen mode Exit fullscreen mode

Verify registration after reload:

-- Monitor registered services
SELECT NAME, NETWORK_NAME, CREATION_DATE
FROM DBA_SERVICES
ORDER BY NAME;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Symptom Command
Instance not open ALTER DATABASE OPEN;
Dynamic registration delay ALTER SYSTEM REGISTER;
Wrong SID in config Update tnsnames.ora with correct SID/SERVICE_NAME
Static registration missing Edit listener.ora, run lsnrctl reload

Prevention Tips

1. Standardize on SERVICE_NAME connections. SID-based connections are deprecated in modern Oracle versions. Migrating all connection strings to SERVICE_NAME reduces SID-related errors and improves compatibility with RAC and Data Guard environments.

2. Include ALTER SYSTEM REGISTER in your startup scripts. Add this command to your post-startup automation to ensure the listener is always aware of the instance immediately after boot, eliminating the dynamic registration delay window.

-- Add to your DB startup script after opening the database
ALTER SYSTEM REGISTER;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-12514 — Listener does not know the requested service name (SERVICE_NAME equivalent of ORA-12505)
  • ORA-12541 — No listener process running at all; start it with lsnrctl start
  • ORA-12154 — TNS alias cannot be resolved in tnsnames.ora
  • ORA-01034 — Oracle instance not available; often appears alongside ORA-12505

📖 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.

Top comments (0)