Evan, You don't need to stop database. If table on which index is based is quite heavily used, then either request users to log out from application or you can stop the application. Drop the index and recreate it. Start the application. Just check resultant invalid objects and compile them. It is again an optional step and does not require down time. Trace files may be removed whenever you need to. No physical backup is required. You can do it in your cloned vm environment to buid your confidence to do it in prod. Regards Rafiq Date: Tue, 24 Aug 2010 16:05:28 -0400 Subject: Re: Oracle XE Corruption From: jepettrey@xxxxxxxxx To: ilmar.kerm@xxxxxxxxx CC: Oracle-L@xxxxxxxxxxxxx Thanks everybody, you've been immensely helpful. I'm going to create a clone of the VM and bring it up in our lab environment to see if it works this evening. I have scheduled an hour downtime tomorrow morning to perform the fix assuming all goes well in the lab environment. My plan for tomorrow is as follows: 1) Stop the application 2) Stop the database 3) Create a clone of the VM the DB resides on 4) Backup and delete the huge trace files that have been created 5) Drop and recreate the indexes 6) Start the database 7) Start the application 8) Pray it all works properly! On Tue, Aug 24, 2010 at 3:34 PM, Ilmar Kerm <ilmar.kerm@xxxxxxxxx> wrote: From XE: SQL> explain plan for alter index SMON_SCN_TIME_TIM_IDX rebuild; Explained. SQL> select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id; Operation Object -------------------------------------------------------------------------------------------------------------- ------------------------------ ALTER INDEX STATEMENT () INDEX BUILD (UNIQUE) SMON_SCN_TIME_TIM_IDX SORT (CREATE INDEX) INDEX (FAST FULL SCAN) SMON_SCN_TIME_TIM_IDX On Tue, Aug 24, 2010 at 9:52 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote: > Not sure if that is true, as I recall alter index rebuild... will fix an > index that is in an invalid state. > > On Tue, Aug 24, 2010 at 1:43 PM, Ilmar Kerm <ilmar.kerm@xxxxxxxxx> wrote: >> >> Hi >> >> Doesn't rebuilding actually use the existing index to read the data >> from, so rebuild will actually hit the corruption? >> From documentation: >> >> http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/server.102/b14231/indexes.htm#i1006864 >> "When you rebuild an index, you use an existing index as the data source." >> >> So drop and create the index. >> >> 1. Get the index DDL using DBMS_METADATA.GET_DDL >> select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX') txt from >> dual; >> >> 2. Drop old index: drop index SMON_SCN_TIME_TIM_IDX; >> >> 3. Run create index statement returned from dbms_metadata.get_ddl -- Ilmar Kerm -- //www.freelists.org/webpage/oracle-l