Re: Oracle XE Corruption
- From: Evan Pettrey <jepettrey@xxxxxxxxx>
- To: ilmar.kerm@xxxxxxxxx
- Date: Tue, 24 Aug 2010 16:05:28 -0400
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
>
>
>
Other related posts:
