Tnsnames.ora file in Oracle | tnsnames.ora sample

I will explain Tnsnames.ora file in Oracle | tnsnames.ora sample in this post.

 

Tnsnames.ora location in Oracle

TNSNAMES.ORA is a SQL*Net configuration file under TNS_ADMIN ( $ORACLE_HOME/network/admin ) and it is used to define database connection string. This file consist of connection string of database(s), thus you can only use the TNS Alias of this entries.

Tnsnames.ora location is $ORACLE_HOME/network/admin

 

Tnsnames.ora Sample

Connection string or TNS of databases are defined as follows in the tnsnames.ora.

TNS_ALIAS=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=HOST_NAME_OR_IP)
(PORT=PORT_NUMBER)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=SERVICE_NAME)
)
)


tns of SAP databases are as follows.

TNS_ALIAS=
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = IP_OR_HOSTNAME)
(PORT = PORT_NUMBER)
)
(CONNECT_DATA =
(SID = ORACLE_SID)
(GLOBAL_NAME = GLOBAL_NAME)
)
)


MSDB=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.63.34)
(PORT=1523)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=MSDB)
)
)

 

Sample of tnsnames.ora exist under $ORACLE_HOME/network/admin/samples directory as follows.

[MSDB1]/u01/app/oracle/product/19.0/dbhome_1/network/admin/samples $ cat tnsnames.ora 
# This file contains the syntax information for 
# the entries to be put in any tnsnames.ora file
# The entries in this file are need based. 
# There are no defaults for entries in this file
# that Sqlnet/Net3 use that need to be overridden 
#
# Typically you could have two tnsnames.ora files
# in the system, one that is set for the entire system
# and is called the system tnsnames.ora file, and a
# second file that is used by each user locally so that
# he can override the definitions dictated by the system
# tnsnames.ora file.

# The entries in tnsnames.ora are an alternative to using
# the names server with the onames adapter.
# They are a collection of aliases for the addresses that 
# the listener(s) is(are) listening for a database or 
# several databases.

# The following is the general syntax for any entry in 
# a tnsnames.ora file. There could be several such entries 
# tailored to the user's needs.

<alias>= [ (DESCRIPTION_LIST = # Optional depending on whether u have 
# one or more descriptions
# If there is just one description, unnecessary ]
(DESCRIPTION=
[ (SDU=2048) ] # Optional, defaults to 2048
# Can take values between 512 and 32K
[ (ADDRESS_LIST= # Optional depending on whether u have
# one or more addresses
# If there is just one address, unnecessary ]
(ADDRESS=
[ (COMMUNITY=<community_name>) ] 
(PROTOCOL=tcp)
(HOST=<hostname>)
(PORT=<portnumber (1521 is a standard port used)>)
)
[ (ADDRESS=
(PROTOCOL=ipc)
(KEY=<ipckey (PNPKEY is a standard key used)>)
)
]
[ (ADDRESS=
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=decnet)
(NODE=<nodename>)
(OBJECT=<objectname>)
)
]
... # More addresses
[ ) ] # Optional depending on whether ADDRESS_LIST is used or not 
[ (CONNECT_DATA=
(SID=<oracle_sid>)
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
[ (SOURCE_ROUTE=yes) ] 
)
(DESCRIPTION= 
[ (SDU=2048) ] # Optional, defaults to 2048
# Can take values between 512 and 32K
[ (ADDRESS_LIST= ] # Optional depending on whether u have more
# than one address or not
# If there is just one address, unnecessary
(ADDRESS
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=tcp)
(HOST=<hostname>)
(PORT=<portnumber (1521 is a standard port used)>)
)
[ (ADDRESS=
(PROTOCOL=ipc)
(KEY=<ipckey (PNPKEY is a standard key used)>)
)
]
... # More addresses
[ ) ] # Optional depending on whether ADDRESS_LIST 
# is being used
[ (CONNECT_DATA=
(SID=<oracle_sid>)
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
[ (SOURCE_ROUTE=yes) ]
)
[ (CONNECT_DATA=
(SID=<oracle_sid>)
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
... # More descriptions 
[ ) ] # Optional depending on whether DESCRIPTION_LIST is used or not


 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 1,454 views last month,  9 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply