RE: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against

As Jarad is pointing out if you perform any nologging operations, then you need to perform a backup on those data files immediately or if you have a standby you need to copy those datafiles (in backup mode) to the standby to fix the nologging operation. Until you complete a backup your overall recovery system is in jeopardy. Also any nologging operation puts your overall recovery system in jeopardy until you test the latest backup to ensure you have a good copy that you could continue to apply logs against. Also you are limited in pulling previous backups to perform some recovery for the business if they wanted to walk data to a point in time after the last backup before the nologging op and to a point in time during the nologging op. However, in most cases the nologging op for most dba's is indexes, so you can still recover through a nologging op from a previous backup and you just need to drop all the indexes affected from the nologging op and rebuild them. You will then be fine, but it will extend your outage to the business. This of course doesn't work well if your nologging op was insert append nologging to a table, then you will have part of the table that is fine and part that corrupt.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Friday, August 19, 2005 12:31 PM
To: joelgarry_at_anabolicinc.com
Cc: oracle-l_at_freelists.org
Subject: Re: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against

On 8/19/05, Joel Garry <joelgarry_at_anabolicinc.com> wrote:

	Believe it.  Time bomb sat there since long ago.  Nologging operations
	bypass the redo logs.  So they bypass the archived logs.  So when you
	restore the datafile by rolling forward, you invalidate those blocks.

Roll forward from when?

If you restore from a backup prior to the nologging operation, and roll forward, the index created with 'nologging' will not be restored.

If you restore a backup taken after the creation of the nologging operation, the nologging object will be restored.

	So you have to fix them with some other mechanism than recovery.  Maybe
	use force logging if you don't want to run into this again.  And all 
	that advice about taking a backup after nologging operations seems
	pretty misleading, huh?

Either you need to test it further, or I don't understand your premise.  

--

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 19 2005 - 15:05:16 CDT