Online Index Rebuild Problem in 12C

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 there’s a new job 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!

 

2 Responses to Online Index Rebuild Problem in 12C

  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.

    Nuts.

    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;

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

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

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: