请求协助解决SQL*Plus 11g登录时ORA-12154 TNS解析错误

Hey there, let's work through this ORA-12154 error together—it’s a super common snag with SQL*Plus 11g on Windows, so we’ll go through the most reliable fixes step by step.

• Double-check your TNSNAMES.ORA file
  This file lives in ORACLE_HOME\network\admin by default. Open it up and verify your database entry follows the correct format:

  YOUR_DB_ALIAS =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = your_db_host)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = your_database_service_name)
      )
    )
  

  Watch for these critical details:

  • HOST should be the correct server address (use localhost for a local database, or your machine’s name/IP)
  • PORT defaults to 1521—make sure your Oracle listener is using this port
  • SERVICE_NAME must match the actual service name of your database (you can confirm this on the database side with SELECT name FROM v$database;)
    Also, scan for silly syntax errors like mismatched parentheses or missing commas—those break things more often than you’d think.

• Verify the TNS_ADMIN environment variable
  Windows uses this variable to find your TNSNAMES.ORA file. Here’s how to check:

  1. Press Win+R, type sysdm.cpl and hit Enter to open System Properties
  2. Go to the Advanced tab, click Environment Variables
  3. Look for TNS_ADMIN in the System Variables section:
     • If it doesn’t exist, create it and set its value to the full path of your network\admin folder (e.g., C:\app\oracle\product\11.2.0\dbhome_1\network\admin)
     • If it does exist, double-check the path is spelled correctly—even a tiny typo will cause this error.

• Test your SQL*Plus connection string
  Make sure you’re using the right syntax when logging in. For example:

  sqlplus your_username/your_password@YOUR_DB_ALIAS
  

  The YOUR_DB_ALIAS here must exactly match the alias name in your TNSNAMES.ORA file (case doesn’t matter on Windows, but consistency helps). If this fails, try skipping TNSNAMES entirely with a direct connection string:

  sqlplus your_username/your_password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your_db_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=your_database_service_name)))
  

  If this direct connection works, the problem is definitely with your TNSNAMES setup or TNS_ADMIN variable.

• Check if the Oracle Listener service is running
  Press Win+R, type services.msc and hit Enter. Look for the service named OracleOraDb11g_home1TNSListener—its status should say Running. If it’s stopped, right-click it and select Start, and consider setting it to Automatic startup so you don’t run into this again.

• Rule out firewall issues
  If your database is on a remote server, or your local Windows Firewall is blocking port 1521, that’ll trigger this error. Try temporarily disabling your firewall to test the connection—if it works, add an inbound rule to allow traffic on port 1521 for the Oracle listener.

• Confirm version compatibility
  While 11g SQL*Plus usually works with newer Oracle servers (like 12c+), double-check that you’re using the correct service name. 12c and later use pluggable databases (PDBs), so you might need to use the PDB’s service name instead of the container database (CDB) name.

内容的提问来源于stack exchange，提问作者Vicky Patel