DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12227 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12227: TNS Syntax Error – Causes, Fixes, and Prevention

What Is ORA-12227?

ORA-12227 is an Oracle Net Services (TNS) error that occurs when Oracle detects a syntax error in one of its network configuration files. This error is most commonly triggered when a client attempts to connect to an Oracle database, and the TNS layer encounters a malformed entry in files such as tnsnames.ora, sqlnet.ora, or listener.ora. The root cause is always a configuration file issue, not a database engine problem.

Top 3 Causes

1. Mismatched Parentheses in tnsnames.ora

The most frequent cause of ORA-12227 is unbalanced parentheses in the tnsnames.ora file. The TNS parser is strict — even a single missing closing bracket will cause the entire file to fail.

Incorrect (causes ORA-12227):

-- Missing closing parenthesis in CONNECT_DATA block
ORCL =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = mydbserver.example.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.example.com)
    -- Missing closing ')' for DESCRIPTION block
Enter fullscreen mode Exit fullscreen mode

Correct syntax:

-- Properly balanced tnsnames.ora entry
ORCL =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = mydbserver.example.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.example.com)
    )
  )
Enter fullscreen mode Exit fullscreen mode

2. Invalid Parameters in sqlnet.ora

Setting unsupported parameter names or incorrect values in sqlnet.ora can trigger ORA-12227. This often happens after security hardening changes.

-- Check current authentication and network parameters (run after connecting)
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME LIKE '%sqlnet%'
   OR NAME LIKE '%authentication%'
ORDER BY NAME;

-- Verify open user accounts and their authentication types
SELECT USERNAME, AUTHENTICATION_TYPE, ACCOUNT_STATUS
FROM DBA_USERS
WHERE ACCOUNT_STATUS = 'OPEN'
ORDER BY USERNAME;
Enter fullscreen mode Exit fullscreen mode

Valid sqlnet.ora example:

-- sqlnet.ora correct configuration
-- SQLNET.AUTHENTICATION_SERVICES = (NONE)
-- NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
-- SQLNET.EXPIRE_TIME = 10
Enter fullscreen mode Exit fullscreen mode

3. Malformed listener.ora Structure

Incorrect block nesting in listener.ora — especially when adding new services or changing ports — is another common trigger.

-- listener.ora correct structure example
/*
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mydbserver)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )
*/

-- Verify registered services after fix
SELECT NAME, NETWORK_NAME, CREATION_DATE
FROM DBA_SERVICES
ORDER BY NAME;

-- Verify instance info
SELECT INSTANCE_NAME, HOST_NAME, STATUS
FROM V$INSTANCE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Locate your TNS files — Check the TNS_ADMIN environment variable or look under $ORACLE_HOME/network/admin/.
  2. Validate with tnsping — Run tnsping <alias> from the command line to test connectivity before and after edits.
  3. Use Oracle Net Manager — Instead of manual edits, use Oracle's GUI tool to avoid syntax mistakes.
  4. Check listener logs — Query the alert log path: 
-- Find diagnostic trace directory
SELECT VALUE
FROM V$DIAG_INFO
WHERE NAME = 'Diag Trace';
Enter fullscreen mode Exit fullscreen mode
  1. Reload listener without downtime — Use lsnrctl reload to apply listener.ora changes without a full restart.

Prevention Tips

  • Always back up before editing — Copy tnsnames.ora, sqlnet.ora, and listener.ora with a timestamp before making any changes (e.g., tnsnames.ora.20240101_bak).
  • Use version control — Store TNS configuration files in Git or another SCM system to track changes and enable instant rollback.
  • Prefer GUI tools — Oracle Net Manager and NETCA perform built-in syntax validation, greatly reducing the risk of introducing errors manually.

Related Errors

Error Code Description
ORA-12154 TNS: could not resolve connect identifier
ORA-12541 TNS: no listener
ORA-12514 TNS: listener does not know of service requested
ORA-12533 TNS: illegal ADDRESS parameters

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