Hi Perrin, I am using Oracle Resident Connection Pool feature. The application randomly got "ORA-03114 not connect to oracle database" error. As per this document:
http://search.cpan.org/~phred/Apache-DBI-1.12/lib/Apache/DBI.pm I have changed the DBD::Oracle to use the following ping instead of ora_ping: sub ping { my ($dbh) = @_; my $ret = 0; eval { local $SIG{__DIE__} = sub { return (0); }; local $SIG{__WARN__} = sub { return (0); }; # adapt the select statement to your database: $ret = $dbh->do('select 1'); }; return ($@) ? 0 : $ret; } After this change, the application can detect "end-of-file communication" error then after the application establishes new connection, the application still throws "ORA-03114 not connect to oracle database" error. below is the errors when Apache::DBI debug is set: 3861 Apache::DBI need ping: yes DBD::Oracle::db ping failed: ORA-03113: end-of-file on communication channel Process ID: 29671 Session ID: 3219 Serial number: 544 (DBD ERROR: OCIStmtExecute/Describe) at /usr/local/lib/perl5/site_perl/5.8.3/Apache/DBI.pm line 200. 3861 Apache::DBI new connect to '...' 3861 Apache::DBI need ping: yes 3861 Apache::DBI already connected to '...' 3861 Apache::DBI need ping: yes 3861 Apache::DBI already connected to '...' 3861 Apache::DBI need ping: yes 3861 Apache::DBI already connected to '...' 3861 Apache::DBI need ping: yes 3861 Apache::DBI already connected to '...' 3861 Apache::DBI need ping: yes 3861 Apache::DBI already connected to '...' 3861 Apache::DBI need ping: yes 3861 Apache::DBI already connected to '...' 3861 Apache::DBI need ping: yes DBD::Oracle::st execute failed: ORA-03114: not connected to ORACLE (DBD ERROR: OCIStmtExecute) 3861 Apache::DBI need ping: yes 3861 Apache::DBI already connected to '' 3861 Apache::DBI disconnect (overloaded) [Wed Nov 6 16:49:50 2013] [crit] ORA-03114: not connected to ORACLE (DBD ERROR: OCIStmtExecute). Process 3861 establishes a new connection. and it says "already connected to" database. While the query execution failed with "ORA-03114" and the httpd process is in CLOSE_WAIT state. How does it thinks it's already connected while it's actually not? * xinhuan ------------------------------------------------------------------------------------------------------------------------------------------------------------ From: Perrin Harkins <phark...@gmail.com<mailto:phark...@gmail.com>> Date: Wednesday, November 6, 2013 5:54 PM To: Xinhuan Zheng <xzh...@christianbook.com<mailto:xzh...@christianbook.com>> Cc: mod_perl list <modperl@perl.apache.org<mailto:modperl@perl.apache.org>> Subject: Re: Apache::DBI connect On Wed, Nov 6, 2013 at 4:07 PM, Xinhuan Zheng <xzh...@christianbook.com<mailto:xzh...@christianbook.com>> wrote: I wonder this line of code should be changed to 'DBI->connect(@args) if ($@)'. If ping failed, that means the connection is already closed, $drh may be no longer valid, will $drh->connect always return a valid new connection? What's the difference between DBI->connect vs $drh->connect? The $drh there is a DBI driver, not a database handle. It should return a new connection every time. You can try putting in DBI->connect() there and see if it fixes the problem for you though. If not, I'd suggest reporting the problem as a DBD::Oracle issue. By the way, which Oracle pooling solution are you using and how is it working out for you, aside from this problem? - Perrin
