ORA-600 [] and ORA-8103 errors can happen after TTS Import into 19c

Photo by Steve A Johnson on Unsplash

What is the issue?

At first, this is an edge case not many of you would see.

Two conditions must be met:

1. You do or did a migration with transportable tablespaces (TTS) or full transportable export/import (FTEX)
2. You have dropped constraints with the KEEP INDEX clause leading to orphaned indexes

I think the TTS or FTEX part is very clear but let me explain the KEEP INDEX requirement with an example.

When you drop a constraint this way:

ALTER TABLE orders
  DROP CONSTRAINT orders_pk KEEP INDEX;

it removes the constraint definition but preserves the index object that was enforcing it.

Afterwards:

• The ORDERS_PK constraint no longer exists in DBA_CONSTRAINTS
• The index remains in DBA_INDEXES and its segment remains allocated
• Its name, tablespace, storage attributes, visibility, statistics, compression, and partitioning remain unchanged
• The optimizer can still use it for access paths
• It is not a physically orphaned/corrupt segment—just potentially a logically unneeded index (an index that is no longer required for the original database rule that caused it to exist)

Hence, again, this is an edge case. But the customer who alerted me found this situation two years after the migration in an archival database where they only randomly access data from.

This blog post is mainly meant to assist you in case you find errors such as those below with the ORA-8103 being the most likely one to see theoretically:

ORA-20000 / ORA-8103
ORA-600 [ktsscrsegfmt:objdchk_kcbnew_3]
ORA-600 [ktrget2:kcbz_objdchk]
ORA-600 [kdifind:kcbz_objdchk]
ORA-600 [ktspfmdb:objdchk_kcbnew_3]
ORA-600 [kcl_mismatch_1]

What may cause the issue?

How can you prevent it?

The first Bug 30978304 is fixed in the Data Pump Bundle Patch since 19.12. You know that we highly recommend the DPBP – and for M5 migrations, it is mandatory to be in place anyway.

But the second issue, a fix for Bug 33171426 is not included in any RU for 19c yet. Hence, we recommend that you apply the one-off patch PATCH 33171426. This fix prevents orphaned indexes from being created on the target. The orphaned index creation will fail with a non-internal error when the fix is present, thus preventing any future corruption due to double extent allocation.

Unfortunately, life isn’t that simple sometimes. Since this fix touches not only a c library but also Data Pump structures and packages, and since we require that you have the DPBP in place for such migrations, you may need to request a Merge Label Request (MLR) based on your patch setup for your target home. Act early, download PATCH 33171426 and check for conflicts. And then request the Merge on top of your desired RU. Several merges have been produced already, and yours may be already available.

But I agree, it is tricky.

Maybe you are on the safe side. But I’d rather apply the fix. Since most people migrate into a new database, having downtime to apply the fix before the migration starts may be not a torture. And in my understanding (and I discussed with the customer who hit this as well) I think you could remove the fix later after the migration in order to allow future RAC rolling patching.

The fix being present in the target home for the migration itself will make it error-out in such cases. Then you can fix it, and attempt the migration again. But you won’t need the fix later anymore usually.

How can you detect it (possibly)?

Bad news: There is no reliable way to detect such cases with a simply query to the dictionary.

Why not?

The database does not keep track of historical actions. Since this blog is about learning as well, I tried with this query below – but it showed zero rows when I created a scenario. Hence, this query is not helpful but I leave it in just in case somebody has a magic idea. You can find my whole test case here: Testcase Keep Index

SELECT i.owner,
       i.table_name,
       i.index_name,
       i.tablespace_name,
       i.status,
       i.uniqueness,
       i.constraint_index,
       s.header_file,
       s.header_block,
       ROUND(s.bytes / 1024 / 1024, 2) AS size_mb
FROM   dba_indexes i
       JOIN dba_segments s
         ON s.owner        = i.owner
        AND s.segment_name = i.index_name
        AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
WHERE  i.tablespace_name = UPPER('&tablespace_name')
AND    i.constraint_index = 'YES'
AND    NOT EXISTS
       (
         SELECT 1
         FROM   dba_constraints c
         WHERE  c.owner       = i.owner
         AND    c.index_owner = i.owner
         AND    c.index_name  = i.index_name
         AND    c.constraint_type IN ('P', 'U')
       )
ORDER BY s.bytes DESC, i.owner, i.index_name;

I thought it would be able to reveal potential candidates, i.e. constraint-created indexes being no longer associated with a PK/UK constraint. But since we do not expose any historical relationship in supported dictionary views once the constraint is gone, it will not work.

The only reliable way I see is checking the audit trail.

You need to check for DDL evidence captured at any time in the past using Unified Auditing,. You would need to query your audit trail to check for any DROP CONSTRAINT ... KEEP INDEX statements. The challenge is how far your audit trail dates back, and how easily you can access it. If you keep your audit trail for a longer period then this may be a good way.

Here is a query to mow through your audit trail to check for such commands – of course, this makes only sense when you audit such DDLs.

set pagesize 100
set linesize 220
set long 20000
set longchunksize 20000
set trimspool on
set tab off

