Vishal Gupta's Blog

Comma Separated Multiple tnsalias For Single Entry

Posted by Vishal Gupta on Jul 5, 2012

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)
 )
)
Lets test to see if it works.
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 !!!

About these ads

6 Responses to “Comma Separated Multiple tnsalias For Single Entry”

  1. Interesting! I didn’t know it. Thanks for sharing! :)

  2. Very interesting, I not used this option, too :)
    How to you think when we use different alias for same service !

    Thanks for share!

    Mahir M. Quluzade

  3. Mark Hughes said

    Yup that is cool, one to remember :)

  4. Zoila Hane said

    What’s Taking place i’m new to this, I stumbled upon this I have discovered It absolutely helpful and it has aided me out loads.
    I’m hoping to contribute & help other customers like its helped me. Good job.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: