Problem:
Recently we encountered ORA-07445 error in our single node EBS (12.1.3)
environment with 11.2.0.3 database on SunOS 5.11 (64-bit platform).
As we know the ORA-7445 is an
unhandled exception error due to an OS exception which should result in the
creation of a core file. An ORA-7445 is
a generic error, and can occur from anywhere in the Oracle code. The precise
location of the error is identified by the core file and/or trace file it
produces.
In our case, after reviewing the trace file, the error is raised from EBS
for an apps query involving ORDER BY reference.
Alert log contents:
Thu Jul 17 21:44:43 2014
Exception [type: SIGSEGV, Address not mapped to object]
[ADDR:0x2000000020] [PC:0x1044777D8, qkxrPXformUnm()+88] [flags: 0x0, count:
1]
Errors in file /<Dump_Location>/IGA_ora_24885.trc (incident=131168):
ORA-07445: exception encountered: core dump [qkxrPXformUnm()+88]
[SIGSEGV] [ADDR:0x2000000020] [PC:0x1044777D8] [Address not mapped to object]
[]
|
Cause:
The issue is caused by Bug 12834800 - ORA-7445 [QKXRPXFORMUNM()+92] and I
found a supporting document on metalink 1355848.1 , according to the doc. any
query involving ORDER BY reference by position to an expression may fail with
an ORA-7445.
Solution:
As a workaround, it is suggested to disable virtual column replacement or
avoid using order by reference and specify the select list expression
explicitly instead.
To disable virtual column replacement:
- alter session set "_replace_virtual_columns" = false;
FYI: Parameter "_replace_virtual_columns" influences the explain
plan in using or not the function based index. Setting
"_replace_virtual_columns" = false doesn't affect the database
functionality, but it will change the execution plan for the queries where a
function based index is inolved.
Two values are possible for this parameter:
"_replace_virtual_columns" = FALSE - explain plan will use the function based
index
"_replace_virtual_columns" = TRUE - explain plan will not use the function
based index
OR
- Apply Patch:12834800
Note: Both the workaround require DB bounce.
Please apply the patch first in non-production environment, and perform
the below test case to reproduce the issue at will.
SQL> create table
test (vendor_name varchar2(10));
Table created.
SQL> CREATE INDEX
TEST_INDEX ON TEST (UPPER(VENDOR_NAME));
Index created.
SQL> select
to_char(sysdate,'DD-MM-YYYY HH:MM:SS') as "Today's Date" from dual;
Today's Date
-------------------
29-07-2014 08:30:42
SQL> SELECT UPPER
(V.vendor_name) C_SORT_VENDOR_NAME FROM test V GROUP BY V.vendor_name ORDER BY
1 ASC;
SELECT UPPER
(V.vendor_name) C_SORT_VENDOR_NAME
*
ERROR at line 1:
ORA-03113: end-of-file
on communication channel
Process ID: 12511
Session ID: 577 Serial
number: 24385
$ tail -20f
alert_IGAPATCH.log
Tue Jul 29 08:34:39 2014
Exception [type:
SIGSEGV, Address not mapped to object] [ADDR:0x2000000020] [PC:0x1044777D8,
qkxrPXformUnm()+88] [flags: 0x0, count: 1]
Errors in file
/IGAPATCHapps/IGAPATCH/db/diag/rdbms/igapatch/IGAPATCH/trace/IGAPATCH_ora_12511.trc (incident=103721):
ORA-07445: exception
encountered: core dump [qkxrPXformUnm()+88] [SIGSEGV] [ADDR:0x2000000020]
[PC:0x1044777D8] [Address not mapped to object] []
HTH
Thanks for reading..!!
Any suggestions or enhancements are most welcome.
