How-to: Oracle Networking - TNSNames, Listener.ora

A quick overview showing how to connect an Oracle client to an Oracle server.

The basic steps are

Set a global_name for the database
Configure the Listener (Listener.ora)
Configure the client (Tnsnames.ora)
Optional - Configure Oracle Enterprise Manager (services.ora)

1. Set a global_name for the database

When creating a database with 'Oracle Database Configuration Assistant' (DBCA) specify a new, unique global_name.
In the example below I’m using the domain ".ss64.com" you should change this for your own domain name:

Many small and medium sized companies do everything under a single domain name, in which case these global names will seem like unnecessary work but if you do end up managing multiple domains at some point in the future (e.g. if your company is merged with another company) then having global names in place will make things much easier to manage.

To change the Global_Name of an existing database:

ALTER DATABASE rename global_name to demo.example.com;

2. Configure the Listener

Run 'Oracle Net Manager' on the server, choose Database Services - Add Database and fill in the global_name.

If this fails to work, rename Listener.ora to Listener.OLD and then run 'Oracle Net Manager' again.

File, Save will save this configuration into the Listener.ora file.

Then load Listener.ora into memory to make the settings active

From the OS command line LSNRCTL reload

Alternatively stop and start the listener completely:
LSNRCTL stop
LSNRCTL start

3. Configure the client

The TNSNAMES.ORA file is a client side file (typically on a remote PC).
The client uses the TNSNAMES.ORA file to obtain connection details for the desired database. (Of course often you will want to install client software on the server as well for testing with SQL*Plus etc but that is optional.)

On a client PC run the 'Oracle Net Configuration Assistant' choose Local Net Service Name (TNSNames)

Specify a global_name that exactly matches the listener settings and fill in the other prompts.

These settings will be saved in $oracle_home/network/admin/Tnsnames.ora

At this point you should be able to connect to the database with sql*plus

SQL > connect scott/tiger@Live.ss64.com

If it fails, rename Tnsnames.ora to Tnsnames.OLD and run the Assistant again.

4. Configure Oracle Enterprise Manager

Start the Oracle Agent service on the server.

If it’s already running, stop and restart it.

If the agent fails to start, Backup and Delete the following and try again:
$Oracle_home/network/agent/*.Q
$Oracle_Home/network/admin/SNMP_RO.ora
$Oracle_Home/network/admin/SNMP_RW.ora,
$Oracle_Home/network/admin/dbsnmp.ver
$Oracle_Home/network/admin/services.ora

On the client, Start the OEM Console, the default OEM administrator is "sysman" with a password of "oem_temp".
Navigate to menu "Navigator/ Discover Nodes"
In case of problems you can delete the Node from the OEM client and re-discover it.

If you create a database using DBCA, you’ll find some of the settings above will have been set automatically.

This page only scratches the surface of Oracle network configuration, full documentation can be found at docs.oracle.com

SQLNET.ora

Other options can be set in the $oracle_home/network/admin/sqlnet.ora file:

names.default_domain = your_network_domain_name

When you set a default domain name (for example, ss64.com), that domain name will be automatically appended to any unqualified name, so typing @Live will be interpreted as @Live.ss64.com To force a lookup without the DEFAULT_DOMAIN being appended, append a dot to the tnsname: TNSPING LIVE.

names.directory_path = (TNSNAMES,ONAMES,HOSTNAME)

A list of naming adaptors to be used when resolving a name. These will be used in the order listed.
TNSNAMES = tnsnames.ora file, ONAMES = Oracle Names, HOSTNAME = use the hostname, NDS = Novell Netware, NIS, CDS = OSF DCE's Cell Directory Service

log_directory_client = /oracle/network/log

The directory to which client log file will be written.
If you notice SQLNET.LOG files littering your disk and turning up in many directories, this is because the default value of LOG_DIRECTORY_CLIENT is the current working directory. Specify one folder here and you will tidy things up.

log_directory_server = /oracle/network/log

The directory to which log files from the server are written

sqlnet.authentication_services = NONE

Password authentication services: {beq, none, all, NTS, kerberos5, cybersafe, radius}

trace_level_server = OFF

The level at which the server program is to be traced: {OFF,USER,ADMIN,SUPPORT, 0-16}

trace_directory_server = /oracle/network/trace

The name of the directory to which trace files from the server are written

trace_level_client = OFF

Indicates the level at which the client program is to be traced in the log file.
Possible values: {OFF,USER,ADMIN,SUPPORT, 0-16}
Default: OFF (0)

trace_directory_client = /oracle/network/trace

The directory to which trace files from the client are written.

tnsping.trace_level = OFF

The level at which TNS is to be traced: {OFF,USER,ADMIN,SUPPORT, 0-16}

sqlnet.expire_time = 10

Verify the client session is alive every n minutes (reclaim resources on a dead client)

sqlnet.connect_timeout = 5

Maximum time interval (in seconds) for the database session to establish a connection between a client and server. This can be used to limit the effect of Denial of Service or brute force dictionary attacks. ( 0 = off)

Related

Common Oracle error codes
Oracle error ORA-12154
agentctl, lsnrctl, oemctl