Re: Oracle XE Corruption

  • From: David Roberts <big.dave.roberts@xxxxxxxxxxxxxx>
  • To: jepettrey@xxxxxxxxx
  • Date: Tue, 24 Aug 2010 21:15:24 +0100

While I appreciate that this isn't a solution to the underlying problem,
I believe that you can limit the maximum trace file size by issuing a
command similar to the following to set a maximum file size for dump files:

alter system set max_dump_file_size=<N>


While this will not fix the problem it might take the pressure off allowing
for a greater amount of time to be spent on investigating and testing
potential solutions for the root cause.

Dave


On Tue, Aug 24, 2010 at 9:05 PM, Evan Pettrey <jepettrey@xxxxxxxxx> wrote:

> 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: