ORA-07445: exception encountered: core dump [qkxrPXformUnm()+88] [SIGSEGV]

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.