Re: ORA-600

Amit,

There is always evidence.  An error message?  A program failing? If the table is "/not supporting DML operations/", then how do they know?  Because there is an error message.

 From what you describe, it could be possible that the DBA may have decided to just rebuild stuff willy-nilly, and along the way something got fixed, but they don't know what was wrong or how it got fixed.  This is not a good place to be, and I hope this is not the case.  Even so, there is still an error message to which they reacted.

If you are being asked to assist, then please insist to share all evidence, especially complete error messages and their full context (where, when, who encountered, etc).

Also, if this is a custom-built MV, then it is a risky decision to create a transactional MV on one of the busiest tables in Oracle EBS.  It might be possible that this MV might need to be "on demand" rather than "on commit", at least temporarily until this problem is resolved.

Hope this helps,

-Tim

On 12/12/2023 10:04 AM, Amit Saroha wrote:
> Hi Tim,
>
> I can't say I have evidence but our DBA mentioned post index rebuild
> the table was able to support DML operations. The index was on the
> master table as per the DBA folks and I am wondering if there is a
> possibility to know what got corrupted due to parallel run of MV
> truncation and table update.
>
> Best Regards,
> AMIT SAROHA
>
>
> On Tue, Dec 12, 2023 at 12:27 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:
>
> Amit,
>
> What was your evidence that it "corrupted" an index and needed to
> be rebuilt?  An "ORA-" error?  A trace file dump?  Something else?
>
> Was the index on the master table, or on the MV?
>
> Thanks!
>
> -Tim
>
>
> On 12/12/2023 4:49 AM, Amit Saroha wrote:
>> Thanks, Tim. I was able to figure out using the note that MV log
>> truncation was in progress while another process started using
>> the table and make sense to me. However, seems it corrupted the
>> index even though the index was active and valid and all DMLs
>> failed on the failed with the same error. After the index is
>> rebuild the DML works fine so I am wondering why it corrupted a
>> index and not any other things and is it possible to see whether
>> it really corrupted the index using alert log or any other mechanism?
>>
>> Thanks for your help.
>>
>> On Mon, Dec 11, 2023 at 1:52 PM Tim Gorman
>> <tim_at_bridgetondata.com> wrote:
>>
>> Amit,
>>
>> If you search MOS with the keywords "ora 600 ktspgfb-1" then
>> in the resulting list of hits you should see Oracle Support
>> note #285847.1 (entitled "/ORA-600 [ktspgfb-1]/"), which is a
>> master note for this issue.  In this article, there are
>> filters for the version affected.  According to your trace
>> output, you are using Oracle database version 19.18, and the
>> clicking the button to filter for issues related to "19.18"
>> in the article yields...
>>
>> * Oracle Support note #34291262.8 (entltled "/Bug 34291862
>> - ORA-600 [ktspgfb-1] or
>> ORA-600[ktecgsc:objdchk_kcbgtcr_5] on update mv master
>> table/")
>>
>> As the article title suggests, Oracle bug 34291862 involves
>> updates to the master table of a materialized view.
>>
>> To determine if OE_ORDER_LINES is a master table in a
>> materialized view, please consider running queries like...
>>
>> select owner, mview_name from all_mview_detail_relations
>> where detailobj_owner = 'ONT' and detailobj_name =
>> 'OE_ORDER_LINES_ALL' and detailobj_type = 'TABLE';
>>
>> ...or...
>>
>> select owner, mview_name from all_mview_detail_relations
>> where detailobj_name = 'OE_ORDER_LINES' and
>> detailobj_type = 'VIEW';
>>
>> ...and perhaps also check with Oracle Support to see if they
>> have considered this chain of evidence?
>>
>> Hope this helps,
>>
>> -Tim
>>
>>
>> On 12/11/2023 10:17 AM, Amit Saroha wrote:
>>> Hi All,
>>>
>>> We are experiencing ORA-600 is routinely raised, which is
>>> negatively affecting our operations. We've been working with
>>> Oracle, but there hasn't been much progress, so I thought
>>> I'd ask the specialists here if they might assist.
>>>
>>> The problem is always raised on one table in our EBS system,
>>> OR_ORDER_LINES_ALL, and this table is heavily used 24 * 7
>>> with a lot of INSERT + UPDATES.
>>>
>>> Our database contains 19c. I looked through the alert log
>>> and the trace file but couldn't make much sense of it, for
>>> example, using the below queries I figured out when the
>>> issue occurred and in the enclosed trace file with 3M rows I
>>> can see the below rows meaningful as they show an update
>>> statement but I don't see anything wrong with the statement
>>> and why would it cause ORA-600.
>>>
>>> Please offer any insights you have if possible.
>>>
>>> SELECT ORIGINATING_TIMESTAMP, DETAILED_LOCATION,
>>> MESSAGE_LEVEL, MESSAGE_TEXT, PROBLEM_KEY
>>>   FROM V$DIAG_ALERT_EXT
>>>  WHERE MESSAGE_LEVEL = 1
>>>    AND MESSAGE_TEXT LIKE '%ORA-00600%'
>>>  ORDER BY ORIGINATING_TIMESTAMP DESC;
>>>
>>> SELECT * FROM V$DIAG_TRACE_FILE_CONTENTS WHERE
>>> trace_filename = 'cebsprd_ora_165301_i848794.trc' ORDER BY
>>> TIMESTAMP;
>>>
>>>
>>>
>>> Best Regards,
>>> AMIT
>>

--
http://www.freelists.org/webpage/oracle-l