oracle-databasetnsnamesoracleclienttnstnsping

tnsping cannot identify the line feed inside tnsnames.ora

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.

Solution

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