Oracle Local Naming


Local Naming uses a configuration file local to each client machine called tnsnames.ora. This configuration allows specific tuning for each client machine. A client process requests a connection using a network service name. The tnsnames.ora file contains the connect descriptor (connection string) for the network service name. The connection string retrieved from the tnsnames.ora file is then used by the client to execute the connection request to the database server. The listener on the server will service the request by passing the client process the address of the database server process required to make the database connection.

A typical Net8 Tool generated file looks as below. Note the second name is declared to allow for calling of external libraries from within PL/SQL code, typically PL/SQL stored procedures. The Net8 Configuration Assistant tool is $ORACLE_HOME/bin/netasst.

<local-name> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>l | <hostname>.xyz.com | <IP-Address>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <SID>)
    )
   )

EXTPROC_CONNECTION_DATA.<hostname>.xyz.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

The example below contains two Net8 names connect descriptors. The first connect identifier, TEST, uses MTS and a shared server process. The second connect identifier is a connection to a Recovery Manager (RMAN) database which requires a dedicated server, non-MTS connection.

<SID> =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = <SID>) (SERVER = SHARED))
  )

RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = RMAN) (SERVER = DEDICATED))
  )

The example below contains multiple addresses utilising multiple protocols. The first is an SPX connection to Oracle Connection Manager and the second is a shared server, MTS configured connect identifier.

<SID> =
  (DESCRIPTION =
    (SOURCE_ROUTE = YES)
    (ADDRESS = (PROTOCOL = SPX) (SERVICE = cman))
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = <SID>) (SERVER = SHARED))
  )

Local Naming Parameters

Local naming parameters reside in the $ORACLE_HOME/network/admin/tnsnames.ora parameter file. The tnsnames.ora file contains aliases for databases. These aliases map to a database service using a connect descriptor. Also within each connect descriptor there can be multiple listener addresses such as for load balancing and failover between listeners and database services as in the example below.

<name> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (FAILOVER = ON)
      (LOAD_BALANCE = ON)
      (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1522))
    )
    (CONNECT_DATA = (INSTANCE_NAME = <SID>) (SERVICE_NAME = <SID>.<Domain name>))
  )

Mandatory Connect Descriptor Attributes

Optional Connect Descriptor Attributes

TNSPING and TRCROUTE

Network names can be tested using the TNSPING utility from a client node (TNSPING name). The TRCROUTE utility can be used to trace a route through a network when a particular network name is accessed. TRCROUTE will collect TNS addresses for every node travelled through thus providing node addresses of nodes causing any routing errors.