19c upgrade - ORA-00600 [qksvcGetGuardCol:2]

 Hi Everyone!

Today was working on 19c upgrade. Leading 19c upgrade for ~100 DBs as a part of enterprise wide DB platform upgrade. One of the profile DB users reported issues during validation. First thing, checked alert logs, it was flooded with ORA-600 errors. 

2022-12-29T08:47:44.962654-07:00

DDE: Problem Key 'ORA 600 [qksvcGetGuardCol:2]' was completely flood controlled (0x6)

Further messages for this problem key will be suppressed for up to 10 minutes

ORA-00600: internal error code, arguments: [qksvcGetGuardCol:2], [195544], [0], [], [], [], [], [], [], [], [], []

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

2022-12-29T09:18:22.561604-07:00

DDE: Problem Key 'ORA 600 [qksvcGetGuardCol:2]' was completely flood controlled (0x6)

Further messages for this problem key will be suppressed for up to 10 minutes

Used Ora 600 Look up tool to see if any known issues with 19c upgrade. couldn't find. 

ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)

Next logical step was to create Oracle SR and uploaded trace after creating Incident Service Package.

I was able to reproduce the issue with SQL execution. SQL I found from trace file. 

SQL> SELECT /*+ INDEX (t TCPA_CONSENT_SUMMARY_AK) */ * FROM contact.tcpa_consent_summary t WHERE (t.global_profile_id, t.phone_number) IN (('582b9be1-1efd-4fc3-ae96-4f28633b24f8', '6518953326'),('582b9be1-1efd-4fc3-ae96-4f28633b24f8', '6518953326'));

SELECT /*+ INDEX (t TCPA_CONSENT_SUMMARY_AK) */ * FROM contact.tcpa_consent_summary t WHERE (t.global_profile_id, t.phone_number) IN (('582b9be1-1efd-4fc3-ae96-4f28633b24f8', '6518953326'),('582b9be1-1efd-4fc3-ae96-4f28633b24f8', '6518953326'))                                                  *

ERROR at line 1:

ORA-00600: internal error code, arguments: [qksvcGetGuardCol:2], [195544], [0],

[], [], [], [], [], [], [], [], []

SQL>

Here is what the solution that we came up with

SQL>select a.obj#,c.name table_name,a.intcol#,a.name column_name,a.property,b.guard_id from col$ a,ecol$ b,obj$ c where a.obj#=b.tabobj# and a.intcol#=b.colnum and a.obj#=c.obj# and a.property=1073741824 and a.null$=0 and b.guard_id is null;

 

    195549

TCPA_CONSENT_DETAIL

        15

INDIANA_CONSENT

1073741824

    195544

TCPA_CONSENT_SUMMARY

        14

INDIANA_CONSENT

1073741824

Based on the output, we have executed the below SQLs.

alter table  contact.TCPA_CONSENT_SUMMARY modify  INDIANA_CONSENT  not null enable novalidate;

alter table contact.TCPA_CONSENT_DETAIL modify INDIANA_CONSENT not null enable novalidate;

alter table  contact.TCPA_CONSENT_SUMMARY modify  INDIANA_CONSENT  null;

alter table contact.TCPA_CONSENT_DETAIL modify INDIANA_CONSENT null;

That resolved our post 19c upgrade functional issue. Hope this helps someone out there.!!

Happy Sharing.!