“I can’t believe there was a missing bracket in the TNS entry”or
“The syntax was incorrect in the TNS”.Yes I admit, I have been one of those DBAs who has hacked away at the tnsnames.ora and has faced this issue in past. Although not widely known or used, there are solutions to this issue.
Solution A:
You can use the netca wizard, if you prefer to click away, however you need to ensure that you have X session enabled.
Solution B: (I prefer this option!)
A quicker and easy way of creating multiple entries for TNS is to use the netca response file with silent mode.
You can use the netca response file to create listener(s) as well but in this example, we will concentrate on looking at creating TNS aliases (or NET service) using a response file. This is not a new feature and has been available in previous versions of Oracle. 1. I want to create the following TNS alias (or NET service) entries for the following:
| TNS Alias | Net Service | Protocol | Hostname | Port |
| BLUE | BLUE | TCP | lnx121 | 1521 |
| GREEN | GREEN | TCP | lnx122 | 1521 |
2. Take note of the following parameters
NSN_NUMBER – This is the number TNS alias(es) you want to create
NSN_NAMES – List all the TNS alias(es) names
NSN_SERVICE – List all the TNS service(s) associated with the TNS alias(es)
NSN_PROTOCOLS – List the protocol,host and port for each TNS alias
NSN_NAMES – List all the TNS alias(es) names
NSN_SERVICE – List all the TNS service(s) associated with the TNS alias(es)
NSN_PROTOCOLS – List the protocol,host and port for each TNS alias
3. The following response file I will create is called 11203_netca.rsp and I have configured it as below:
4. Now execute the netca utility in silent mode using the response file (eg. 11203_netca.rsp)
##################### 11203_netca.rsp ######################
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]
INSTALL_TYPE=""custom""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
#--------------- Modify settings in this section ---------------
NSN_NUMBER=2
NSN_NAMES={"BLUE","GREEN"}
NSN_SERVICE={"BLUE","GREEN"}
NSN_PROTOCOLS={"TCP;lnx121;1521","TCP;lnx122;1521"}
#---------------------------------------------------------------
#######################################################4. Now execute the netca utility in silent mode using the response file (eg. 11203_netca.rsp)
oracle@lnx121:[BLUE]$ netca /silent /responseFile /home/oracle/11203_netca.rsp
Output:
Parsing command line arguments:
Parameter "silent" = true
Parameter "responsefile" = /home/oracle/11203_netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Warning: Oracle Restart is not running.
Listener configuration will not be registered/unregistered with Oracle Restart.
Default local naming configuration complete.
Created net service name: BLUE
Default local naming configuration complete.
Created net service name: GREEN
Profile configuration complete.
Oracle Net Services configuration successful. The exit code is 0
5. Verify that the TNS aliases for BLUE and GREEN is now created in tnsnames.ora:
oracle@lnx121:[BLUE]$ cat $TNS_ADMIN/tnsnames.ora
Output:
# tnsnames.ora Network Configuration File: /u01/app/oracle/11.2.0.3/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BLUE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx121)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = BLUE)
)
)
GREEN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx122)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GREEN)
)
)
6. Perform a tnsping for each TNS alias, to confirm entry is working.
a) TNS alias - BLUE
oracle@lnx121:[BLUE]$ tnsping BLUE
Output:
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2013 11:13:30
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/11.2.0.3/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = lnx121)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =BLUE)))
OK (0 msec)
a) TNS alias - GREEN
oracle@lnx121:[BLUE]$ tnsping GREEN
Output:
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2013 11:14:21
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/11.2.0.3/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = lnx122)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =GREEN)))
OK (0 msec)
7. This now completes the TNS alias configuration.
