Re: Archive logs causing data block corruption !!??

TunaFish wrote:
>
> - rebuilt a standby database from cold backup of production
> - applied 2 days worth of archive logs to bring database upto date
> - every thing went smooth
> - opened the database ok with resetlogs
> - on running 'select count(*) from ..' all tables; turned out 3 of 315
> tables
> sitting on a datafile had corrupted data blocks
>
> - started over; by this time bringing up the database from the same
> backup with
> reset logs; but did not apply the archive logs this time;
> - database came up fine; the 3 tables in question were A-ok as well;
>
> - started over again; restored database from same backup;
> - applied about first half of archive logs;
> - the same 3 tables were corrupted
>
> - started over again; restored database from same backup;
> - applied only the first (1) archive log;
> - the same 3 table were A-ok
>
> Anyone know what gives?
> Funne part is while applying archive logs there is no error; every
> thing goes smooth; database comes up fine; only while doing count(*)
> on these 3 table out of the thousand odd tables i get data block
> corruption error..
>
> Database opened.
> SVRMGR> select 'GS.SYR00908_POSBHD_TMP', count(*) from
> GS.SYR00908_POSBHD_TMP
> 2> ;
> 'GS.SYR00908_POSBHD_TM COUNT(*)
> ---------------------- ----------
> ORA-01578: ORACLE data block corrupted (file # 912, block # 57543)
> ORA-01110: data file 57: '/dbfprd1/p27/userb5'
>
> thanks!
> Bruce

I assume you've tried this same query on the production database and did not find any corruption? That would tend to rule out a corruption at the datafile level. Assuming this is the case, sounds like you have a corrupt archive log. From what you've tested, it appears there was a corruption introduced at some point after the first archive log but before the 2nd half of the logs you're applying - one of your archives has corrupt redo. To get past this issue on the standby side, you could just take a backup of the problematic datafile from production from a point in time *after* the bad redo and use that in your standby build. That way the recovery process won't try to apply the corrupt redo to that datafile, and you should be OK. Also, instead of having to activate the standby to test your query, you could just open the standby read-only (and save you the trouble of recreating the standby for each test).

Regards,
Sean Received on Tue Aug 27 2002 - 14:35:54 CDT