oracle network configuration

10g Network Configuration |SQL*Net

About Oracle Net Services

Oracle Net Services provides a Secured and High-availability network

infrastructure for Oracle environment. (Supports network communication)

between a client application and a remote or local database.

Oracle Net is the transport software for communication between client and

database server as well as communication between distributed database

servers. Net8 (called SQL*Net prior to Oracle8).

Connectivity

Oracle Net Services enables a network session from a client application to an

Oracle database server. Once a network session is established, Oracle Net acts

as the data courier for both the client application and the database server.

It is responsible for establishing and maintaining the connection between the

client application and DB server, and exchanging messages between them.

SQL*NET

SQL*NET (OR NET8) is Oracles Client/Server Product.

SQL*NET enables Clients and Servers to connect to each other.

SQL*NET ( Oracle's n/w Software) allows remote data - access between

programs and the Oracle database , or among multiple Oracle Databases.

SQL*Net provides the software layer between Oracle and the networking

software, communication between an Oracle client machine (for ex, SQL*Plus)

Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu


and the database server or from one database server to another. Net8 is

located on each machine in the network.

SQL*Net Configuration Files

In most basic form , Oracle uses three files for n/w configuration. They are

listener.ora file On Server - Side Setup.

tnsnames.ora file On a Client - Side Setup.

sqlnet.ora file (Optional) - Either server or client side.

SQLNET.ORA

The sqlnet.ora file is the Net8 profile configuration file.

This file is a text file that contains basic configuration details (encryption ,

default domain , tracing options etc ) and used by SQL *Net.

Profiles are stored and implemented using this file.

It resides on the client machines and the database server.

By default, this file is located in the $ORACLE_HOME/network/admin dir.

The database server can be configured with access control parameters in the

sqlnet.ora file. These parameters specify whether clients are allowed or denied

access based on the protocol. (Protocol Parameters Restrict access to

the database)

Profile Parameters for sqlnet.ora

SEND_BUF_SIZE



RECV_BUF_SIZE

NAMES.DIRECTORY_PATH

NAMES.DEFAULT_DOMAIN

SQLNET.AUTHENTICATION_SERVICES and etc ..

The sqlnet.ora file is created by running netca command.

LISTENER.ORA

Listener.ora is a SQL*Net configuration file.

Listerner.ora file contains server side network configuration parameters.

It is used to configure DB Listeners (to accept remote connection requests).

By default , this file is located in the $ORACLE_HOME/network/admin dir.

Important point to remember, should compulsorily have a listener.ora file

(if users connecting from a different server).

TNSNAMES.ORA

TNSNAMES.ORA is a SQL*Net Configuration file.

It contains information (about the server where our database is present);

For ex : ( The port no , protocol, database service_name) and etc ..

This file contains net service names mapped to connect descriptors for (local

naming method ) or mapped to listener protocol addresses.

By default, this file is located in the $ORACLE_HOME/network/admin dir.

Important point to remember, If a user wants to connect to a database,

that the user would definitely need a tnsnames.ora file.

POINTS TO NOTE



If million no of users are connecting to a website for ex www.google.com ;

then the user don’t need to have a tnsnames.ora on their own machine ,

because the website is located on a web server , so the tnsnames.ora file

would already be in the webserver.

If NETCA runs successfully , the sqlnet.ora , tnsnames.ora and listener.ora

files will be created automatically with default settings. If NETCA doesn’t

succeed , it is possible to create the files manually.

SQL*Net Protocols

BEQ Bequeath Protocol

TCP Transmission Control Protocol

IPC Inter Process Communications

TCP/IP Transmission Control Protocol/Internet Protocol

Bequeath Protocol

BEQ is a SQL*Net Protocol.

BEQ Connections doesn’t required a listener.

BEQ only used for local connections (Similar to the IPC protocol) .

BEQ enable Clients to connect to the database. I.e.

(Client and Server reside on the same system).

BEQ works only in Dedicated Server mode and not in Shared Server mode.

Database Local connections - where an (Oracle client application - SQL*Plus)

communicates with an Oracle server running on the same machine.

IPC


http://www.google.com/


IPC Stands for Inter Process Communication.

IPC is a SQL*Net protocol.

A listener is required to make IPC connections.

IPC only used for local connections (similar to the BEQ protocol).

IPC enable Clients to connect to the database. i.e.

(Client and Server reside on the same system).

IPC works on Dedicated Server/Shared Server mode.

Database Local connections - where an (Oracle client application - SQL*Plus)

communicates with an Oracle server running on the same machine.

TCP and TCP/IP

TCP and TCP/IP protocols are supported by SQL* Net.

TCP Transmission control Protocol and used for (remote administration).

TCP/IP is a combination of network and transport level protocols .

TCP/IP is used for CLIENT/SERVER communication over a network.

TCP vs IPC

When we use IPC Connectivity ?

When we use TCP Connectivity ?

We can connect to a local database without any listener only by using IPC.

IPC connection does require listener! Listener only requires TCP and it is

required when two different hosts are involved.

$ sqlplus scott/tiger@orcl This is using TCP.



$ sqlplus scott/tiger TPC not required but IPC.

When we use ICP Connectivity ?

“ IPC can only be used when the Client and Server reside on the same host

”.

The (IPC) takes place between two processes within the memory space of the

Operating System , so two processes must reside on the same host.

Another important fact is that IPC is only be used by having the Client connect

through the Oracle Listener.

If the Listener is bypassed, the Client and Server processes will use the

(BEQ) Bequeath protocol. Refer Oracle metalink note: IPC Explained [ID

29232.1]

Overview of LISTENER

The Oracle Listener (process) is part of the Oracle network.

This process is only needed on the database server side

The listener is controlled via the lsnrctl utility

The Listener configuration is done via listener.ora

A listener is required for allowing remote (not local) clients to connect to the

Oracle database via the network.

LSNRCTL :- (Listener Control)


http://www.orafaq.com/wiki/Lsnrctl


lsnrctl is an SQL*Net utility used for controlling database listeners.

lsnrctl controls listener functions such as (starting/stopping listeners), and

reporting status of the listeners.

To invoke "lsnrctl" utility from the command line.

$ lsnrctl

LSNRCTL for Linux : Version 10.2.0.1.0 - Production on 02-MAR-2013 09:42:19

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command :

start stop status

services version reload

save_config trace spawn

change_password quit exit

set* show*

POINTS TO NOTE :

When a client requests a connection to the DB, (If it is remote to the server)

Clients first contact a listener process ( typically resides on DB Server ), The

listener primary purpose is receives incoming client connection requests and

sends these requests to the DB Server. After the connection is established, the

client and database server communicate directly.

We can configure multiple listeners, each with unique name , in listener.ora

file. Listener configuration stored in listener.ora file.



$ cat listener.ora (Files edits are in RED )

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME= orcl.orgdb.com)

