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!
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 processingThe 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.htmlSimilarly, 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 fileAfter 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 processThe ".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 .protoSubsequent 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.A subsequent message did indicate that this resolved the similar issue as reported on the ITRC.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.
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.