1 2 3 Previous Next 43 Replies Latest reply: Apr 26, 2013 7:03 PM by sb92075 RSS

    Difference between server and client tnsnames files

    996129
      My Instructor had us copy the tnsnames.ora file from the server directory into the client directory, so that they would both be the same and that the computer name or ip address would not have to be added to the end of the hoststring after the database name for example (trial01_winxp). However this does not seem right and I believe the only reason we can still connect to the database is because the client is on the same machine as the server. Also it does not make sense to me because its like were telling oracle to ignore the client and just go directly to the server, but what if the client was not on the same machine as the server and how would it know the name of the computer or ip address to look for if the tnsnames files are the same because they must be different for a reason and not just because the Database Configuration Assistant made them that way. Could someone please explain why this may be incorrect and why we shouldn't copy the tnsnames file from the server directory into the client directory.
        • 1. Re: Difference between server and client tnsnames files
          jgarry
          The tnsnames file is what tells sqlnet where to go to communicate with the server. It can be on the same machine, and yet still be different than a local connection. A local connection is called a BEQ (Bequeath) connection, and uses a different transport mechanism than the network connection.

          So, if you say sqlplus username/password, that will bequeath a local connection on your local machine. If you say sqlplus username/password@somehost, you get sqlplus to make a transport layer on top of your networking layer and talk to somehost, which could be on your machine or across the planet, depending on how the translation goes in your tnsnames.ora. You could also specify all the information directly in your connect string, which is how some tools work. Some information can be defaulted, too.

          All the details are in the docs, which you should become familiar with. Granted, there are so many details booting your brain up can be difficult.

          Normally, the server tnsnames.ora is different than clients, since the one on the server is for the server, which may want to connect to different other servers than regular clients. But there is nothing to stop someone from wanting to make them all the same, so they don't have to troubleshoot everyone's client.

          So, what is your connect string? (Don't publish passwords or IP's publicly, of course)
          • 2. Re: Difference between server and client tnsnames files
            sb92075
            tnsnames.ora file is simply a lookup file that is used to translate TNS_ALIAS names to details where the specific DB can be located.
            It is analogous to /etc/hosts file which is used to translate host names to IP#s.

            If the list of TNS_ALIAS names is complete in a single copy of tnsnames.ora file (contains every known database; regardless upon which system any specific database resides), then it can be used on any DB Server system or any CLIENT system.
            • 3. Re: Difference between server and client tnsnames files
              EdStevens
              These might help

              read: [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
              read: [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex) 
              • 4. Re: Difference between server and client tnsnames files
                996129
                Actually none of the responses so far have been helpful.
                • 5. Re: Difference between server and client tnsnames files
                  996129
                  My connect or hoststring, which I believe that is what you are referring to is trial01 when connecting locallly to the server and trial01_winxp when connect through the client to the database where trial01 is the name of my database and winxp is the name of my computer. The same applies to the second database we've created using scripts and then running the .bat file to create, in which case you would replace trial01 with trial02.
                  • 6. Re: Difference between server and client tnsnames files
                    rp0428
                    >
                    Actually none of the responses so far have been helpful.
                    >
                    Then you must not understand what they have been telling you as both sb92075 and EdStevens are experts in that area.

                    This is what sb92075 said:
                    >
                    tnsnames.ora file is simply a lookup file that is used to translate TNS_ALIAS names to details where the specific DB can be located.
                    >
                    Is there anything in particular about that that you dont' understand?

                    He also said this
                    >
                    If the list of TNS_ALIAS names is complete in a single copy of tnsnames.ora file (contains every known database; regardless upon which system any specific database resides), then it can be used on any DB Server system or any CLIENT system.
                    >
                    Let me put it another way.

                    If your cell phone has your contact list with the telephone numbers of all of your friends it doesn't matter if you keep your cell phone in your left pocket, your right pocket, jacket pocket, in the car, at home or in a shoe box in the closet.

                    It STILL has the telephone numbers of all of your friends. If you want to call one of them you open your contact list and punch in the number.

                    The TNSNAMES.ora file is Oracle's cell phone. As long as it has the contact information for all of your databases you can keep it anywhere you want. If you want to connect to a database you tell Oracle what TNSNAMES.ora file to use (your PATH does that) and the ALIAS for the database you want to connect to. Oracle looks up the connection information in the 'ora' file.

                    I'm not sure how they could have made it any clearer.
                    • 7. Re: Difference between server and client tnsnames files
                      sb92075
                      993126 wrote:
                      My connect or hoststring, which I believe that is what you are referring to is trial01 when connecting locallly to the server and trial01_winxp when connect through the client to the database where trial01 is the name of my database and winxp is the name of my computer. The same applies to the second database we've created using scripts and then running the .bat file to create, in which case you would replace trial01 with trial02.
                      a single database can have multiple alias entries associated with it as shown below

                      TRIAL01, TRAIL01_WINXP,TRIAL01_XP=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XP_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TRIAL01)))
                      • 8. Re: Difference between server and client tnsnames files
                        996129
                        No let me put it this way How can a client communicate with a server across a network without a tnsnames file that doesn't specify the name of the computer or domain of the server. If you copy the tnsnames file from the server it ignores the name of the server or domain and connects directly to the server, ignore the client, so how can this work across a network when the client is not on the server.

                        Edited by: 993126 on Apr 21, 2013 2:08 PM
                        • 9. Re: Difference between server and client tnsnames files
                          996129
                          I don't have multiple alias entries associated with my database. As I stated to connect locally I use the hoststring trial01 to connect to that database and trial02 to connect to the other. When connecting through the client I use trial01_winxp for that database and trial02_winxp to connect to the other database.

                          My tnsnames file contents on the server are as follows and have not been edited by me and this is how Oracle made them:

                          # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
                          # Generated by Oracle configuration tools.

                          TRIAL02 =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                          (SERVER = DEDICATED)
                          (SERVICE_NAME = trial02.classroom)
                          )
                          )

                          TRIAL01 =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                          (SERVER = DEDICATED)
                          (SERVICE_NAME = trial01.classroom)
                          )
                          )

                          ORACLEXP =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                          (SERVICE_NAME = OracleXP)
                          )
                          )

                          EXTPROC_CONNECTION_DATA =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                          )
                          (CONNECT_DATA =
                          (SID = PLSExtProc)
                          (PRESENTATION = RO)
                          )
                          )

                          The content of the tnsnames file on my client are as follows and have not been edited by me and this is how Oracle created them:

                          # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
                          # Generated by Oracle configuration tools.

                          ORACLEXP =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                          (SERVICE_NAME = OracleXP)
                          )
                          )

                          TRIAL01_WINXP =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = WInXP)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                          (SID = trial01)
                          (SERVER = DEDICATED)
                          )
                          )

                          TRIAL02_WINXP =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                          (SID = trial02)
                          (SERVER = DEDICATED)
                          )
                          )

                          TRAIL02_WINXP =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                          (SID = trail02)
                          (SERVER = DEDICATED)
                          )
                          )

                          EXTPROC_CONNECTION_DATA =
                          (DESCRIPTION =
                          (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                          )
                          (CONNECT_DATA =
                          (SID = PLSExtProc)
                          (PRESENTATION = RO)
                          )
                          )
                          • 10. Re: Difference between server and client tnsnames files
                            sb92075
                            Difference between server and client tnsnames files
                            There is NO difference!

                            Any tnsnames.ora file that works on a client, it will work on any DB Server; & vice versa.
                            • 11. Re: Difference between server and client tnsnames files
                              996129
                              Are you blind the difference is clearly in the use of the SERVICE_NAME= in the server tnsnames file and in the SERVER= in the client tnsnames file.

                              If your trying to tell me that doesn’t matter I’d don’t see how that is possible and you better have a good explanation because so far I definitely don’t agree with you. I already know they work locally, but my point is that you only get away with it because your not connecting remotely.

                              Edited by: 993126 on Apr 21, 2013 4:54 PM

                              Edited by: 993126 on Apr 21, 2013 4:55 PM
                              • 12. Re: Difference between server and client tnsnames files
                                sb92075
                                993126 wrote:
                                Are you blind the difference is clearly in the use of the SERVICE_NAME= in the server tnsnames file and in the SERVER= in the client tnsnames file.
                                SERVER=
                                line is optional & will either have a value of "DEDICATED" or "SHARED".
                                If your trying to tell me that doesn’t matter I’d don’t see how that is possible
                                Yes, I am telling you that it does not matter.
                                and you better have a good explanation because so far I definitely don’t agree with you. I already know they work locally, but my point is that you only get away with it because your not connecting remotely.
                                take a copy of tnsnames.ora file from the DB Server & use it to replace the tnsnames.ora file on client.
                                You will still be able to connect to the DB Server from the client.
                                Try it; you'll like it!
                                • 13. Re: Difference between server and client tnsnames files
                                  sb92075
                                  http://docs.oracle.com/cd/E11882_01/network.112/e10836/naming.htm#NETAG1125

                                  EZCONNECT allows client to connect to remote DB without using any tnsnames.ora file.
                                  It is effectively using JDBC:thin to initiate packet exchange with the listener.
                                  • 14. Re: Difference between server and client tnsnames files
                                    EdStevens
                                    993126 wrote:
                                    Are you blind the difference is clearly in the use of the SERVICE_NAME= in the server tnsnames file and in the SERVER= in the client tnsnames file.

                                    If your trying to tell me that doesn’t matter I’d don’t see how that is possible and you better have a good explanation because so far I definitely don’t agree with you. I already know they work locally, but my point is that you only get away with it because your not connecting remotely.

                                    Edited by: 993126 on Apr 21, 2013 4:54 PM

                                    Edited by: 993126 on Apr 21, 2013 4:55 PM
                                    OK, take a look at this tnsnames entry:
                                    fubar.WORLD =
                                      (DESCRIPTION =
                                        (ADDRESS_LIST =
                                          (ADDRESS = (PROTOCOL = TCP)(HOST = vbdwdev)(PORT = 1521))
                                        )
                                        (CONNECT_DATA =
                                          (SERVICE_NAME = fubar)
                                        )
                                      )
                                    If you seem to think that there is something different about a tnsnames file on a server vs. on a client, tell me which one the above entry came from, and why you think so.

                                    A tns connection is simply a network connection between a client process and a database instance. Notice I say "client *process*". It doesn't matter what machine that process is running on. It can be on the very same machine that the database instance is running on. (In fact, that is exactly what happens with a database link. One database acts as a client to another, exactly as if it -- the first database -- were sqlplus). The mechanism is still the same. The net service name is located in tnsnames, and a request is built and passed on to the OS network stack, requesting a connection to the specified host and port. It doesn't matter if that specified host is the machine we are running on, or a machine on the other side of the planet -- or even off the planet ...

                                    Go back and re-read the two links I gave you.
                                    1 2 3 Previous Next