(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)

(SID_NAME=orcl )

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =MYSERVER)(PORT = 1521))

)

)

DB_NAME and DB_DOMAIN parameters combine to create the global

database name and the value assigned to service_names parameter.

LSNRCTL Commands

Start a listener

Stop a listener

Reload a listener

Checking listner status



Checking Registered Services

To Start a listener

lsnrctl> start listener_name

To Stop a listener

lsnrctl> stop listener_name

Reload a listener

lsnrctl> reload listener_name

To reload a database listener (stop and restart without disrupting the service).

Check listener status

lsnrctl> status listener_name

Status about the listener, including start date, uptime and trace level.

Check registered services

lsnrctl> services listener_name

To see what database services is registered with a listener:

lsnrctl> version

Displays the version information of the listener.

NETCA

NETCA stands for NETwork Configuration Assistant.

NETCA utility is used to configure SQL*Net.



Screen shots are taken from an OEL system with Oracle 10gR2.

Select add

The default name is LISTENER. (if we wish to change , it is possible)



Select which protocols the Listener will use. TCP is selected by default.

Click Next ..



The default port number used by an Oracle Listener is 1521

No Option is selected and click the Next button to continue.



Click Next ..

The Listener configuration is complete. Click the Next button to complete the

Listener configuration wizard and to return the welcome page.



The Listener configuration is completed successfully and that the Listener was

started. Here the listener name is LISTENER_ORCL .

We can validate that the Listener is running by issuing lsnrctl

status. In oracle Terminology , there are two ways database instance

registering with the listener. They are STATIC/DYNAMIC instance

registration.



Starting up and shutting down the oracle listener is a routine task for a

database administrator. Let us check how to start, stop, check status of an

