Understanding TNSNAMES.ORA file in oracle database (and how to generate it)

hello and welcome to this new tutorial in this tutorial i'm going to talk as much as i can about trs names.ora file and answer as much questions as possible on this machine i have just installed the oracle database 21c standard edition and i have done nothing yet other than installing the database so i did not set any environment variables manually or anything so tns names the tnsnames.org file is an important file when working with oracle databases so we need to understand what it is where it is located and how to create and edit this file tnsnames.ora is a configuration file that the oracle database uses it allows users and applications to connect to oracle databases by by matching a connection name with all of the relevant details in this file as we are going to see the tns names.ora file has a specific syntax now where is the tnasenames.ora file located if i look to my installation of oracle database this is the home uh folder for my oracle database it's c up uh oracle product and then 21.3.000 so this is the oracle home under oracle home if you go to network and then admin this is where you should find the tns names.ora file i don't have it yet here because i have not created it yet but this is the location of the tnsnames.ora file let me open the sublime text editor so the location of dns names file is oracle home slash network slash admin and oracle home is an environment variable let's check if this variable is set for my machine so type here in search environment variables and then click environment variables and look for oracle home i don't have oracle home i do have java home but no oracle home so i can create it so to create this variable click on new oracle underscore home and for the value give it this location click ok click ok and click ok to save so now the oracle home environment variable has been created another interesting environment variable is the tns admin variable which contains the value or the path directly to the tns names.ora file location so you can have this environment variable and if it is there it's going to point directly to this location so i'm going to copy this path and then create the tns admin variable so it's tns underscore admin with this value and you can notice that it has in the beginning the oracle home value and then network and then admin okay the way this works is that the oracle is going to check first for the tns admin variable if it is set it's going to use it to find the tnsnames.ora file and all the details in it if tns admin environment variable is not set then oracle is going to look into the oracle home slash network slash admin location and for this the oracle home variable has to be set in the environment variables if you notice here in this folder in this admin folder you have a folder called sample which contains a tns.ora sample file so let's copy this and paste it here and let's edit it with sublime text and here you can find the syntax for this file i'm going to remove everything and paste the syntax of tns names entries here so this is the syntax and you can you can have an entry for each database in your server so if you have 10 databases you can have 10 entries here now let's see different parts of this entry so first net service name and this is the name that you use for the connection string so this is like the name of this entry all of these details can have this name here host here is either the server name or the ip address of this of your database server and this is where the database that you want to connect to exists the port is required for the connection the default port for uh oracle database is 1521 but you can change it of course and the service name is the name of the database you want to connect to so here i want to tell you uh something don't confuse this service name with this net service name this is the database or the name of the database you want to connect to and this is the name for this whole entry so your service name can be for example hr db for example and your service name net service name can be something else something else my hr database they can be the same but they don't have to be the same but you have to understand that this is the database and this is the name of this entry so this is the sid of your database if i go to the oracle enterprise manager for my database i do have here two pluggable databases one called orclpdb and one called demo so let's create tns names uh entries for both of these pluggable databases so here i'm going to keep the port to 1521 i'm going to change the host and instead of using a host name i'm going to use an ip address so let's check the ip address of this machine it's 172 dot 26 dot 221 dot 280 so this is my ip address for this server the service name is going to be oscl pdb for the orcl pluggable database and my i am going to call my entry for example orcl underscore pdd but you can call it whatever you want i'm going to copy this and paste it down below and change the service name to demo and change this to demo underscore pdb so now i have two entries in my tnsnames.ora file the good news is that you don't have to create tns name entries this way you don't have to manually edit the dns names not or a file and add entries to it or change it you can do this in an easy way using a utility called net manager so let me show you how to do this so under the start menu look for oracle database and then open this utility net manager click yes to confirm that you want to open it expand oracle net configuration then expand local and then expand service naming if you don't have any tns name entries configured you will find nothing here so this is where you create your tns name entries for your oracle database so to create a new tns name entry click on the plus button here sorry i forgot to tell you to delete these two files here because they will be generated automatically through the net manager utility now go back to this utility and select service naming and click on this plus button to create a new service name the net service name is the name of your tns entry so let's name it for example orcl and this is going to be a an entry for for my orcl container database click next choose tcpip protocol as the communication protocol and click next for the host name this is going to be the host of this database so since i am on this machine that has the oracle database installed on it i'm going to just type local host but you can type the ip address of the server name of your server click next on this screen you need to type the service name meaning the database name for me it's orcl and click next click on this button test to test your tns entry and before doing this test make sure that your listener is started to check the status of your listener just type lsnr ctl and then status and for me you see that it is up if it is not up do start you can start it like this as an rctl start and for me as you can see it says that is that it is already started if you click on tests it's going to say invalid username password click on change login and then type here a user for your database i'm going to type the sysuser as sysdba and password for this user click ok and click test again i think i might have the wrong password here let me try the system user ah test successful so i just had a problem with my uh cis user password so close this with this i have validated the dns name entry if i go back to my admin folder and refresh or sorry i should see the tns names that are filed but i know why i did not i do not see it yet because i have not yet saved this configuration so if i do save network configuration i go back here without refreshing i can see my new tns name entry and it's right here it's orcl uh with host localhost and port 1521 and the database name is overseer okay i kept the default 1521 port because that's the default part for oracle databases but you can change it of course if you want i think you agree with me that this is a simpler way to create tns entries another way to confirm that your tns name or tns entry is working fine is using the command tns ping and then the name of your tns entry which is orcl and as you can see it says here okay 10 milliseconds it means that it was able to ping this entry if there is a problem you will not see this okay message you will see something else like error or failed or something like this if i go back to this file you see here that the tns entry and the service name are the same but they don't have to be the same you can rename this if you want so if i go back here i say orcl for example dns for example i save the configuration i come back here i reopen this as you can see sorry i changed the database this is not what i wanted to change what i wanted to change is not this this but this so the way to do this is to select your service name and then click on edit and rename and if i type here orcl tns click ok click on save network configuration as you can see it is renamed here so be careful that this service name is the name of the database and this this name right here is the name of the entry and if i do tns ping of orcl it's going to say there is a problem for sure because there is no entry with the name orcl failed to resolve name but if i say tns being orcl dns it's going to say ok and i think you get the point here and you understand the difference between this name right here and this name right here the last thing i want to show you with tns names is how to use them with sql developer so if i open a sql developer here and then i go to tools and then preferences i'm going to give it a moment here to load under databases and advanced you have this tns names directory option so here you can set the directory for tns for your tns names dot aura file for me it's this folder so i'm going to copy it and paste it here and press ok to save the changes just making sure that it is saved okay let me make this a little bit bigger so now if i do a new connection and i call my connection orcl con the user system password my system password and connection type i set it as tns and then i choose my network alias which is orcl tns if you have multiple tns entries you will you will you will see these entries right here so if i use orcltns and click test and as you can see it is successful if i didn't i did not use tns and i used basic i would have to type the host name the port and the seed but i don't have to do that if i have tns names file okay so now i can save this and connect to my database so this is it guys i'm going to stop here the video i think is too long uh already i think this is all i wanted to show you about tns names in this video please let me know if i missed anything of or if you have any questions i hope this video has been helpful for you and thank you for watching

Channel: Math Info DZ

Views: 22,474

Keywords: tnsnames, tnsnames.ora file, NET Managet, oracle service names, oracle database, tnsping

Id: 8ad8OhbN5-Y

Length: 20min 57sec (1257 seconds)

Published: Sat May 07 2022