Online Index Rebuild fails with ORA-08106: cannot create journal table

One of my daily index rebuild job abnormally failed with error “ORA-08106: cannot create journal table”. Following was the investigation & solution to fix the problem

Before investigation few things to remember about online index rebuild

1. It will do a full table scan on the base table.
2. A journal table is maintained for DML data, which has changed during this index rebuilding operation.
3. At the end of index rebuild, it will merge the journal table with the base table. At this time, it has to release the exclusive lock on the base table to complete the activity.
4. Tables named SYS_JOURNAL_% are temporary tables and their only use is as working tables for an online index rebuild command

— Check which JOURNAL tables are created

SQL> column OBJECT_NAME format a30
SQL> select OWNER,OBJECT_NAME,OBJECT_ID from 
dba_objects where object_name like 'SYS_JOURNAL%';
OWNER OBJECT_NAME OBJECT_ID
------- ---------------------- ----------
SCOTT SYS_JOURNAL_11282 63502

— Find out mapping for INDEX & JOURNAL

SQL> column TEMP_TABLE_NAME format a30
SQL> select a.object_name, b.table_name temp_table_name
2 from dba_objects a,
3 ( select substr(object_name,13) as obj_id,
4 object_name as table_name
5 from dba_objects
6 where object_name like 'SYS_JOURNAL_%') b
7 where a.OBJECT_ID = b.obj_id;
OBJECT_NAME TEMP_TABLE_NAME
------------ -----------------
DAILY_JOB_IX1 SYS_JOURNAL_11282

— SMON process is responsible for cleaning up the JOURNAL tables. But sometimes SMON cannot perform its and task. We can force it by using following PL/SQL (This is provided by Oracle)

SQL> declare
isclean boolean;
begin
isclean := false;
while isclean = false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN 
(dbms_repair.all_index_id, dbms_repair.lock_wait);
dbms_lock.sleep (10);
end loop;
end;
/

PL/SQL procedure successfully completed.

But in my case this did not help i.e. it did not clean up the JOURNAL entries. Also bouncing instance did not help.

— Alert log has evidence that, it tried cleaning it

online index (re)build cleanup: objn=11282 maxretry=2000 forever=0
online index (re)build cleanup: objn=11618 maxretry=2000 forever=0

— Now only option is to drop these JOURNAL tables manually

But there is another problem. JOURNAL tables are considered as data dictionary tables. So we need to follow steps provided my oracle for data dictionary maintenance (I strongly recommend to involve oracle support at this point)

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

— This is most important for data dictionary maintenance

SQL> startup restrict
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2211064 bytes
Variable Size 1979712264 bytes
Database Buffers 1207959552 bytes
Redo Buffers 16953344 bytes
Database mounted.
Database opened.

SQL> select OWNER, OBJECT_NAME, OBJECT_ID from dba_objects 
where object_name like 'SYS_JOURNAL%';
OWNER OBJECT_NAME OBJECT_ID
----- ----------------- ----------
SCOTT SYS_JOURNAL_11282 63502

SQL> drop table SCOTT.SYS_JOURNAL_11282 purge;
Table dropped.


SQL> select OWNER, OBJECT_NAME, OBJECT_ID from dba_objects 
where object_name like 'SYS_JOURNAL%';
no rows selected
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2211064 bytes
Variable Size 1979712264 bytes
Database Buffers 1207959552 bytes
Redo Buffers 16953344 bytes
Database mounted.
Database opened.

— JOURNAL tables are dropped now. But online index rebuild now failing with different error now

SQL> alter index SCOTT.DAILY_JOB_IX1 rebuild online;
alter index SCOTT.DAILY_JOB_IX1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_INDREBUILD1) violated

— Later I had to use following to clean up the entries in SYS tables and then rebuild online worked

SQL> declare
isclean boolean;
begin
isclean := false;
while isclean = false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN 
(dbms_repair.all_index_id, dbms_repair.lock_wait);
dbms_lock.sleep (10);
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> alter index SCOTT.DAILY_JOB_IX1 rebuild online;
Index altered.

SMON would have eventually cleaned up these entries. But cleanup cycle is executed every 60 mins & there is a possibility that SMON cannot get a lock on the object with NOWAIT it will just try again later.

 

Metalink Ref docs –

NOTE:1324941.1 – How To Find Out Temporary Table Sys_journal_xxxxx Is Created By Rebuilding Which Index
NOTE:1378173.1 – How to use DBMS_REPAIR.ONLINE_INDEX_CLEAN ?
NOTE:247487.1 – Rebuilding Index Online Gives ORA-8106

 

Advertisements
This entry was posted in Oracle Index and tagged . Bookmark the permalink.

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