Getting PHP working with a remote Oracle DB

[bpoppa]

Ok,

I have yanked my hair out long enough, and need help. Anybody, please.

Here is my situation.

I have PHP5 running on a Redhat 8 box with Apache2 w/ssl. I need to connect to a working remote Oracle 9i machine running windows 2k.

So, on the linux box I installed the Oracle 9i client and OCI. I can tnsping the remote server fine. I can even sqlplus to the remote server fine. However, in PHP5 I get the error

---

Error while trying to retrieve text for error ORA-12541 in .......

I have the following env vars:

export ORACLE_HOME=/home/oracle/product/9.0.1
export ORACLE_BASE=/home/oracle
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export ORACLE_TERM=xterm
export ORACLE_SID=RAPPY
export PATH=$PATH:$ORACLE_HOME/bin:/usr/local/java/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin
export TWO_TASK=$ORACLE_HOME/network/admin/tnsnames.ora
export LD_PRELOAD=/home/oracle/product/9.0.1/lib/libclntsh.so.9.0

These vars were also set while compiling PHP and are also in the apache startup script.

I have tried everything I can think of and am google'd out, please anyone with any ideas are certainly welcome.

Also, my tnsnames.ora looks like this:

RAPPY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = RAPPY)
)

Of course there is an IP where the x's are.

)

Thanks!

Jim

[tsinka]

Hi,

the ORA error message means that the listener is not running. Check if the listener is running at all and if the SID/SERVICE_NAME exists. Also check if the listener.ora file contains an entry for the given SID.

Make sure that the given SID/SERVICE_NAME exists

The errror message about "could not find..." means that you might need to set the environment variable NLS_LANG to e.g.

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
or
NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1

That variable should match the character set of the instance you want to connect to.

EDIT: You can tnsping the remote server ... so it seems to be set up almost correctly ... how does the PHP script look like ?

EDIT 2: I just woke up ... my brain is a little bit slow ... how did you export the environment variables ? By hand or in a profiles file (user or global) ? You need to make sure that the apache daemon knows about that environment variables. The not so beautiful approach would be to put that exports in the apache init script.

Thomas

[bpoppa]

Thomas,

Thank you so much for your reply.

I checked the listener.ora on the remote server and it does have an entry for the SID.

Also, I manually set the env variables and then compiled PHP. I also set then in the Apache init script as well.

I have tried the basic php5 oracle commands, as well as ADODB and a few others with the same exact results.

I also have TCPDUMP data between the servers on an attempted connect if that will help.

Any ideas?

Thanks!

Jim

[tsinka]

Please post the PHP code (using the oci functions).

EDIT: and listener.ora, tnsnames.ora (both tnsnames.ora, the one on the oracle server and the one on the web server)

Thomas

[bpoppa]

Thomas,

Here ya go. Also, on the Oracle Server in the Windows Services, the Oracle Instance and Listener are listed as started. Could it be an ENV issue on the windows server?

Thanks!

Jim

TNSNAMES.ORA on webserver

TNSNAMES.ORA Network Configuration File: /home/oracle/product/9.0.1/network/admin/tnsnames.ora

Generated by Oracle configuration tools.

TFMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.150.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = TFMA)
)
)

PHP Code using ADODB

<?

include "adodb/adodb.inc.php";
$db = NewADOConnection("oci8");
$db->Connect("HOST", "username", "password","TFMA");

$rs = $db->Execute("select * from port_lookup");
while ($arr = $rs->FetchRow()) {
    print_r($arr);
        echo "<hr>";
}



?>

TNSNAMES.ORA from Oracle Server

TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora

Generated by Oracle configuration tools.

TFMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SID = TFMA)
(SERVER = DEDICATED)
)
)

LISTENER.ORA from Oracle Server

LISTENER.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\listener.ora

Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx)(PORT = 1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = TFMA)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = TFMA)
)
)

[tsinka]

Please post the code that uses OCILogon directly.

[tsinka]

One more question: Which user and group did you set for apache in httpd.conf ?

Thomas

[bpoppa]

Ok, here is the code:

<?
$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx)(PORT = 1521)))(CONNECT_DATA=(SID=TFMA)))";

$c1 = ocilogon("username","password",$db);
?>

And in httpd.conf I am using apache for User and Group.

Jim

[tsinka]

Did you already try this ?

<?
$c1 = ocilogon("username","password","TFMA");
?> 

