Oracle Network Configuration Files
Introduction:
To communicate with other systems (server machines to client machines or client machines to server machines) we need three network configuration files in order to communicate with the systems in oracle three files need to configure that is listener.ora,tnsnames.ora,sqlnet.ora,oracle net enables a network connection between a client and database server oracle net is a software component that resides on both the client and the database server.oracle net is layered on top of a network protocol -rules that determine how applications access the network and how data is subdivided into packets for transmission across the network .
The example files below are relevant for an Oracle installation and instance with the following values.
• HOST : chaitanyaoracledba.blogspot.com
• ORACLE_HOME : /u01/app/oracle/product/12.1.0.2/db
• ORACLE_SID : orcl
• Service : orcl
• DOMAIN : blogspot.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 "chaitanyaoracledba.blogspot.com". If this is incorrect, the listener will not function correctly.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))
)
)
After the "listener.ora" file is amended the listener should be restarted or reloaded to allow the new configuration to take effect.
$ # Restart
$ lsnrctl stop
$ lsnrctl start
$ # Or Reload.
$ lsnrctl reload
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.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.blogspot.com)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db)
(SID_NAME = orcl)
)
)
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.
LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))
orcl.blogspot.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
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.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = blogspot.com
# The following entry is necessary on Windows if OS authentication is required.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
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.blogspot.com).
$ sqlplus scott/tiger@orcl.blogspot.com
Common Problems
The OS hostname command must return the correct hostname of your database server. If not, fix it so it does.
$ hostname
chaitanyaoracledba.blogspot.com
$
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.
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.0.100 chaitanyaoracledba.blogspot.com chaitanyaoracledba
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.
export ORACLE_HOSTNAME=chaitanyaoracledba.blogspot.com
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.
Tools for Network configuration
Oracle enables you to manage your network configuration with the following tools
Oracle Net Configuration Assistant
Enterprise manager
Oracle Net Manager
Oracle Net Configuration Assistant: The oracle universal installer launches Oracle Net Configuration Assistant after the database is installed use oracle net configuration assistant to configure the listening protocol address and service information for an oracle database,during a typical database install,oracle net configuration assistant automatically configures a listener called LISTENER that has TCP/IP listening protocol address for the database.Oracle Net Configuration assistant prompts you to configure a listener name and protocol address of your choice.
use Oracle Network Configuration Assistant for initial network configuration after database installation there after you can use the Oracle Enterprise Manager and Oracle Net Manager to configure and administer your network
Oracle Net Manager:
You can access Oracle Net Manager from the command line or for windows platforms through the start menu
command line (Unix,Linux or windows )run netmgr
on windows click the start button and select programs,oracle-oracle_home,configuration and migration tools, and then Net Manager
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME
