Creation Of Unique Indexes I_col_mig1, I_col_mig2 & I_col_mig3 Take Long During Upgrade To 19c (Doc ID 2966285.1)

Last updated on OCTOBER 06, 2023

Applies to:

Symptoms

Upgrading EBS database 12c(12.1.0.2) to 19c(19.16.0.0) in ExaCS.

During upgrade dbaascli creates 3 unique indexes but it took quite long. It took almost 42% of total upgrade time.

For example if upgrade took 22hrs in which these 3 index creation took 9hrs 42 mins.

21:09:26 SQL> create unique index i_col_mig1 on col$mig(obj#, name)
21:09:26 2 storage (initial 30k next 100k maxextents unlimited pctincrease 0)
21:09:26 3 tablespace system
21:09:26 4 /

Index created.

Elapsed: 02:01:12.87
23:10:38 SQL> create index i_col_mig2 on col$mig(obj#, col#)
23:10:38 2 storage (initial 30k next 100k maxextents unlimited pctincrease 0)
23:10:38 3 tablespace system
23:10:38 4 /

Index created.

Elapsed: 02:19:46.98
01:30:25 SQL> create unique index i_col_mig3 on col$mig(obj#, intcol#)
01:30:25 2 storage (initial 30k next 100k maxextents unlimited pctincrease 0)
01:30:25 3 tablespace system
01:30:25 4 /

Index created.

Elapsed: 03:21:32.03


Total time for these 3 indexes 09hrs 42mins
There were no ORA- error messages in alert log .

Cause

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