DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12222 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12222: TNS – No Support Is Available for the Protocol Indicated

ORA-12222 is an Oracle Net (TNS) error that fires when the network layer cannot find or load the protocol adapter required to handle a connection request. It typically means that the protocol string in your tnsnames.ora, listener.ora, or sqlnet.ora is either misspelled, unsupported, or the corresponding Oracle Net adapter binary is missing from the installation. This error is entirely a configuration-side issue and does not indicate a database corruption problem.

Top 3 Causes

1. Typo or Invalid Protocol String in tnsnames.ora / listener.ora

The most common cause. Oracle TNS accepts only TCP, TCPS, IPC, or NMP as valid protocol values. Any deviation — such as TCPP, TCPIP, or tcp (in some legacy versions) — will immediately trigger ORA-12222.

-- After fixing tnsnames.ora, verify your current session's connection protocol
SELECT
    sys_context('USERENV', 'NETWORK_PROTOCOL') AS protocol,
    sys_context('USERENV', 'IP_ADDRESS')        AS client_ip,
    sys_context('USERENV', 'SERVICE_NAME')      AS service_name
FROM dual;
Enter fullscreen mode Exit fullscreen mode 
-- Check registered listener services from the database side
SELECT name, network_name, pdb
FROM   v$services
ORDER  BY name;
Enter fullscreen mode Exit fullscreen mode

Bad config (causes ORA-12222):

-- MYDB =
--   (DESCRIPTION =
--     (ADDRESS = (PROTOCOL = TCPP)(HOST = dbhost)(PORT = 1521))  -- WRONG
--     (CONNECT_DATA = (SERVICE_NAME = mydb)))

-- Correct config:
-- MYDB =
--   (DESCRIPTION =
--     (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))   -- CORRECT
--     (CONNECT_DATA = (SERVICE_NAME = mydb)))
Enter fullscreen mode Exit fullscreen mode

2. Missing or Corrupt Oracle Net Protocol Adapter

When you specify a protocol like NMP (Named Pipes) or TCPS (SSL) but the corresponding adapter library is not installed or was removed during a partial reinstall or patch, Oracle cannot load the adapter and raises ORA-12222.

-- Check Oracle component registry to spot missing network components
SELECT comp_name, version, status
FROM   dba_registry
WHERE  comp_name LIKE '%Net%'
ORDER  BY comp_name;
Enter fullscreen mode Exit fullscreen mode 
-- Verify SSL/Wallet status if TCPS is in use (Oracle 12c+)
SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;
Enter fullscreen mode Exit fullscreen mode

Quick fix: If Named Pipes is not required, remove NMP entries from tnsnames.ora and replace with TCP. If TCPS is needed, run Oracle Universal Installer to repair/add the SSL adapter component.

3. sqlnet.ora Misconfiguration Blocking the Protocol

Settings like SSL_VERSION, WALLET_LOCATION, or strict NAMES.DIRECTORY_PATH entries in sqlnet.ora can prevent certain protocol adapters from initializing correctly, causing ORA-12222 even when the protocol string itself is valid.

-- Check SSL-related parameters at the database level
SELECT name, value
FROM   v$parameter
WHERE  name IN ('ssl_version', 'ssl_cipher_suites');
Enter fullscreen mode Exit fullscreen mode 
-- Review current parameter settings that affect Oracle Net
SELECT name, value, isdefault
FROM   v$parameter
WHERE  name IN (
    'local_listener',
    'remote_listener',
    'service_names',
    'db_domain'
)
ORDER  BY name;
Enter fullscreen mode Exit fullscreen mode

Quick fix: Ensure sqlnet.ora contains the correct WALLET_LOCATION and SSL_VERSION = 1.2 when using TCPS. For pure TCP environments, remove all SSL-related lines from sqlnet.ora and reload the listener.

Quick Fix Checklist

  1. Validate protocol strings — Open tnsnames.ora and confirm every PROTOCOL = value is exactly TCP, TCPS, IPC, or NMP.
  2. Run tnsping — Execute tnsping <alias> from the OS prompt to isolate whether the issue is in name resolution or protocol handling.
  3. Reload the listener — After any config change, run lsnrctl reload (or lsnrctl stop + lsnrctl start) to apply updates.
  4. Check adapter libraries — On Linux, verify that libsql* and liboracle* adapter .so files exist under $ORACLE_HOME/lib.
  5. Review sqlnet.ora — Temporarily rename sqlnet.ora to isolate whether it is causing the protocol restriction.

Prevention Tips

  • Use NETCA or Oracle Net Manager instead of manually editing TNS files. These GUI tools let you select the protocol from a dropdown, eliminating typos entirely.
  • Implement a test-before-deploy policy: Always run tnsping and a full sqlplus user/pass@alias connection test in a staging environment before pushing TNS configuration changes to production. Store all TNS files in version control (Git) so you can roll back instantly if ORA-12222 reappears after a change.

Related Errors

Error Code Description
ORA-12154 TNS alias not found — often co-occurs with ORA-12222
ORA-12203 Unable to connect to destination after protocol failure
ORA-12560 Protocol adapter error on local/IPC connections
ORA-12545 Target host or object does not exist

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