garden1.jpg

Seen Errors And Fixes


This document will outline recent database problems encountered and fixes or workaround.

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

What we do:

idbasolutions makes software that allows easy navigation into database objects, sessions, storage, wait conditions, memory structures and more, all with drill-down sub-windows in a cascading and intuitive fashion.

If you like reading our papers, please take a moment and navigate to Product Demo and lookup each modules presentations. Or navigate to Free Version Tab and download a demo copy of our software for you to keep and use at no charge. To buy please navigate to Buy tab to purchase a full copy.

Support documents such as how to install, system and database requirements and so on are available in Support tab.

Problem A):
Checking swap space: 0 MB available, 1535 MB required. Failed.
Description of the problem:

While installing Oracle Lite 10g on Windows XP I am getting the following error regarding swap space:

Checking swap space: 0 MB available, 1535 MB required. Failed
To check swap space allocation on my XP desktop which is called Virtual Memory as follow:
Right click my computer àProperties àAdvanced Tab àPerformance section and click Settingsà

Advanced Tab àVirtual Memory click Change I made sure that I had over 3000 MB of Virtual

Memory.
If you do not have enough Virtual Memory then just re-adjust and restart your desktop.
But I am still getting this error from the Oracle installer, here is the full output of the

error:
Starting Oracle Universal Installer…
Checking installer requirements…
Checking operating system version: must be 5.0, 5.1, 5.2 or 6.0 . Actual 5.1
Passed
Checking monitor: must be configured to display at least 256 colors . Actual
4294967296 Passed
Checking swap space: 0 MB available, 1535 MB required. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before
continuing with the installation, at which time they will be rechecked.
Continue? (y/n) [n]

Workaround:
Even though I have enough Virtual Memory on my desktop I continue to get this error:
Checking swap space: 0 MB available, 1535 MB required. Failed
The workaround that I used was to disable the check for Virtual Memory as outlined here:
Navigate to where you have unziped the Oracle software. In my environment I have the Oracle

Lite 10g in the foloowing area:

D:\Downloads\Oracle\Database\Lite\OLITE_10.3.0.2.0_Windows\OLITE_10.3.0.2.0_Windows\install

Save the current oraparam.ini and then edit oraparam.ini and do a Find (Ctrl F) and search

for swap. There are several sections here each corresponding to a different OS. For my

environment I comment out the line that says SWAP_SPACE=1535 under the Windows XP section

by adding a # right before SWAP_SPACE=1535 as seem here:

#Windows XP use winver.exe to find version
[Windows-5.1-required]
#Minimum display colours for OUI to run
MIN_DISPLAY_COLORS=256
#SWAP_SPACE=1535
TEMP_SPACE=150

Then I re-run the installer by running Setup.exe and this time Oracle installer will not

check for swap space and I get a successful install.


Problem B):
10-Jul-08:
User reported the following while calling procedure cr_x66 from Cobol:
ORA-06576: not a valid function or procedure name
Cobol is logon as idbasol and the procedure is also owned by idbasol.
The call is just like this:
CALL idbasol.cr_x66
Fix:
This error went away when the procedure was called like as follow:
CALL idbasol.cr_x66()


Problem C):
Today: 20-Mar-09
Description of the problem:
alert log is displaying:

Fri Mar 20 15:39:03 2009
Errors in file /u11/app/testdb/9.2.0/admin/TEST_ux108/udump/test_ora_20719.trc:
ORA-07445: exception encountered: core dump [nsoexc()+184] [SIGBUS] [unknown code]

[0x000000110] [] []

The trace file test_ora_20719.trc points to:
Exception signal: 10 (SIGBUS), code: 0 (unknown code), addr: 0x0000000000000110, PC:

[0x4000000002b2bc70, nsoexc()+184]
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [nsoexc()+184] [SIGBUS] [unknown code]

[0x000000110] [] []

And user's session get the following error:
ORA-03113 end-of-file on communication channel


