Print Friendly, PDF & Email

 
ISSUE

A database writes ORA-00600 [qsmkzfs:NULL seminfo] errors into the alert log everytime when a materialized view is being created or being compiled.

 
RELATED

 
SYMPTOMS

In the alert log and trace file, you see the following errors

[oracle@dbpilot.net trace]$ tail -30f alert_sovadb.log
Tue Mar 13 10:32:05 2018
Errors in file /d01/oracle/diag/rdbms/sovadb/sovadb/trace/sovadb_ora_16037.trc  (incident=295350):
ORA-00600: internal error code, arguments: [qsmkzfs:NULL seminfo], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /d01/oracle/diag/rdbms/sovadb/sovadb/incident/incdir_295350/sovadb_ora_16037_i295350.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Mar 13 10:32:07 2018
Dumping diagnostic data in directory=[cdmp_20180313103207], requested by (instance=1, osid=16037), summary=[incident=295350].
Tue Mar 13 10:32:08 2018
Sweep [inc][295350]: completed
Sweep [inc2][295350]: completed

[oracle@dbpilot.net trace]$ cat sovadb_ora_16037.trc
Trace file /d01/oracle/diag/rdbms/sovadb/sovadb/trace/sovadb_ora_16037.trc
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
ORACLE_HOME = /d01/oracle/12.1.0.1
System name:    Linux
Node name:      dbpilot.net
Release:        2.6.39-400.297.3.el5uek
Version:        #1 SMP Fri Jun 30 10:12:39 PDT 2017
Machine:        x86_64
Instance name: sovadb
Redo thread mounted by this instance: 1
Oracle process number: 49
Unix process pid: 16037, image: oracle@dbpilot.net (TNS V1-V3)

*** 2018-03-13 10:32:05.367
*** SESSION ID:(1012.49873) 2018-03-13 10:32:05.367
*** CLIENT ID:() 2018-03-13 10:32:05.367
*** SERVICE NAME:(SYS$USERS) 2018-03-13 10:32:05.367
*** MODULE NAME:(SQL*Plus) 2018-03-13 10:32:05.367
*** ACTION NAME:() 2018-03-13 10:32:05.367

*** TRACE FILE RECREATED AFTER BEING REMOVED ***

Incident 295350 created, dump file: /d01/oracle/diag/rdbms/sovadb/sovadb/incident/incdir_295350/sovadb_ora_16037_i295350.trc
ORA-00600: internal error code, arguments: [qsmkzfs:NULL seminfo], [], [], [], [], [], [], [], [], [], [], []

 
SOLUTION

Review a trace file containing incident details. For this testcase the trace file is sovadb_ora_16037_i295350.trc.
At the beginning of the trace file, you would find a statement causing the ORA-00600 error.

[oracle@dbpilot.net trace]$ head -85 /d01/oracle/diag/rdbms/sovadb/sovadb/incident/incdir_295350/sovadb_ora_16037_i295350.trc
Dump file /d01/oracle/diag/rdbms/sovadb/sovadb/incident/incdir_295350/sovadb_ora_16037_i295350.trc
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
ORACLE_HOME = /d01/oracle/12.1.0.1
System name:    Linux
Node name:      dbpilot.net
Release:        2.6.39-400.297.3.el5uek
Version:        #1 SMP Fri Jun 30 10:12:39 PDT 2017
Machine:        x86_64
Instance name: sovadb
Redo thread mounted by this instance: 1
Oracle process number: 49
Unix process pid: 16037, image: oracle@dbpilot.net (TNS V1-V3)


*** 2018-03-13 10:32:05.368
*** SESSION ID:(1012.49873) 2018-03-13 10:32:05.368
*** CLIENT ID:() 2018-03-13 10:32:05.368
*** SERVICE NAME:(SYS$USERS) 2018-03-13 10:32:05.368
*** MODULE NAME:(SQL*Plus) 2018-03-13 10:32:05.368
*** ACTION NAME:() 2018-03-13 10:32:05.368

Dump continued from file: /d01/oracle/diag/rdbms/sovadb/sovadb/trace/sovadb_ora_16037.trc
ORA-00600: internal error code, arguments: [qsmkzfs:NULL seminfo], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 295350 (ORA 600 [qsmkzfs:NULL seminfo]) ========

