Tuesday, June 22, 2010

ORA-08106: cannot create journal table ... and ORA-00600


OS: Win2003 32 bit

Problem description: My colleague told me  that he can not rebuild index online. Index partition datafile was lost. Trying to rebuild index  in online mode, Oracle raised error ORA-08106: cannot create journal table XXX.SYS_JOURNAL_123473.


bash-2.05$ oerr ora 08106
08106, 00000, "can not create journal table %s.%s"
// *Cause:  The online index builder could not create its journal table 
// *Action: Rename the conflicting table or rerun the SQL statement. There
// *        may be a concurrent online index rebuild on the same object.

It seemed he already executed the same operation, however, because of network problem with database server, operation terminated.

As error description states, I followed below steps:

sql>drop table XXX.SYS_JOURNAL_123473 purge;

Table dropped.

Now, retrying the rebuild operation:

sql>alter index XXX.CALLS_ONCD_UK rebuild partition CDR200809_4 tablespace  TS_CALLS_IDX_CDR_200809_4 online;

I received error like : ORA-00600: internal error code, arguments: [kkdlfjou_1], [], [], [], [], [], [], []

Interesting, what could be done?  After investigation metalink, seems I have done something wrong.

As removal of the journal table is considered a data dictionary patching exercise, therefore, the standard recommendations for data dictionary patching apply:
1. The patching should be performed when the database is in restricted mode
2. The instance should be stopped/started (bounced) after patching is completed

None of above steps was performed by me.

Fortunately, in such case restarting database instance should solve the problem.

Exactly, after rebouncing database, I could manage rebuild index partition online.

1 comment:

SAI said...

There is no need to bounce the DB.
Rename the Index (twice to get into the same name)
Then Rebuild the index.