3 important Oracle Net files (tnsnames.ora,sqlnet.ora & listner.ora)

tnsnames.ora
A tnsnames.ora file maps net service names to connect descriptors.
In simple words, tnsnames.ora file provides the ability to reference oracle databases by a simple alias.
It can be found in the ORACLE_HOME/network/admin or ORACLE_HOME/net80/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself.
The net service name thus becomes a (most likely shorter and more readable) alias for the somewhat cumbersome net service name.

In the following example, the «text» after the equal sign is the connect descriptor while the text before the equal sign (net_service_name) is the net service name.

$ORACLE_HOME/network/admin/tnsnames.ora
---------------------------------------
dbname1, aliasname1, aliasname2 =
(description =
(address_list =
(address =
(protocol = tcp)(host = yourHost.domain)(port = 1521)))
(connect_data =(sid = yourSID)))

sqlnet.ora
The sqlnet.ora file contains client side network configuration parameters.

The sqlnet.ora file enables you to:
Specify the client domain to append to unqualified names
Prioritize naming methods
Enable logging and tracing features
Route connections through specific processes
Configure parameters for external naming
Configure Oracle Advanced Security
Use protocol-specific parameters to restrict access to the database
It can be found in the ORACLE_HOME/network/admin or ORACLE_HOME/net80/admin directory on the client.

Here is an example of an sqlnet.ora file :
$ORACLE_HOME/network/admin/sqlnet.ora
-------------------------------------
automatic_ipc = ON # Set to OFF for PC's
trace_level_client = OFF # Set to 16 if tracing is required
sqlnet.expire_time = 0 # Idle time in minutes
sqlnet.authentication_services = (ALL)
names.directory_lookup = (TNSNAMES,ONAMES)
names.default_domain = world
name.default_zone = world

listener.ora

Listener.ora file consists of the listener configurations like,
Listener's name
Protocol addresses that it is accepting connection requests on
Services it is listening
Control parameters
By default, the listener.ora file is located in the $ORACLE_HOME/network/admin

A Sample Listener.ora file
$ORACLE_HOME/network/admin/listener.ora
---------------------------------------
LISTENER = # Listener name is LISTENER
(address_list =
(address=
(protocol=ipc)
(key=yourSID)
)
(address=(protocol = tcp)(host = yourHost.domain)(port = 1521)
)
)

TNS:12541 ERROR

TNS:12541 error: No Linstner



Solution:



1.Check the target Db's listener.ora , check for HOST and PORT ensure that both are correct.



2. Bounce the LNSRCTL (STOP and then start it)



3. Now ping the target database again.



EXAMPLE:

C:\orcl>tnsping earth

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-FEB-2008 16:28:11
Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:C:\orcl\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.16.12.35)(PORT = 1522))) (CONNECT_DATA = (SID = EARTH) (SERVER = DEDICATED) (SERVICE_NAME = EARTH)))

TNS-12541: TNS:no listener

C:\myorcl>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-FEB-2008 16:24:19
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.


LSNRCTL> stop



Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))The command completed successfully



LSNRCTL> start



Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - ProductionSystem parameter file is C:\myorcl\network\admin\listener.oraLog messages written to C:\myorcl\network\log\listener.logListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.16.12.35)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - ProductionStart Date 26-FEB-2008 16:24:33

Uptime 0 days 0 hr. 0 min. 2 sec

Trace Level off

Security OFF

SNMP OFF

Listener Parameter File C:\myorcl\network\admin\listener.oraListener

Log File C:\myorcl\network\log\listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.16.12.35)(PORT=1522)))Services Summary...Service "EARTH" has 1 instance(s). Instance "EARTH", status UNKNOWN, has 1 handler(s) for this service...Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully



C:\orcl>tnsping earth

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-FEB-2008 16:35:18
Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:C:\orcl\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.16.12.35)(PORT = 1522))) (CONNECT_DATA = (SID = EARTH) (SERVER = DEDICATED) (SERVICE_NAME = EARTH)))

OK (20 msec)

Network Configuration

A client is any application that needs to connect to the Oracle database to send or retrieve data. An Oracle client application can reside on any machine provided it has Oracle client software installed.

Oracle Net is a software component that resides on the client and the Oracle database server. It is responsible for establishing and maintaining the connection between the client application and the server, as well as exchanging messages between them, using industry standard protocols.

For the client application and a database to communicate, the client application must specify location details for the database it wants to connect to and the database must provide some sort of identification, or address.

Oracle Net Listener (Server side) Configuration

On the database server side, the Oracle Net listener, commonly known as the listener, is a process that listens for client connection requests. It is configured in a file named listener.ora, with a protocol address that identifies the database. This address defines the protocol the listener is listening on and any other protocol specific information.
For example, the listener could be configured to listen at the following protocol address:

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=my-server) (PORT=1521)))
This example shows a TCP/IP protocol address that specifies the host machine of the listener and a port number.

Client Configuration

When a client configured with the same protocol address broadcasts a request for a connection, the listener on the server machine brokers the request and forwards it to the Oracle database.

The client uses a connect descriptor to specify the database it wants to connect to. This connect descriptor contains a protocol address and a database service name. A database can have multiple services defined, so a specific one must be specified for the connection. In the case of the preconfigured database that you installed, there is only one service, the name of which defaults to the global database name.
The following example shows a connect descriptor that enables clients to connect to a database service called mydb.us.acme.com:
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=my-server) PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=mydb.us.acme.com)))

Connection Requests

Users initiate a connection request by providing a connect string. A connect string includes a username and password, along with a connect identifier. This connect identifier can be the connect descriptor itself, or a name that resolves to the connect descriptor. One of the most common connect identifiers is a net service name, a simple name for a service. The following example shows a connect string that uses net service name mydb as the connect identifier.

CONNECT scott/tiger@mydb

While the connect identifier is relatively simple as shown, it can be long and inconvenient to use if your environment configuration is more complex. With long connect identifiers, you can use a mapping method that maps the net service name to the connect descriptor. This mapping information is stored in one or more repositories of information that are accessed with naming methods.

Naming methods:

Local Naming
The local naming method stores connect descriptors, identified by their net service name, in a local (on the client) configuration file named tnsnames.ora.

Directory Naming
The directory naming method stores connect identifiers in a centralized LDAP-compliant directory server to access a database service.

Easy Connect Naming
The easy connect naming method enables clients to connect to an Oracle database server by using a TCP/IP connect string consisting of a host name and optional port and service name:

CONNECT username/password@host[:port][/service_name]

For example:CONNECT scott/tiger@my-server:1521/mydb

The easy connect naming method requires no configuration.

External Naming
The external naming method stores net service names in a supported non-Oracle naming service. These supported third-party services include:

1.Network Information Service (NIS) External Naming
2.Distributed Computing Environment (DCE) Cell Directory Services (CDS)

Tools for Network Configuration
Oracle enables you to manage your network configuration with the following tools:
A.Oracle Net Configuration Assistant,
B. Enterprise Manager,
C. Oracle Net Manager

A.Oracle Net Configuration Assistant
The Oracle Universal Installer launches Oracle Net Configuration Assistant after the database is installed. Oracle Net Configuration Assistant enables you to configure the listening protocol address and service information for an Oracle database.
During a typical database install (as opposed to client install), Oracle Net Configuration Assistant automatically configures a listener with a name of LISTENER that has a TCP/IP listening protocol address for the Oracle database that you install. If you do a custom install, Oracle Net Configuration Assistant prompts you to configure a listener name and protocol address of your choice.
Oracle Net Configuration Assistant is primarily a post installation tool. Thereafter, Oracle provides other means for network configuration, which are the Oracle Enterprise Manager and Oracle Net Manager.

B. Enterprise Manager
Enterprise Manager enables you to manage your server-side network configuration with two pages: the Listener page and the Net Services Administration page.
The Listener page displays the listener status and enables you to shut it down. You can navigate to this page from the Home page by clicking the Listener link

C. Oracle Net Manager
You can access Oracle Net Manager from the command line or for Windows platforms through the Start menu.
For command line (UNIX, Linux, or Windows) run netmgr.
On Windows choose:
Start > Programs > Oracle - home-name > Configuration and Migration Tools > Net Manager

Starting and Shutting Down the Listener

The listener runs on your database server machine and brokers incoming client requests. With Enterprise Manager, you can view the status of the listener, which is set to start automatically whenever your server machine is restarted.

listener status : lsnrctl status

listener start : lsnrctl start

