Re: Should we be afraid of online index rebuild?

I had a case rebuilding index online where the 'journaling' table ran out of extents.

THe index was being rebuild on a very active table w/ 10's of millions rows in it (activity was mostly new inserts).

I also had some locking issues, but don't recall the specifics.

On 11 Feb 2002 11:32:11 -0800, dave.evans_at_eds.com (David Evans) wrote:

>We have Oracle 8.1.7 on a production database, and we are thinking of
>rebuilding indexes online. Our main motivation is that we would not
>have to negotiate any additional maintenance window with the customer.
>However, our testing and our reading of items on Metalink has made us
>apprehensive. We were wondering if anybody has used the online index
>rebuild on a regular basis and can give us the benefit of their
>experience - should we be very afraid or not?
>
>Here are some of the things which made us worry:
>
>1. We saw that an interruption of the index rebuild could lead to
>severe problems. In one test, an accidental deletion of a journal
>table that Oracle had in play led to the following results on the
>rebuild of an index subpartition:
>
>any attempt to use any part of the index returned an ORA 0600. In
>other words, the entire index was unusable. This was in spite of the
>fact that all subpartitions showed in the dictionary as 'usable'.
>
>In this case, we dropped and recreated the entire index. (Perhaps
>other solutions were available.) A search of Metalink turned up a
>case where someone else who interrupted an index rebuild had to
>manually reset a flag in Ind$ before he was even able to drop and
>recreate the index.
>
>2. We were worried about possible performance problems. We are not
>sure what would happen if we rebuilt an index or part of an index
>while it was being updated heavily, and Oracle had to juggle writing
>to its journal table for the rebuild, building the index, and
>performing the updates. Our initial impulse is to avoid scheduling
>online rebuilds while heavy updating is taking place, but we aren't
>sure if this is precaution enough.
>
>3. Oracle has to put a short-term initial lock on the table itself
>(or maybe, where applicable, just the partition/subpartition) before
>starting the rebuild. We were wondering if people had many problems
>with this.
>
>
>Can anybody give us any practical advice about this, before we find
>out the hard way.
>
>Thanks.

.......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email