*** 2018-03-13 10:32:05.368
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=1nb68xqzzcmf9) -----
create materialized view v_xml_parser_899880 as
   select * from (
                  select /*+ optimizer_features_enable('9.2.0') */
                         subj.item_id,
                         subj.parent_id,
                         subj.item_name,
                         ico.attribute_string_value as ico,
                         nvl(target.counter,0) as counter,
                         level lvl
                  from (select i1.item_version_id,
                               i1.item_id,
                               i1.parent_id,
                               i1.item_name,
                               i1.ord
                        from rds_dat_object_items i1
                        where i1.object_id in
                              (select o.object_id
                                      from rds_mdt_objects o
                                      where o.object_code = 'DIR_COMP')) subj,
                              (select a.attribute_string_value, a.item_version_id
                                      from rds_dat_object_item_add_attrs a
                                      where a.item_version_id in
                              (select i2.item_version_id
                                      from rds_dat_object_items i2
                                      where i2.object_id in
                              (select o.object_id
                                      from rds_mdt_objects o
                                      where o.object_code = 'DIR_COMP'))
                                      and a.attribute_id in
                              (select o1.attribute_id
                                      from rds_mdt_object_attributes o1
                                      where o1.attribute_code = 'ICON')) ico,
                              (SELECT a3.attribute_string_value,
                                      COUNT(a3.attribute_string_value) AS counter
                                      FROM rds_dat_object_item_add_attrs a3,
                                      rds_dat_object_items i3
                                      WHERE i3.item_version_id = a3.item_version_id
                                      AND i3.item_id IN
                                      (SELECT v.item_id
                                      FROM v_rds_dat_obj_200282686_i118 v
                                      WHERE v.STATUS = 230614803)
                                      AND a3.attribute_id IN (SELECT o3.attribute_id
                                      FROM rds_mdt_object_attributes o3
                                      WHERE o3.attribute_code = 'ENTERPRISE')
                                      AND a3.object_id IN (SELECT o4.object_id
                                      FROM rds_mdt_objects o4
                                      WHERE o4.object_code = 'BEST_PRACTICIANS')
                                      GROUP BY a3.attribute_string_value) target
                                      where subj.item_version_id = ico.item_version_id(+)
                                      and subj.item_id = to_number(target.attribute_string_value(+))
                                      CONNECT BY PRIOR subj.item_id = subj.parent_id
                                      START WITH subj.parent_id = -1
                                      ORDER BY level, subj.ord
                  ) where 1 = 1

----- Call Stack Trace -----

In this testcase the CREATE MATERIALIZED VIEW statement has caused the ORA-00600 error. The error is raised because of the OPTIMIZER_FEATURES_ENABLE hint, which was migrated from previous database releases (9i,10g) to the current one (12.1.0.1.0) and left after the migration.

Meanwhile, if I try to compile the same materialized view the database writes ORA-00600 error into the alert log as well

SQL> ALTER MATERIALIZED VIEW V_XML_PARSER_899880 COMPILE;

Materialized view altered.
[oracle@dbpilot.net trace]$ tail -30f alert_sovadb.log
Errors in file /d01/oracle/diag/rdbms/sovadb/sovadb/trace/sovadb_ora_16037.trc  (incident=295351):
ORA-00600: internal error code, arguments: [qsmkzfs:NULL seminfo], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /d01/oracle/diag/rdbms/sovadb/sovadb/incident/incdir_295351/sovadb_ora_16037_i295351.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Mar 13 10:35:17 2018
Dumping diagnostic data in directory=[cdmp_20180313103517], requested by (instance=1, osid=16037), summary=[incident=295351].
Tue Mar 13 10:35:18 2018
Sweep [inc][295351]: completed
Sweep [inc2][295351]: completed

As the hint refers to old database release (9i) I just removed it and the materialized view was created without any errors in the alert log.

So, removing the hint fixes the issue, and no new ORA-00600 is generated anymore. Well, now I’m going to find an object containing the former materialized view code

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL NAME FOR A20 
COL TYPE FOR A15 
COL LINE FOR 999
COL TEXT FOR A90
SELECT OWNER, NAME, TYPE, LINE, TEXT FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE UPPER('%CREATE%XML_PARSER%');

OWNER    NAME        TYPE          LINE TEXT
-------- ----------- ------------- ---- --------------------------------------------------------------------------------
COMPANY  XML_TABLES  PACKAGE BODY    17 l_sql_stmt := 'create materialized view v_xml_parser_' || i || ' as  ' || p_xml;

From the result of the query, I see that the materialized view is built into COMPANY.XML_TABLES package body on the 17th line of the code. The mview is generated dynamically through l_sql_stmt variable. So, to fix the issue (removing the hint), I should modify the package body either through GUI tool (TOAD, PL/SQL Developer) or by retrieving DDL of the package body with the help of DBMS.METADATA package.

 
SUMMARY

You would have another reason for the ORA-00600 [qsmkzfs:NULL seminfo] but the steps to find it will be exactly the same.

 
 

Version  : 11:17 13.03.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.1.0.1.0