8 Replies Latest reply on Feb 18, 2011 9:20 PM by 841076

    Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error

    WalterM2
      I am building the Perl DBD::Oracle module using InstantClient 10.2.0.4.0 on HPUX 11.11. The build is successful, but the TEST fails with the following error:

      t/10general.t ...... DBI connect('','user/pwd@dbname','') failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS \
      settings, permissions, etc. at tmp.pl line 12
      Error ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, \
      permissions, etc. at tmp.pl line 12.

      The build and the testing is done as the "oracle" unix account which owns the Oracle RDBMS installation, the perl installation (which was built from scratch) and the instantclient installation.

      I have confirmed that the ORACLE_HOME points to the instantclient directory and that the basic, sdk and sqlplus zip files have been unpackaged there.

      SQLPLUS does connect properly as
      sqlplus user/pwd@dbname

      Any idea why the BUILD works, but the TEST fails?

      Specs:
      ======
      OS: HPUX 11.11
      RDBMS (for TNS_ADMIN and ORA_NLS10) setting: 10gR2
      InstantClient: 10.2.0.4.0 (for hp-parisc-32, according to READ it is for HPUX 11.11) (unpackaged by same account)
      Perl: 5.8.8 (built from source by same account)
      DBI: 1.607 (built from source by same account)
      DBD-Oracle: 1.22 (built from source by same account)
      ORACLE_HOME=<directory where instantclient was unpackaged) (basic, sdk and sqlplus)
      SHLIB_PATH=$ORACLE_HOME
      TNS_ADMIN=<tnsadmin directory of RDBMS 10gR2)
      ORA_NLS10=<RDBMS oracle_home>/nls/data
        • 1. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
          Laurenz Albe
          It is hardly surprising that it fails, as your environment and installation seems to be a mix of Instant Client and sever.

          If you want to have more than one Oracle installations on one machine, you should use different OS users for each.
          That way you can control the environment better.

          If you want to build DBD::Oracle, create a new user for it.
          Make sure that there are no Oracle environment variables set (NLS_..., ORA..., ..._ADMIN, TWO_TASK, ...).
          Add the instant client directory to PATH and SHLIB_PATH (if that is the shared library path on HP-UX) and make sure no other Oracle software is in these paths.
          Temporarily set ORACLE_HOME to the Instant Client directory and export it.
          Then build DBD::Oracle.
          Unset ORACLE_HOME. It is not needed for Instant Client.

          To test DBD::Oracle against the server installed on the same machine, set ORACLE_SID to the database name and ORACLE_USERID to the username/password pair you want to use for testing.

          Yours,
          Laurenz Albe
          • 2. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
            WalterM2
            Laurens,

            Thanks for your suggestion. I have tried what you suggested, but I am still encountering the same problem.

            I have been trying to build PERL DBD using InstantClient on an HPUX 11.11 server. The build fails when running the most basic test of the DBD module. This has been an ongoing issue which I need to resolve.

            The problem is not on the build, but on the running the tests of DBD.

            The environment:
            ================
            OS: HPUX 11.11
            Perl: 5.8.8
            DBI: 1.607
            DBD: 1.22
            InstantClient: 10.2.0.4
            Username: oracle, owner of the RDBMS 10.2.0.4 ORACLE_HOME and instance.
            Group: dba

            Due to a restriction on the customer's side, we have to install on the same UNIX account where the DB resides. To avoid interactions with the RDBMS ORACLE_HOME install, I have unset any variables associated with the RDBMS install. The following variables were explicitly unset:
            ORACLE_HOME
            ORACLE_SID
            TWO_TASK
            SHLIB_PATH
            LD_LIBRARY_PATH (not applicable to HPUX, but just in case)
            NLS_LANG
            ORA_NLS
            ORA_NLS10
            TNS_ADMIN
            ORAHOME
            This has been confirmed by running the following:
            $ env | grep NLS
            $ env | grep TNS
            $ env | grep PATH
            $ env | grep TWO
            $ env | grep ORA


            The build:
            ==========
            After that the following variables were set to the following minimal values:
            PATH=/usr/local/bin:/usr/bin:.:/usr/sbin:/bin
            SHLIB_PATH=/local/apps/oracle/ringm12/instantclient/instantclient_10_2:/usr/lib
            ORACLE_HOME=/local/apps/oracle/ringm12/instantclient/instantclient_10_2


            Then DBD was built without errors

            The test:
            =========
            The ORACLE_HOME was unset, since it is not needed for the test, and the ORACLE_SID was set. Since we are logged in as the UNIX oracle account that runs the ORACLE_SID, then we should not have file permissions problems on the underlying files.
            So, what was unset
            ORACLE_HOME
            What was set
            ORACLE_SID=DB2TEST

            The test fails. Following are the commands and resulting output:
            -----------------------------------------------------------------
            $ env | grep NLS
            $ env | grep TNS
            $ env | grep PATH
            $ env | grep TWO
            $ env | grep ORA
            ORACLE_SID=DB2TEST
            ORACLE_USERID=<user>/<pwd>
            ORACLE_TERM=vt220
            ORAENV_ASK=NO
            MANPATH=/usr/share/man/%L:/usr/share/man:/usr/contrib/man/%L:[...snip...]
            PATH=/usr/local/bin:/usr/bin:.:/usr/sbin:/bin
            FORMS30PATH=/local/apps/oracle/product/10.2/forms30/admin/resource
            SHLIB_PATH=/local/apps/oracle/ringm12/instantclient/instantclient_10_2:/usr/lib
            $ make test
            PERL_DL_NONLAZY=1 /local/apps/oracle/ringm12/perl/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
            t/01base................# Test loading DBI, DBD::Oracle and version
            ok
            t/10general.............DBI connect('','<user>/<pwd>',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc. at t/10general.t line 22
            FAILED--Further testing stopped: Unable to connect to Oracle (ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc.)
            make: *** [test_dynamic] Error 25
            -------------------------------------------------------------------


            Threading
            =========
            Reading some of the warnings and googling on some builds and tests, there was some reference to building with and without threading on both the PERL and the DBD builds. So I tested four combinations:
            PERL DBD
            ============= =============
            Threaded Non-threaded
            Threaded Threaded
            Non-threaded Non-threaded
            Non-threaded Threaded (which does not make sense)

            All combinations produce the same error when testing. Following are the build options used for PERL and DBD.
            PERL (Threaded) : sh Configure -Dcc=gcc -Dprefix=/local/apps/oracle/ringm12/perl -Duseithreads -A prepend:libswanted='cl pthread ' -de

            PERL (Non-threaded): sh Configure -Dcc=gcc -Dprefix=/local/apps/oracle/ringm12/perl -A prepend:libswanted='cl pthread ' -de
            DBD (Threaded) : perl Makefile.PL -l
            DBD (Non-threaded) : perl Makefile.PL -v


            Next Steps
            ==========
            I am not sure what else to try out. What other options can I try out?
            • 3. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
              Laurenz Albe
              Sorry, I must have been sleeping when I wrote that.
              You cannot make local connections with Instant Client.
              You'll have to connect via TCP.

              - Unset ORACLE_SID.
              - Make sure that PATH contains the Instant Client directoy, but not the Oracle server binaries.
              - export TWO_TASK='//localhost:1521/DBNAME.DBDOMAIN'
              (assuming that your listener port is 1521 and your service name is DBNAME.DBDOMAIN)
              - Set ORACLE_USERID as appropriate

              Now it should work!

              Yours,
              Laurenz Albe
              • 4. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
                WalterM2
                Laurenz,

                Once again, thanks for your suggestion. I tried UNsetting the ORACLE_SID and setting the TWO_TASK, but it still fails.

                So, I wondered if perhaps the instantclient libraries or binaries were corrupt or did not work on HPUX 11.11. I ran some tests (sqlplus) to make sure I could connect to the DB server. The sqlplus connectivity tests were successful, but the PERL-DBD-Instantclient test still fails.

                I attempted to set the TWO_TASK using the URL format you suggested, but sqlplus did not work correctly with it. The TWO_TASK format that did work was the old tnsnames.ora entry format.

                As part of the test, I made sure that tnsping was not in the PATH (to confirm there was no pathing to any other ORACLE_HOME) but that the sqlplus binary was only from the Instantclient directory.

                Building DBD:
                ==============
                As before, the only variables set were as follows:
                PATH=/local/apps/oracle/ringm12/instantclient/instantclient_10_2:/usr/local/bin:/usr/bin:.:/usr/sbin:/bin
                SHLIB_PATH=/local/apps/oracle/ringm12/instantclient/instantclient_10_2:/usr/lib
                ORACLE_HOME=/local/apps/oracle/ringm12/instantclient/instantclient_10_2

                Then DBD built without error:
                $ perl Makefile.PL -l
                <no error>
                $ make
                <no error>


                Testing Instantclient:
                ======================
                Right after the build I test the location of tnsping (not there) and sqlplus (instantclient directory only):
                -------------------------------------------------------------------------------------------------------------
                $ which tnsping
                no tnsping in /local/apps/oracle/ringm12/instantclient/instantclient_10_2 /usr/local/bin /usr/bin . /usr/sbin /bin
                $ which sqlplus
                /local/apps/oracle/ringm12/instantclient/instantclient_10_2/sqlplus

                I then set the TWO_TASK to the old format tnsnames.ora entry:
                -------------------------------------------------------------
                TWO_TASK="(description=(address=(community=tcp)(protocol=tcp)(host=dbserver.domain)(port=1529))(connect_data=(sid=db2test)))"


                I then attempt to connect using a bogus login and password via sqlplus (which fails as expected):
                --------------------------------------------------------------------------------------------------
                $ sqlplus bogus/pwd
                SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 23 13:20:53 2009

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

                ERROR:
                ORA-01017: invalid username/password; logon denied

                Finally I attempt to connect using the correct login and password (which connect as expected):
                ----------------------------------------------------------------------------------------------
                $ sqlplus CorrectLogin/CorrectPassword
                SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 23 13:20:53 2009

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


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

                SQL> SHOW USER
                CorrectLogin

                So, I believe I have confirmed that:
                1. I am not pathing or pointing to any other ORACLE_HOME, only to InstantClient
                2. The Instantclient's sqlplus correctly tests when provided a TWO_TASK and both a bogus login/password and a correct login/passwrod.
                3. At this point I believe my instantclient install is not corrupted or incompatible with my OS.

                Testing DBD:
                ============
                I now issue the test and it fails with the same error as in previous attempts:

                $ make test
                PERL_DL_NONLAZY=1 /local/apps/oracle/ringm12/perl/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
                t/01base................# Test loading DBI, DBD::Oracle and version
                ok
                t/10general.............DBI connect('','CorrectLogin/CorrectPassword',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc. at t/10general.t line 22
                FAILED--Further testing stopped: Unable to connect to Oracle (ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc.)
                make: *** [test_dynamic] Error 25


                PERL Threading:
                ===============
                For this test I have built PERL without threading and DBD without threading.


                Next steps:
                ===========
                What else can I try? Are there reports that perhaps this version of InstantClient does not work with HPUX 11.11, Platform: PA-RISC?
                I have been able to build DBD-instantclient against a HPUX 11.23, platform: Itanium without an issue. Could it be that InstantClient 10.2.0.4 does not work on HPUX 11.11, PA-RISC?

                As usual, thanks so much for your help.

                Yours,

                Walter
                • 5. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
                  Laurenz Albe
                  I don't know for sure.

                  First, it looks as if ORACLE_HOME is set when you "make test". Try without.

                  What is the result of "set" on the same command line where you "make test"?
                  Maybe there's some variable we didn't notice.

                  Then it would be interesting is you can make a simple program run (after make install)
                  to see if it is a problem with the execution of "make test" or something more serious.

                  You could use:

                  use DBI;

                  my $dbh = DBI->connect("dbi:Oracle:host=localhost;port=1521;sid=YOURSID", "username", "password");
                  my $sth = $dbh->prepare("SELECT sysdate FROM dual");
                  my $rv = $sth->execute;
                  DBI::dump_results($sth) if $rv;
                  $dbh->disconnect;

                  Yours,
                  Laurenz Albe
                  • 6. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
                    725646
                    Hi Walter,
                    I searched your posts. Your issue is the exactly the same as mine, except for my DBD version is 1.23, and OS is 11.23.
                    I tried Instant Client 10.1.0.5 and 10.2.0.2, no luck.
                    So did you get the solution now?

                    I appreciate your response.

                    Thank you,
                    Roy
                    • 7. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
                      633720
                      I face the same problem when trying to connect with a linux client to an aix server.
                      I noticed that the test runs successfully when run as root. So I did the following on the client side:

                      oraclient:/opt/oracle/oracle10# find . -type d | xargs chmod a+rx
                      oraclient:/opt/oracle/oracle10# find . -type f | xargs chmod a+r

                      now connect works.

                      Hope It helps.
                      • 8. Re: Perl DBD::Oracle InstantClient 10.2.0.4.0 on HPUX OCIEnvNlsCreate error
                        841076
                        Laurenz Albe wrote:

                        Sorry, I must have been sleeping when I wrote that.

                        You cannot make local connections with Instant Client.

                        You'll have to connect via TCP.



                        - Unset ORACLE_SID.

                        - Make sure that PATH contains the Instant Client directoy, but <B>not</B> the <font face="tahoma,verdana,sans-serif" size="1" color="#000">Oracle</font> server binaries.

                        - export TWO_TASK='//localhost:1521/DBNAME.DBDOMAIN'

                        (assuming that your listener port is 1521 and your service name is DBNAME.DBDOMAIN)

                        - Set ORACLE_USERID as appropriate



                        Now it should work!



                        Yours,

                        Laurenz Albe
                        My problem has been solved with it, Here is also the same case, Thanks for your answer!