Amit,
I find troubleshooting to be as much fun as is possible anywhere in this
career. It is all detective work, separating opinion from facts,
coaxing more information than initially shared, and testing hypotheses.
Some avoid troubleshooting, while others are drawn to it. The only way
to learn is to watch, listen, and practice. As with anything else
10,000 hours of practice is a requirement to start. 10,000 hours is
equivalent to 5 years full-time, at a minimum. This is true for all
skills, and there is no workaround.
Be patient, observe carefully, listen closely, and use every opportunity
to build up those hours.
Enjoy!
-Tim
On 12/12/2023 12:54 PM, Amit Saroha wrote:
Thank you for responding. However, the program that was truncating MV was error out after another program that was accessing the database was terminated, and none of them showed any error messagein their logs. The error message below comes from a subsequent program run after DML fails, and I guess this is what DBA investigated, although it is not clear from this message that the problem was with the index.
I apologize for wasting your time, but I was wondering if there was anything I could learn because troubleshooting is intriguing.
Cause: FDPSTP failed due to ORA-12096: error in materialized view log on
"ONT"."OE_ORDER_LINES_ALL"
ORA-00600: internal error code, arguments: [kdifind:objdchk_kcbgcur_3], [], [],
[], [], [], [], [], [], [], [], []
Best Regards,
AMIT
On Tue, Dec 12, 2023 at 3:09 PM Tim Gorman <tim.evdbt@xxxxxxxxx> wrote:
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@xxxxxxxxx>
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@xxxxxxxxxxxxxxxxx> 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
