The Application DBA  

Network Configuration

The following are sample network configuration files. The tnsnames.ora, sqlnet.ora and ldap.ora if used, should be located in a directory outside of the Oracle home directories since they are not Oracle home dependent. The files should then be symbolically linked back to the $ORACLE_HOME/network/admin directory for each $ORACLE_HOME supported. There is no administrative advantage to relocating the listener.ora and any snmp*.ora files since they are Oracle home dependent and have ORACLE_HOME directories embedded in them and some functions don't follow the symbolic links.

Although service registration happens dynamically, there are situations where static registration through the listener.ora is desirable. In general, creating databases is a big enough effort that adding a static listener entry to the listener.ora sid list is not that big a deal.

The service name defaults to the global database name, but caution must be exercised or you will spend a lot of time troubleshooting. The following parameters are all inter-related and depending on their settings some things may work and some may not.

Initialization Parameters

  • db_name
  • db_domain
  • global_name
  • service_names
  • local_listener
  • remote_listener

Database Parameters

  • global_name

listener.ora Parameters

  • global_dbname
  • sid_name

tnsnames.ora Parameters

  • service_name
  • instance_name
  • sid

Search Order

The search order for the listener.ora, sqlnet.ora and tnsnames.ora files are documented in Oracle MOS Note 464410.1.
For the sqlnet.ora file the search order is:

  • .sqlnet.ora in the client home directory
  • The directory specified by the TNS_ADMIN environment variable
  • The $ORACLE_HOME/network/admin directory.

The search order for the cman.ora, listener.ora, tnsnames.ora is:

  • .tnsnames.ora in the client home directory
  • The directory specified by the TNS_ADMIN environment variable
  • Solaris: /var/opt/oracle; other platforms: /etc
  • The $ORACLE_HOME/network/admin directory

The search order for the ldap.ora file (see Oracle MOS Note: 363283.1):

Version 8i: TNS_ADMIN can not used. Search path order is $ORACLE_HOME/network/admin
Version 9i: TNS_ADMIN can be used. Search path order is TNS_ADMIN if set, Then ORACLE_HOME/network/admin
Version 10.1: TNS_ADMIN can not used. Search path order is LDAP_ADMIN if set, ORACLE_HOME/ldap/admin and then ORACLE_HOME/network/admin
Version 10.2: TNS_ADMIN can be used. Search path order is LDAP_ADMIN if set, ORACLE_HOME/ldap/admin, TNS_ADMIN if set and then ORACLE_HOME/network/admin
Version 11g: TNS_ADMIN can be used. Search path order is LDAP_ADMIN if set, ORACLE_HOME/ldap/admin, TNS_ADMIN if set and then ORACLE_HOME/network/admin.

listener.ora


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      (ADDRESS = (PROTOCOL = IPC)(KEY = <DBSID>))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>.<db_domain>)(PORT = 1521))
    )
  )
#
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
LOGGING_LISTENER = ON
LOG_DIRECTORY_LISTENER =   /u01/app/oracle/admin/snet/log
LOG_FILE_LISTENER = listener.log
TRACE_LEVEL_LISTENER = OFF
TRACE_DIRECTORY_LISTENER =   /u01/app/oracle/admin/snet/trace
TRACE_FILE_LISTENER = listener.trc 
#
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = <DBSID>.<db_domain>)
      (SID_NAME = <DBSID>)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      # (PRESPAWN_MAX = 50)
      # (PRESPAWN_LIST = 
      # (PRESPAWN_DESC = (PROTOCOL   = TCP) (POOL_SIZE = 10) (TIMEOUT = 12))
      # )
    )
  )
#
#----ADDED BY TNSLSNR 14-MAR-2005 14:33:40---
PASSWORDS_LISTENER =   B1BE1D6C11A248C5
#--------------------------------------------

Basic listener.ora File


# listener.ora Network Configuration File:
#
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = <DBSID>))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>.<db_domain>)(PORT = 1521))
    )
  )
#
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = <DBSID>.<db_domain>)
      (SID_NAME = <DBSID>)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
  )

Password Protected Listener

The listener(s) can be password protected. This is accomplished with the "change_password" command followed by the "set password" and "save_config" commands from within the lsnrctl command environment:


LSNRCTL> help change_password
change_password [<listener_name>]:   changes the password of the listener
 
 
LSNRCTL> change_password
Old password: 
New password: 
Reenter new password: 
Connecting to   (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>.<db_domain>)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL> set password
Password: 
The command completed successfully
LSNRCTL> save_config
Connecting to   (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>.<db_domain>)(PORT=1521)))
Saved LISTENER configuration   parameters.
Listener Parameter File     /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
Old Parameter File     /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.bak
The command completed successfully
LSNRCTL>

Notes:

1) Prior to 10g, all listener commands must be issued in the format:


LSNRCTL> set current_listener LISTENER (optional)
LSNRCTL> set password <unencrypted or encrypted password>
LSNRCTL> <command>

2) In order for the "save_config" command to run, the listener.ora file must be located in the $ORACLE_HOME/network/admin directory. If this file is linked somewhere else, the command will fail.

3) In Oracle 10g, OS authentication is supported and listener commands can be issued from the database account without needing the password.

4) If using the Open Source utility "dbcontrol" then it should also be updated. In the DEPVAR function the line "LISTENER_pwd" should be updated with the encrypted password from the listener.ora file.

ldap.ora

The ldap.ora is used to identify the connection to an Oracle Internet Directory server.


DIRECTORY_SERVERS=  (<oid server.domain>:389:636)
DEFAULT_ADMIN_CONTEXT  = "dc=appsdba,dc=com"
DIRECTORY_SERVER_TYPE  = OID

sqlnet.ora


DISABLE_OOB = ON
TRACE_DIRECTORY_CLIENT = /u01/app/oracle/admin/snet/trace
LOG_DIRECTORY_CLIENT = /u01/app/oracle/admin/snet/log
LOG_DIRECTORY_SERVER = /u01/app/oracle/admin/snet/log
SQLNET.EXPIRE_TIME = 100
NAMES.DEFAULT_DOMAIN = company.com
NAME.DEFAULT_ZONE = company.com
NAMES.DIRECTORY_PATH= (TNSNAMES)
# Needed for Windows NT/2000
# SQLNET.AUTHENTICATION_SERVICES=(NTS)
 
USE_DEDICATED_SERVER = ON
DISABLE_OOB = ON
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF  
TRACE_DIRECTORY_CLIENT = /u01/app/oracle/admin/snet/trace
TRACE_FILE_CLIENT = sqlnet.trc
TRACE_LEVEL_SERVER = OFF  
TRACE_DIRECTORY_SERVER = /u01/app/oracle/admin/snet/trace
TRACE_FILE_SERVER = sqlnet_srvr.trc
LOG_DIRECTORY_CLIENT = /u01/app/oracle/admin/snet/log
LOG_FILE_CLIENT = sqlnet.log
SQLNET.EXPIRE_TIME = 240
#
NAMES.DEFAULT_DOMAIN = company.com
NAME.DEFAULT_ZONE = company.com
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

Basic sqlnet.ora File


# sqlnet.ora Network Configuration File
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
NAMES.DEFAULT_DOMAIN = <db_domain>

tnsnames.ora


<SID> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = <SID>))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>.<db_domain>)(Port = 1521))
    )
    (CONNECT_DATA =
      #(SID = <SID)
      # or
      (SERVICE_NAME = <SID>.<db_domain>)
    )
  )
#
<SID> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>.<db_domain>)(Port = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <SID>.<db_domain>)
    )
  )
#
<SID>_BEQ =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = BEQ)
      (PROGRAM = oracle)
      (ARGV0 = oracle<SID>)
      (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
    )
    (CONNECT_DATA =
      (SID = <SID>)
      # or
      #(SERVICE_NAME = <SERVICE>.<db_domain>)
    )
  )
#
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Basic tnsnames.ora File


# tnsnames.ora Network Configuration File
DBSID =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = DBSID))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>.<db_domain>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DBSID.<db_domain>)
    )
  )

RAC

Transparent Application Failover (TAF)

To enable TAF the CONNECT_DATA clause is modified. The following shows a basic TAF configuration:

DBSID_TAF  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>.<db_domain>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DBSID.<db_domain>)
      (FAILOVER_MODE=
        (TYPE=select) 
        (METHOD=basic))
    )
  )

VIPs

Pre 11.2 RAC connect strings typically listed each VIP and used the LOAD_BALANCE and FAIL_OVER parameters:

DBSID =
  (DESCRIPTION=
    (LOAD_BALANCE=on) 
    (FAILOVER=on) 
    (ADDRESS=(PROTOCOL=tcp)(HOST=<host1 vip>)(PORT=1521)) 
    (ADDRESS=(PROTOCOL=tcp)(HOST=<host2 vip>)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=<host3 vip>)(PORT=1521))
    (CONNECT_DATA=
      (SERVICE_NAME=DBSID.<db_domain>) 
      (FAILOVER_MODE=
        (TYPE=select) 
        (METHOD=basic))
    )
  )

SCAN

With 11.2 just the SCAN name is needed, but be careful because pre 11.2 clients may have problems with this format.

DBSID =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=<SCAN name>)(PORT=1521)) 
    (CONNECT_DATA=
      (SERVICE_NAME=DBSID.<db_domain>) 
      (FAILOVER_MODE=
        (TYPE=select) 
        (METHOD=basic))
    )
  )

Global DBName

The global_dbname parameter in the SID_LIST section of the listener.ora creates a static service name for the database specified. Normally if this is used the parameter should specify  SID.hostname.domain, which is also normally the global database name.

However if using connect-time failover or TAF then this parameter should not be set.

This parameter is also used for Data Guard during instantiation:

From Note: 308943.1

