1 2 3 4 Previous Next 53 Replies Latest reply: Aug 28, 2012 6:05 AM by 958430 Go to original post RSS
      • 30. Re: connecting to Oracle with Instant Client
        Eduardo Legatti
        Hi,

        >>You don't need an ORACLE_HOME environment variable. - You don't need a TWO_TASK environment variable
        These variables is not mandatory but, in this case, are necessary for the OP.

        Take a look on this below from documentation:

        How do I specify connection strings in Instant Client mode?
        All Oracle net naming methods that do not require use of ORACLE_HOME or TNS_ADMIN (to locate configuration files such as tnsnames.ora or sqlnet.ora) work in the Instant Client mode. In particular, the connect string can be specified in the following formats:

        A SQL Connect URL string of the form:

        //host:[port][service name]

        such as:

        //dbase-server-5:4321/ORDERS

        As an Oracle Net keyword-value pair. For example:

        "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242)
        (PORT=5521))
        (CONNECT_DATA=(SERVICE_NAME=bjava21)))"

        Naming methods that require TNS_ADMIN to locate configuration files continue to work if the TNS_ADMIN environment variable is set.

        If the TNS_ADMIN environment variable is not set, and TNSNAMES entries such as inst1, and so on, are used, then the ORACLE_HOME variable must be set, and the configuration files are expected to be in the $ORACLE_HOME/network/admin directory.

        Please note that the ORACLE_HOME variable in this case is only used for locating Oracle Net configuration files, and no other component of Client Code Library (OCI, NLS, and so on) uses the value of ORACLE_HOME.

        The bequeath adapter or the empty connect strings are not supported. However, an alternate way to use the empty connect string is to set the TWO_TASK environment variable on UNIX, or the LOCAL variable on Windows, to either a tnsnames.ora entry or an Oracle Net keyword-value pair. If TWO_TASK or LOCAL is set to a tnsnames.ora entry, then the tnsnames.ora file must be able to be loaded by TNS_ADMIN or ORACLE_HOME setting.

        Docs:
        http://www.oracle.com/technology/tech/oci/instantclient/index.html
        http://www.oracle.com/technology/tech/oci/instantclient/ic-faq.html

        Cheers
        • 31. Re: connecting to Oracle with Instant Client
          LaurenzAlbe
          Yes, yes, I know, I know...

          But what is the connection to the original problem?
          Since TNS_ADMIN is set in the environment, ORACLE_HOME will not be used, right?
          And since an explicit TNS-Name was given in the connection attempt, why should setting TWO_TASK help?

          Yours,
          Laurenz Albe
          • 32. Re: connecting to Oracle with Instant Client
            Eduardo Legatti
            Hi,

            >>Since TNS_ADMIN is set in the environment, ORACLE_HOME will not be used, right?
            Right. But I don't see any problem to set it with the same value to the TNS_ADMIN variable

            >>why should setting TWO_TASK help?
            Well, the TWO_TASK environment variable specifies a SQL*Net connect string for connecting to a remote machine in a client/server configuration. Then the OP do not need specify the @SERVICE on your connection string

            Cheers
            • 33. Re: connecting to Oracle with Instant Client
              470072
              Hi All,
              I set ORACLE_HOME and the TWO_TASK and still no luck. I did get the trace on the SQLNET.ORA on and here's what I got:

              [28-DEC-2006 10:13:46:749] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
              [28-DEC-2006 10:13:46:749] New trace stream is /Library/instantclient10_1/cli_240.trc
              [28-DEC-2006 10:13:46:749] New trace level is 16
              [28-DEC-2006 10:13:46:749] --- TRACE CONFIGURATION INFORMATION ENDS ---
              [28-DEC-2006 10:13:46:749] --- PARAMETER SOURCE INFORMATION FOLLOWS ---
              [28-DEC-2006 10:13:46:749] Attempted load of system pfile source /Library/instantclient10_1/sqlnet.ora
              [28-DEC-2006 10:13:46:749] Load contained errors
              [28-DEC-2006 10:13:46:749] Error stack follows:
              NL-00422: Message 422 not found; No message file for product=network, facility=NL

              [28-DEC-2006 10:13:46:749]
              [28-DEC-2006 10:13:46:749] Attempted load of local pfile source /Users/michaelcoppage/.sqlnet.ora
              [28-DEC-2006 10:13:46:749] Parameter source was not loaded
              [28-DEC-2006 10:13:46:749]
              [28-DEC-2006 10:13:46:749] -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
              [28-DEC-2006 10:13:46:749] Some parameters may not have been loaded
              [28-DEC-2006 10:13:46:749] See dump for parameters which loaded OK
              [28-DEC-2006 10:13:46:749] -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
              [28-DEC-2006 10:13:46:749] TRACE_LEVEL_CLIENT = SUPPORT
              [28-DEC-2006 10:13:46:749] NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME)
              [28-DEC-2006 10:13:46:749] TRACE_DIRECTORY_CLIENT =
              [28-DEC-2006 10:13:46:750] --- PARAMETER SOURCE INFORMATION ENDS ---
              [28-DEC-2006 10:13:46:750] --- LOG CONFIGURATION INFORMATION FOLLOWS ---
              [28-DEC-2006 10:13:46:750] Log stream will be "/Library/instantclient10_1/sqlnet.log"
              [28-DEC-2006 10:13:46:750] Log stream validation not requested
              [28-DEC-2006 10:13:46:750] --- LOG CONFIGURATION INFORMATION ENDS ---

              [28-DEC-2006 10:13:46:750] nigini: entry
              [28-DEC-2006 10:13:46:750] nigini: Count in the NL global area is now 1
              [28-DEC-2006 10:13:46:750] nigini: Count in NI global area now: 1
              [28-DEC-2006 10:13:46:750] nrigbi: entry
              [28-DEC-2006 10:13:46:750] nrigbni: entry
              [28-DEC-2006 10:13:46:750] nrigbni: Unable to get data from navigation file tnsnav.ora
              [28-DEC-2006 10:13:46:750] nrigbni: exit
              [28-DEC-2006 10:13:46:750] nrigbi: exit
              [28-DEC-2006 10:13:46:750] nigini: exit
              [28-DEC-2006 10:13:46:750] niqname: Using nnfsn2a() to build connect descriptor for (possibly remote) database.
              [28-DEC-2006 10:13:46:750] nnfgiinit: entry
              [28-DEC-2006 10:13:46:761] nncpcin_maybe_init: first request sent to name server will have ID 0
              [28-DEC-2006 10:13:46:761] nncpcin_maybe_init: initial retry timeout for all name servers is 1500 csecs
              [28-DEC-2006 10:13:46:761] nncpcin_maybe_init: max request retries per name server is 1
              [28-DEC-2006 10:13:46:761] nngsini_init_streams: initializing stream subsystem, cache size is 10
              [28-DEC-2006 10:13:46:761] nngtini_init_msg: initializing PDU subsystem, initial pool size is 2
              [28-DEC-2006 10:13:46:761] nncpcin_maybe_init: default name server domain is [root]
              [28-DEC-2006 10:13:46:761] nnfgiinit: Installing read path
              [28-DEC-2006 10:13:46:761] nnfgsrsp: entry
              [28-DEC-2006 10:13:46:762] nnfgsrsp: Obtaining path parameter from names.directory_path or native_names.directory_path
              [28-DEC-2006 10:13:46:762] nnfgsrdp: entry
              [28-DEC-2006 10:13:46:762] nnfgsrdp: Setting path:
              [28-DEC-2006 10:13:46:762] nnfgsrdp: checking element TNSNAMES
              [28-DEC-2006 10:13:46:762] nnfgsrdp: checking element HOSTNAME
              [28-DEC-2006 10:13:46:762] nnfgsrdp: Path set
              [28-DEC-2006 10:13:46:762] nnfun2a: entry
              [28-DEC-2006 10:13:46:762] nlolgobj: entry
              [28-DEC-2006 10:13:46:762] nnfgrne: entry
              [28-DEC-2006 10:13:46:762] nnfgrne: Going though read path adapters
              [28-DEC-2006 10:13:46:762] nnfgrne: Switching to TNSNAMES adapter
              [28-DEC-2006 10:13:46:762] nnftboot: entry
              [28-DEC-2006 10:13:46:762] nlpaxini: entry
              [28-DEC-2006 10:13:46:762] nlpaxini: exit
              [28-DEC-2006 10:13:46:762] nnftmlf_make_local_addrfile: entry
              [28-DEC-2006 10:13:46:762] nnftmlf_make_local_addrfile: construction of local names file failed
              [28-DEC-2006 10:13:46:762] nnftmlf_make_local_addrfile: exit
              [28-DEC-2006 10:13:46:762] nlpaxini: entry
              [28-DEC-2006 10:13:46:762] nlpaxini: exit
              [28-DEC-2006 10:13:46:762] nnftmlf_make_system_addrfile: entry
              [28-DEC-2006 10:13:46:763] nnftmlf_make_system_addrfile: system names file is /Library/instantclient10_1/tnsnames.ora
              [28-DEC-2006 10:13:46:763] nnftmlf_make_system_addrfile: exit
              [28-DEC-2006 10:13:46:763] nnftboot: exit
              [28-DEC-2006 10:13:46:763] nnftrne: entry
              [28-DEC-2006 10:13:46:763] nnftrne: Original name: ORCL
              [28-DEC-2006 10:13:46:763] nnfttran: entry
              [28-DEC-2006 10:13:46:763] nncpdpt_dump_ptable: --- /Library/instantclient10_1/tnsnames.ora TABLE HAS THE FOLLOWING CONTENTS ---
              [28-DEC-2006 10:13:46:763] nncpdpt_dump_ptable: ORCL =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
              )
              )

              [28-DEC-2006 10:13:46:763] nncpdpt_dump_ptable: --- END /Library/instantclient10_1/tnsnames.ora TABLE ---
              [28-DEC-2006 10:13:46:763] nnfttran: exit
              [28-DEC-2006 10:13:46:763] nnftrne: Using tnsnames.ora address
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
              )
              )
              for name ORCL
              [28-DEC-2006 10:13:46:763] nnftrne: exit
              [28-DEC-2006 10:13:46:763] nnfgrne: exit
              [28-DEC-2006 10:13:46:763] nlolgserv: entry
              [28-DEC-2006 10:13:46:764] nnfggav: entry
              [28-DEC-2006 10:13:46:764] nnftgav: entry
              [28-DEC-2006 10:13:46:764] nnftgav: exit
              [28-DEC-2006 10:13:46:764] nnfgfrm: entry
              [28-DEC-2006 10:13:46:764] nnftfrm: entry
              [28-DEC-2006 10:13:46:764] nnftfrm: exit
              [28-DEC-2006 10:13:46:764] nnfgfrm: exit
              [28-DEC-2006 10:13:46:764] nlolgserv: exit
              [28-DEC-2006 10:13:46:764] nlolgobj: exit
              [28-DEC-2006 10:13:46:764] nlolfmem: entry
              [28-DEC-2006 10:13:46:764] nlolfmem: exit
              [28-DEC-2006 10:13:46:764] nigtrm: Count in the NI global area is now 1
              [28-DEC-2006 10:13:46:764] nigtrm: Count in the NL global area is now 1
              [28-DEC-2006 10:13:46:765] nigini: entry
              [28-DEC-2006 10:13:46:765] nigini: Count in the NL global area is now 2
              [28-DEC-2006 10:13:46:765] nigini: Count in NI global area now: 2
              [28-DEC-2006 10:13:46:765] nigini: exit
              [28-DEC-2006 10:13:46:765] niqname: Using nnfsn2a() to build connect descriptor for (possibly remote) database.
              [28-DEC-2006 10:13:46:765] nnfun2a: entry
              [28-DEC-2006 10:13:46:765] nlolgobj: entry
              [28-DEC-2006 10:13:46:765] nnfgrne: entry
              [28-DEC-2006 10:13:46:765] nnfgrne: Going though read path adapters
              [28-DEC-2006 10:13:46:765] nnfgrne: Switching to TNSNAMES adapter
              [28-DEC-2006 10:13:46:765] nnftrne: entry
              [28-DEC-2006 10:13:46:765] nnftrne: Original name:
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
              )
              )

              [28-DEC-2006 10:13:46:765] nnfttran: entry
              [28-DEC-2006 10:13:46:765] nnfttran: Error querying
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
              )
              )
              of attribute A.SMD errcode 408
              [28-DEC-2006 10:13:46:765] nnfgrne: Query unsuccessful, skipping to next adapter
              [28-DEC-2006 10:13:46:765] nnfgrne: Switching to HOSTNAME adapter
              [28-DEC-2006 10:13:46:765] nnfhboot: entry
              [28-DEC-2006 10:13:46:765] nnfhboot: exit
              [28-DEC-2006 10:13:46:767] nnfhrne: Error querying
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
              )
              )
              of attribute A.SMD errcode 406
              [28-DEC-2006 10:13:46:767] nnfgrne: Query unsuccessful, skipping to next adapter
              [28-DEC-2006 10:13:46:767] nnfgrne: exit
              [28-DEC-2006 10:13:46:767] nnfun2a: address for name "
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
              )
              )
              " not found
              [28-DEC-2006 10:13:46:767] niqname: Return code from nnfsn2a is 1
              [28-DEC-2006 10:13:46:767] nigtrm: Count in the NI global area is now 1
              [28-DEC-2006 10:13:46:767] nigtrm: Count in the NL global area is now 1
              • 34. Re: connecting to Oracle with Instant Client
                470072
                Hi All,
                Here's what's now in my .profile:

                DYLD_LIBRARY_PATH=/Library/instantclient10_1:$DYLD_LIBRARY_PATH
                LD_LIBRARY_PATH=/Library/instantclient10_1:$LD_LIBRARY_PATH
                SQLPATH=/Library/instantclient10_1
                TNS_ADMIN=/Library/instantclient10_1
                PATH=/Library/instantclient10_1:$PATH
                ORACLE_HOME=/Library/intantclient10_1
                TWO_TASK=ORCL
                export DYLD_LIBRARY_PATH
                export LD_LIBRARY
                export SQLPATH
                export TNS_ADMIN
                export PATH
                export ORACLE_HOME
                export TWO_TASK

                I'm not sure what's needed and what's not. I bet it's one little annoying thing I'm either missing or I've set wrong.

                Thanks to everyone for your help!

                Mike
                • 35. Re: connecting to Oracle with Instant Client
                  Eduardo Legatti
                  Hi,

                  This below is a configuration on a client Linux machine that connect to a remote Oracle XE Linux Server.
                  oracle@icaro:~/instantclient> ls -l
                  total 130856
                  -rwxr-xr-x  1 oracle users 33145508 2000-01-01 00:15 classes12.jar
                  -rwxr-xr-x  1 oracle users     1525 2006-02-05 04:50 glogin.sql
                  -rwxr-xr-x  1 oracle users 18774535 2006-02-05 04:50 libclntsh.so.10.1
                  -rwxr-xr-x  1 oracle users  5623929 2006-02-05 04:50 libnnz10.so
                  -rwxr-xr-x  1 oracle users  1398088 2006-02-05 04:50 libocci.so.10.1
                  -rwxr-xr-x  1 oracle users 70690282 2006-02-05 04:50 libociei.so
                  -rwxr-xr-x  1 oracle users   119919 2006-02-05 04:50 libocijdbc10.so
                  -rwxr-xr-x  1 oracle users  1434235 2006-02-05 04:50 libsqlplusic.so
                  -rwxr-xr-x  1 oracle users  1053325 2006-02-05 04:50 libsqlplus.so
                  -rwxr-xr-x  1 oracle users  1540457 2006-02-05 04:50 ojdbc14.jar
                  -rwxr-xr-x  1 oracle users     8867 2006-02-05 04:50 sqlplus
                  -rw-r--r--  1 oracle users      169 2006-04-18 11:21 tnsnames.ora

                  oracle@icaro:~/instantclient> cat tnsnames.ora

                  XE =
                    (DESCRIPTION =
                      (ADDRESS_LIST =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.x.x)(PORT = 1521))
                      )
                      (CONNECT_DATA =
                        (SID = XE)
                      )
                    )

                  oracle@icaro:~/instantclient> cd ..

                  oracle@icaro:~> cat .profile

                  export TEMP=/tmp
                  export TMPDIR=/tmp
                  export ORACLE_HOME=/home/oracle/instantclient
                  export PATH=$PATH:$ORACLE_HOME
                  export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
                  export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
                  export NLS_DATE_FORMAT="DD/MM/YYYY"
                  export TNS_ADMIN=$ORACLE_HOME
                  export TWO_TASK=XE

                  oracle@icaro:~> sqlplus system/password@XE

                  SQL*Plus: Release 10.2.0.2.0 - Production on Qui Dez 28 14:34:53 2006

                  Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

                  Connected to:
                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

                  SQL> exit

                  Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product                                              ion
                  oracle@icaro:~> sqlplus system/password

                  SQL*Plus: Release 10.2.0.2.0 - Production on Qui Dez 28 14:35:11 2006

                  Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

                  Connected to:
                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

                  SQL> exit

                  Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product                                              ion
                  oracle@icaro:~> sqlplus system/password@xx.xx.x.x:1521/XE

                  SQL*Plus: Release 10.2.0.2.0 - Production on Qui Dez 28 14:35:43 2006

                  Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

                  Connected to:
                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

                  SQL> exit

                  Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                  oracle@icaro:~>
                  Cheers
                  • 36. Re: connecting to Oracle with Instant Client
                    470072
                    Hi Legatti,
                    I think we're close. It may not even matter, but I can connect using these methods.

                    sqlplus system/password@xx.xx.x.x:1521/orcl
                    sqlplus system/password

                    but not:
                    sqlplus system/password@ORCL

                    My dir listing is:

                    README_IC.htm
                    libocci.dylib.10.1
                    orai18n.jar
                    classes12.jar
                    libociei.dylib
                    sqlnet.log
                    glogin.sql
                    libocijdbc10.dylib
                    sqlnet.ora
                    libclntsh.dylib.10.1
                    libsqlplus.dylib
                    sqlplus
                    libheteroxa10.dylib
                    ocrs12.jar
                    tnsnames.ora
                    libnnz10.dylib
                    ojdbc14.jar

                    It's strange "system/Password" would work and not "system/Password@ORCL"

                    Thanks,
                    Mike
                    • 37. Re: connecting to Oracle with Instant Client
                      Eduardo Legatti
                      Hi,

                      >>sqlplus system/password@xx.xx.x.x:1521/orcl
                      sqlplus system/password

                      I agree that is very strange why the @ORCL not work, but if these both methods above works, why to be worried ?

                      Cheers
                      • 38. Re: connecting to Oracle with Instant Client
                        470072
                        Hi Legatti,
                        I went back and made my .profile match yours and commented out the DYLD_LIBRARY_PATH and SQLPATH. I also noticed you didn't have a sqlnet.ora file so I renamed mine to see what happens. After restarting I tried to run sqlplus and got an error about it finding a lib file that located in the same dir. Upon putting the DYLD_LIBRARY_PATH back in, I was able to run sqlplus and connect using either:
                        sqlplus system/password@xx.xx.x.x:1521/orcl
                        sqlplus system/password

                        I still can't use:
                        sqlplus system/password@ORCL

                        So right now, I'm not using a sqlnet.ora file and I'm not setting SQLPATH in my .profile.

                        Your right. I'm able to connect. I just feel like I'm doing something wrong and want to know what and how things work. Say I want to connect to another Oracle Server, then I would just have to change the TWO_TASK variable right?

                        Thanks!
                        Mike
                        • 39. Re: connecting to Oracle with Instant Client
                          Eduardo Legatti
                          Hi,

                          >>then I would just have to change the TWO_TASK variable right?
                          Technically, yes.

                          If you unset the TWO_TASK variable, you can get to connect?
                          Currently, exist some other oracle client installed on your machine beyond instant client?

                          Cheers
                          • 40. Re: connecting to Oracle with Instant Client
                            470072
                            Hi,
                            If I comment out the TWO_TASK, I can only log into my DB using the sqlplus system/password@xx.xx.x.x:1521/orcl format. The system/password@ORCL still doesn't work. If I leave the TWO_TASK alone, I can use the long format to connect to another machine/database.

                            There isn't another client that I know of. I did try to install 10G R1 on this computer a long time ago, but I've since removed the Oracle home dir, Oracle User and any files that I found associated with it.

                            I've checked by environment varibles and nothing (like PATH) is pointing anywhere that other than the directory for my instantclient, sbin, and bin. I've also done a search on my hard drive for any tnsnames file.

                            Thanks,
                            Mike
                            • 41. Re: connecting to Oracle with Instant Client
                              Eduardo Legatti
                              Hi,

                              Try add these service names on your TNSNAMES.ORA:
                              TEST1 =
                                (DESCRIPTION =
                                  (ADDRESS_LIST =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
                                  )
                                  (CONNECT_DATA =
                                    (SID = orcl)
                                  )
                                )

                              TEST2 = "CentOSServ6:1521/orcl"
                              Make a test and see what's happen:
                              sqlplus system/password@TEST1
                              sqlplus system/password@TEST2
                              Cheers
                              • 42. Re: connecting to Oracle with Instant Client
                                470072
                                TEST2 works. TEST1 gives me the following error:

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

                                Thanks,
                                Mike
                                • 43. Re: connecting to Oracle with Instant Client
                                  Eduardo Legatti
                                  Hi,

                                  >>TEST2 works.
                                  Ok, then you can use the easy connect sintax...
                                  ORCL = "CentOSServ6:1521/orcl"
                                  Cheers
                                  • 44. Re: connecting to Oracle with Instant Client
                                    470072
                                    Thanks for your help!

                                    Mike