Re: NOLOGGING recovery errors

 I did this for one of the indexes

 alter index da.ST_PRODTXWIPSRC_ON_FK_I rebuild logging
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 5341)
ORA-01110: data file 6: '/oradata/semst01/idx01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

It's a non unique index.  I think it's complaining about the primary key index 
on that table... which is weird... 
Anyhow, i'm trying to recreate those primary key/ unique key indexes and i 
can't cause they have constraints on them:

Here's a primary key:
semst01:SQL> alter index da.ST_WI_PROP_VALS_PK rebuild logging;
alter index da.ST_WI_PROP_VALS_PK rebuild logging
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 19549)
ORA-01110: data file 6: '/oradata/semst01/idx01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Was hoping someone had a quick script to disable a constraint then drop the 
index...

Lyall

 


 

 

-----Original Message-----
From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante@xxxxxxxxxxxx>
To: lyallbarbour@xxxxxxxxxxxxxxx <lyallbarbour@xxxxxxxxxxxxxxx>; 
oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
Sent: Mon, Dec 6, 2010 12:00 pm
Subject: RE: NOLOGGING recovery errors



And what happens if you do not use the “NOLOGGING” option?
 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of lyallbarbour@xxxxxxxxxxxxxxx
Sent: Monday, December 06, 2010 11:36 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: NOLOGGING recovery errors

 

 

Actually when i try and ALTER INDEX REBUILD these indexes with the block 
NOLOGGING it says it has block corruption, so that's probably with Oracle tells 
you to drop then create.

 

 

-----Original Message-----
From: lyallbarbour@xxxxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Sent: Mon, Dec 6, 2010 11:25 am
Subject: Re: NOLOGGING recovery errors

Cause Metalink Article #794505.1

says to rebuild indexes by drop/create them and i can't do that with primary 
keys or unique indexes in general.  I need to disable the constraint, then drop 
then re-create with LOGGING turned on.  
I'm just following that article since i have this NOLOGGING problem with 
corrupt blocks after the recovery

Lyall



-----Original Message-----
From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante@xxxxxxxxxxxx>
To: lyallbarbour@xxxxxxxxxxxxxxx <lyallbarbour@xxxxxxxxxxxxxxx>
Sent: Mon, Dec 6, 2010 11:17 am
Subject: RE: NOLOGGING recovery errors


Lynn,

 

Why not just rebuild the invalid indexes?

 

Declare

Begin

For c1 in (select index_name from user_index

                     Where status=’INVALID’) loop

   Execute immediate ‘alter index  ‘ || c1.index_Name || ‘ rebuild’;

End loop;

 

 


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of lyallbarbour@xxxxxxxxxxxxxxx
Sent: Monday, December 06, 2010 11:04 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: NOLOGGING recovery errors


 

Hi,
  I rebuilt some indexes on our production database yesterday with NOLOGGING.  
They took a cold back up.  But this morning when we refreshed our test 
instance, they refreshed with the hot backup, so after applying the archive 
logs, we are getting corrupt block errors.  I'm trying to rebuild the indexes, 
drop then create from the ddl, but i'm having a hard time with the script, 
since we are on 10.2.0.4  How can i generate the ddl for

alter table disable constraint -- then
drop index

for all these primary keys?  I'm sure this is on google somewhere, but i 
haven't found it yet.

Thanks,
Lyall


 


=

Other related posts: