TNSNAMES.ORA

The TNSNAMES.ORA file is normally located in:

$ORACLE_HOME/network/admin/tnsnames.ora

An alternate location can be specified by the TNS_ADMIN environment variable or Registry key.

The places where Oracle looks for the tnsnames.ora file...

  • Location specified by TNS_ADMIN enviornment variable

  • Location specified by TNS_ADMIN Registry Key

  • ORACLE_HOME/network/admin

  • The working directory (this seems to be tool dependent. i.e. it doesn't always happen)

TNS_ADMIN

UNIX/Linux


export TNS_ADMIN=/etc/oracle

Windows


  • Windows Registry

  • TNS_ADMIN

  • %ORACLE_HOME\network\admin

  • Working directory

Windows uses this order of precedence...

  1. Local session environment variable.

  2. Global environment variable.

  3. User registry setting under HKey_Current_User\Software\Oracle (or individual Oracle home registry entry.)

  4. System registry setting under HKey_Local_Machine\Software\Oracle (or individual Oracle Home registry entry.)

Example Configurations

Regular Client Configuration (Dedicated Server)

"Dedicated Server" means there is a 1-to-1 relationship between user processes and server processes.

You can specify a SID instead of a SERVICE_NAME if necessary:(SID=MYSID) You should favour SERVICE_NAMEs where possible.

MYDB=

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYDB))

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

)

ENABLE=BROKEN is a key setting for Dead Connection Detection (DCD). It is the client side equivalent of SQLNET.EXPIRE_TIME on the server. For full details see Franck Pachot's excellent exploration here:https://blog.dbi-services.com/sqlnet-expire_time-and-enablebroken/

Regular Client Configuration (Shared Server)

"Shared Server" means Server Processes can be shared by more than one user process.

TODO

Example MGW Configuration

  • Assumes names.default_domain in SQLNET.ORA is not set

MGW_AGENT=

(DESCRIPTION=

(CONNECT_DATA=(SID=mgwextproc))

(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))

)

DataGuard Physical Standby SERVER Configuration

  • Ensure the Standby TNSNAMES.ORA includes stanzas for both the Standby and Primary database.

  • Ensure the Primary TNSNAMES.ORA includes stanzas for both the Standby and Primary database.


(UR=A) enables the standby database to be connected to when it is in RESTRICTED or NO MOUNT state.

PRIMARYDB=

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRIMARYDB))

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

)

STANDBYDB=

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STANDBYDB)(UR=A))

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

)

DataGuard Physical Standby CLIENT Configuration

Option #1

This configuration will automatically connect to STANDBYHOST when the connection to PRIMARYHOST fails.


Note that the connection to STANDBYHOST will wait for the default TCP timeout (default 60 seconds) before failing.

MYALIAS=

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVICE_NAME=MYSERVICE))

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARYHOST)(PORT=1521))

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

)

Option #2

This configuration uses TRANSPORT_CONNECT_TIMEOUT to override the TCP timeout value.


The value of 4 indicates that the connection will now fail after only 4 seconds.

MYALIAS=

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVICE_NAME=MYSERVICE))

(TRANSPORT_CONNECT_TIMEOUT=4)

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARYHOST)(PORT=1521))

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

)

Option #3

This configuration fails the first connection after only 1 second but will retry with a higher timeout if necessary.

MYALIAS=

(DESCRIPTION_LIST=

(LOAD_BALANCE=off)

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVICE_NAME=MYSERVICE))

(TRANSPORT_CONNECT_TIMEOUT=1)

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARYHOST)(PORT=1521))

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

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVICE_NAME=MYSERVICE))

(TRANSPORT_CONNECT_TIMEOUT=2)

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARYHOST)(PORT=1521))

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

(DESCRIPTION=

(ENABLE=BROKEN)

(CONNECT_DATA=(SERVICE_NAME=MYSERVICE))

(TRANSPORT_CONNECT_TIMEOUT=3)

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARYHOST)(PORT=1521))

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

)

Oracle Net Configuration Assistant

To manage your tnsnames.ora using the Oracle Net Configuration Assistant...

The above steps generate this tnsnames.ora file in the default location...

# tnsnames.ora Network Configuration File: C:\app\client\product\19.0.0\client_1\NETWORK\ADMIN\tnsnames.ora

# Generated by Oracle configuration tools.


ORCL3 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1523))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL3)

)

)

Oracle Net Manager

To manage your tnsnames.ora using the Oracle Net Manager...

The above steps generate this tnsnames.ora file in the default location (or the location where you chose to save it)...

# tnsnames.ora Network Configuration File: C:\app\client\product\19.0.0\client_1\NETWORK\ADMIN\tnsnames.ora

# Generated by Oracle configuration tools.


ORCL3 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1523))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL3)

)

)