WARNING: This web is not used anymore. Please use PDBService.ConnectionManagement instead!
 

Connection management with Oracle servers

Main considerations

This page is a very short guide on connection management for Oracle applications. It does not replace the use of specific Oracle documentation. Whenever possible use Oracle OCI driver instead of JDBC:Thin or ODBC. Also, use whenever possible connection pooling or persistent connections.

Oracle Errors to catch

In a database application the errors thrown by the database should never be ignored. At least an e-mail alert should be generated and sent to the developer. However in many cases the application should also be able to correctly handle the error and follow up dependent on the error retrieved. Below is a list of common errors which should have a special handling.

Password related

  • ORA-28000 - The account is locked
    • Wait for PASSWORD_LOCK_TIME (1 minute) and retry
    • Someone is probably trying to connect with wrong password
    • Contact phydb.support@cernNOSPAMPLEASE.ch (if wait does not solve)
  • ORA-28001 - The password has expired
    • Do not retry, stop the application, you need to change the password interactively
    • Log-on using interactive tool (sqlplus, SQLDeveloper, TOra ...), when the password is expired it will ask you to change it during log-on (or try the password command in SQL*Plus)
  • ORA-28002 - The password will expire within %s days
    • "ignore" for application, but warn developer
    • Change password
    • Application should not get this error if using correct accounts (_R/_W)
  • ORA-01017 - Invalid username/password; logon denied
    • Do not retry. Correct the password.

Connection related

A robust application should be able to handle server or network failures. Below are the most common errors your application should correctly handle. In case of server failure (or rolling patch) your connection should be automatically moved to an available server. However some of the errors below might happen also.

  • ORA-17008 - Closed Connection
    • Retry the connection request
    • Replay the transaction
  • ORA-02396 - Exceeded max Idle Time, please connect again
    • Retry the connection request
    • Replay the transaction
  • ORA-25402 - Transaction must rollback
    • Cause: connection was automatically moved to a another instance in middle of transaction, possible due a network or instance problem
    • Solution: Rollback and replay the transaction
  • ORA-25408 - Can not safely replay call
    • Cause: connection was automatically moved to a another instance in middle of transaction, possible due a network or instance problem
    • Solution: Rollback and replay the transaction
  • ORA-03113 - End-of-file on communication channel
    • Cause: possible due a server/client software error or network problem the connection was lost.
    • Solution: Retry the connection request and replay the transaction
  • ORA-03135 - Connection lost contact
    • Cause: possible due a network problem the connection was lost. It should be moved to another instance and on next retry it should work
    • Solution: Rollback and replay the transaction

Developer's Guide for Oracle

Below are listed some links and guidelines for Oracle programming with each specific language.

PHP

Python

PERL

C/C++

Java

              hibernate.c3p0.acquire_increment=1
              hibernate.c3p0.idle_test_period=3600
              hibernate.c3p0.min_size=2
              hibernate.c3p0.max_size=5
              hibernate.c3p0.max_statements=5
              hibernate.c3p0.timeout=3600

From: Wojciech Lapka

There's a parameter poolPreparedStatements=true responsible for setting the pool for prepared SQL queries 
(In such case these queries stay in DB and can be seen via dynamic view v$open_cursor).

There are 2 ways of solving this problem (Just in case somebody will come to you in the future with similar problem ;)):
1.   Adding parameter maxOpenPreparedStatements
2.   Setting poolPreparedStatements=false

I have chose the first option and I'm testing it now. Just for your information: The configuration of my DB pool is following:
    <Resource auth="Container"
        driverClassName="oracle.jdbc.driver.OracleDriver"
        initialSize="1"
        maxActive="5"
        maxIdle="5"
        maxWait="10000"
        name="jdbc/..."
        password="...."
        poolPreparedStatements="true"
        maxOpenPreparedStatements="100"
        testOnBorrow="true"
        testWhileIdle="true"
        timeBetweenEvictionRunsMillis="180000"
        type="javax.sql.DataSource"
        url="jdbc:oracle:oci:@..."
        username="..."
        validationQuery="select 1 from dual"
    />

Other links

Edit | Attach | Watch | Print version | History: r9 < r8 < r7 < r6 < r5 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r9 - 2009-07-02 - DawidWojcik
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback