Hi all,

Phew!  After two days of blood, sweat, and tears my Oracle DBA and I have
come upon a solution that fixes the ORA-03113 end-of-file on communication
channel error that I see posted on here often.  Since quite a number of list
members have come across this bug, I though I would post our fix for
posterity in the hopes that others can get work done :-)  My Oracle DBA has
given his blessing to this post.  I hope the fix works for all people, but
if it works for a few I'll be happy.

In our case we received the error due to a (caching?) bug with Oracle.  I am
not sure what the error is exactly, but, short of patching our database
(Oracle 8.0.5 for Solaris), we bypassed it by disabling a session parameter
using the following SQL query immediately after establishing a database
connection:

        alter session set hash_join_enabled=FALSE

Our connection routine now looks as follows:

  1029  sub DBConnect
  1030  {
  1031  #    my ($dbh) = Connection->new (
  1032      my ($dbh) = DBI->connect (
  1033                      $HockeyPoolConstants::db_connect_string,
  1034                      $HockeyPoolConstants::db_username,
  1035                      $HockeyPoolConstants::db_password,
  1036                      {
  1037                          RaiseError => 1,
  1038                          AutoCommit => 0
  1039                      }
  1040                  )   ||  crash_and_burn  ("Database connection not
made:  $DBI::errstr");
  1041
  1042      my ($sql) = 'alter session set hash_join_enabled=FALSE';
  1043      my ($sth) = $dbh->prepare ($sql)    ||  &ToolBox::crash_and_burn
($DBI::errstr);
  1044      $sth->execute ()                                   ||
&ToolBox::crash_and_burn ($DBI::errstr);
  1045
  1046      return ($dbh);
  1047  }

By executing this query immediately after establishing a connection, we have
disabled the hash joining feature of Oracle for the life of the session
(i.e. for the life of the database handle.)  It is in disabling this
parameter that the Oracle bug is addressed, and that the ORA 03113 bug is
prevented.  This fixed our problem for us :-)  My apologies for not being
too technical.

BTW, this problem could also be resolved (in our case) by applying the
following patches to the database:

        Thus, in order to solve this problem by patching your software, you
would first need to apply the 8.0.5.2   (patchset) and then apply a one-off
patch (1118132).

I recommend you read up on the patches to see if they're appropriate for
your environment before applying them as I am no database administrator.

Cheers,
Darryl Staflund

Reply via email to