ORA-600[25027] OR ORA-1578 WITH CELL OFFLOADING AND AGGREGATION. GOOD WITH CELL OFFLOADING DISABLED (Doc ID 2620924.1)

Last updated on AUGUST 22, 2023

Applies to:

Symptoms

imageinfo
-------------
Active image version: 12.1.2.3.4.170111
Active image kernel version: 2.6.39-400.294.1.el6uek

Errors in file /u01/app/oracle/diag/rdbms/ebsprod/ebsprod2/trace/ebsprod2_ora_6548.trc (incident=9241091):
ORA-01578: ORACLE data block corrupted (file # 110, block # 1855360)
ORA-27616: ASM Allocation Unit: 17945
ORA-01110: data file 110: '+DATA_EBSP/ebsprod/datafile/apps_ts_tx_data33.dbf'details in: /u01/app/oracle/diag/rdbms/ebsprod/ebsprod2/incident/incdir_9241091/ebsprod2_ora_6548_i9241091.trc

Changes

 
Errors in file
/u01/app/oracle/diag/rdbms/ebsprod/ebsprod2/trace/ebsprod2_ora_6548.trc  
(incident=9241091):
ORA-01578: ORACLE data block corrupted (file # 110, block # 1855360)
ORA-27616: ASM Allocation Unit: 17945
ORA-01110: data file 110: '+DATA_EBSP/ebsprod/datafile/apps_ts_tx_data33.dbf'
Incident details in:
/u01/app/oracle/diag/rdbms/ebsprod/ebsprod2/incident/incdir_9241091/ebsprod2_o
ra_6548_i9241091.trc

++ Symptoms were attributed to Bug 26103526 which is found as a duplicate to
Bug 26939314 by the ARE. So Patch 26939314 was suggested.


++ After the application of patch 26939314, CT gets various other errors.
They are intermittently getting the below error. Especially ORA-00600
[25027].

ORA-00600: internal error code, arguments: [qerghFetch:inc], [], [], [], [],
[], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [25027], [0], [0], [15], [0], [],
[], [], [], [], [], []


++ The customer is now getting ORA-00600 [25027] for the below query.

---- SQL QUERY (sql_id=80h2jfjfur47p) ----

select item_type, count(*)
from applsys.WF_ITEM_ACTIVITY_STATUSES
group by item_type
order by 2 desc



SQL> select item_type, count(*) from applsys.WF_ITEM_ACTIVITY_STATUSES group
by item_type order by 2 desc;
select item_type, count(*) from applsys.WF_ITEM_ACTIVITY_STATUSES group by
item_type order by 2 desc
                                                                           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [0], [0], [15],
[1229149192], [], [], [], [], [], [], []

DIAGNOSTIC ANALYSIS:
--------------------
++ When the cell_offload_processing was set to false, the query works but it
is very slow since smart scan is not used

select /*+ OPT_PARAM('cell_offload_processing' 'false') */ item_type,
count(*) from applsys.WF_ITEM_ACTIVITY_STATUSES
group by item_type order by 2 desc;

++ When _optimizer_aggr_groupby_elim was set to false corruption reported

22:44:04 SQL> alter session set "_optimizer_aggr_groupby_elim"=FALSE ;

Session altered.

Elapsed: 00:00:00.02
22:44:09 SQL>  select item_type, count(*) from
applsys.WF_ITEM_ACTIVITY_STATUSES group by item_type order by 2 desc;

select item_type, count(*) from applsys.WF_ITEM_ACTIVITY_STATUSES group by
item_type order by 2 desc
                                                                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 116, block # 1478784)
ORA-27616: ASM Allocation Unit: 186954
ORA-01110: data file 116:
'+DATA_EBST1/ebsdev2/datafile/apps_ts_tx_data36.dbf'

++ We tried running the wrong results script from Doc ID 1260804.1 but that
did not help.


WORKAROUND:
-----------
When the cell_offload_processing was set to false, the query works but it is
very slow since smart scan is not used

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.