Robelle Tech: Weird Oracle Error

July 2004

Weird Oracle Error

By Neil Armstrong, Robelle

Recently we had a support call and email from a customer saying that they thought they had a problem with their Suprtool/UX licensing of the Oracle module. The strange part was how seemingly unrelated the initial problem was from the eventual solution:

Open Oracle somedatabase someuser
SELECT * FROM V_ITP

Error: Unable to process the select statement

Error: ORA-01116: error in opening database file 14
ORA-01110: data file 14: ‘/u03/oracle/amdv/data/amisys_data_tb3.dbf’
ORA-27092: skgfof
Error: Fatal problem during processing

The first thing we did was check the licensing information, and also we Googled the Oracle error message. If licensing was an issue, the customer’s script would have failed on the preceding OPEN command rather than on the Select command. So licensing was not the problem.
We called the Oracle error message routines in our Oracle Call Interface, so we knew the error message was coming from Oracle. We did a Google search on the Oracle error messages and found strong indications that there was most likely a data file missing from the database. The following links indicate that after mistakenly deleting an Oracle datafile other users got the error message that our customer encountered: (You can read the full messages at www.baseltd.com/wwwboard/messages/
1444.html and www.jlcomp.demon.co.uk/faq/lostdatafile
_nobackup.html)
Similarly, PowerHouse was also failing for the customer, as follows:

*E* Data access error. (UITP)
*E* DMS-E-GENERAL, A general exception has occurred during operation
‘asynchronous open’. (UITP)
*E* ORA-01116: error in opening database file

After asking whether the customer could confirm that the data files had not been deleted, the customer replied that the error was related to the fact that the version of ‘at’ on their system could not handle large files:
“Thanks for your help. We figured out the problem. The error only occurred for some tables, and only in batch. Turns out these are large tables (2.5Gb in this case), and the version of “at” on the HP-UX machine has a problem with this. Only some versions of UX 11i standard, at some patch level, have this issue (and possibly some older enterprise versions from before 7/2002). When they do have the problem, a file ‘.proto’ needs to be added to /var/adm/cron.”
“I’ve included the .proto file for you to add to your knowledge base. When I switched the code to simply access the table using SQLPLUS, I got a better error message, which is what clued us in, the ‘exceeds file size limit of the process’ message.“

SQL> SELECT * FROM UITP where letter_id_who = ‘somerecord’
*
ERROR at line 1:
ORA-01116: error in opening database file 15
ORA-01110: data file 15: ‘/u41/oracle/amdv/data/amisys_indexes_tx1.dbf’
ORA-27092: skgfofi: size of file exceeds file size limit of the process

Here’s the .proto file that helps with the problem:

cat /var/adm/cron/.proto

# @(#)B.11.11_LR
cd $d
echo starting ..proto l is $l
if [ $l -ge 4194303 ]
then
echo setting to unlimited
ulimit unlimited
else
echo setting to $l
ulimit $l
fi
umask $m
$<
echo ending .proto

Subsequent research on the HP ITRC forums found a fairly cryptic message that did refer to patches which did help with the problem:
1. Check the .profile file of the oracle user to see whether the oraenv script is present in the startup file. This oraenv file sets the ulimit value as 4194303. Remove the script from .profile or change the script from “4194303” to “unlimited” in the “if condition.”
2. If that is not present, Load the patches PHCO_26783 (11.00) and PHCO_27019(11.11) which should make the ulimit values for ksh to unlimited.
A subsequent message did indicate that this resolved the similar issue as reported on the ITRC. It just goes to show that errors can come from anywhere and are not always directly related to the immediate application being used that generated the error.
Next month: Debugging a client-server application is MORE interesting.