oracle listener using Oracle listener control utility LSNRCTL

Before starting, stopping or restarting a listener, manually need to execute

lsnrctl status command to check the oracle listener status.

If Oracle Listener is not running ; [ Error Message ]

$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-MAR-2013 21:02:08


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

Oracle listener is not running , so manually start the listener

$ lsnrctl start listener_orcl



Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production

System parameter file is

/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Log messages written to



/u01/app/oracle/product/10.2.0/db_1/network/log/listener_orcl.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)

(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxserver)

(PORT=1521)))

STATUS of the LISTENER

Alias listener_orcl

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 08-MAR-2013 21:24:34

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File

/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File

/u01/app/oracle/product/10.2.0/db_1/network/log/listener_orcl.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))

Services Summary...

Service "MYDB.PROD.ORCLPROD.COM" has 1 instance(s).

Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...

Service "MYDB.TEST.ORCLTEST.COM" has 1 instance(s).

Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully



Listener name is not case sensitive. i.e listener_ORCL= listener_orcl ;

Listener is running and I am going to stop the listener

$ lsnrctl stop LISTENER_ORCL




(PORT=1521)))


Reloading the listener

$ lsnrctl reload LISTener_ORcl




(PORT=1521)))


Checking Registered Services with Listener

$ lsnrctl services



Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "MYDB.PROD.ORCLPROD.COM" has 1 instance(s).



Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:3 refused:0

LOCAL SERVER

Service "MYDB.TEST.ORCLTEST.COM" has 1 instance(s).

Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:8 refused:0

LOCAL SERVER


Listener help Show

$ lsnrctl help show



The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

rawmode displaymode

rules trc_file

trc_directory trc_level

log_file log_directory

log_status current_listener

inbound_connect_timeout startup_waittime

snmp_visible save_config_on_stop

About HOST=localhost/127.0.0.1



linux> # cat /etc/hosts 127.0.0.1 xxxx xxxxx localhost

If listner configuration is specified "HOST=localhost" or "HOST=127.0.01".

This is a NONROUTABLE IP address .

In # cat etc/ hosts file LOCALHOST and IP ADDRESS 127.0.0.1 always

mean "This machine on which I am sitting". So, all computers are known as

"localhost" or "127.0.0.1".

FYI - No remote client can ever connect to DB Server

using 127.0.0.1

NOTE : If we specify this address, the listener will only be capable of receiving

requests from the machine on which it is running.

Some Points to Remember

The listener is a server side process only.

It creates the connection. It doesn't sustain the connection.

It's entire purpose is to receive requests for connections to databases and set

up those connections. Once the connection is established, the listener is out of

the picture.

Configure Naming Methods : ( Client )

Lets us configure connectivity for client connections to the database server.

Oracle Net supports four(4) naming methods.



Local Naming

External Naming

Directory Naming

Easy Connect Naming ( EZCONNECT)

EZCONNECT : EZCONNECT is Oracle's easy connect naming method for TCP/IP

environments. This method enables clients to connect to a database server

without any configuration.

LOCAL NAMING : A net service_name stored in tnsnames.ora on a client.

The local naming method stores connect descriptors, identified by their net

service name, in a configuration file (tnsnames.ora).

Connection Requests

When users initiate a connection request , should provide connect string;

A connect string includes a username and password, and a connect identifier;

One of the most common connect identifiers is a net service name;

Here , connect string that uses the net service name;

An example of a Complete Connection String could be scott/tiger@orcl .

SQLPLUS username/password@net_service_name

CONNECT username/password@net_service_name



“@” symbol is used to mark the beginning of the connect identifier so the

connect identifier is “orcl”.

When net service name is used , connection processing first mapping to the

connect descriptor. The mapped information that are accessed with naming

methods.

Understading the TNSNAMES.ORA File

The tnsnames.ora is a network configuration file that contains

net_service_name mapped to connect descriptor for the local

naming method. The tnsnames.ora file is used by clients to identify

server destination.

By default , tnsnames.ora is located in the

$ORACLE_HOME/network/admin directory on UNIX/LINUX operating

systems.

Connect Descriptor:

Connect Descriptor (TNS Connect String).

When a Client needs to connect an Oracle Server, should supply three

pieces of information USERID/PASSWORD@NET_SERVICE_NAME.

