TNS Redirection?

Question

Is it possible to redirect a TNS declaration to another one?

For my current project, I require "flipping" from server to server in certain circumstances. To do this I use 3 TNS entries.

One to hold the TNS name that my application will connect to:

# application access to DB
DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SAMPLENAME)
    )
  )

So that I can connect "manually" to either of the databases myself, I use 2 more TNS entries:

# Central_Server "Manual"
Central_Server =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SAMPLENAME)
    )
  )

# Local "Manual"
Local =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SAMPLENAME)
    )
  )

Rather than tweaking the TNS entry for DB all the time, is it possible to do something along the lines of?:

DB = Local

or

DB = Central_Server

Due to deployment strategy, I'd rather not implement this logic in my application directly.

Thanks :)

Answer 1

You can configure multiple addresses in the tnsnames.ora file. It's not clear from your description if you are doing this for fail-over or load balancing. The Oracle documentation gives a good reference for how this should look.

Answer 2

Depending on the environment you could use the ORACLE_SID environment variable.

For example SQLPLUS fred/flintstone (without an @ to explicitly identify a database) will connect to the database specified in the ORACLE_SID.

Answer 3

Seems to me you'll have to "tweak" something somewhere. If you're connecting via sqlplus on windows, try:

set local=<Your TNS Alias Here>
sqlplus userid

On *nix, try:

TWO_TASK=<your TNS Alias here>; export TWO_TASK
sqlplus userid

With other clients, it might be trickier, depending on if they honor the above environment variables. Of course, if you're supplying a connect string, you can simply use the following for your connect strings:

userid@$TWO_TASK

or

userid@%local%

Answer 4

After all suggestions given, I found the easiest method was to remain with my manual "full" re-writing of the tnsnames.ora entries.

Answer 5

Have you considered solutions outside of the tnsnames.ora?

If you have control over local DNS you could set up an alias. When it comes time to switch you can update the DNS entry... of course propagation of the DNS and any local DNS caches would need to be considered.

A second possibility is Oracle Connection Manager. It can serve as a proxy and is highly configurable. You would have your single TNSNAMES entry point to the instance of Connection Manager. Then when you want to switch things around make the changes in the Connection Manager configs to point your incoming port to a new outgoing server and then put the changes into play.