tnsping cannot identify the line feed inside tnsnames.ora

Question

I installed oracle client WINDOWS.X64_193000_client_home in my computer. And I set up my tnsnames.ora file. When using tnsping command to test the connection, I find it is OK when I put all the value in just one line, no line feed.

Name1 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = xxx)(SERVER = DEDICATED)))

But if I put the value in several lines with line feed, it throws out error.

Name1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = x.x.x.x)
(SERVER = DEDICATED)
)
)

Error as below

PS C:\Oracle\WINDOWS.X64_193000_client_home\network\admin> tnsping sap TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 03-MAY-2022 16:33:13

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files: C:\Oracle\WINDOWS.X64_193000_client_home\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = TNS-12533: TNS:illegal ADDRESS parameters

How can make it to recognise the line feed?

Edit: Some anwsers are about the incorrect content of tnsnames.ora file. Yes, after I change the tns with some indent, it is working.

But I also installed Oracle SQL Developer in my computer. It is working fine to use the tns and can be connected with oracle server successfully.

No idea, but - I've also never seen such a TNSNAMES.ORA entry. What is the purpose of all those ADDRESS lines? Isn't only one enough? — Littlefoot, May 03 '22 at 09:11
Are you certain that there isn't a stray (possibly non-printing) character in your tnsnames.ora file? That's usually what I've seen cause this. — Justin Cave, May 03 '22 at 10:45
On Windows I'd go back through the file with Notepad and make sure there aren't any special characters embedded in the file (delete and replace all the line breaks and spaces). For HA (Oracle RAC, presumably) you should use the single SCAN hostname (one ADDRESS) and let the Oracle cluster handle the connection distribution. — pmdba, May 03 '22 at 11:17
Check carefully your `tnsnames.ora` file. Perhaps it is written as UTF-8 and has a [BOM](https://en.wikipedia.org/wiki/Byte_order_mark) — Wernfried Domscheit, May 03 '22 at 14:30

Wernfried Domscheit · Accepted Answer · 2022-05-03T14:42:12.807

Try this:

Name1 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = x.x.x.x)
     (SERVER = DEDICATED)
   )
 )

I found this Syntax Rules for Configuration Files:

Oracle Corporation recommends that you set up the files so that indentation reflects what keyword is the "parent" or "owner" of other keyword-value pairs. This format is not required, but it does make the files much easier to read and understand. This is the format that Network Manager generates.

Even if you do not choose to indent your files in this way, you must indent a wrapped line by at least one space, or it will be misread as a new parameter. The following layout is acceptable:
(ADDRESS=(COMMUNITY=tcpcom.world)(PROTOCOL=tcp)
  (HOST=max.world)(PORT=1521))
The following layout is not acceptable:
(ADDRESS=(COMMUNITY=tcpcom.world)(PROTOCOL=tcp)
(HOST=max.world)(PORT=1521))
Further Syntax Rules for TNS Configuration Files

The following rules apply to the syntax of configuration files:

Any keyword in a configuration file that should be recognized as beginning a parameter that includes one or more keyword-value pairs must be in the far left column of a line. If it is indented by one or more spaces, it is interpreted as a continuation of the previous line.

Thanks. It works after I change the tnsname.ora as you suggested. But I still don't understand why Oracle SQL Developer can use the wrong format? — Robin Sun, May 05 '22 at 01:43
Perhaps SQL Developer parses the tnsnames.ora file by itself. — Wernfried Domscheit, May 05 '22 at 05:36

tnsping cannot identify the line feed inside tnsnames.ora

1 Answers1