column event_timestamp        format a30 heading 'Timestamp'
column dbusername             format a20 heading 'Database user'
column os_username            format a20 heading 'OS user'
column userhost               format a30 heading 'Client host'
column client_program_name    format a35 heading 'Client program'
column object_schema          format a20 heading 'Object owner'
column object_name            format a30 heading 'Object name'
column action_name            format a16 heading 'Action'
column return_code            format 999999 heading 'ORA code'
column unified_audit_policies format a35 heading 'Audit policy'
column sql_text               format a100 heading 'DDL statement'

prompt
prompt ===============================================================
prompt Unified Audit: DROP CONSTRAINT ... KEEP INDEX in last 12 months
prompt ===============================================================
prompt

with audit_ddl as
(
  select con_id,
         event_timestamp,
         dbusername,
         os_username,
         userhost,
         client_program_name,
         object_schema,
         object_name,
         action_name,
         return_code,
         unified_audit_policies,
         sql_text,
         regexp_replace(sql_text, '[[:space:]]+', ' ') as normalized_sql
  from   cdb_unified_audit_trail
  where  event_timestamp >= systimestamp - interval '12' month
  and    action_name = 'ALTER TABLE'
  and    sql_text is not null
)
select con_id,
       event_timestamp,
       dbusername,
       os_username,
       userhost,
       client_program_name,
       object_schema,
       object_name,
       action_name,
       return_code,
       unified_audit_policies,
       sql_text
from   audit_ddl
where  regexp_like(
         normalized_sql,
         'DROP[[:space:]]+(CONSTRAINT|PRIMARY[[:space:]]+KEY|UNIQUE)[[:space:]].*KEEP[[:space:]]+INDEX',
         'i'
       )
order by event_timestamp, con_id;

alter table t drop constraint t_pk keep index;
alter table t drop primary key keep index;
alter table t drop unique (col1) keep index;

How would you repair it?

1. Create a repair table

   BEGIN
     DBMS_REPAIR.ADMIN_TABLES (
       TABLE_NAME => 'REPAIR_TABLE',
       TABLE_TYPE => dbms_repair.repair_table,
       ACTION     => dbms_repair.create_action,
       TABLESPACE => 'SYSAUX');
   END;
   /
   

2. Check for the ORA-8103 on the partitioned table

   set serveroutput on
   
   DECLARE num_corrupt INT;
   BEGIN
     num_corrupt := 0;
     DBMS_REPAIR.CHECK_OBJECT (
       SCHEMA_NAME       => '&OWNER',
       OBJECT_NAME       => '&TABLE',
       PARTITION_NAME    => '&PARTITION_NAME',
       REPAIR_TABLE_NAME => 'REPAIR_TABLE',
       CORRUPT_COUNT     => num_corrupt);
     DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
   END;
   /

3. Find the affected block

   set linesize 250
   col object_name format a25
   col schema_name format a10
   col corrupt_description format a50
   col partition_name format a30
   
   select object_id,
          relative_file_id,
          block_id,
          corrupt_type,
          schema_name,
          object_name,
          partition_name,
          corrupt_description,
          marked_corrupt
   from repair_table;
   

4. Mark the identified block(s) as corrupted

   DECLARE num_fix INT;
   
   BEGIN
     num_fix := 0;
     DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
        SCHEMA_NAME => '&OWNER',
        OBJECT_NAME=> '&TABLE',
        partition_name => '&PARTITION_NAME',
        OBJECT_TYPE => dbms_repair.table_object,
        REPAIR_TABLE_NAME => 'REPAIR_TABLE',
        FIX_COUNT => num_fix);
     DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
   END;
   /

5. Skip the corrupted block(s)

   BEGIN
     DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
       SCHEMA_NAME => '&OWNER',
       OBJECT_NAME => '&TABLE',
       OBJECT_TYPE => dbms_repair.table_object,
       FLAGS => dbms_repair.skip_flag);
   END;
   /

6. Move the partition

   alter table <table owner>.<table_name> move partition <partition name> online;

7. Rebuild Index(es)

   select
     owner,
     index_name,
     index_type,
     status,
     table_name,
     table_owner
   from
     dba_indexes
   where
     table_owner = '&OWNER' and  
     table_name = '&TABLE_NAME' and 
     tatus = 'UNUSABLE';

8. Remove the skip flag

   execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS( SCHEMA_NAME => '&OWNER', OBJECT_NAME => '&TABLE_NAME', flags=>sys.dbms_repair.NOSKIP_FLAG); 
   

    

Further Links and Information

• (PALRT2281) ALERT: ORA-600 [<objdchk>] and ORA-8103 Errors Possible After TTS Import Into 19c Due to Overlapping Extents
• Bug 30978304 – ORA-20000 DURING IMPDP WITH STATS AND THE UNIQUE INDEX FOR THE PK IS NOT CREATED
  • (KI28059) Bug 30978304 – ORA-20000 During non-TTS Data Pump Import While Importing Statistics, or ORA-600 [<objdchk>] / ORA-8103 Errors After TTS Operations
• (KB107134) Data Pump Recommended Proactive Patches For 19.10 and Above
• (KB144840) M5 Cross Endian Platform Migration using Full Transportable Data Pump Export/Import and RMAN Incremental Backups
• (KB138768) How to identify the SEGMENT reported by dbms_space_admin.TABLESPACE_VERIFY

• Testcase Keep Index