Location Tnsnames.ora

[Virgil Gardiner]

Theinstallation process creates a tnsnames.ora file on each node. This file acts as a repository of net service names. Each net service name is associated with a connect identifier. A connect identifier is an identifier that maps a user-defined name to a connect descriptor.

The SERVICE_NAME parameter that you use in the tnsnames.ora file is singular, because you can specify only one service name. The SERVICE_NAME parameter is not the same as the service_names database initialization parameter. The service_names database parameter defaults to the global database name, a name comprising the db_name and db_domain parameters in the initialization parameter file. When you add service names using SRVCTL or Oracle Enterprise Manager Cloud Control, it lists additional cluster-managed services for the database.

The tnsnames.ora file is located in both the Grid_home/network/admin and Oracle_home/network/admin directories. By default, the tnsnames.ora file is read from the Grid home when Oracle Grid Infrastructure is installed.

DBCA no longer sets the LOCAL_LISTENER parameter. The Oracle Clusterware agent that starts the database sets the LOCAL_LISTENER parameter dynamically, and it sets it to the actual value, not an alias. So listener_alias entries are no longer needed in the tnsnames.ora file.

The REMOTE_LISTENER parameter is configured by DBCA to reference the SCAN and SCAN port, without any need for a tnsnames.ora entry. Oracle Clusterware uses the Easy Connect naming method with scanname:scanport, so no listener associations for the REMOTE_LISTENER parameter are needed in the tnsnames.ora file.

For example, after you create the database, to add a second listener, listening on port 2012, use a command similar to the following command to have the database register with both listeners on startup:

dbForge seems to use either the tnsnames.ora file in either location #2 or #3. I know this by systematically renaming all but one of the tns files and then checking to see if the connection names read from the file are available when trying to create a new connection from with the app.

However, TOAD seems to only recognize the tnsnames.ora file in location #3 and it did not recognize the tnsnames.ora file in location 2 at all! (Being that it was a 32 bit program, I did not expect it to recognize the tns file in location 2 and that was the case). TO summarize the TOAD test for the sake of hopeful clarity, TOAD only recognized the tns file in location 3.

However, based on some tests I made with ODP.NET Managed Driver (4.121.2.0) it takes %ORACLE_HOME%\network\admin and TNS_ADMIN Environment variable into account. Locks like the documentation is not 100% correct.

Just based on your paths you have two installed clients as you suspect (Toad and dbforge are tools, not clients so your terminology is a bit off). One 32-bit, the other 64-bit. It appears that Toad is 32-bit based on its installation path, but execute it and go to HelpSupport Bundle. You'll see the top header will be "APPLICATION INFORMATION (32-bit)" or "APPLICATION INFORMATION (64-bit)" just to confirm. Toad 11.6 was the first to introduce a 64-bit version.

Toad will only see the Oracle client that is for the same platform as it. So your 64-bit client is irrelevant for Toad's sake. The C:\Windows\TNS appears to be a folder used for TNS_ADMIN folder given its odd location and the fact that Toad sees it. At command prompt execute SET TNS_ADMIN and see if it reports "TNS_ADMIN=C:\Windows\TNS" If it does, then all Tools should be using that tnsnames.ora. That's a global override if you will that points to the folder containing your net configuration files. If you don't have TNS_ADMIN set as an environment variable then look for it in your Oracle root registry: HKEY_LOCAL_MACHINE\Software\Oracle.

If you use a common set of connections for all of your tools I'd delete all of your tnsnames.ora files. I'd also relocate that C:\Windows\TNS folder to somewhere more appropriate like C:\Oracle\Admin and create your tnsnames.ora, sqlnet.ora, and ldap.ora (if applicable) there. Create a TNS_ADMIN environment variable pointing to that location.

The tnsnames.ora file is a configuration file that contains network service names that are mapped to connect descriptors for the local naming method or net service names that are mapped to listener protocol addresses.

A net service name is an alias that is mapped to a database network address that is contained in a connect descriptor. A connect descriptor contains the location of the listener that is accessed through a protocol address and the service name of the database to which to connect. Clients and database servers that are clients of other database servers use the net service name when connecting with applications.

