Oracle tnsname.ora的各种正确与错误格式写法总结

1、测试机操作系统及数据库版本如下
[oracle@db ~]$ cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.5 (Santiago)

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


2、tnsname.ora配置如下

说明：建议按照ORCL服务名方式配置（严格缩进）；中间行内容严禁顶格写。

      或者使用oracle客户端工具netmanager进行配置。

[oracle@db admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


ORCL_a_legal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
)


ORCL_b_illegal =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)


ORCL_1_legal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
ORCL_1_illegal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
(SERVER = DEDICATED)          #####该行顶格写
      (SERVICE_NAME = orcl)
    )
  )


ORCL_2_legal =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
ORCL_2_illegal =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))   #####该行顶格写
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


ORCL_3_legal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
  (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
ORCL_3_illegal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =                     #####该行顶格写
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


ORCL_4_legal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
  (SERVICE_NAME = orcl)
    )
  )
  
ORCL_4_illegal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
(SERVICE_NAME = orcl)     #####该行顶格写
    )
  )
 
ORCL_5_legal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
  )
  )
  
ORCL_5_illegal =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
)                    #####该行顶格写
  )


3、tnsping测试结果
[oracle@db admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:09
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@db admin]$ tnsping ORCL_a_legal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:20
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@db admin]$ tnsping ORCL_b_legal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:25
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name

[oracle@db admin]$ tnsping ORCL_1_legal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:33
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@db admin]$ tnsping ORCL_1_illegal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:36
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA =
TNS-12533: TNS:illegal ADDRESS parameters

[oracle@db admin]$ tnsping ORCL_2_legal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:45
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@db admin]$ tnsping ORCL_2_illegal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:48
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
TNS-12533: TNS:illegal ADDRESS parameters

[oracle@db admin]$ tnsping ORCL_3_legal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:55
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@db admin]$ tnsping ORCL_3_illegal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:44:57
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
TNS-12533: TNS:illegal ADDRESS parameters

[oracle@db admin]$ tnsping ORCL_4_legal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:45:01
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

[oracle@db admin]$ tnsping ORCL_4_illegal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:45:04
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
TNS-12533: TNS:illegal ADDRESS parameters

[oracle@db admin]$ tnsping ORCL_5_legal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:45:07
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

[oracle@db admin]$ tnsping ORCL_5_illegal
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 16:45:11
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)
TNS-12533: TNS:illegal ADDRESS parameters