tnsnames.ora Secrets

I learn something new every day. The todays lession I want to share with you.
When reading the headline, I assume you wonder what secrets could be hidden inside tnsnames.ora file. It is nothing magic, maybe some magic when HA features like multiple addresses, description lists or backup aliases for preconnect come into play. But that is not the topic of this post.
You all know the entries in tnsnames.ora which look like this:

db12c =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db12c)
    )
  )

It works as espected:

C:\Users\marco.mischke> tnsping db12c

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 02-DEC-2015 20:06:46

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

Used parameter files:
C:\oracle\product\11.2.0.4\client\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db12c)))
OK (10 msec)

But did you know, that you can define multiple aliases for the same description? I didn’t.

myalias1, myalias2, whatever, db12c =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)
    )
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = db12c)
    )
  )

Now you can use all the aliases:

C:\Users\marco.mischke> tnsping db12c

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 02-DEC-2015 20:10:51

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

Used parameter files:
C:\oracle\product\11.2.0.4\client\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db12c)))
OK (10 msec)
C:\Users\marco.mischke> tnsping myalias1

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 02-DEC-2015 20:10:56

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

Used parameter files:
C:\oracle\product\11.2.0.4\client\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db12c)))
OK (10 msec)
C:\Users\marco.mischke> tnsping myalias2

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 02-DEC-2015 20:10:57

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

Used parameter files:
C:\oracle\product\11.2.0.4\client\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db12c)))
OK (10 msec)
C:\Users\marco.mischke> tnsping whatever

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 02-DEC-2015 20:11:05

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

Used parameter files:
C:\oracle\product\11.2.0.4\client\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db12c)))
OK (10 msec)

A quick check in the Net Services Reference returned no further information about that. If you have information about this, please let me know.
Otherwise, maybe that helps some of you to consolidate their tnsnames.ora file. You no longer need to maintain several aliases with the same description pointing to a single database.

Advertisements