By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory. Oracle Net checks the other directories for the configuration file. For example, the order of checking the tnsnames.ora file is as follows:

A tnsnames.ora file can contain net service names with one or more connect descriptors. Each connect descriptor can contain one or more protocol addresses. The following example shows two connect descriptors with multiple addresses. Use the tnsnames.ora parameter DESCRIPTION_LIST to define the list of connect descriptors.

The tnsnames.ora file supports connect descriptors with multiple lists of addresses, each with its own characteristics. The following example shows two address lists. The first address list features client load balancing and no connect-time failover. These setting apply only to protocol addresses that are within its ADDRESS_LIST. The second protocol address list does not enable client load loading balancing, but the list does enable connect-time failover. These settings affect only protocol addresses that are included in its ADDRESS_LIST. The client first tries the first or second protocol address at random, then it tries protocol addresses number three and four, in that order, and so on.

Oracle Net Services supports the IFILE parameter in the tnsnames.ora file, with up to three levels of nesting. You must add the parameter manually to the file. The following is an example of the syntax:

When tnsnames.ora file connect descriptors have at least two protocol addresses for Oracle Connection Manager, you can also include parameters for connect-time failover and load balancing in the file.

Oracle Connection Manager connects to the first protocol address of another Oracle Connection Manager. If the first protocol address fails, then it tries to connect to the second protocol address. This sequence is specified with the following configuration:

The protocol address section of a tnsnames.ora file specifies listener protocol addresses. If there is only one listener protocol address, then use the ADDRESS parameter. If there is more than one address, then use the ADDRESS_LIST parameter.

Your clients can tunnel secure connections over HTTP proxy using the HTTP CONNECT method. This helps access the public cloud database service because it eliminates the need to open an outbound port on a client-side firewall. This parameter is applicable only to the connect descriptors where PROTOCOL=TCPS. This is similar to the web browser setting for intranet users who want to connect to internet hosts. You can increase the forward web proxy read timeout for requests to a higher value depending on client queries. Otherwise, the forward web proxy closes the connection assuming that no requests are made from the client.

A successful connection depends on your specific proxy configurations. The performance of your data transfers depend on the proxy capacity. Oracle recommends against using this feature in production environments where performance is critical.

Contact your network administrator to open outbound connections to hosts that are in the oraclecloud.com domain by using the relevant port, without going through an HTTP proxy. For example, port 1522.

It forwards the HTTP proxy host port that receives the HTTP CONNECT method. Use this parameter with HTTPS_PROXY_PORT. The value for the HTTPS_PROXY_PORT parameter takes effect only when you set SQLNET.USE_HTTPS_PROXY=1 set in your sqlnet.ora file.

The keepalive feature on the supported TCP transports can be enabled for a net service client by putting (ENABLE=broken) under the DESCRIPTION parameter in the connect string. On the client side, the default for tcp_keepalive is off. Operating system TCP configurables, which vary by platform, define the actual keepalive timing details.

When you set the parameter to on, yes, or true, Oracle Net fails over at connect time to a different address if the first protocol address fails. When you set the parameter to off, no, or false, Oracle Net tries one protocol address.

When you set the parameter to on, yes, or true, Oracle Net goes through the list of addresses in a random sequence, balancing the load on the various listener or Oracle Connection Manager protocol addresses. When you set the parameter to off, no, or false, Oracle Net tries the protocol addresses sequentially until one succeeds.

A network object is identified by a protocol address. When a connection is made, the client and the receiver of the request (listener or Oracle Connection Manager) are configured with identical protocol addresses. The client uses this address to send the connection request to a particular network object location, and the recipient "listens" for requests on this address, and grants a connection based on its address information matching the client information.

SESSION: Fails over the session. For example, if a user's connection is lost, then a new session is automatically created for the user on the backup. This type of failover does not attempt to recover selects.

Use this parameter with the CONNECT_DATA parameter in the tnsnames.ora file. Alternatively, you can specify KERBEROS5_CC_NAME in the connect string along with the optional KERBEROS5_PRINCIPAL parameter to connect as a different Kerberos principal.

3a8082e126