IGA Migration from v14.5.1 to v15 Blocked by ORA-01429 and Incomplete Schema Upgrade
Article ID: 439251
Updated On:
Products
Issue/Introduction
Automated database schema migration from Identity Governance (IGA) v14.5.1 to v15 fails to complete successfully, leading to critical database-level errors and missing schema elements.
The migration initially fails to incorporate new columns into existing tables, and subsequent attempts are blocked by an Oracle error.
Observed Failures:
Missing Columns:The upgrade process skips necessary ALTER TABLE ADD COLUMN statements, resulting in missing v15 columns (e.g., `AttributeName`, `IsDefaultAttribute`) in existing tables like `BFDisplayAttributes`.
Critical Block Error: The core internal upgrade script (UPGRADE_SDB_ORACLE.sql) fails to execute due to an Oracle database error: ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces.
Environment
Source Product Version: Identity Governance (IGA) v14.5.1
Target Product Version: Identity Governance (IGA) v15
Source Database: Oracle 11g (or 12c)
Target Database: Oracle 19C
Cause
The migration failure is attributed to two primary issues:
1. Conditional DDL Script Logic: The segment of the upgrade script responsible for adding new columns to existing tables used a conditional CREATE TABLE statement (`F tableExists = 0 THEN...). Since tables from the source (v14.5.1) already existed, this condition was false, causing the script to bypass the corresponding ALTER TABLE ADD COLUMN commands required to update the schema to v15.
2. Oracle 19C Index-Organized Table (IOT) Compatibility: The ORA-01429 error is a database-level issue related to IOT overflow, suggesting a block sizing limitation specific to the Oracle 19C target environment. This incompatibility blocked the upgrade script (UPGRADE_SDB_ORACLE.sql) from running successfully.
Resolution
The successful migration was achieved after addressing the Oracle ORA-01429 error and then re-running the IGA service startup to execute the database upgrade scripts.
Note: Manual schema changes (e.g., to QRTZ tables) are generally advised against, and a fresh copy of the replicated 14.5.1 database should be used before attempting the steps below. Ensure the database user has the necessary DBA role applied from the start.
Step 1 Resolve Oracle ORA-01429 (Index-Organized Table Overflow)
The Oracle ORA-01429 error, which typically occurs with Index-Organized Tables, can be resolved by manually modifying the RESOURCES table to include an OVERFLOW TABLESPACE.
Execute the following commands as a system user connected to the Oracle DB:
sql
connect as system user to the DB
Step 1: Add OVERFLOW TABLESPACE to the IOT
ALTER TABLE EUREKIFY_SDB.RESOURCES ADD OVERFLOW TABLESPACE EUREKIFY_SDB;
Step 2: Modify and re-enable constraints
ALTER TABLE EUREKIFY_SDB.RESOURCES MODIFY CONSTRAINT FK_RESOURCES_DATABASES ENABLE VALIDATE;
ALTER TABLE EUREKIFY_SDB.RESOURCES ADD CONSTRAINT FK_RESOURCES_DATABASES FOREIGN KEY (DATABASEID) REFERENCES EUREKIFY_SDB.DATABASES (DATABASEID) ENABLE NOVALIDATE;
ALTER TABLE EUREKIFY_SDB.RESOURCES MODIFY CONSTRAINT FK_RESOURCES_DATABASES ENABLE VALIDATE;
Step 3: Compile the schema
BEGIN
DBMS_UTILITY.COMPILE_SCHEMA(
schema => 'EUREKIFY_SDB',
compile_all => FALSE
);
END;
The following queries were used for confirmation and can be run optionally)
sql
SELECT table_name, iot_type, iot_name, tablespace_name
FROM dba_tables
WHERE owner = 'EUREKIFY_SDB'
AND (
table_name = 'RESOURCES'
OR iot_name = 'RESOURCES'
)
ORDER BY table_name;
SELECT constraint_name, constraint_type, status, validated
FROM dba_constraints
WHERE owner = 'EUREKIFY_SDB'
AND constraint_name = 'FK_RESOURCES_DATABASES';
SELECT object_type, COUNT(*) AS total
FROM dba_objects
WHERE owner = 'EUREKIFY_SDB'
AND status = 'INVALID'
GROUP BY object_type;
Step 2: Restart IGA Service to Complete Migration
After resolving the ORA-01429 error, stop and restart the Identity Governance service. The internal upgrade process should now execute successfully and complete the schema update to v15.
The logs and the SAGE_VERSION table should reflect that the database is now at v15, and tables like PROPERTIES and REPORTS should be present.
If the service still fails to start due to missing columns (as detailed in the Missing Columns cause above), follow up with the support team sharing the logs, put IG in DEBUG mode in IGX Console to get more details in the logs.
Feedback
