Re: Oracle XE Corruption
- From: Evan Pettrey <jepettrey@xxxxxxxxx>
- To: ilmar.kerm@xxxxxxxxx
- Date: Wed, 25 Aug 2010 08:15:48 -0400
Greetings everybody,
I wanted to follow up concerning the issue I brought to you guys yesterday
and let you know how things turned out. Rebuilding the index didn't actually
fix the problem, what fixed the issue was dropping and recreating the
indexes on table smon_scn_time using the below:
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade;
I created a clone of our Oracle DB VM and then performed the work while the
system was live without any downtime whatsoever, far exceeding my initial
expectations.
Just wanted to thank all of you guys for your help yesterday. I'm not a DBA
by any means, but as the sys admin for a small company (only around 150
employees) I am expected to wear a lot of different hats. With your help I
was able to resolve this issue and ensure I live to see another paycheck.
I've been in the IT industry for awhile now and I make frequent use of
forums. However, I can honestly say this is the most helpful one I've come
across to date. If only they had something this helpful for sys admins!
Thanks again everybody, it is greatly appreciated.
Regards,
Evan
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:
