Tnsnames ora где лежит
The installation process creates a tnsnames.ora file on each node.
The tnsnames.ora 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. A connect descriptor contains the following information:
The network route to the service, including the location of the listener through a protocol address
The SERVICE_NAME parameter, with the value set to the name of a database service
The SERVICE_NAME parameter you use in the tnsnames.ora file is singular, because you can specify only one service name.
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.
With Oracle Clusterware 11 g Release 2 and later, the listener association no longer requires tnsnames.ora file entries. The listener associations are configured as follows:
Oracle Database Configuration Assistant (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.
Example 7-2 Adding a Second Listener to an Oracle RAC Database
If you created a database named orcl1 , 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:
Tnsnames ora где лежит
The installation 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.
A connect descriptor contains the following information:
The network route to the service, including the location of the listener through a protocol address
The SERVICE_NAME parameter, with the value set to the name of a database service
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.
With Oracle Clusterware 11g Release 2 and later, the listener association no longer requires tnsnames.ora file entries. The listener associations are configured as follows:
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:
Oracle Network Configuration (listener.ora , tnsnames.ora , sqlnet.ora)
In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration.
Assumptions
The example files below are relevant for an Oracle installation and instance with the following values.
- HOST : myserver.example.com
- ORACLE_HOME : /u01/app/oracle/product/11.2.0.4/db_1
- ORACLE_SID : orcl
- Service : orcl
- DOMAIN : example.com
listener.ora
The «listerner.ora» file contains server side network configuration parameters. It can be found in the «$ORACLE_HOME/network/admin» directory on the server. Here is an example of a basic «listener.ora» file from Linux. We can see the listener has the default name of «LISTENER» and is listening for TCP connections on port 1521. Notice the reference to the hostname «myserver.example.com». If this is incorrect, the listener will not function correctly.
After the «listener.ora» file is amended the listener should be restarted or reloaded to allow the new configuration to take effect.
The listener defined above doesn’t have any services defined. These are created when database instances auto-register with it. In some cases you may want to manually configure services, so they are still visible even when the database instance is down. If this is the case, you may use a «listener.ora» file like the following.
If there are multiple database instances on the server, you can added multiple SID_DESC entries inside the SID_LIST section.
tnsnames.ora
The «tnsnames.ora» file contains client side network configuration parameters. It can be found in the «$ORACLE_HOME/network/admin» directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a «tnsnames.ora» file.
The alias used at the start of the entry can be whatever you want. It doesn’t have to match the name of the instance or service. Notice the PROTOCOL , HOST and PORT match that of the listener. The SERVICE_NAME can be any valid service presented by the listener. You can check the available services by issuing the lsnrctl status or lsnrctl service commands on the database server. Typically there is at least one service matching the ORACLE_SID of the instance, but you can create more.
sqlnet.ora
The «sqlnet.ora» file contains client side network configuration parameters. It can be found in the «$ORACLE_HOME/network/admin» directory on the client. This file will also be present on the server if client style connections are used on the server itself, or if some additional server connection configuration is required. Here is an example of an «sqlnet.ora» file.
There are lots of parameters that can be added to control tracing, encryption, wallet locations etc. These are out of the scope of this article.
Testing
Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (orcl.example.com).
Common Problems
The OS hostname command must return the correct hostname of your database server. If not, fix it so it does.
The server must have a correct entry in the «/etc/hosts» file matching the hostname and IP address of the server, as well as the loopback entry for localhost. For example in this case the values are as follows.
If these are provided by DNS, that is fine also.
If you are using the ORACLE_HOSTNAME environment variable, possibly set in your «/home/oracle/.bash_profile», it must be set to the correct value.
The HOST entry in the «listener.ora» file must point to an active network adapter, either real or loopback. If not, the listener will fail to start.
If the HOST entry in the «listener.ora» file is set to «localhost», the listener will start and accept connections from the local server, but not from other clients. You would typically expect this to be set to the hostname of the database server, although some people use the IP address instead.
For the client to make a connection via the listener, there must be a clear route through the network between the two machines. If you are struggling to connect, check network firewalls and the local firewall (iptables, firewalld, Windows Firewall) on the database server.
Where is Tnsnames.ora Location
Suppose you have known the usage of TNSNAMES, now let’s see where we can find it in this post.
In fact, the location of tnsnames.ora depends on what platform you use. Here we introduce two major platforms: Windows and Linux.
1. tnsnames.ora in Windows OS
For Windows OS, the location of tnsnames.ora file is usually at:
Problem is, there’s no TNS_ADMIN environment variable in most database systems by default. However, you can use ORACLE_HOME to derive it.
Therefore, the location of tnsnames.ora is usually at:
But first of all, you have to know where ORACLE_HOME is in Windows OS before finding the file.
Let’s see an example.
tnsnames.ora in Windows
If there’s no tnsnames.ora , please copy one in sample folder for yourself.
2. tnsnames.ora in Linux and Unix-liked OS
For Linux and Unix-like OS, the location of tnsnames.ora file is at:
Again, you have to know where ORACLE_HOME is in Linux before finding the file.