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)
export TNS_ADMIN=/etc/oracle
Windows Registry
TNS_ADMIN
%ORACLE_HOME\network\admin
Working directory
Windows uses this order of precedence...
Local session environment variable.
Global environment variable.
User registry setting under HKey_Current_User\Software\Oracle (or individual Oracle home registry entry.)
System registry setting under HKey_Local_Machine\Software\Oracle (or individual Oracle Home registry entry.)
"Dedicated Server" means there is a 1-to-1 relationship between user processes and server processes.
MYDB=
(DESCRIPTION=
(ENABLE=BROKEN)
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYDB))
(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521))
)
"Shared Server" means Server Processes can be shared by more than one user process.
TODO
Assumes names.default_domain in SQLNET.ORA is not set
MGW_AGENT=
(DESCRIPTION=
(CONNECT_DATA=(SID=mgwextproc))
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))
)
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.
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))
)
This configuration will automatically connect to STANDBYHOST when the connection to PRIMARYHOST fails.
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)))
)
This configuration uses TRANSPORT_CONNECT_TIMEOUT to override the TCP timeout value.
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)))
)
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))))
)
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)
)
)
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)
)
)