or (ADOD😎

...
$db->Connect("TFMA", "username", "password"); 
....

Create a phpinfo() script, save the output with your browser and post the file (rename it to .txt).

Thomas

Thomas

[bpoppa]

Ok,

Here is the error I get with that code.

Warning: ocilogon() [function.ocilogon]: _oci_open_server: Error while trying to retrieve text for error ORA-12154 in /usr/local/apache2/htdocs/tfma/test2.php on line 2

And attached is the phpinfo.

Thomas: Again thank you so much for your help. Hopefully we can figure this one out.

Jim

[bpoppa]

Thomas,

Any ideas since the last information I sent?

Thanks!

Jim

[tsinka]

I oversaw your last post with the attachment ... my "subscribed threads" list is very long.

NLS_LANG is set to english. As a first step I'd suggest to change it to e.g.

NLS_LANG=AMERICAN_AMERICA.WE8IS8859P1

I think it's important to have at least a state where you get correct error messages instead of the "error while trying to retrieve".

The NLS_LANG setting must match the one on the server (instance).

Restart the web server after you modified NLS_LANG.

Thomas

[bpoppa]

Thomas,

I know you are busy, you are an awesome contributor and I thank you immensely for it.

I change the NLS_LANG and it now reflects that in the phpinfo, however I still get the exact same error.

Let me ask you this. What ENV Vars should be set on the windows Oracle server?

Jim

[tsinka]

Sorry,

there was a typo in the variable value:

Replace
AMERICAN_AMERICA.WE8IS8859P1
with
AMERICAN_AMERICA.WE8ISO8859P1

and restart the web server.

EDIT:

The environment variables on the Windows server should be fine. The environment variables on the Linux server itself should be sufficient if you can sqlplus to the server.

So let's check some things with a PHP script:

[PHP
echo getenv("ORACLE_HOME");
if (is_file("/home/oracle/product/9.0.1/network/admin/tnsnames.ora")) {
echo "tnsnames.ora found";
} else {
echo "tnsnames.ora not found";
}
[/code]

Additionally, check which libraries PHP has been linked against on the shell:

ldd /full/path/to/libphp5.so

EDIT: Did you add the oracle lib directory to ld.so.conf and execute ldconfig ?

EDIT2: I read somewhere that on RedHat 8 you can have problems with the web server's user (www or whatever).

Two things to try: Either add the www user to the oracle (or oinstall) group (/etc/group) or change the apache user in httpd.conf to the oracle user (and maybe group). Then restart the server.
I'd prefer if the first one works. The second one is some kind of "ugly".

Thomas

[bpoppa]

Yes,

I added the Oracle Lib (/home/oracle/product/9.0.1/lib) to the conf file and executed ldconfig.

When I did an ldd on the so I got:

   /home/oracle/product/9.0.1/lib/libclntsh.so.9.0 => /home/oracle/product/9.0.1/lib/libclntsh.so.9.0 (0x4035a000)
    libcrypt.so.1 => /lib/libcrypt.so.1 (0x40d2b000)
    libmhash.so.2 => /usr/local/lib/libmhash.so.2 (0x40d58000)
    libgdbm.so.2 => /usr/lib/libgdbm.so.2 (0x40d94000)
    libz.so.1 => /usr/lib/libz.so.1 (0x40d9b000)
    libssl.so.0.9.7 => /usr/local/ssl/lib/libssl.so.0.9.7 (0x40da9000)
    libcrypto.so.0.9.7 => /usr/local/ssl/lib/libcrypto.so.0.9.7 (0x40dd9000)
    libresolv.so.2 => /lib/libresolv.so.2 (0x40ecd000)
    libm.so.6 => /lib/libm.so.6 (0x40ede000)
    libdl.so.2 => /lib/libdl.so.2 (0x40eff000)
    libnsl.so.1 => /lib/libnsl.so.1 (0x40f02000)
    libc.so.6 => /lib/libc.so.6 (0x40f16000)
    libwtc9.so => /home/oracle/product/9.0.1/lib/libwtc9.so (0x41041000)
    libpthread.so.0 => /lib/libpthread.so.0 (0x41044000)
    /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x80000000)

Also, The script said tnsnames.ora not found, but if I copy and paste that path into my shell it opens fine and the file is there.

I tried adding apache to the group with no luck.

Jim

[tsinka]

Ok,

you might need to chmod one of the directories.
Which file access permissions does /home/oracle (and maybe it's subdirectories) have ? Something like 0700 ? You might need to chmod -R a+r the oracle home directory so that the web server can access the files.

Thomas

[bpoppa]

Thomas,

No go.

I copied the tnsnames.ora to /tmp and then it found it.

This is quite frustrating.

Jim

[tsinka]

Which are the oracle user and groups ? Did you try to change the apache user in httpd.conf to that user and one of the groups (just to test if that changes anything) ?

Thomas

P.S.: in the output of the phpinfo script I saw the configure option --enable-discart-path which should be --enable-discard-path (but that shouldn't cause that specific oracle problem)

[bpoppa]

WOW......that was it. I put in the oracle username and password and it works fine. So, it just couldn't access the tnsnames.ora file.

Wow...in your opinion, should I leave it like this, or other thoughts on how to get apache to read that file?

Thank you so so so much.

Jim

[tsinka]

Ok,

that's not the best solution but at least one that works 🙂

Next question:

how does the web user look like in /etc/passwd ? Does it have a shell and a home directory ?

Another thing I'd try is to change the web user so that it has the group oinstall as primary group.

How do your /etc/passwd and /etc/group files look like. Post the parts that have to do with the web user/group and the oracle user/group.

Thomas