Cause:
This seems to be a bug with Oracle 9.2.0.7 and 9.2.0.8
Metalink note 409048.1:
This issue is due to Bug 4683638 ORA-7445[NSOEXC] RAISED FROM CTAS SQL
This is a known problem in the following versions of Oracle.
9.2.0.8.0
10.2.0.2.0
10.1.0.5.0


Workaround:
Metalink note 409048.1 describes a similar problem and suggests the following workaround:
ALTER SESSION SET "_COMPLEX_VIEW_MERGING"=FALSE;

Or use a hint for the SQL that is casuing this error:
select /*+ no_merge (v1) */
...
from tableT t1, viewV v1
where
...
;


Problem d):
Getting the following error when adding new columns to an existing working SQL:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
The SQL is automatically generated by cristal report which uses ANSI joins.
Cause:
Google search I could read a few discussion on this error which appears to be a bug with

Oracle 9.2:
Oracle 9.2 has a limit of 1050 columns in any query that uses ANSI joins. By my reckoning

the tables in my FROM clause between them had about 1100 columns.
Fix:
Upgrade to 10g or remove a few of the un-necessary columns in the SQL to keep it under 1050

columns.


Problem cd2):
Getting the following error when running this SQL:
SELECT subgrp.b28, subgrp.yyyymm
FROM cr_x108 reg
INNER JOIN idbasol.xcr_crpt012_gtyq_grp_totals grp
ON reg.g78 = grp.g78
AND reg.regseq = grp.regseq
AND reg.yyyymm = grp.yyyymm
AND reg.report_id = grp.report_id
INNER JOIN idbasol.xcr_crpt012_tqy_totals grd
ON reg.g78 = grd.g78
AND reg.yyyymm = grd.yyyymm
AND reg.report_id = grd.report_id
INNER JOIN idbasol.xcr_crpt012_gsqy_sgrp_totals subgrp
ON subgrp.yyyymm = grp.yyyymm
AND subgrp.b20_grp = grp.b20_desc
AND subgrp.g78 = grp.g78
AND subgrp.report_id = grp.report_id;
ERROR at line 12:
ORA-01445: cannot select ROWID from a join view without a key-preserved table

Cause:
Google search I could read a few discussion on this error which appears to be a problem

with Oracle not being able to understand ANSI style SQL. This is a 9.2.0.4 database.
Fix:
Even though Oracle should understand ANSI style SQL, well sometimes it does not.
I rewrote the query with Oracle style old fashion where clause as seen below, and the SQL

run with no error.

SELECT subgrp.b28, subgrp.yyyymm
FROM cr_x108 reg, idbasol.xcr_crpt012_gtyq_grp_totals grp,
idbasol.xcr_crpt012_tqy_totals grd, idbasol.xcr_crpt012_gsqy_sgrp_totals subgrp
where reg.g78 = grp.g78
AND reg.regseq = grp.regseq
AND reg.yyyymm = grp.yyyymm
AND reg.report_id = grp.report_id
and reg.g78 = grd.g78
AND reg.yyyymm = grd.yyyymm
AND reg.report_id = grd.report_id
and subgrp.yyyymm = grp.yyyymm
AND subgrp.b20_grp = grp.b20_desc
AND subgrp.g78 = grp.g78
AND subgrp.report_id = grp.report_id;

Problem E)
oraenv

I logon as oratrn after 9.2.0.7 to 10.2 upgrade and run:
. oraenv
but I get the following error:

