Re: Atypical ORA-03114 help please

Evan Ehrenhalt wrote:

> I am looking for a clue on an Oracle / vendor problem.
>
> The thin client reports occasional ORA-03114 errors, while SQLPLUS and
> TNSPING have no troubles. We can not detect a pattern or cause.
>
> The sqlnet.log, alert_SID.log and, TNS_ADMIN/../log/* has no errors. No
> trace files are generated related to this issue (just the usual backup
> to trace, occasional arc, etc.).
>
> I see no oracle related problems, except what the vendor log and the
> clients report. The clients are screaming bloody murder.
> The vendor help desk has not been much help, and I do not find anything
> that helps much on Metalink.
> Since it is an ORA- error and it points to an Oracle procedure, I hope
> someone out there can help.
>
> Database is a 8.1.6.0.0 EE on SunOS 5.8 Generic_108528-09 sun4u sparc
> SUNW,Ultra-80.
> It has 9 GB EMC disk available, with less than 3 GB in tablespaces
> (plenty of free space in each).
> We are using a vendor product which will not allow us to upgrade.
> There are details below and I can provide as many more as you like.
>
> If you have any clues or hints for us, please post here or e-mail me.
>
> Thank you,
>
> Evan Ehrenhalt
>
> -----begin ERROR LOG FROM VENDOR-----
> Wed Jun 25 22:32:58 2003] [error] [Wed Jun 25 22:32:58 2003]:
> CI::dbh_error_catch, called by CI,
> /usr/local/blackboard/perl/lib/site_perl/5.005/CI.pm line 388
> Error: can't execute: BEGIN get_session_md5_data (md5_in => ?,
> vals_out => ?); END;
> ARGS: ERR: DBD::Oracle::st execute failed: ORA-03114: notconnected to
> ORACLE (DBD ERROR: OCIStmtExecute) at
> /usr/local/blackboard/perl/lib/site_perl/5.005/CI.pm line 386.
> -----end ERROR LOG FROM VENDOR-----
> -----begin Oracle Procedure---------------------
> CREATE OR REPLACE PROCEDURE "BB50"."GET_SESSION_MD5_DATA"
> get_session_md5_data(md5_in IN VARCHAR2,
> vals_out OUT VARCHAR2) IS
> BEGIN
> UPDATE sessions
> SET timestamp = SYSDATE
> WHERE md5 = md5_in;
> COMMIT;
> SELECT
> 'session_id='||session_id||','||'user_id_pk1='||user_id_pk1||','||'user_id_sos_id_pk2='||
>
> user_id_sos_id_pk2||','||'user_id='||user_id||','||'one_time_token='||
> one_time_token||','||'batch_uid='||batch_uid
> INTO vals_out
> FROM sessions
> WHERE md5 = md5_in;
> EXCEPTION
> WHEN OTHERS THEN
> vals_out := NULL;
> END get_session_md5_data;
> -----end Oracle Procedure---------------------
> -----begin /etc/system values--------------------
> set shmsys:shminfo_shmmax=4294967295
> set shmsys:shminfo_shmmin=1
> set shmsys:shminfo_shmmni=100
> set shmsys:shminfo_shmseg=10
>
> set semsys:seminfo_semmni=100
> set semsys:seminfo_semmsl=70
> set semsys:seminfo_semmns=1000
> set semsys:seminfo_semopm=100
> set semsys:seminfo_semvmx=32767
> -----end /etc/system values--------------------

Only one thing jumps out at me ... an unpatched unsupported Oracle version that has a reputation for being
extremely buggy.

Forget the usual questions such as what prevented you from upgrading to 8.1.7 or above. What has
prevented you from applying the patches to 8.1.6? They have been available for years at on cost.

After you have answered that question ... patch the darned thing and talk to your management about upgrading
to a supported version or at least one sold during this millenium. Right now your resume' is growing more stale
by the day and that should not be a happy thought in this economy.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)