1 2 3 Previous Next 43 Replies Latest reply on Apr 27, 2013 12:03 AM by sb92075 Go to original post
      • 30. Re: Difference between server and client tnsnames files
        996129
        sb92075 wrote:
        993126 wrote:
        sb92075 wrote:
        open Command Window on the client & then issue the OS commands below


        TYPE %ORACLE_HOME%\NETWORK\ADMIN\TNSNAMES.ORA

        sqlplus scott/tiger@TRAIL02_WINXP

        sqlplus scott/tiger@TRAIL02

        COPY the commands & the results from above, then PASTE all back here
        I did as you specified and the following is the output:

        C:\Documents and Settings\Daniel J. Grossi II>TYPE C:\oracle\product\10.2.0\clie
        nt_1\NETWORK\ADMIN\tnsnames.ora
        # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NET
        WORK\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)
        )
        )


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


        C:\Documents and Settings\Daniel J. Grossi II>sqlplus scott/tiger@TRAIL02_WINXP

        SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 22 23:17:47 2013

        Copyright (c) 1982, 2005, Oracle. All rights reserved.

        ERROR:
        ORA-12154: TNS:could not resolve the connect identifier specified


        Enter user-name: system
        Enter password:
        ERROR:
        ORA-12560: TNS:protocol adapter error


        Enter user-name: sqlplus scott/tiger@TRAIL02
        SP2-0306: Invalid option.
        Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
        where <logon> ::= <username>[<password>][@<connect_identifier>] | /
        SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

        C:\Documents and Settings\Daniel J. Grossi II>
        It appears that SQL*Net is NOT using the tnsnames.ora file at the top of this message.

        use Window Find File wizard & locate all copies of tnsnames.ora file on this system.

        open Command Window & issue OS command below

        SET

        COPY the results from above, then PASTE all back here
        I executed the find command and got the following:

        C:\Documents and Settings\Daniel J. Grossi II>dir
        C:\oracle\product\10.2.0\clien
        t_1\NETWORK\ADMIN | find "tnsnames"
        04/22/2013 01:39 PM 979 tnsnames.ora
        04/18/2013 10:08 PM 1,189 tnsnames.ora.0
        04/22/2013 01:39 PM 979 tnsnames.ora.1

        C:\Documents and Settings\Daniel J. Grossi II>

        The output of the SET command is as follows:

        C:\Documents and Settings\Daniel J. Grossi II>SET
        ALLUSERSPROFILE=C:\Documents and Settings\All Users
        CommonProgramFiles=C:\Program Files\Common Files
        COMPUTERNAME=WINXP_CLIENT
        ComSpec=C:\WINDOWS\system32\cmd.exe
        FP_NO_HOST_CHECK=NO
        NUMBER_OF_PROCESSORS=1
        OS=Windows_NT
        Path=C:\oracle\product\10.2.0\client_1\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WIN
        DOWS\System32\Wbem
        PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
        PROCESSOR_ARCHITECTURE=x86
        PROCESSOR_IDENTIFIER=x86 Family 6 Model 23 Stepping 6, GenuineIntel
        PROCESSOR_LEVEL=6
        PROCESSOR_REVISION=1706
        ProgramFiles=C:\Program Files
        PROMPT=$P$G
        SystemDrive=C:
        SystemRoot=C:\WINDOWS
        TEMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp
        TMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp
        USERDOMAIN=WINXP_CLIENT
        USERNAME=Administrator
        USERPROFILE=C:\Documents and Settings\Administrator
        windir=C:\WINDOWS

        C:\Documents and Settings\Daniel J. Grossi II>
        • 31. Re: Difference between server and client tnsnames files
          sb92075
          993126 wrote:

          I executed the find command and got the following:

          C:\Documents and Settings\Daniel J. Grossi II>dir
          C:\oracle\product\10.2.0\clien
          t_1\NETWORK\ADMIN | find "tnsnames"
          04/22/2013 01:39 PM 979 tnsnames.ora
          04/18/2013 10:08 PM 1,189 tnsnames.ora.0
          04/22/2013 01:39 PM 979 tnsnames.ora.1
          use Window Find File wizard & locate all copies of tnsnames.ora file on this system.

          the "find" command does NOT locate all copies of tnsnames.ora file on this system
          C:\Windows\System32>find /?
          Searches for a text string in a file or files.
          
          FIND [/V] [/C] [/N] [/I] [/OFF[LINE]] "string" [[drive:][path]filename[ ...]]
          
            /V         Displays all lines NOT containing the specified string.
            /C         Displays only the count of lines containing the string.
            /N         Displays line numbers with the displayed lines.
            /I         Ignores the case of characters when searching for the string.
            /OFF[LINE] Do not skip files with offline attribute set.
            "string"   Specifies the text string to find.
            [drive:][path]filename
                       Specifies a file or files to search.
          
          If a path is not specified, FIND searches the text typed at the prompt
          or piped from another command.
          
          C:\Windows\System32>
          • 32. Re: Difference between server and client tnsnames files
            EdStevens
            993126 wrote:
            sb92075 wrote:
            open Command Window on the client & then issue the OS commands below


            TYPE %ORACLE_HOME%\NETWORK\ADMIN\TNSNAMES.ORA

            sqlplus scott/tiger@TRAIL02_WINXP

            sqlplus scott/tiger@TRAIL02

            COPY the commands & the results from above, then PASTE all back here
            I did as you specified and the following is the output:

            C:\Documents and Settings\Daniel J. Grossi II>TYPE C:\oracle\product\10.2.0\clie
            nt_1\NETWORK\ADMIN\tnsnames.ora
            # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NET
            WORK\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)
            )
            )


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


            C:\Documents and Settings\Daniel J. Grossi II>sqlplus scott/tiger@TRAIL02_WINXP

            SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 22 23:17:47 2013

            Copyright (c) 1982, 2005, Oracle. All rights reserved.

            ERROR:
            ORA-12154: TNS:could not resolve the connect identifier specified


            Enter user-name: system
            Enter password:
            ERROR:
            ORA-12560: TNS:protocol adapter error


            Enter user-name: sqlplus scott/tiger@TRAIL02
            SP2-0306: Invalid option.
            Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
            where <logon> ::= <username>[<password>][@<connect_identifier>] | /
            SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

            C:\Documents and Settings\Daniel J. Grossi II>

            By the way is the TYPE command in windows the same as the cat command in Linux/Unix?

            Edited by: 993126 on Apr 22, 2013 8:32 PM
            As SB already pointed out, it would appear that you are using some other tnsnames file.

            Try this command:
            c:> tnsping TRAIL02_WINXP
            The important thing that will come out of this is a message saying where the net config files are found:
            C:\>tnsping fubar
            
            TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-APR-2
            013 06:39:24
            
            Copyright (c) 1997, 2010, Oracle.  All rights reserved.
            
            Used parameter files:
            C:\app\oracle\product\tnsadmin\sqlnet.ora
            
            TNS-03505: Failed to resolve name
            
            C:\>
            That tns-03505 is tnsping reporting exactly the same problem that you are seeing with sqlplus. But the important thing is that line: "Used parameter files:" Your tnsnames should be in the same directory.
            • 33. Re: Difference between server and client tnsnames files
              996129
              I ran the tnsping command as request and the following is the output:

              Microsoft Windows XP [Version 5.1.2600]
              (C) Copyright 1985-2001 Microsoft Corp.

              C:\Documents and Settings\Daniel J. Grossi II>tnsping TRAIL02_WINXP

              TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-APR-2
              013 17:51:03

              Copyright (c) 1997, 2005, Oracle. All rights reserved.

              Used parameter files:
              C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

              TNS-03505: Failed to resolve name

              C:\Documents and Settings\Daniel J. Grossi II>
              • 34. Re: Difference between server and client tnsnames files
                996129
                EdStevens wrote:
                993126 wrote:
                sb92075 wrote:
                open Command Window on the client & then issue the OS commands below


                TYPE %ORACLE_HOME%\NETWORK\ADMIN\TNSNAMES.ORA

                sqlplus scott/tiger@TRAIL02_WINXP

                sqlplus scott/tiger@TRAIL02

                COPY the commands & the results from above, then PASTE all back here
                I did as you specified and the following is the output:

                C:\Documents and Settings\Daniel J. Grossi II>TYPE C:\oracle\product\10.2.0\clie
                nt_1\NETWORK\ADMIN\tnsnames.ora
                # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NET
                WORK\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)
                )
                )


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


                C:\Documents and Settings\Daniel J. Grossi II>sqlplus scott/tiger@TRAIL02_WINXP

                SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 22 23:17:47 2013

                Copyright (c) 1982, 2005, Oracle. All rights reserved.

                ERROR:
                ORA-12154: TNS:could not resolve the connect identifier specified


                Enter user-name: system
                Enter password:
                ERROR:
                ORA-12560: TNS:protocol adapter error


                Enter user-name: sqlplus scott/tiger@TRAIL02
                SP2-0306: Invalid option.
                Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
                where <logon> ::= <username>[<password>][@<connect_identifier>] | /
                SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

                C:\Documents and Settings\Daniel J. Grossi II>

                By the way is the TYPE command in windows the same as the cat command in Linux/Unix?

                Edited by: 993126 on Apr 22, 2013 8:32 PM
                As SB already pointed out, it would appear that you are using some other tnsnames file.

                Try this command:
                c:> tnsping TRAIL02_WINXP
                The important thing that will come out of this is a message saying where the net config files are found:
                C:\>tnsping fubar
                
                TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-APR-2
                013 06:39:24
                
                Copyright (c) 1997, 2010, Oracle.  All rights reserved.
                
                Used parameter files:
                C:\app\oracle\product\tnsadmin\sqlnet.ora
                
                TNS-03505: Failed to resolve name
                
                C:\>
                That tns-03505 is tnsping reporting exactly the same problem that you are seeing with sqlplus. But the important thing is that line: "Used parameter files:" Your tnsnames should be in the same directory.
                I can connect to trial02 now. The problem was that you and I had mispelled trial02 and typed trail02, so the following is the output with the correction:

                C:\Documents and Settings\Daniel J. Grossi II>sqlplus test/test@trial02_winxp

                SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 17:58:16 2013

                Copyright (c) 1982, 2005, Oracle. All rights reserved.


                Connected to:
                Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                With the Partitioning, OLAP and Data Mining options

                SQL>

                and the correction to the tnsping is as follows:

                C:\Documents and Settings\Daniel J. Grossi II>tnsping TRIAL02_WINXP

                TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-APR-2
                013 18:02:32

                Copyright (c) 1997, 2005, Oracle. All rights reserved.

                Used parameter files:
                C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora


                Used TNSNAMES adapter to resolve the alias
                Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
                (HOST = WinXP)(PORT = 1521))) (CONNECT_DATA = (SID = trial02) (SERVER = DEDICATE
                D)))
                OK (20 msec)

                C:\Documents and Settings\Daniel J. Grossi II>

                I guess you could say that problem is solved because I deleted the mispelled entry in enterprise manager and add the selected database from my local tnsnames.ora fle instead of manually and now enterprise manager works. I'm not worried about Net Manager because Net Manager is just testing to see if the user scott can connect and that user doesn't exist yet in trial01 or trial02. Now to explain why OracleXP can connect no matter what even if it is on the same machine as the server and why trial01 could connect without the hostname or domain name even with the tnsnames copy from the server directory to the new WinXP_client machine.

                I guess the following proves that my theory was right that you cannot connect without the hostname or domain or IP address of the server when connecting to the server while using the tnsnames file from the client and the you can't connect at all using the tnsnames from the server directory when copied to the client if connecting to the server from a remote machine except when using the OracleXP database because it connects no matter what and I can't explain why at the moment.

                C:\Documents and Settings\Daniel J. Grossi II>sqlplus test/test@trial02

                SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 18:15:34 2013

                Copyright (c) 1982, 2005, Oracle. All rights reserved.

                ERROR:
                ORA-12154: TNS:could not resolve the connect identifier specified


                Enter user-name: test
                Enter password:
                ERROR:
                ORA-12560: TNS:protocol adapter error


                Enter user-name:
                ERROR:
                ORA-12560: TNS:protocol adapter error


                SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

                C:\Documents and Settings\Daniel J. Grossi II>sqlplus test/test@trial01

                SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 18:16:00 2013

                Copyright (c) 1982, 2005, Oracle. All rights reserved.

                ERROR:
                ORA-12154: TNS:could not resolve the connect identifier specified


                Enter user-name:
                ERROR:
                ORA-12560: TNS:protocol adapter error


                Enter user-name:
                ERROR:
                ORA-12560: TNS:protocol adapter error


                SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

                C:\Documents and Settings\Daniel J. Grossi II>sqlplus
                scott/tiger@OracleXP

                SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23
                18:45:08 2013

                Copyright (c) 1982, 2005, Oracle. All rights reserved.


                Connected to:
                Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
                Production
                With the Partitioning, OLAP and Data Mining options

                SQL>
                Also the problem I was having with getting trial02 to connect could have also been related to the fact that I started up the machine with the Oracle server after the remote machine with the client, which is also a problem when using any server machine for that matter.

                Edited by: 993126 on Apr 23, 2013 3:24 PM

                Edited by: 993126 on Apr 23, 2013 3:49 PM

                Edited by: 993126 on Apr 23, 2013 4:00 PM

                Edited by: 993126 on Apr 23, 2013 4:59 PM
                • 35. Re: Difference between server and client tnsnames files
                  EdStevens
                  >
                  <snip>
                  I guess you could say that problem is solved because I deleted the mispelled entry in enterprise manager and add the selected database from my local tnsnames.ora fle instead of manually and now enterprise manager works. I'm not worried about Net Manager because Net Manager is just testing to see if the user scott can connect and that user doesn't exist yet in trial01 or trial02. Now to explain why OracleXP can connect no matter what even if it is on the same machine as the server and why trial01 could connect without the hostname or domain name even with the tnsnames copy from the server directory to the new WinXP_client machine.

                  I guess the following proves that my theory was right that you cannot connect without the hostname or domain or IP address of the server when connecting to the server while using the tnsnames file from the client and the you can't connect at all using the tnsnames from the server directory when copied to the client if connecting to the server from a remote machine except when using the OracleXP database because it connects no matter what and I can't explain why at the moment.
                  Now there is a run-on sentence that would make Herman Melville scream. I'm afraid I've read it several times and still can't parse out exactly what you are trying to say. But against my better judgement I'll try ..
                  you cannot connect without the hostname or domain or IP address of the server when connecting to the server while using the tnsnames file from the client
                  You cannot make any connection between any two computers without an IP address. A host name is just an alias that ultimately has to get resolved to in IP address. The tnsnames file is just a means of supplying that information to the TNS layer so that it can pass it on to the standard network stack.

                  "you can't connect at all using the tnsnames from the server directory when copied to the client"

                  Wrong, wrong, wrong, wrong, wrong. One more time. The tnsnames file is nothing more than a telephone directory. The information in it is NOT dependent on what machine it is on. I've asked you before, and since you insist on believing this, I'll ask again. Please indicate what bit of information in the tnsnames file needs to be changed when you copy it from the serve to a remote client.

                  Here's an extract from your own tnsnames file. I've added line numbers for reference
                  1 ORACLEXP =
                  2 (DESCRIPTION =
                  3 (ADDRESS_LIST =
                  4 (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                  5 )
                  6 (CONNECT_DATA =
                  7 (SERVICE_NAME = OracleXP)
                  8 )
                  9 )
                  Line 1 is just an alias. You can put anything you want there. Whatever you put there is what you put in your connection request : SQLPLUS SCOTT/TIGER@ORACLEXP

                  Line 4 specifies three things - the protocol to be used in the communications (tcp), the host on which the database lives (WinXP) and the port the listener is useing (1521). All three of those items is about the server on which the database lives. That information does not change if the client moves from the database server to your laptop.

                  Line 7 is the name of the service the database is registered with the listener. Again, it has to do with the database. It doesn't change with the location of the client.

                  C:\Documents and Settings\Daniel J. Grossi II>sqlplus test/test@trial02

                  SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 18:15:34 2013

                  Copyright (c) 1982, 2005, Oracle. All rights reserved.

                  ERROR:
                  ORA-12154: TNS:could not resolve the connect identifier specified
                  Sigh. We are back to where we started. There is no entry 'trial02' in the tnsnames being used by this client.
                  >
                  Enter user-name: test
                  Enter password:
                  ERROR:
                  ORA-12560: TNS:protocol adapter error
                  You get a protocol adapter error at this point because sqlplus has thrown away the earlier attempt and is now trying a bequeath connection to a local database as specified by the value of the ORACLE_SID environment variable. No such database so the connection fails.
                  >
                  Enter user-name:
                  ERROR:
                  ORA-12560: TNS:protocol adapter error


                  SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

                  C:\Documents and Settings\Daniel J. Grossi II>sqlplus test/test@trial01

                  SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 18:16:00 2013

                  Copyright (c) 1982, 2005, Oracle. All rights reserved.

                  ERROR:
                  ORA-12154: TNS:could not resolve the connect identifier specified


                  Enter user-name:
                  ERROR:
                  ORA-12560: TNS:protocol adapter error


                  Enter user-name:
                  ERROR:
                  ORA-12560: TNS:protocol adapter error


                  SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

                  C:\Documents and Settings\Daniel J. Grossi II>sqlplus
                  scott/tiger@OracleXP

                  SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23
                  18:45:08 2013

                  Copyright (c) 1982, 2005, Oracle. All rights reserved.


                  Connected to:
                  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
                  Production
                  With the Partitioning, OLAP and Data Mining options

                  SQL>
                  Also the problem I was having with getting trial02 to connect could have also been related to the fact that I started up the machine with the Oracle server after the remote machine with the client, which is also a problem when using any server machine for that matter.
                  Well, you certainly cannot connect to a machine that is not started. But an ORA-12154 never got far enough to find out. With an ORA-12154, no packet ever reached the nic of the client machine. In fact, with an ORA-12154, tns never even passed a request to the standard network stack.


                  >
                  Edited by: 993126 on Apr 23, 2013 3:24 PM

                  Edited by: 993126 on Apr 23, 2013 3:49 PM

                  Edited by: 993126 on Apr 23, 2013 4:00 PM

                  Edited by: 993126 on Apr 23, 2013 4:59 PM
                  • 36. Re: Difference between server and client tnsnames files
                    sb92075
                    Ed,

                    You have more patience & perseverance than I have.
                    As I have said in the past, you can lead some folks to knowledge; but you can't make them think.
                    I feel that your odds of changing OP's mind is less than 50%.

                    OP says, "Don't try to convince me with any facts, I've already made up my mind!"
                    • 37. Re: Difference between server and client tnsnames files
                      996129
                      EdStevens wrote:
                      >
                      <snip>
                      I guess you could say that problem is solved because I deleted the mispelled entry in enterprise manager and add the selected database from my local tnsnames.ora fle instead of manually and now enterprise manager works. I'm not worried about Net Manager because Net Manager is just testing to see if the user scott can connect and that user doesn't exist yet in trial01 or trial02. Now to explain why OracleXP can connect no matter what even if it is on the same machine as the server and why trial01 could connect without the hostname or domain name even with the tnsnames copy from the server directory to the new WinXP_client machine.

                      I guess the following proves that my theory was right that you cannot connect without the hostname or domain or IP address of the server when connecting to the server while using the tnsnames file from the client and the you can't connect at all using the tnsnames from the server directory when copied to the client if connecting to the server from a remote machine except when using the OracleXP database because it connects no matter what and I can't explain why at the moment.
                      Now there is a run-on sentence that would make Herman Melville scream. I'm afraid I've read it several times and still can't parse out exactly what you are trying to say. But against my better judgement I'll try ..
                      you cannot connect without the hostname or domain or IP address of the server when connecting to the server while using the tnsnames file from the client
                      You cannot make any connection between any two computers without an IP address. A host name is just an alias that ultimately has to get resolved to in IP address. The tnsnames file is just a means of supplying that information to the TNS layer so that it can pass it on to the standard network stack.

                      "you can't connect at all using the tnsnames from the server directory when copied to the client"

                      Wrong, wrong, wrong, wrong, wrong. One more time. The tnsnames file is nothing more than a telephone directory. The information in it is NOT dependent on what machine it is on. I've asked you before, and since you insist on believing this, I'll ask again. Please indicate what bit of information in the tnsnames file needs to be changed when you copy it from the serve to a remote client.

                      Here's an extract from your own tnsnames file. I've added line numbers for reference
                      1 ORACLEXP =
                      2 (DESCRIPTION =
                      3 (ADDRESS_LIST =
                      4 (ADDRESS = (PROTOCOL = TCP)(HOST = WinXP)(PORT = 1521))
                      5 )
                      6 (CONNECT_DATA =
                      7 (SERVICE_NAME = OracleXP)
                      8 )
                      9 )
                      Line 1 is just an alias. You can put anything you want there. Whatever you put there is what you put in your connection request : SQLPLUS SCOTT/TIGER@ORACLEXP

                      Line 4 specifies three things - the protocol to be used in the communications (tcp), the host on which the database lives (WinXP) and the port the listener is useing (1521). All three of those items is about the server on which the database lives. That information does not change if the client moves from the database server to your laptop.

                      Line 7 is the name of the service the database is registered with the listener. Again, it has to do with the database. It doesn't change with the location of the client.

                      C:\Documents and Settings\Daniel J. Grossi II>sqlplus test/test@trial02

                      SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 18:15:34 2013

                      Copyright (c) 1982, 2005, Oracle. All rights reserved.

                      ERROR:
                      ORA-12154: TNS:could not resolve the connect identifier specified
                      Sigh. We are back to where we started. There is no entry 'trial02' in the tnsnames being used by this client.
                      Enter user-name: test
                      Enter password:
                      ERROR:
                      ORA-12560: TNS:protocol adapter error
                      You get a protocol adapter error at this point because sqlplus has thrown away the earlier attempt and is now trying a bequeath connection to a local database as specified by the value of the ORACLE_SID environment variable. No such database so the connection fails.
                      Enter user-name:
                      ERROR:
                      ORA-12560: TNS:protocol adapter error


                      SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

                      C:\Documents and Settings\Daniel J. Grossi II>sqlplus test/test@trial01

                      SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 18:16:00 2013

                      Copyright (c) 1982, 2005, Oracle. All rights reserved.

                      ERROR:
                      ORA-12154: TNS:could not resolve the connect identifier specified


                      Enter user-name:
                      ERROR:
                      ORA-12560: TNS:protocol adapter error


                      Enter user-name:
                      ERROR:
                      ORA-12560: TNS:protocol adapter error


                      SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

                      C:\Documents and Settings\Daniel J. Grossi II>sqlplus
                      scott/tiger@OracleXP

                      SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23
                      18:45:08 2013

                      Copyright (c) 1982, 2005, Oracle. All rights reserved.


                      Connected to:
                      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
                      Production
                      With the Partitioning, OLAP and Data Mining options

                      SQL>
                      Also the problem I was having with getting trial02 to connect could have also been related to the fact that I started up the machine with the Oracle server after the remote machine with the client, which is also a problem when using any server machine for that matter.
                      Well, you certainly cannot connect to a machine that is not started. But an ORA-12154 never got far enough to find out. With an ORA-12154, no packet ever reached the nic of the client machine. In fact, with an ORA-12154, tns never even passed a request to the standard network stack.

                      Edited by: 993126 on Apr 23, 2013 3:24 PM

                      Edited by: 993126 on Apr 23, 2013 3:49 PM

                      Edited by: 993126 on Apr 23, 2013 4:00 PM

                      Edited by: 993126 on Apr 23, 2013 4:59 PM
                      So what your saying is tnsnames is like dns in that it resolves hostnames to IP addresses or maybe a better way to put it is that it resolves hostnames to databases or listeners on port 1521.

                      Edited by: 993126 on Apr 24, 2013 2:44 PM
                      • 38. Re: Difference between server and client tnsnames files
                        sb92075
                        993126 wrote:

                        So what your saying is tnsnames is like dns in that it resolves hostnames to IP addresses or maybe a better way to put it is that it resolves hostnames to databases or listeners on port 1521.
                        tnsnames.ora file resolves TNS_ALIAS to hostname, SID/SERVICE_NAME & port number;
                        which allows ANY client to connect to any database specified within the tnsnames.ora file.
                        • 39. Re: Difference between server and client tnsnames files
                          EdStevens
                          >
                          <snip>
                          So what your saying is tnsnames is like dns in that it resolves hostnames to IP addresses or maybe a better way to put it is that it resolves hostnames to databases or listeners on port 1521.

                          Edited by: 993126 on Apr 24, 2013 2:44 PM
                          You are getting close. tnsnames does not resolve a host name. A host name is the name of a computer, which at some point needs to be resolved to an IP address. It is not Oracle's responsibility to make that resolution, that comes early in the OSI network stack. But in more general terms, yes the tnsnames serves a very similar function to dns, just earlier in the process. It provides a resolution from a 'net service name' to a host, port, and service. Look again at the example I took from your own tnsnames.ora. Line 1, "ORACLEXP" is the net service name. Again, that can be anything -- anything. It is just an alias. By convention and convenience we usually use the same value as the name of the database, but that is just a convenience for us humans. Look again at the example on my blog. I deliberately made the net service name different from the database name specifically to drive home that point.

                          And what does the tnsames file resolve that to? Look again at line 4 of the example. It resolves it to a host name and a port. That is so that tns (oracle's Transparent Network Substrate) can build a package to pass on to the OSI network stack. Nothing there about databases. Nothing there about listeners. And you could hard-code the IP address in place of the host (host=192.168.56.101 instead of host=myserve). The packet then gets delivered to the designated IP address on the designated port, to be handled by whatever service is using that port. In our case, that service needs to be the listener. It should be obvious that the port number is also configurable, but 1521 is the port that the listener uses by default. If you configured the listener to use a different port -- say 1523 -- you'd have to modify tnsnames accordingly. The request goes to the listener, not the database. The only thing in that tnsnames entry that is associated directly with the database is the SERVICE_NAME parameter. That is the name by which the listener knows the database, which may or may not be the actual database name.

                          And once the listener has connected the client and the database instance, it -- the listener -- is out of the picture. It creates connections, it does not sustain them. You can kill the listener, and any existing connections will continue on their merry way.
                          • 40. Re: Difference between server and client tnsnames files
                            996129
                            EdStevens wrote:
                            >
                            <snip>
                            So what your saying is tnsnames is like dns in that it resolves hostnames to IP addresses or maybe a better way to put it is that it resolves hostnames to databases or listeners on port 1521.

                            Edited by: 993126 on Apr 24, 2013 2:44 PM
                            You are getting close. tnsnames does not resolve a host name. A host name is the name of a computer, which at some point needs to be resolved to an IP address. It is not Oracle's responsibility to make that resolution, that comes early in the OSI network stack. But in more general terms, yes the tnsnames serves a very similar function to dns, just earlier in the process. It provides a resolution from a 'net service name' to a host, port, and service. Look again at the example I took from your own tnsnames.ora. Line 1, "ORACLEXP" is the net service name. Again, that can be anything -- anything. It is just an alias. By convention and convenience we usually use the same value as the name of the database, but that is just a convenience for us humans. Look again at the example on my blog. I deliberately made the net service name different from the database name specifically to drive home that point.

                            And what does the tnsames file resolve that to? Look again at line 4 of the example. It resolves it to a host name and a port. That is so that tns (oracle's Transparent Network Substrate) can build a package to pass on to the OSI network stack. Nothing there about databases. Nothing there about listeners. And you could hard-code the IP address in place of the host (host=192.168.56.101 instead of host=myserve). The packet then gets delivered to the designated IP address on the designated port, to be handled by whatever service is using that port. In our case, that service needs to be the listener. It should be obvious that the port number is also configurable, but 1521 is the port that the listener uses by default. If you configured the listener to use a different port -- say 1523 -- you'd have to modify tnsnames accordingly. The request goes to the listener, not the database. The only thing in that tnsnames entry that is associated directly with the database is the SERVICE_NAME parameter. That is the name by which the listener knows the database, which may or may not be the actual database name.

                            And once the listener has connected the client and the database instance, it -- the listener -- is out of the picture. It creates connections, it does not sustain them. You can kill the listener, and any existing connections will continue on their merry way.
                            Now your starting to make sense and now I kinda have the answer I was looking for. Also it would seem though that if Oracle had intended you to copy the tnsnames file from the server directory to the client directory whether a remote client or on the server that they would have designed it, so that both files would be the same in the first place. An then everything would still work as I was pointing out because when you do this you can't access all the databases in Enterprise Manager. It's one of the reasons why I disagree with do that is because why change something that works even if it is to much it function simpler, but then cause everything else to break especially when using a remote client.

                            Edited by: 993126 on Apr 24, 2013 4:51 PM

                            Edited by: 993126 on Apr 24, 2013 4:52 PM
                            • 41. Re: Difference between server and client tnsnames files
                              sb92075
                              993126 wrote:
                              Now your starting to make sense and now I kinda have the answer I was looking for. Also it would seem though that if Oracle had intended you to copy the tnsnames file from the server directory to the client directory whether a remote client or on the server that they would have designed it, so that both files would be the same in the first place.
                              Oracle does NOT require use of SQL*Net or listener to start or use any Oracle database.
                              Exactly how is "oracle" supposed to know or determine which databases exist on which system
                              in order to populate tnsnames.ora file to match YOUR environment?
                              You need to take responsibility for your own configuration.

                              An then everything would still work as I was pointing out because when you do this you can't access all the databases in Enterprise Manager.
                              EM is a lame & brain dead piece of software as far as I am concerned.
                              the default configuration is that it can only inter-operate with a single database;
                              regardless of the content of tnsnames.ora file.
                              It's one of the reasons why I disagree with do that is because why change something that works even if it is to much it function simpler, but then cause everything else to break especially when using a remote client.
                              HUH?
                              When you shoot yourself in the foot, you should not blame the pistol for being the problem!

                              Problem Exists Between Keyboard And Chair!!!!
                              • 42. Re: Difference between server and client tnsnames files
                                996129
                                sb92075 wrote:
                                993126 wrote:
                                Now your starting to make sense and now I kinda have the answer I was looking for. Also it would seem though that if Oracle had intended you to copy the tnsnames file from the server directory to the client directory whether a remote client or on the server that they would have designed it, so that both files would be the same in the first place.
                                Oracle does NOT require use of SQL*Net or listener to start or use any Oracle database.
                                Exactly how is "oracle" supposed to know or determine which databases exist on which system
                                in order to populate tnsnames.ora file to match YOUR environment?
                                You need to take responsibility for your own configuration.

                                An then everything would still work as I was pointing out because when you do this you can't access all the databases in Enterprise Manager.
                                EM is a lame & brain dead piece of software as far as I am concerned.
                                the default configuration is that it can only inter-operate with a single database;
                                regardless of the content of tnsnames.ora file.
                                It's one of the reasons why I disagree with do that is because why change something that works even if it is to much it function simpler, but then cause everything else to break especially when using a remote client.
                                HUH?
                                When you shoot yourself in the foot, you should not blame the pistol for being the problem!

                                Problem Exists Between Keyboard And Chair!!!!
                                I didn't shoot myself in the foot, but it's like everyone is telling me to and that it's ok. However I don't agree with this method and have stated my reasons and have even test my theory to prove it causes problems, but everyone still insists that it's ok.

                                Edited by: 993126 on Apr 26, 2013 4:56 PM
                                • 43. Re: Difference between server and client tnsnames files
                                  sb92075
                                  There is NO difference between tnsnames.ora file used on client or DB Server.

                                  If you claim differently, then post reproducible test case that demonstrates the purported difference(s).
                                  1 2 3 Previous Next