Connecting to remote oracle database

[Anon]

Dear All

I am trying to connect to an oracle 8.0 database using php 4 and apapche 1.3.17. I believe I have compiled everything ok (originally I was getting unknown function errors but that appears to have gone away). However I still can't connect. I'm using the following script:

<?php // oratest.php

putenv("ORACLE_HOME=/oracle/mowlem01/OraHome1");
putenv("ORACLE_SID=ORCL");

OCIInternalDebug(1);

if ($conn=OCILogon("edm@edm","edm")) {
echo "SUCCESS ! Connected to database\n";
} else {
echo "Failed :-( Could not connect to database\n";
}
OCILogOff($conn);
?>
<P>
<?php
phpinfo();
?>

and get the following error messages:

OCIDebug: _oci_open_server new conn=0 dname=

Warning: OCISessionBegin: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory in /usr/local/apache/htdocs/oratest.php on line 8
OCIDebug: oci_open_session: FAILURE -> CLEANUP called
OCIDebug: START oci_close_session: logging-off sess=0
OCIDebug: oci_close_session: logging-off DEAD session
OCIDebug: oci_do_connect: FAILURE -> CLEANUP called
OCIDebug: START oci_conn_list_dtor: id=0
OCIDebug: END _oci_conn_list_dtor: id=0
Failed :-( Could not connect to database

I have two questions, I was hoping some one might answer.

Is there some where on the web where I can find a list of these oracle error messages?

Does any one know the cause of these messages and can you tell me what I'm doing wrong?

Many thanks

Paul

[Anon]

A few steps which should help you solve this problem:

1) Verify that the correct Oracle components are installed on the machine running PHP. You will need at least Oracle Net8 (previously known as SQL*Net). It should be located off the ORACLE_HOME (/oracle/mowlem01/OraHome1) directory you specified.

2) Verify that Net8 is configured correctly. In the Oracle Net8 directory, look for a tnsnames.ora file (.../net/admin/tnsnames.ora) and verify that the "edm" service has been configured. A quick way to test the service is to try to connect to the database using SQLPlus with edm/edm@edm as the username/password@service. If you can't get in like this with SQL*Plus, then PHP won't be able to either.

3) Change the OCILogon line:
if ($conn=OCILogon("edm", "edm", "edm")) {
If the Oracle database is local to the machine running PHP, you can often get away with defaulting the service name to the local database instance:
if ($conn=OCILogon("edm", "edm")) {

-- Michael
Darkstreak Consulting

[Anon]

Michael

Many thanks for your reply.

I checked tnsnames.ora and it exists and appears to point to the right server etc.

If I run sqlplus and enter thew user as "edm"
I get exactly the same error messages.

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory.

If, however I enter the user as "edm@edm" it works fine.

Also, on making your suggested changes to the oratest.php file the connection is made with no errors.

It appears that the database name (?) is not being sent to oracle unless it is specifically stated.

There is obviuosly some thing wrong with my client installation. I did manage to find the error messages on the oracle web site but they are not particularly helpful.

Do you have any more ideas?

Many thanks

Paul

[Anon]

If username/password@service ("edm/edm@edm") works in SQLPlus(or PHP), but username/password ("edm/edm") does NOT work in SQLplus, then a default database service has not been set up in your process environment. Many DBA's don't set up all the environment variables for 'average users' for a number of reasons. I won't go into the reasons since the details are relevant to PHP. Suffice to say that this is a relatively easy thing to change.

You need to make sure that the correct environment variables are set for process running PHP. This can be done by using putenv() or by adding the information to the username's startup sequence (ie: .profile, .login, whatever...). Work with your Oracle DBA to do this.

If you are responsible for the Oracle environment on your system(s), then let me know and I'll try to talk you through it. My Oracle on nix has gotten a little rusty since I've been mostly working with Oracle on WindowsNT and OpenVMS lately. The same rules apply as for nix, but the environment variable names may differ slightly.

-- Michael
Darkstreak Consulting

[Anon]

Michael

Once again many thanks for you prompt reply.

I would be grateful for any help you might be able to give me.

I have inherited this set up from some one and am now trying to extend it by giving access to users via the web. However my knowledge of Oracle is only what I've gleaned from the web/books and most of the material out there is very high level and not very specific.

Once again, many thanks.

Paul

[Anon]

Summary of a long response sent by private e-mail:

1) I highly recommend using all three parameters in your OCILogon() statements.

2) If using all three parameters really is an issue, then experiment with changing the 'putenv("ORACLE_SID=ORCL");' to 'putenv("ORACLE_SID=edm");'. Note that the "edm" in the "ORACLE_SID=edm" might be case sensitive on Linux. If this doesn't work, you may need to put it in the process startup files of the account running the Apache webserver.

3) If you frequently use SQL*PLus or other Oracle applications, you may want to add an ORACLE_SID environment variable to your account/process startup files to eliminate the need to explicitly name the database service.

Let me know if I can be of further assistance.

-- Michael
Darkstreak Consulting

[Anon]

Paul wrote:

If I change the ORACLE_SID environment to edm it appears to make no
difference to sqlplus, I still receive the same error messages.

It appears that the environment variable name is slightly different under
Linux. A quick rummage through Oracle's TechNet website
(http://otn.oracle.com) yielded the following: (from the Oracle8 documentation
for Linux)

]If you have a client/server configuration, you must set the TWO_TASK
]environment variable on the client to point to the server. Set the TWO_TASK
]environment variable on the client machines to the service name for the
]server (available from the tnsnames.ora file).

The resolution to Paul's situation was to use "TWO_TASK=edm" instead of
"ORACLE_SID=edm".

-- Michael
Darkstreak Consulting
[e-mail]

[Anon]

I think you didn't startup your listner. Otherwise you when you started your sqlplus, you would have seen user name/password invalid those kind of messages. Try this:
Login as oracle owner, then:
"lsnrctl start"
then:
"svrmgrl"
"connect interal"
"startup"
"quit"

then try "sqlplus", if you can logon, you should be able to get the connection through the script.

GOOD LUCK!

Paul Rogers wrote:

Michael

Many thanks for your reply.

I checked tnsnames.ora and it exists and appears to point to the right server etc.

If I run sqlplus and enter thew user as "edm"
I get exactly the same error messages.

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory.

If, however I enter the user as "edm@edm" it works fine.

Also, on making your suggested changes to the oratest.php file the connection is made with no errors.

It appears that the database name (?) is not being sent to oracle unless it is specifically stated.

There is obviuosly some thing wrong with my client installation. I did manage to find the error messages on the oracle web site but they are not particularly helpful.

Do you have any more ideas?

Many thanks

Paul

[Anon]

Thanks for the reply.

It turned out in the end that i hadn't set one of the environment variables (TWO_TASK) necessary for the client to connect to the database.

Once again

many thanks

Paul