The net service name provides all necessary information, in the form of a

connect descriptor. The CONNECT DESCRIPTOR usually having two

parts .

ADDRESS DATA Section

CONNECT_DATA Section



( DESCRIPTION=

( ADDRESS=(address specific things) )

( CONNECT_DATA= (connect data specific things ) ))

The Address data part contains PROTOCOL/HOST/PORT.

The Connect data part contains SERVER/SERVICE_NAME.

The service_name can be same as ORACLE_SID or Global_dbname (defult).

A Connect Descriptor mapping to simple name : ORCLMYTEST

ORCLDB , orcltest =

(DESCRIPTION=

(ADDRESS_LIST =

(ADDRESS= (PROTOCOL=TCP) (HOST=MYSERVER) (PORT=1521)) ) (CONNECT_DATA=

(SERVICE_NAME= orcl.orcldb.com)

(SERVER=DEDICATED )

))

In this example, the destination service is a DB service named orcl.orgdb.com.

A Complete Connect Descriptor

CONNECT scott/tiger@(DESCRIPTION=(ADDRESS=

(PROTOCOL=tcp) (HOST=MYSERVER) (PORT=1521))

(CONNECT_DATA= (SERVICE_NAME= orcl.orgdb.com)))

Example for tnsnames.ora file File edits are in RED



ORCLDB , orcltest = TNS alias name

DESCRIPTION =

(ADDRESS_LIST=

(ADDRESS = (PROTOCOL = TCP) (HOST=MYSERVER)

( PORT = 1521)) must, match port in listener.ora

)

(CONNECT_DATA =

(SERVER=DEDICATED)

(SERVICE_NAME =orcl.orcldb.com)

)

)

POINTS TO NOTE

The net_service_names are ‘ORCLDB , ‘orcltest ’.

The service_name is obviously "orcl.orcldb.com" .

'net_service_name' is just another term , same thing as 'tns_alias_name'.

Oracle provides for the ability to have multiple tnsnames aliases for connection

to the same database instance in a standard TNSNAMES.ORA.

POINTS TO REMEMBER

Oracle Net Services , enables a network session from a client application to

an Oracle Database server. Oracle Net is responsible for establishing and

maintaining the connection between the client application and database,

as well as exchanging messages between them.



Oracle Client Software is required on workstations.

A client uses a service name to identify the database.

Service name is an identifier of a database service.

Clients use a connect identifier in the connect string to connect to a service.

The connect identifier (sales) that maps to a connect descriptor.

A connect descriptor mapped to simple name called sales.

A connect descriptor is comprised of PROTOCOL/HOST/PORT and the connect

information for the destination service.

sales=

(DESCRIPTION=

(ADDRESS= (PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))

(CONNECT_DATA= (SERVICE_NAME=sales.us.example.com)))

If we don’t specify service_name for the database , the service name is default

to the database's global_name; DB_NAME parameter and the DB_DOMAIN

name parameter combine to create the global database name and value

assigned to the SERVICE_NAMES in the initialization parameter file.

SQLPLUS system/oracle@MYORCL

system/oracle – is the username/password MYORCL – an alias.


http://docs.oracle.com/cd/B13789_01/network.101/b10775/glossary.htm#i1000044

http://docs.oracle.com/cd/B13789_01/network.101/b10775/glossary.htm#i434365


@ – sign indicates that you want to connect to a remote database.

sqlplus – is the binary executable tool you can use to connect to Oracle.

The tnsnames.ora file that will point to the database server to connect. In

order to allow remote connections to an Oracle database the database must

be registered with an Oracle Listener.

When an instance , it registers itself with a listener using one or more service

names. PMON process registers instance information with a listener.

A listener is required for allowing remote (not local) clients to connect to

the Oracle database via the network. A listener.ora file contains

Name of the listener

Database services

Protocol addresses that the listener is accepting connection requests

Address to make remote connection (hostname, listenerport)

The listener knows which services can handle connection requests because an

Oracle database dynamically registers this information with the listener.

This process of registration is called service registration.

If the client is on the same machine as the db instance, it is possible to

connect without referencing tnsnames and without going through the

listener. Now, we can issue a connect , sqlplus scott/tiger

Tnsnames.ora and Listener.ora



PING and TNSPING

Using ping command , we can verify client connectivity.

