My Oracle Support Banner

DDLIMP Process Recreating Custom Function Based Indexes as Part Of Incremental Repository Migration (Doc ID 3014887.1)

Last updated on AUGUST 12, 2024

Applies to:

Siebel CRM - Version 24.2 and later
Information in this document applies to any platform.

Symptoms

Custom Function-based index tries to rebuild every time while performing Incremental Repository Migration from source to target environments.

There is a function-based index on the S_USER table (UPPER("LOGIN")) that has not changed in about a year. For some reason, the DDLSync(DDLIMP) process drops these indexes and rebuilds them even though no changes exist.

This can be identified from the DDLIMP log file - ddlimport_*.log

2024-03-22 15:28:51        Merging   table                            S_USER ...  
2024-03-22 15:28:51    drop index S_USER_X01_X
2024-03-22 15:28:51    create  index S_USER_X01_X on S_USER
(UPPER("LOGIN"), "PAR_ROW_ID") parallel nologging
tablespace siebel
2024-03-22 15:28:51    alter index S_USER_X01_X noparallel logging
2024-03-22 15:28:51          Modifying index                    S_USER_X01_X ... 0.750s

In production S_USER table used heavily, it is failing with below ORA-00054 errors - ddlimport_*.log:

2024-03-20 20:04:52 [tp][ODBC Oracle driver][Oracle]ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2024-03-20 20:04:52 Dropping index with the same column signature and retrying...
2024-03-20 20:04:52 alter index S_USER_X01_X noparallel logging


The issue can be reproduced at will with the following steps:


1. Log in DR(Development) environment Siebel Tools and access S_USER table and lock the object
2. Create a function-based index on the S_USER table (UPPER("LOGIN")) - S_USER_X01_X
3. Apply and activate the table
4. Performed Full RR migration from DR to RR with Siebel Migration Application
5. Renamed Migrated Repository to Siebel Repository and existing Siebel Repository to  Siebel Old Repository
6. Create a New Workspace in DR(Development) environment with help of Siebel Tools
7. Perform small changes like adding comments to any BC or Applet or any other custom tables
8. Deliver the workspace
9. log in to the Siebel Migration Application and perform IRR migration.

During ddlimp step, it is rebuilding the index even its untouched as explained above

Changes

 Applied 24.1 monthly updates on top of the 23.7 version

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.