Getting Oracle Connection Errors Faster in PHP

[This article may also help tune slow connections that succeed without errors.]

When a connection fails, you want to know about it as soon as possible.  With Oracle Net there are many ways to configure connection and authentication.  For example a connection:

   $c = oci_connect("hr", "hr", "abc");
could be evaluated by Oracle 10g as using the Easy Connect syntax to machine "abc" (using the default port and database service) or using a net alias "abc" configured in a tnsnames.ora file.

In this example both internal connection methods may be tried sequentially adding to the time delay before you see an error.  It depends on your Oracle Net and DNS settings.

How Oracle is configured to authenticate the user's credentials (here a username and password) can also have an effect.

The problem is not specific to PHP.  In Oracle's SQL*Plus the connection
    $ sqlplus hr/hr@abc
would be the same.

In a basic Oracle 10g installation, one way to return an error as soon as possible is to set this in your sqlnet.ora file:
    NAMES.DIRECTORY_PATH= (TNSNAMES)
    SQLNET.AUTHENTICATION_SERVICES= (NONE)
The DIRECTORY_PATH value disables Easy Connect's "hostname:port/service" syntax.  Instead, create a tnsnames.ora and use a network alias.

Setting AUTHENTICATION_SERVICES to NONE may prevent privileged database connections.  On Windows you may want to use NTS.  Check the Oracle Net Services Administrator's Guide and Oracle Net Services Reference guides for details and for the other authentication methods and authentication-type specific timeout parameters.

If you use Oracle Instant Client, you'll need to create sqlnet.ora on your client machine and set the TNS_ADMIN environment variable to its directory.

I tried this out.  I created tnsnames.ora and sqlnet.ora in my instant client directory. Here is tnsnames.ora.  The service is an invalid service on a far-distant machine so connection will always fail.
    MYDB2 = (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)
          (HOST = www.google.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)))
And here is sqlnet.ora:
    NAMES.DIRECTORY_PATH=(TNSNAMES)
    SQLNET.AUTHENTICATION_SERVICES= (NONE)
I set the environment:
    $ export LD_LIBRARY_PATH=`pwd`
    $ export TNS_ADMIN=`pwd`
Note TNS_ADMIN is set to a directory, not the tnsnames.ora file itself.

When I ran SQL*Plus the error returned after a few seconds.  This would be just the time delay from my machine to the non existent database service:
    $ ./sqlplus -l hr/hr@MYDB2
    SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 4 11:17:22 2006
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    ERROR:
    ORA-12560: TNS:protocol adapter error
Without the sqlnet.ora settings the connection took so long that I killed SQL*Plus from another shell:
    $ mv sqlnet.ora sqlnet.ora.bak
    $ ./sqlplus -l hr/hr@MYDB2
    SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 4 11:15:02 2006
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    Killed
To use the sqlnet.ora and tnsnames.ora files in PHP, make sure that TNS_ADMIN is set in the shell that starts the webserver.  See "Connection Strings" in PHPfest Tutorial.

Comments:

This is a damned good tip. In the event of a catastrophic failure on the DB server, or it just falls over, I expect oci_connect to return a value pretty quickly so I can deal with its failure to connect appropriately. Hopefully it does what I think it does.

Posted by Gareth Roberts on May 15, 2006 at 09:28 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today