RE: Oracle XE Corruption

  • From: Mohammad Rafiq <rafiq9857@xxxxxxxxxxx>
  • To: <jepettrey@xxxxxxxxx>, <ilmar.kerm@xxxxxxxxx>
  • Date: Tue, 24 Aug 2010 16:18:34 -0400

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



                                          

Other related posts: