Re: Oracle XE Corruption
- From: Evan Pettrey <jepettrey@xxxxxxxxx>
- To: daniel.fink@xxxxxxxxxxxxxx
- Date: Wed, 25 Aug 2010 12:22:50 -0400
The rebuild did work, but the errors still remained and the log continued to
grow. After dropping and recreating the indexes everything worked properly.
Thanks again for everybody's help.
On Wed, Aug 25, 2010 at 11:35 AM, Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>wrote:
> Glad to hear that all worked out well.
>
> What error did you get when you tried to rebuild the index? Or did the
> rebuild 'work' but the errors remained?
>
> As someone else has pointed out, you can actually export objects owned by
> sys if you specify the table name. That is the power of a collective
> intelligence - we learn something new on a daily basis!
>
> ------- Original Message -------
>
> On 8/25/2010 12:15 PM Evan Pettrey wrote:
> 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
>>
>>
>>
>> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
