Online Index Rebuild Problem in 12C/18C/19C

When building an index in Oracle Enterprise edition using the keyword “ONLINE”, if the index build fails for whatever reason a messy set of extents can get left behind.

In Oracle 12.1 [update still there in 12.2 and 18C and 19.6] there’s a job (located in DBA_SCHEDULER_JOBS) to help with this: “SYS”.”CLEANUP_ONLINE_IND_BUILD” which ticks away, undocumented, in the background cleaning up “rogue” extents once an hour, every hour (start time based upon job creation time)However, if I run a CREATE INDEX … ONLINE command on a table and the index creation happens to coincide with the invocation of the cleanup job I get the following error in my alert log

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl1:
2017-05-12 05:28:37.191123 -04:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_j000_12235.trc:
ORA-12012: error on auto execute of job "SYS"."CLEANUP_ONLINE_IND_BUILD"
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SYS.DBMS_PDB", line 76
ORA-06512: at line 4

Oh no! The job has failed to clean up the extents that I was currently using. Phew!

The CREATE INDEX … ONLINE command completed with success a few minutes later.

A few minutes after that, an update statement failed with:

ORA-08102 index key not found for obj# 107102 file 16 block 1171234

Yes, I have a corrupt index! index_fadeMy assumption is that the cleanup job had removed all of the extents which had been allocated before the cleanup job started. The index create statement did not notice as the current extent removal attempt blocked (hence the job error) and future to-be created extents were not affected. However, most of the index had been “cleaned up”. #sadface

Solution: re-create the index and make sure the index creation is complete before your hourly job kicks in. Better still, disable the undocumented hourly job… make sure get the full backing of an Oracle Support SR first!

Next Step: Raise SR with Oracle about this!

Version Check 12/11/2018:

select banner_full from v$version

Oracle Database 18c EE High Perf Release - Production

21:17:19 SYS @ ORCL > select owner,job_name,start_date,repeat_interval,state from dba_scheduler_jobs where job_name = 'CLEANUP_ONLINE_IND_BUILD';

------------------------------ ------------------------------ ----------------------------------- ------------------------------ ---------------



MOS Solution Note 2280374.1 is not helpful:


Rebuild the index at a time other than the time frame when “SYS”.”CLEANUP_ONLINE_IND_BUILD” is running as auto job. #facepalm #whatifittakesover1hourtobuild



3 Responses to Online Index Rebuild Problem in 12C/18C/19C

  1. Bryan Hall says:

    What if the index takes (much) longer than an hour to create?

    I just ran into this after creating a long-running online index (ran serial to minimize system impact). Does Oracle really expect us to work around (not at the top of the hour) online index creation? If so – where is that in the docs? Or do I have to somehow disable this new wonderful “feature” before I create an index, and then re-enable it afterwards.


    Fixed in 12.2?


    • I’m afraid it’s very much in 12.2, running the undocumented function dbms_pdb.cleanup(2) every hour 😦

      Don’t know if they’ve improved the code, but I doubt it.

      SQL> select * from v$version;

      ——————————————————————————– ———-
      Oracle Database 12c Enterprise Edition Release – 64bit Production 0

      2 from dba_scheduler_jobs
      3 where job_name = ‘CLEANUP_ONLINE_IND_BUILD’
      4* order by owner,job_name
      15:40:50 SQL> /

      —————————— ——————————————————————————–
      FREQ = HOURLY; INTERVAL = 1 declare
      myinterval number;
      myinterval := dbms_pdb.cleanup_task(2);
      if myinterval 0 then
      next_date := systimestamp +
      numtodsinterval(myinterval, ‘second’);
      end if;


  2. Pingback: Проблемы online index (re)build и избыточное преобразование Table Expansion | Oracle mechanics

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.