If ping doesn’t work, then client is having network issues, and there is no

problem with the server. When a web browser fails to connect to a web site on



the Internet or LAN (local area network) the cause is often the web server,

which may be down or overloaded or network failiure.

TNSPING

tnsping is a utility in the $ORACLE HOME/bin directory.

tnsping does only tries to reach (ping) the listener.

tnsping is used to test (SQL*Net) connect string can connect to a listener.

tnsping tests the connection is okay ( tnsping will only report if the

listener process is up ) and the port is open. It doesn’t guarantee that

the database is up and running. It doesn't try to connect to the database . The connection can be established from a client to a server (or server to server).

To Invoke the TNSPING Utility using net service_name

$ tnsping <net_service_name>

net_service_name must exist in tnsnames.ora file.

If the net service name specified , tnsping utility attempts to contact the

corresponding listener. It doesn’t actually determine whether or not the

database itself is running.

$ tnsping <net_service_name>

$ tnsping ORCLTEST

$ tnsping <net_service_name> <count>



$ tnsping ORCLTEST 5

The “count” parameter is optional and will show the number of times the

command try to connect to the specified net_service_name. The tnsping

reports the number of milliseconds it took to reach the remote service.

TNSPING Utility Using net _ service _ name

$ tnsping ORCLTESTDB

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 13-MAR-2013

05:13:23


Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =

linuxserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)

(SERVICE_NAME = orcltest)))

OK (10 msec))

TNSPING Utility Using net _ service _ name with Count

$ tnsping orclproddb 4

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 13-MAR-2013


Used parameter files:

Used TNSNAMES adapter to resolve the alias



Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =

linuxserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)

(SERVICE_NAME =MYDB.PROD.ORCLPROD.COM)))

OK (10 msec)

OK (10 msec)

OK (10 msec)

OK (20 msec)

Some Additional Information about TNSNAMES.ORA

The tnsnames.ora is a network configuration file for the local naming

method on client side. This file is used by clients used to identify server

destinations. In this file net service name s mapped to connect

descriptor. connect descriptor contains two sets of data ; They

are

ADDRESS_LIST and

CONNECT_DATA

DESCRIPTION -- ADDRESS_LIST ----ADDRESS ------PROTOCOL=TCP

------HOST=LINUXSERVER ------PORT=1521 ----CONNECT_DATA ----

SERVER=DEDICATED ------SERVICE_NAME=orcltest

ADDRESS describes how to connect to the server;

CONNECT_DATA describes how to connect to a specific database instance;

SERVER=DEDICATED tells the client to make a direct connection to the server.

General Syntax of tnsnames.ora



Basic syntax for a tnsnames.ora file

net_service_name=

(DESCRIPTION=

(ADDRESS=(protocol_address_information))

connect_descriptor

(CONNECT_DATA=

(SERVICE_NAME=service_name)))

In this ex : DESCRIPTION contains the connect descriptor, ADDRESS

contains the protocol address, and CONNECT_DATA contains the

database service

identification information.

Oracle Net Naming Methods

In Oracle 10g , Oracle Net provides four naming methods. They are

Local Naming

Directory Naming

Easy Connect Naming

External Naming

LOCAL NAMING

The local naming method stores connect descriptors, identified by their net

service name, in a configuration file (tnsnames.ora) on the client side.

EASY CONNECT NAMING (Central with an LDAP)

EZCONNECT is Oracle's Easy Connect naming method.Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu


The easy connect naming method requires no configuration.

The easy connect naming method eliminates the need for service name lookup

in the tnsnames.ora files for TCP/IP environments.

To Invoke EZCONNECT Method

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

SQL> CONNECT hr/hr@my-server:1521/mydb

HOST/PORT/SERVICE_NAME are required to connect EZCONNECT method.

DIRECTORY NAMING

The directory naming method stores connect identifiers in a centralized, LDAP-

Compliant dir server. Ths is apt for large org with several databases.

DATABASE Connectivity using Easy Connect Method


http://gerardnico.com/wiki/database/oracle/tnsnames.ora


DATABASE Connectivity using net_service_name : ( TCP -

Protocol )



DATABASE Connectivity using net_service_name : ( IPC -

Protocol)


oracle network configuration

Documents

sqlnet configuration

oracle dba

local naming

sqlnet protocol

database local

oracle client

oracle database

network configuration