jueves, 26 de noviembre de 2020

ORA-600[ktliReadStreamAdvanceBuf_2] with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

 While working with  a database 12.1.0.2 I was trying to gather statistics in fixed objects however I was receiving the error ORA-600[ktliReadStreamAdvanceBuf_2] :








After to investigate, I found the note: ORA-600[ktliReadStreamAdvanceBuf_2] when Select on UNIFIED_AUDIT_TRAIL (Doc ID 2446958.1)

The note basically says that this is caused by the UNIFIED_AUDIT_TRAIL and there are two solutions:

  1. Upgrade to Oracle RDBMS 20.1.
  2. Apply Patch 27873364 if available for your version and platform.

However in the database UNIFIED AUDIT TRAIL is not used:


SQL> select parameter,value from v$option where parameter = 'Unified Auditing'

PARAMETER            VALUE
-------------------- --------------------
Unified Auditing     FALSE

SQL>

I confirmed that when I try to select rows from any view of unified audit trail the error is raised:


SQL> SELECT COUNT(*)  FROM V_$UNIFIED_AUDIT_TRAIL;
SELECT COUNT(*)  FROM V_$UNIFIED_AUDIT_TRAIL
                      *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktliReadStreamAdvanceBuf_2], [29],
[29], [117], [239], [1], [32], [32], [0], [], [], []

even in the table:

SQL> select count(*) from x$unified_audit_trail;
select count(*) from x$unified_audit_trail
                     *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktliReadStreamAdvanceBuf_2], [29],
[29], [117], [239], [1], [32], [32], [0], [], [], []


SQL>

Since my goal was to gather statistics in the fixed objects of the database, and since the database is not using unified audit trail, I decided to "exclude" only that specific table from the gather stats job:

SQL> exec dbms_stats.lock_table_stats('SYS', 'x$unified_audit_trail');

PL/SQL procedure successfully completed.

SQL> 

Then I executed again the gather stats job:

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;

PL/SQL procedure successfully completed.

SQL> 

The job was Successful and I was able to gather statistics for all fixed objects except the unified audit trail table.

No hay comentarios:

Publicar un comentario

Oracle ACE Director Award - Deiby Gómez

Thanks #OracleACE Program for this awesome certificate recognizing the work I have done in the community for the last year. Looking forwa...