This discussion is archived
7 Replies Latest reply: Oct 30, 2012 4:42 PM by cj RSS

oci_connect() fails sometimes

854760 Newbie
Currently Being Moderated
Hello!

I am familiar with php and mysql, but just beginning to use oracle. I have a very simple script which seems to sometimes work and sometimes not, and I can't really figure out the reason.

The php script simply reads some values from a table (see below). Sometimes it works as it should. But when I reload it a few times the following error appears:

Warning: oci_connect() [function.oci-connect]: OCIEnvNlsCreate() failed. There is something wrong with your system - please check that LD_LIBRARY_PATH includes the directory with Oracle Instant Client libraries in ...

And sometimes I get the following error:

Warning: oci_connect() [function.oci-connect]: ORA-00604: error occurred at recursive SQL level 1 ORA-12705: Cannot access NLS data files or invalid environment specified in ...

Sometimes, after a few more reloads, the script works again. I don't see any pattern.

The errors seem to indicate that something on the server side is messed up. But on the other hand, the script works from time to time. So my basic questions is: are such error produced by my php script, or are they php bugs, is it an apache problem or maybe some problem with the oracle installation? (I'm using the db of a large institution, that's why I doubt that the reason is a mis-configuration, but who knows). Maybe I should say that I also use sqldeveloper and without problems so far, so the connection seems to work. I also made sure I use the correct service string as specified in the tnsnames.ora.

Here is the php script, I guess it's pretty standard:

ini_set ("display_errors", "1");
error_reporting(E_ALL);
$c = oci_connect('...', '...', '...');
if(!$c) { /* some error handling */ }
$s = oci_parse($c, 'select TS from TEST_LOG');
oci_execute($s);
while ($res = oci_fetch_array($s, OCI_ASSOC)) { echo $res['TS']; }
oci_close($c);

I'm using php 5.2.11 on SunOS 5.10 and oracle 10.2g.

