Online Index Rebuild Problem in 12C/18C/19C
12/05/2017 3 Comments
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! My 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 BANNER_FULL ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production Version 18.1.0.0.0 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'; OWNER JOB_NAME START_DATE REPEAT_INTERVAL STATE ------------------------------ ------------------------------ ----------------------------------- ------------------------------ --------------- SYS CLEANUP_ONLINE_IND_BUILD 07-FEB-18 08.13.44.415343 PM +00:00 FREQ = HOURLY; INTERVAL = 1 SCHEDULED
MOS Solution Note 2280374.1 is not helpful:
Solution
Rebuild the index at a time other than the time frame when “SYS”.”CLEANUP_ONLINE_IND_BUILD” is running as auto job. #facepalm #whatifittakesover1hourtobuild
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?
LikeLike
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;
LikeLike
Pingback: Проблемы online index (re)build и избыточное преобразование Table Expansion | Oracle mechanics