TNS Name

3 Ways to Connect to Database without Modifying tnsnames.ora

Yes, sometimes we are not allowable to change tnsnames.ora, even a tiny bit. How can we connect to new or moved databases?

In this post, I provided some ways to connect to databases without touching the original tnsnames.ora.

  1. Use Full Connect Descriptor
  2. Use Your Own tnsnames.ora
  3. Use Easy Connect Method

1. Use Full Connect Descriptor

Intrinsically, tnsnames.ora is alias management of service names in a file. Every entry in the file contains a pair of connect identifier and connect descriptor. For example:

...
ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g-1.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

In the above entry, ORA11G is the connect identifier, everything behind the equal sign is the connect descriptor. More specifically, the connect identifier is just an alias which can be used in connection string to represent their own lengthy connect descriptor for convenience.

C:\Users\edchen>sqlplus /nolog
..
SQL> conn hr/hr@ora11g
Connected.

In other words, the connect identifier, the alias will be translated into its own connect descriptor at connect-time. That is to say, the real working thing is the connect descriptor. That’s why we replace the connect identifier with the connect descriptor to connect a new or moved database.

SQL> conn hr/hr@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g-2.example.com)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = ORCL)))"
Connected.

For safety, we use double quotes to wrap the string. It connected successfully.

2. Use Your Own tnsnames.ora

I know you cannot touch the original tnsnames.ora, but you can use your own tnsnames.ora. The trick is to set a TNS_ADMIN environment variable temporarily for your session and put your own tnsnames.ora in that directory. Then connection tools including sqlplus will know where to find the new tnsnames.ora.

For Linux Platform

Set TNS_ADMIN Path

[oracle@test ~]$ export TNS_ADMIN=/tmp
[oracle@test ~]$ echo $TNS_ADMIN
/tmp

This setting will override the original path to tnsnames.ora.

Modify tnsnames.ora File

[oracle@test ~]$ vi /tmp/tnsnames.ora
...
ORA11G_NEW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g-2.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

Test Old Connect Identifier

[oracle@test ~]$ sqlplus /nolog
...
SQL> conn hr/hr@ora11g
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Of course it got error, because we point the network configuration to the new location TNS_ADMIN.

Test New Connect Identifier

SQL> conn hr/hr@ora11g_new
Connected.

For Windows Platform

Set TNS_ADMIN Path

C:\Users\edchen>set TNS_ADMIN=d:\
C:\Users\edchen>echo %TNS_ADMIN%
d:\

This setting will override the original path to tnsnames.ora.

Modify tnsnames.ora File

C:\Users\edchen>notepad d:\tnsnames.ora
...
ORA11G_NEW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g-2.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

Test Old Connect Identifier

C:\Users\edchen>sqlplus /nolog
...
SQL> conn hr/hr@ora11g
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Of course it got error, because we point the network configuration to the new location TNS_ADMIN.

Test New Connect Identifier

SQL> conn hr/hr@ora11g_new
Connected.

Please note that, TNS_ADMIN set here is just only for your session, once you exit, the environment variable is reset. Of course, you can also set TNS_ADMIN permanently.

3. Use Easy Connect Method

By default, you can use Easy Connect naming method to connect databases, this is because the default connect methods of NAMES.DIRECTORY_PATH in sqlnet.ora includes EZCONNECT. Which is:

NAMES.DIRECTORY_PATH = (TNSNAMES, LDAP, EZCONNECT)

You have to provide the following information in order to use Easy Connect naming method.

  • Where do you want to go?
  • What is the listener port?
  • What is the service name?

In this case, the destination is ora11g-2.example.com, the listener port is 1521 and the service name is ORCL.

C:\Users\edchen>sqlplus /nolog
...
SQL> conn hr/hr@ora11g-2.example.com:1521/orcl
Connected.

It seems a simple way to connect to database, you can find out more concepts and examples about Easy Connect naming method in Oracle documentation.

On the other way, if DBA explicitly disabled the naming method by removing EZCONNECT from the list in sqlnet.ora, like the following:

NAMES.DIRECTORY_PATH = (TNSNAMES)

You will no longer to use Easy Connect.

C:\Users\edchen>sqlplus /nolog
...
SQL> conn hr/hr@ora11g-2.example.com:1521/orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Leave a Reply

Your email address will not be published. Required fields are marked *