There needs to be a correct entry of the GLOBAL_DBNAME in the SID_LIST of the listener (in the listener.ora file) for the Primary and Standby Databases for usage with DGMGRL.

The GLOBAL_DBNAME must have the Format:

GLOBAL_DBNAME = db_unique_name_DGMGRL.db_domain

So a SID_LIST-Entry may look like this:

(SID_DESC =
  (GLOBAL_DBNAME = DG1_DGMGRL.world)
  (SID_NAME = DG1)
  (ORACLE_HOME = /oracle/ora1010)
  (SERVICE_NAME = DG1.world)
)

Local Listener

When using the default port of 1521 database services can automatically register with the listener, otherwise the LOCAL_LISTENER init.ora parameter needs to be set to insure that all services can register with the listener.

NOTE: If the local listener is set to a tnsnames alias then that alias must exist or the database will not start.

Remote Listener

The REMOTE_LISTENER init.ora parameter is used to register with a listener on another host. The Oracle connection manager listener is a common example or in RAC the SCAN listener.

NOTE: If the remote listener is set to a tnsnames alias then that alias must exist or the database will not start.

Bequeath Connections

Bequeath connections are typically made with the ORACLE_SID environment variable set. Bequeath connections do not connect through the listener and do not incur a network protocol overhead. “Oracle's Bequeath protocol internally spawns a server process for each client application. In a sense, it does the same operation that a remote network listener does for your connection, yet locally.”

It is possible to connect to a service with a bequeath connection. This can be useful for capturing and classifying the workload of long running processes. The following shows a tnsnames.ora entry that allows a bequeath connect to connect to a specific service.


ORADB_BEQ = 
  (DESCRIPTION = 
    (ADDRESS =
      (PROTOCOL = BEQ) 
      (PROGRAM = oracle) 
      (ARGV0 = oradb)
      (ARGS =  '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
    ) 
    (CONNECT_DATA =
      (SERVICE_NAME = backup.oradb.appsdba.com) 
    ) 
  ) 

IPC Connections

IPC connections should be enabled so that server based connections (i.e. local on the database server) don’t incur the overhead of the tcp network layer. This is simple to do if you remember that the IPC key is case sensitive. The following shows listener.ora and tnsnames.ora syntax that support local IPC connections for the “oradb” database.

listener entry:


LISTENER  =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = ORADB))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oravm.appsdba.com)(PORT = 1521))
    )
  )

tnsnames entry:


ORADB  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = ORADB))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oravm.appsdba.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb.appsdba.com)
    )
  )

Note: the IPC key has to match and it is case sensitive.

Database Links

Database links should be created at as high a level as possible and with location transparency a primary goal. This means that a global database link should be the preferred connection to another database over a private database link. Ideally connections should be to the same username/password on the remote database to avoid needing to code a private database link.

Connect strings should only use tns aliases to avoid having to change the actual database link, which means dropping and recreating them, when connect strings change.

The database link name should match the target database’s global name. This is required if the global_names initialization parameter is set to true, but is a good practice in any case. The global name should reflect the database function and not necessarily the actual SID. An example:

Global name = oradb.appsdba.com
SID name(s) = oradb1, oradb2
Database name = oradb

In this example database links connecting to the oradb database should be named “oradb.appsdba.com” which matches the global name. An example database link definition might be:

create database link oradb.appsdba.com
connect to user1 identified by password
using 'oradb';

Shared Database Links

A shared database link uses a shared server process on the local database to connect to a shared or dedicated user process on the remote database.

Location Transparency

A database link should have the same name as the target database's global name. If the database is cloned or renamed then the database link name may no longer make sense if the actual database name is used.

Database links should never be referenced directly in objects or code. They should always be referenced through a synonym that defines the remote object(s). In other words, in a view or in PL/SQL you should not have a query like this:

select count(*) from dba_hist_snapshot@remotedb;

It should be:

select count(*) from dba_hist_snapshot_remote;

Where dba_hist_snapshot_remote is defined as:

create synonym dba_hist_snapshot_remote for dba_hist_snapshot@remotedb;

When performing joins on remote objects it is usually advantageous to perform as much of the join work as possible on the remote database. This can be accomplished by creating a view on the remote database with the join SQL and then accessing the view through a database link. Note that this is not always possible, but should be considered and tested.

Connection Qualifiers

Connection qualifiers allow you to specify an optional service name in the database link name. This is most useful in allowing the use of different connection strings connecting to the same database. This might be the case if using application partitioning and needing to connect to specific RAC nodes (this could also be accomplished using private database links and connect strings that use specific services).

create database link oradb.appsdba.com
using 'oradb';

create database link oradb.appsdba.com@node1
using 'oradb1';

create database link oradb.appsdba.com@node2
using 'oradb2';

 

Oracle9i Installation and Database Administration Guide, Release 2 (9.2.0.2.10) for Fujitsu Siemens Computers BS2000/OSD, Part Number B12034-01

 

Copyright İ 1998-2013 AppsDBA Consulting. All Rights Reserved.