Any hints pointing me in the right direction are welcome!
  • 1. Re: oci_connect() fails sometimes
    cj Employee ACE
    Currently Being Moderated
    Curious.

    How/where/what are you setting Oracle environment variables to?

    How is PHP built - what libraries did the OCI8 extension build with and now link with?

    Have you tried setting the character set in the oci_connect() call? Once in a strange hacked test environment I saw this help but in that case no connections were succeeding. I never got a chance to look into it.
  • 2. Re: oci_connect() fails sometimes
    865056 Newbie
    Currently Being Moderated
    I am experiencing a very similar condition. I am seeing -intermittent- occurrences of the error message:

    "Warning: oci_connect() [function.oci-connect]: OCIEnvNlsCreate() failed. There is something wrong with your system - please check that LD_LIBRARY_PATH includes the directory with Oracle Instant Client libraries ..."

    All of the info I have found so far relates to a -persistent- error, and is generally solved by fixing access / permissions of the associated library files.

    My error is not due to access / permissions. -Most- of the time, the webapp works just fine. After 4 or 5 days, the above oci_connect() error shows up. Once it shows up, it persists until I restart Apache. Sometimes, I need to restart Apache more than once to make it go away again.

    My error is also not due to any path problems. Whether the error is present or not, phpinfo() shows all the relevant paths set to the correct values matching where the Oracle InstantClient is installed.

    Today, while the error condition was present, I ran strace on an Apache child process, and found the following lines:

    open("/usr/lib64/oracle/10.2.0.3/client/lib64/nls/data/lx1boot.nlb", O_RDONLY) = -1 ENOENT (No such file or directory)
    open("/usr/lib64/oracle/10.2.0.3/client/lib64/oracore/zoneinfo/timezlrg.dat", O_RDONLY) = -1 ENOENT (No such file or directory)
    open("/usr/lib64/oracle/10.2.0.3/client/lib64/oracore/zoneinfo/timezlrg.dat", O_RDONLY) = -1 ENOENT (No such file or directory)

    These occur right after Apache reads in the .php file containing the function call to connect to the Oracle database.

    I certainly understand what this strace output is telling me. What I don't understand is why this is only an -intermittent- error. If the absence of these files causes this error, the webapp should -always- be failing. If OCI can function without them (which clearly it does most of the time), then what causes it to change behavior and start bombing out?

    For the sake of completeness, when the error condition is -not- present, at that same point in the process, strace shows:

    fcntl(-960582384, F_SETFD, FD_CLOEXEC) = -1 EBADF (Bad file descriptor)

    and then merrily goes on to read tnsnames.ora, open a socket to the Oracle host, etc. Despite the fact that this is an error, it does -not- trigger the oci_connect() problem.

    Also, this only started happening when we moved the webapp from one host to another newly built host. On the previous host, which was the "same" in every way that seems to matter, this webapp worked consistently. On that host, none of the files mentioned in the above strace exist. The Oracle InstantClient directory trees are identical between the two hosts.

    Some basic system info:
    # uname -a
    Linux webapp04.revol.com 2.6.18-164.15.1.el5 #1 SMP Wed Mar 17 11:30:06 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
    # httpd -V
    Server version: Apache/2.2.3
    Server built: Mar 27 2010 13:52:09
    Server's Module Magic Number: 20051115:3
    Server loaded: APR 1.2.7, APR-Util 1.2.7
    Compiled using: APR 1.2.7, APR-Util 1.2.7
    Architecture: 64-bit
    # php -v
    PHP 5.1.6 (cli) (built: Jan 13 2010 17:09:42)
    Copyright (c) 1997-2006 The PHP Group
    Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies
    # Oracle InstantClient version is 10.2.0.3

    Thanks for any information or insight that anyone can provide.
  • 3. Re: oci_connect() fails sometimes
    cj Employee ACE
    Currently Being Moderated
    Make sure environment variables are set before Apache starts. I'm guessing you have Oracle Linux or RHEL.
    If so, put the variables in /etc/sysconfig/httpd

    What version of the OCI8 extension do you have?

    I can't explain why it is intermittent.
  • 4. Re: oci_connect() fails sometimes
    802936 Newbie
    Currently Being Moderated
    Hey, we have been experiencing the same issue. Have you found out a solution on this?

    Thanks,
    Sumit
  • 5. Re: oci_connect() fails sometimes
    cj Employee ACE
    Currently Being Moderated
    Can you give more (a lot more) detail about your configuration? This might help in spotting a pattern.
    See all the questions in my previous posts in this thread.
  • 6. Re: oci_connect() fails sometimes
    971196 Newbie
    Currently Being Moderated
    Hello,

    I experienced same problem. Problem has gone after adding ORACLE_HOME variable to /etc/sysconfig/httpd

    But before I experienced problems with apache / php / oci communication.

    System Details:

    OS: CentOS 5,6 (both 32 and 64 bits) (tried on 4 different virtual servers to investigate case)
    Oracle Client: 11gR2, not instant client.

    ORACLE_HOME is set via /etc/profile and is system wide.

    OCI8 compiled without problems. Works in mod_php no CGI.

    PHP CLI mode was working fine but I was getting OCIEnvNlsCreate error all the time from apache. Adding environment variables via apache SetEnv does not help. Workaround was to add putenv ORACLE_HOME variable in php files.

    but experienced OCIEnvNlsCreate error time by time. (I traced it to oci8.c file line 2925 for oci8 version 1.4.9 - this is if you see error not permanently and if error is permanent when it's line 1082 same file )

    All issues fixed after adding ORACLE_HOME variable to /etc/sysconfig/httpd.
  • 7. Re: oci_connect() fails sometimes
    cj Employee ACE
    Currently Being Moderated
    Setting the environment correctly is important and I'm glad you got the right location to configure the values.

    This thread started about intermittent failures. So far no one has commented on how the environment in those cases was set.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points