General
===============================
Large trace file are generated from time to time.
===============================
Evidences
===============================
less /software/oracle/diag/rdbms/orainst/orainst/trace/alert_orainst.log
Errors in file /software/oracle/diag/rdbms/orainst/orainst/trace/orainstorainst_ora_28448.trc (incident=79868):
ORA-00600: internal error code, arguments: [ktspfupdst-1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/orainst/orainst/incident/incdir_79868/orainst_ora_28448_i79868.trc
Wed Oct 24 02:31:17 2018
Trace dumping is performing id=[cdmp_20181024023117]
less /software/oracle/diag/rdbms/orainst/orainst/incident/incdir_79868/orainst_ora_28448_i79868.trc
Dump file /software/oracle/diag/rdbms/orainst/orainst/incident/incdir_79868/orainst_ora_28448_i79868.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /software/oracle/111
System name: Linux
Node name: col-com-1-aps-4
Release: 2.6.18-308.24.1.el5
Version: #1 SMP Wed Nov 21 11:42:14 EST 2012
Machine: x86_64
Instance name: orainst
Redo thread mounted by this instance: 1
Oracle process number: 83
Unix process pid: 28448, image: oracle@col-com-1-aps-4
*** 2018-10-24 02:31:15.971
*** SESSION ID:(488.25914) 2018-10-24 02:31:15.971
*** CLIENT ID:() 2018-10-24 02:31:15.971
*** SERVICE NAME:(SYS$USERS) 2018-10-24 02:31:15.971
*** MODULE NAME:(JDBC Thin Client) 2018-10-24 02:31:15.971
*** ACTION NAME:() 2018-10-24 02:31:15.971
Dump continued from file: /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_28448.trc
ORA-00600: internal error code, arguments: [ktspfupdst-1], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 79868 (ORA 600 [ktspfupdst-1]) ========
*** 2018-10-24 02:31:15.971
----- Current SQL Statement for this session (sql_id=akh1m5g2waych) -----
MERGE into SFI_CUSTOMER_PROFILE o using (select :1 KEY1 from dual) o1 on (o.KEY1 = o1.KEY1) when matched then update SET IMSI=:2 , TS_LAST_MODIFIED=sysdate, KEY2=:3 , KEY3=:4 , MSISDN=:5 , ATTR41=:6 , ATTR42=:7 , ATTR43=:8 , ATTR44=:9 , ATTR45=:10 , ATTR46=:11 , ATTR47=:12 , ATTR48=:13 , ATTR49=:14 , ATTR50=:15 , ATTR51=:16 , ATTR52=:17 , ATTR53=:18 , ATTR54=:19 , ATTR55=:20 , ATTR56=:21 , ATTR57=:22 , ATTR58=:23 , ATTR59=:24 , ATTR60=:25 , ATTR61=:26 , ATTR62=:27 , ATTR63=:28 , ATTR64=:29 , ATTR65=:30 , ATTR66=:31 , ATTR67=:32 , ATTR68=:33 , ATTR69=:34 , ATTR70=:35 , ATTR71=:36 , ATTR72=:37 , ATTR73=:38 , ATTR74=:39 , ATTR75=:40 , ATTR76=:41 , ATTR77=:42 , ATTR78=:43 , ATTR79=:44 , ATTR80=:45 , TS_LAST_TRY_FETCH=:46 , EI_STATUS_CODE_ID=:47 when not matched then Insert (o.KEY1, o.IMSI, o.TS_LAST_MODIFIED,o.KEY2,o.KEY3, o.MSISDN, o.ATTR41,o.ATTR42,o.ATTR43,o.ATTR44,o.ATTR45, o.ATTR46,o.ATTR47,o.ATTR48,o.ATTR49,o.ATTR50, o.ATTR51,o.ATTR52,o.ATTR53,o.ATTR54,o.ATTR55, o.ATTR56,o.ATTR57,o.ATTR58,o.ATTR59,o.ATTR60, o.ATTR61,o.ATTR62,o.ATTR63,o.ATTR64,o.ATTR65, o.ATTR66,o.ATTR67,o.ATTR68,o.ATTR69,o.ATTR70, o.ATTR71,o.ATTR72,o.ATTR73,o.ATTR74,o.ATTR75, o.ATTR76,o.ATTR77,o.ATTR78,o.ATTR79,o.ATTR80, o.TS_LAST_TRY_FETCH,o.EI_STATUS_CODE_ID) Values (:48 ,:49 ,sysdate,:50 ,:51 ,:52 ,:53 ,:54 ,:55 ,:56 ,:57 ,:58 ,:59 ,:60 ,:61 ,:62 ,:63 ,:64 ,:65 ,:66 ,:67 ,:68 ,:69 ,:70 ,:71 ,:72 ,:73 ,:74 ,:75 ,:76 ,:77 ,:78 ,:79 ,:80 ,:81 ,:82 ,:83 ,:84 ,:85 ,:86 ,:87 ,:88 ,:89 ,:90 ,:91 ,:92 ,:93 ,:94 )
===============================
Solution
===============================
Searching for ORA-00600: internal error code, arguments: [ktspfupdst-1], [], [], [], [], [], [], [], [], [], [], []
Reading the Matalink description, is seems that the issue is related to DML operations on a table with corrupt Index.
Reading the Matalink description, is seems that the issue is related to DML operations on a table with corrupt Index.
Indeed, in this case, trace files were generated during MERGE statement.
Per Matalink, two possible solutions
1st Solution
A. Rebuilding all indexes on the updated table, and it partitions.
B. Running DBMS_SPACE_ADMIN.assm_segment_verify();
DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY example
BEGIN
DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY ('PERFSTAT','STATS$EVENT_HISTOGRAM', 'TABLE', NULL, 10);
END;
/
PL/SQL procedure successfully completed.
PERFSTAT - owner
STATS$EVENT_HISTOGRAM - object name
TABLE - object type
NULL - partition name
10 - stands for SEGMENT_VERIFY_DEEP - Deep verification involves consistency checks between datablocks and space metadata blocks
9 - stands for SEGMENT_VERIFY_BASIC - Basic verification involves consistency checks of space metadata.
2nd Solution
A. ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
B. In case of error: rebuilding all indexes on the updated table, and it partitions. then validate structure with same command.
C. ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
===============================
1st Solution
===============================
For Non Partitioned Tables:
SELECT 'ALTER INDEX '||table_owner||'.'||index_name||' REBUILD ONLINE ;'
FROM DBA_INDEXES
WHERE index_name IN
(SELECT index_name
FROM USER_INDEXES
WHERE table_name = 'SFI_CUSTOMER_PROFILE')
AND index_owner = 'MY_USER';
For Partitions:
SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||';'
FROM DBA_IND_PARTITIONS
WHERE index_name IN
(SELECT index_name
FROM USER_PART_INDEXES
WHERE table_name = 'SFI_CUSTOMER_PROFILE')
AND index_owner = 'MY_USER';
===============================
2nd Solution by Example
===============================
sqlplus system/XXX@orainst
Several users own table SFI_CUSTOMER_PROFILE
For all except one, the command is successful.
SELECT 'ANALYZE TABLE '||owner||'.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE ONLINE;'
FROM DBA_SEGMENTS WHERE segment_name = 'SFI_CUSTOMER_PROFILE' AND blocks > 10;
SQL> ANALYZE TABLE USER_A.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE ONLINE;
Table analyzed.
SQL> ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE ONLINE;
ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE ONLINE
*
ERROR at line 1:
ORA-14508: specified VALIDATE INTO table not found
Need to use syntax:
ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
To create the table INVALID_ROWS:
@/software/oracle/111/rdbms/admin/utlvalid.sql
oracle@user:~/scripts>% less /software/oracle/111/rdbms/admin/utlvalid.sql
rem
Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM UTLVALID.SQL
Rem FUNCTION
Rem Creates the default table for storing the output of the
Rem analyze validate command on a partitioned table
Rem NOTES
Rem MODIFIED
Rem syeung 06/17/98 - add subpartition_name
Rem mmonajje 05/21/96 - Replace timestamp col name with analyze_timestamp
Rem sbasu 05/07/96 - Remove echo setting
Rem ssamu 01/09/96 - new file utlvalid.sql
Rem
create table INVALID_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
SQL> ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
ANALYZE TABLE COL_CLARO_SPXQQ.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
To locate the trace file
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'ALEC.DBA';
SQL> ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
Trace file:
/software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_21706_ALEC.DBA
*** 2019-05-12 08:10:20.401
*** SESSION ID:(528.12704) 2019-05-12 08:10:20.401
*** CLIENT ID:() 2019-05-12 08:10:20.401
*** SERVICE NAME:(orainst) 2019-05-12 08:10:20.401
*** MODULE NAME:(SQL*Plus) 2019-05-12 08:10:20.401
*** ACTION NAME:() 2019-05-12 08:10:20.401
Table/Index row count mismatch1?RAaDG~ODU
table 449980 : index 449979, 0
Partitioned Index root = tsn: 5 rdba: 0x0140843a
The root cause of failure is mismatch in rows between table and primary key index.
This is not a valid situation.
SQL> SELECT index_name FROM DBA_PART_INDEXES WHERE table_name = 'SFI_CUSTOMER_PROFILE' AND owner = 'USER_B';
INDEX_NAME
------------------------------
SFI_CUSTOMER_PROFILE_PK
SQL> SELECT 'ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION '||partition_name||' ONLINE; '
FROM DBA_IND_PARTITIONS
WHERE index_name = 'SFI_CUSTOMER_PROFILE_PK'
AND index_owner = 'USER_B';
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P69 ONLINE;
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P70 ONLINE;
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P71 ONLINE;
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P72 ONLINE;
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P73 ONLINE;
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P74 ONLINE;
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P75 ONLINE;
ALTER INDEX USER_B.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P76 ONLINE;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'ALEC.DBA_2';
SQL> ANALYZE TABLE COL_CLARO_SPXQQ.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
ANALYZE TABLE COL_CLARO_SPXQQ.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
SQL> SELECT COUNT(*) FROM INVALID_ROWS;
COUNT(*)
----------
0
Trace file: /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_21706_ALEC.DBA_2
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /software/oracle/111
System name: Linux
Node name: col-com-1-aps-3
Release: 2.6.18-308.24.1.el5
Version: #1 SMP Wed Nov 21 11:42:14 EST 2012
Machine: x86_64
Instance name: orainst
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 21706, image: oracle@col-com-1-aps-3
*** 2019-05-12 08:20:50.739
*** SESSION ID:(528.12704) 2019-05-12 08:20:50.739
*** CLIENT ID:() 2019-05-12 08:20:50.739
*** SERVICE NAME:(orainst) 2019-05-12 08:20:50.739
*** MODULE NAME:(SQL*Plus) 2019-05-12 08:20:50.739
*** ACTION NAME:() 2019-05-12 08:20:50.739
*** TRACE CONTINUED FROM FILE /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_21706_ALEC.KAPLAN ***
Table/Index row count mismatch1?1rEN5~2FU
table 449990 : index 449989, 0
Partitioned Index root = tsn: 5 rdba: 0x0140c5b2
orainst_ora_21706_ALEC.KAPLAN_2 (END)
Same error as before...
The rebuild of index did not solve error.
This is the index in question:
ALTER TABLE SFI_CUSTOMER_PROFILE
ADD CONSTRAINT SFI_CUSTOMER_PROFILE_PK PRIMARY KEY (key1)
USING INDEX LOCAL;
Lets TRUNCATE the table, and re-populate is from a backup table.
CREATE TABLE SFI_CUSTOMER_PROFILE_BAK AS SELECT * FROM SFI_CUSTOMER_PROFILE WHERE 1=2;
ALTER TABLE SFI_CUSTOMER_PROFILE_BAK NOLOGGING;
INSERT /*+ APPEND */ INTO SFI_CUSTOMER_PROFILE_BAK SELECT * FROM SFI_CUSTOMER_PROFILE;
3593549 rows created.
TRUNCATE TABLE SFI_CUSTOMER_PROFILE;
INSERT /*+ APPEND */ INTO SFI_CUSTOMER_PROFILE SELECT * FROM SFI_CUSTOMER_PROFILE_BAK;
COMMIT;
SQL> ANALYZE TABLE USER_B.SFI_CUSTOMER_PROFILE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
Table analyzed.
Now table data and table PK are in sync.
Need to drop the INVALID_ROWS table:
SQL> DROP TABLE INVALID_ROWS;
Table dropped.