listener start : lsnrctl stop

Configuring Oracle Networking on Client Machines
Client machines need to be configured before they can connect to an Oracle database. To configure the client machine, you must first install Oracle client software, which includes Oracle Net software.
This example shows you how to configure a Windows client with local naming, which involves adding a connect descriptor entry to the local tnsnames.ora file. The connect descriptor specifies the host name, protocol address, and service name of the database to connect to.
On Windows, you can use the Oracle Net Manager program to create a new net service entry in your tsnames.ora file as follows:

1.Start Oracle Net Manager by clicking Start->Programs->Oracle->Configuration and Migration Tools->Net Manager

2.Highlight Local Naming and click the plus (+) on the left hand side of the page. The Net Service Name Wizard appears.

3.Enter a net service name to identify it. The name you choose will appear under the Service Naming folder. Click Next.

4.Select the protocol to use to connect to your database. For example, you can accept the default TCP/IP (Internet Protocol). The database listener must be configured to use the same network protocol. Click Next.

5.Enter the host name of the database machine, such as coconuts.island.com.

6.Enter either the database service name or the database SID. In addition, select a Connection Type, either Shared or Dedicated. Shared connections require your database to be running in shared mode. If you choose Dedicated, the database dedicates a single server process to serve your connection. If you are unsure or would like to use the default database connection type, select Database Default. Click Next.

7.The last screen of the wizard enables you to test the connection using the information you entered. To do so, click Test. To log in to the database, you can use the SYS user and the password you specified when you configured your database. The wizard tells you if the connection test succeed or not.

8.Click Finish. The new service appears under the Service Naming folder. You can view or modify the configuration for the highlighted service under Service Identification and Address Configuration.

Where is the SQL*Net configuration files located?

The SQL*Net configuration files (like SQLNET.ORA and TNSNAMES.ORA) can be found in one of the following locations (SQL*Net searches for it in this order):
Directory pointed to by the TNS_ADMIN parameter ($TNS_ADMIN on Unix)
/etc (Unix only)
/var/opt/oracle (Unix only)
$ORACLE_HOME/network/admin (or sometimes net8/admin directory)

ORA-12154 ERROR !!!

This error message occurs when a user attempts to logon to an Oracle database. The tnsnames.ora file was not found or has a syntax error. The Oracle communications software (SQL*Net) did not recognize the database connect descriptor as being valid. For GUI utilities, this is the name entered in the third field of the ORACLE logon box.

ORA-12154 TNS:Could not resolve service name

Cause: The service name specified is not defined in the TNSNAMES.ORA file.

Action: Make the following checks and correct the error:
- Verify that a TNSNAMES.ORA file exists and is in the proper
place and accessible. See the operating system specific manual
for details on the required name and location.
- Check to see that the service name exists in one of the
TNSNAMES.ORA files and add it if necessary.
- Make sure there are no syntax errors anywhere in the file.
Particularly look for unmatched parentheses or stray characters.
Any error in a TNSNAMES.ORA file makes it unusable. If possible
regenerate the configuration files using the Oracle Network
Manager.

sample configuration files

$ORACLE_HOME/network/admin/sqlnet.ora
-------------------------------------
automatic_ipc = ON # Set to OFF for PC's
trace_level_client = OFF # Set to 16 if tracing is required
sqlnet.expire_time = 0 # Idle time in minutes
sqlnet.authentication_services = (ALL)
names.directory_lookup = (TNSNAMES,ONAMES)
names.default_domain = world
name.default_zone = world


$ORACLE_HOME/network/admin/tnsnames.ora
---------------------------------------
dbname1, aliasname1, aliasname2 =
(description =
(address_list =
(address =
(protocol = tcp)
(host = yourHost.domain)
(port = 1521)
)
)
(connect_data =
(sid = yourSID)
)
)

$ORACLE_HOME/network/admin/listener.ora
---------------------------------------
LISTENER = # Listener name is LISTENER
(address_list =
(address=
(protocol=ipc)
(key=yourSID)
)
(address=
(protocol = tcp)
(host = yourHost.domain)
(port = 1521)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = ON
TRACE_FILE_LISTENER = $ORACLE_HOME/network/trace/listener.trc
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
(SID_NAME=yourSID)
(ORACLE_HOME=YOUR_ORACLE_HOME)
)
)