Oracle Tips and Stories


Sites for Oracle Help

Here are a number of useful web sites where you can look for help using Oracle, particularly the forums.

orafaq

Oracle Tech Network

Oracle Community Forums

On-line manuals:

Oracle 8 documentation: http://www-rohan.sdsu.edu/doc/oracle/

Oracle 9 manuals (and some 10): http://otn.oracle.com/documentation/index.html

A great website by Ben Brumm, called Database Star has a lot of interesting articles and great information!


Weird Oracle Error

By Neil Armstrong, Robelle

Recently we had a and 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 customers script would have failed on the preceeding OPEN command rather than on the Select command. So licensing was not the problem.

We call 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 indicates that after mistakenly deleting an Oracle datafile other users got the error message that our customer encountered:

  http://www.baseltd.com/wwwboard/messages/1444.html
  http://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 datafiles 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.5 gig 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 knowledgebase. 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
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 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".

If that is not present.

2. Load the patches:

    PHCO_26783 (11.00)
    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.


IMAGE and Oracle Data Tyles

Click here to discover how TurboIMAGE data types are transformed into the appropriate Oracle data types.

Exporting to Oracle with Suprtool

Click here to read how Hammacher Schlemmer used Suprtool to export data from TurboIMAGE to Oracle.