Vishal Gupta's Blog

I learned something new today, so thought i would share on my blog here. Did you know that you can specify multiple tnsalias (or net service name link , as Oracle documentation likes to call it) for a single entry. Its not documented as is the case with many cool hidden features. I searched Oracle documentation and Oracle Support site, but could not find any reference to this feature. Google gave this link to Quest’s toad bug with search string of “comma separated tns alias”.

Usually tns entry is made in tnsnames.ora file as per below syntax (in its simplest form).

net_service_name=
  (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521))
     (CONNECT_DATA=
        (SERVICE_NAME=service_name)
     )
  )

You can also specify multiple net_service_name separated by command in a single entry. There may or may not be a space between comma and alias.

alias1,alias2, alias3 =
  (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521))
     (CONNECT_DATA=
        (SERVICE_NAME=service_name)
     )
  )

I have checked that above syntax works in versions from 9.2.x to 11.2.x. I did not have earlier version handy to verify this. Given below is my TNS entry.

[oracle@linux1:11203] cat $TNS_ADMIN/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/tns_admin/tnsnames.ora
# Generated by Oracle configuration tools.

alias1,alias2,alias3 = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=11203))(ADDRESS=(PROTOCOL=TCP)(HOST=11203.vishalgupta.com)(PORT=1521)))
alias4,alias5,alias6 =
(DESCRIPTION =
 (ADDRESS= (PROTOCOL=TCP)(HOST=11203.vishalgupta.com)(PORT=1521))
 (CONNECT_DATA=
 (SERVICE_NAME=11203)
 )
)

for i in `cat /opt/oracle/bin/dbs.txt `;
do
      echo "############## $i #######################";
      export ORACLE_SID=$i;
      export ORAENV_ASK=NO
      . oraenv ;
      $ORACLE_HOME/bin/tnsping alias2;
done

############## 9204 #######################

TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 04-JUL-2012 02:10:41

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
/opt/oracle/tns_admin/sqlnet.ora

############## 10103 #######################

The Oracle base for ORACLE_HOME=/opt/oracle/product/rhel4/database/10.1.0.3/home is /opt/oracle/product/rhel4/database/11.2.0.3

TNS Ping Utility for Linux: Version 10.1.0.3.0 - Production on 04-JUL-2012 02:10:41

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

Used parameter files:
/opt/oracle/tns_admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=11203))(ADDRESS=(PROTOCOL=TCP)(HOST=11203.vishalgupta.com)(PORT=1521)))
OK (0 msec)

############## 10201 #######################

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 04-JUL-2012 02:10:41

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

Used parameter files:
/opt/oracle/tns_admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=11203))(ADDRESS=(PROTOCOL=TCP)(HOST=11203.vishalgupta.com)(PORT=1521)))
OK (0 msec)

############## 11106 #######################
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 04-JUL-2012 02:28:48

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

Used parameter files:
/opt/oracle/tns_admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=11203))(ADDRESS=(PROTOCOL=TCP)(HOST=11203.vishalgupta.com)(PORT=1521)))
OK (0 msec)

############## 11201 #######################

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-JUL-2012 02:28:48

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

Used parameter files:
/opt/oracle/tns_admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=11203))(ADDRESS=(PROTOCOL=TCP)(HOST=11203.vishalgupta.com)(PORT=1521)))
OK (0 msec)

Its working, cool !!!