su: Syntax error: `then' is not matched.

I cd /usr/local/bin/oraenv and restore oraenv and coraenv and dbhome from the
before upgrade.
But I am still getting the error.

Now I do a find:
find $ORACLE_HOME -name oraenv -exec ls -l {} \;

Which finds one:
-rwxr-xr-x 1 oratrn dba 2544 Apr 26 2002 /u02/app/traindb/9.2.0/bin/oraenv

Fix:
So I do the following:
cp /usr/local/bin/oraenv /u02/app/traindb/9.2.0/bin/oraenv

Fixed.....
No . oraenv works fine again.


Problem F)
Getting the following error when trying to connect /as sysdba:
ora-12162 tns net service name is incorrectly specified

Cause:
ORACLE_SID is not set.

Fix:
Vi .profile
And add Oracle home and SID:
ORACLE_HOME=/u02/app/traindb/10.2.0
export ORACLE_HOME
ORACLE_SID=TRAIN
export ORACLE_SID


Problem G)
After upgrading TRAIN Oracle db 9.2.0.7 to 10.2.0.4 on a system running Oracle Ebusiness

11.5.10.2. I do a gracefull bounce of TRAIN. I shutdown the 9.2 listner which is still

running after dbua completed.
Now I try to start the 10.2 listner:
logon as oratrn
TNS_ADMIN and ORACLE_HOME are both pointing to 10.2 home
I do:
lsnrctl start TRAIN
but I get the following error:
tns-01106 listener using listener name listener has already been started for idbsoldv

Cause:
I did a google search for "PLSExtProc" which I belive is causing this since it is in

lisntner.ora of both TRAIN and idbsoldv. It looks like "PLSExtProc" is for some feature to

interact with outside word and it appears that Oracle Applications might use it.

Fix:
So I went ahead and remove any reference to "PLSExtProc" and EXTPROC0
from lisntner.ora of instance idbsoldv:
First logon as oracle and stop lsn:
lsnrctl stop idbsoldv
Then change
Original listener.ora. for idbsoldv was:

SID_LIST_idbsoldv =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u07/app/oracle/10.2.0)
(PROGRAM = extproc)
)
)

idbsoldv =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ux108.idbasol.com)(PORT = 1525))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

I changed it to:
idbsoldv =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ux108.idbasol.com)(PORT = 1525))
)
)

as oracle Start lsn:
lsnrctl start idbsoldv

Now without removing "PLSExtProc" and EXTPROC0 from TRAIN listener.ora startup the lsn:
as oratrn pointing to 10g home:
lsnrctl start TRAIN
-->This worked and I now can have both listner running.

Problem H:
Today: 24-Sep-09
Description of the problem encountered by a user:
Getting the following error when trying to recompile view view800_v

alter view view800_v compile;

ERROR at line 1:
ORA-04031: unable to allocate 139880 bytes of shared memory ("shared
pool","alter view t011_x33_...","sql area","view_text : prscvw")

This is in TEST.
Google search of ORA-04031 seems to suggest not enough shared memory for shared_pool_size

or large_pool_size.
Looking at TEST I have shared_pool_size set to 300 MB but I have not allocated
large_pool_size in TEST. Compared to PROD where I do not get this error where I have

large_pool_size set to 200MB.

Fix:
Modified TEST init.ora file and set large_pool_size to 100 MB and bounced TEST.

View now compiles successfully:
alter view view800_v compile;

View altered.
Update: 13-Oct-09: This happened again, I increased arge_pool_size to 200 MB and bounced TEST.


Problem I)
Today: 25-May-2010
Exported idbasol schema from SID23 instance which is 10g. When trying to import this into a

9i instance I get the following error:
IMP-00010: not a valid export file, header failed verification

Cause: the header of 10g exports are not readable in 9i import.
Fix:
Export from 9i using TNS into 10g then import using 10g import:
In 9i tnsnames.ora create an entry to connect to the 10g database.
Logon as the owner of the 9i database and do the export of the schema in the 9i database.
Logon as the owner of the 10g database, in the tnsnames.ora create an entry to connect to

the 9i database, then imprt the 9i export into the 10g database.


Problem G)
Today: 05-Oct-2010
After cloning PROD over TEST, we get this error in TEST:
ORA-00379: no free buffers available in buffer pool string for block size 16k
This error was generated from a view trying to access a table that was stored in a 16K

block size tablespace.

Cause: I had DB_32K_CACHE_SIZE=200M set in TEST but it should have been

DB_16K_CACHE_SIZE=200M
since I have a few tables I have in a tablespace with block size set to 16k and not 32k.

Fix:
remove DB_32K_CACHE_SIZE=200M and set DB_16K_CACHE_SIZE=200M in init and bounce.

Problem K):
On 02-Nov-2010, after we enabled some multi org featers in PROD, then some standard
and custom apps programs started to fail with following error:
ORA-30554: function-based index GL.idbasol_GL_JE_LINES_N1 is disabled

I recreated the index:
Drop INDEX GL.idbasol_GL_JE_LINES_N1;
CREATE INDEX GL.idbasol_GL_JE_LINES_N1 ON GL.GL_JE_LINES (CODE_COMBINATION_ID,

APPS.idbasol_PERIOD_NAME_TO_DATE(PERIOD_NAME))
TABLESPACE GLX;

-->This caused around 100 invalid objects which all recompiled successfully using adadmin

Then things started to run for a few minutes then same error came up.

For example when running this:
select * from idbasol_TRANS_EXT
I would get this error.

Cause:
Here is the explanantion of the error:

ORA-30554: function-based index string.string is disabled
Cause: An attempt was made to access a function-based index that has been marked disabled

because the function on which the index depends has been changed.

Action: Perform one of the following actions: -- drop the specified index using the DROP

INDEX command -- rebuild the specified index using the ALTER INDEX REBUILD command --

enable the specified index using the ALTER INDEX ENABLE command -- make the specified index

usable using the ALTER INDEX UNUSABLE command

Fix:
Dropped the index GL.idbasol_GL_JE_LINES_N1 on 2-Nov-2010 and did not recreate it and the

error went away.
-->Drop caused around 100 apps invalid objects which all recompiled successfully using

adadmin


Problem L):
Today: 29-Dec-10
A stored procedure doing an insert. The insert outside of the procedure works just fine and

inserts 1677 rows. But from with the procedure it only inserts 15 rows.
CREATE OR REPLACE PROCEDURE idbasol.yut897 (P_DATE IN DATE, P_COMPANY IN NUMBER)

Fix:
It turned out that if I rename the parameter P_DATE and use say P12_DATE then it works.

Looks like it does not like parameter names P_DATE even though it does not generate and

error.
It turned out that one of the tables involved in the insert has a column named P_DATE, so that possibly created some un-seen problem.


Conclusion:
So make sure that stored procedures, functions or packages IN and OUT parameters do not accidentally match an existing name of a column from a table or view referenced by the stored object.

Problem M):
Today: 04-Jan-2011
In listener log (ORACLE_HOME/ network/log/idbsoldv.log)
I see repeated warning messages:
WARNING: Subscription for node down event still pending

Cause
These messages are related to the Oracle10g TNS Listener's default subscription to the

Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.
Solution
Set the following parameter in the listener.ora:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

For example for idbsoldv listener.ora will look like this:

# listener.ora Network Configuration File:

/u07/app/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

idbsoldv =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ux108.idbasol.com)(PORT = 1525))
)
)
SUBSCRIBE_FOR_NODE_DOWN_EVENT_idbsoldv=OFF

Then bounce listner as seen here:
lsnrctl stop idbsoldv
lsnrctl start idbsoldv

This solution worked great in idbsoldv.

Problem N):
Today: 01-Oct-2011
Getting the following error when trying to startup Oracle database 10.2.0.4 from SQL Prompt:
ORA-00201: control file version 10.2.0.0.0 incompatible with ORACLE version
9.2.0.0.0
ORA-00202: control file: '/u01/oradata/proddata/cntrl01.dbf'

When I run addbctl.sh to startup the database it works fine, no error.
Cause:
It looks like 10.2.0.4 if you do not specify pfile then it would try to use spfile if it

exists in $ORACLE_HOME/dbs
In my situation I had an old spfile which had compatible set to 9.2.0 which was

incompatible with the current database control files which were 10.2.0 compatible.
addbctl.sh uses pfile hardcoded in
/u02/app/proddb/10.2.0/appsutil/scripts/PROD_win32xx/adstopdb.sql
which does this:
startup pfile=/u02/app/proddb/10.2.0/dbs/initPROD.ora
Fix:
Startup the database using the correct pfile the create a new spfile:
startup pfile=/u02/app/proddb/10.2.0/dbs/initPROD.ora
create spfile from pfile;
Now bouncing the database from sql prompt will work since the new spfile contains compatible=10.2.0

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com