tnsnames.ora alias entries

Well, I’ve been an Oracle DBA for well over 7 years and have been working with Oracle for 15+ years. Just the other day I ran across an article which noted that you can actually use more than one alias in each tnsnames.ora entry!

I know I should have known this before, however there is sooo much to learn it’s not surprising. So most tns entries I’ve seen up to this point have only one alias like this:

DBTST.DOMAIN =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = db-syst-scan)(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = dbtst)
   (FAILOVER_MODE = (TYPE = select)(METHOD = basic)(RETRIES = 20)(DELAY = 15))
  )
 )

The above allows connectivity to a RAC database named DBTST.DOMAIN hosted on db-syst-scan. However in my environment developers have decided to have many different names for the same database connection. I have no idea why they would do this. Maybe my perspective is from the DBA side of things.

In order to simplify the tnsnames.ora file you can simply add more than one alias like this:

DBTST.DOMAIN, DBTST, REPORTS.DOMAIN =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = db-syst-scan)(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = dbtst)
   (FAILOVER_MODE = (TYPE = select)(METHOD = basic)(RETRIES = 20)(DELAY = 15))
  )
 )

Now you can connect to the same database with DBTST.DOMAIN, DBTST or REPORTS.DOMAIN. Pretty slick!

Advertisements

One thought on “tnsnames.ora alias entries

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