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

>>I would just as soon deal with...extra time involved
In a 24X7 shop with a the boss standing on your head and an application dying because of a missing HUGE index...NOLOGGING not only looks good but like a life saver.

>>extra time involved with the restore than what Chris has been dealing with.
Two things;
1.)I have used NOLOGGING "to build indexes" for many years with out error and again top shorten application upgrade outages. 2.)In this recent case the error was not with the NOLOGGING but rather with human error and recovery skills. This happened *AFTER* a database file had been lost because of a disk error. A more experienced DBA would not have created NOLOGGING indexes when the database was tossed over to the standby and was going to need to be rebuilt (rolling logs) on the primary.

But general I agree with what you are saying and taking a more conservative approach to DBA work is almost always a the best plan.

Chris Marquez
Oracle DBA

-----Original Message-----
From: Mercadante, Thomas F (LABOR) [mailto:Thomas.Mercadante_at_labor.state.ny.us] Sent: Mon 8/22/2005 8:02 AM
To: jkstill_at_gmail.com
Cc: Marquez, Chris; oracle-l_at_freelists.org Subject: RE: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against  

All,  

I was thinking about this whole topic over the weekend. It just affirms my feeling that using the NOLOGGING option needs to be used judiciously. And I question how much time we are saving here.  

I have now come up with a "NEW RULE" (to borrow from Bill Mahar).  

If the total time to reload using NOLOGGING and a total backup is less than a reload with LOGGING and no backup, then don't use it.  

I personally have never liked turning logging off. I would just as soon deal with the extra archive logs and extra time involved with the restore than what Chris has been dealing with. There are far too many things happening in a database for me to remember or even know about. So if *everything* is covered by archive logs, then a recovery will always work.  

Tom  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Saturday, August 20, 2005 4:18 PM
To: rjsearle_at_gmail.com
Cc: Brandon.Allen_at_oneneck.com; cmarquez_at_collegeboard.org; oracle-l_at_freelists.org
Subject: Re: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against    

On 8/20/05, rjsearle_at_gmail.com <rjsearle_at_gmail.com> wrote:

Forgive me for jumping in here in the middle of a conversation but I can't push these thoughts from my mind... Are all of these observations supported by the simple fact the modifications to the data dictionary ARE logged (recursive SQL) So the entries into the obj$ tables as a result of the new objects would be logged as well as the extent allocation actions.

Yes, the changes to the data dictionary are all logged.          

        You can see this by the small amount of redo generated even when inserting with append hint that results in a new extent (DMT obviously). So the block operations on the index may not be logged but the changes to the schema are logged.

Yup. There's some other stuff in there as well. A session with logminer would reveal what is being logged.          

        Also I consider it unwise to create *persistent* segments use nologging option for exactly this reason. But indexes can always be rebuilt, but at what cost? (how many days processing?)

Unless you do direct path loads or use the APPEND hit, all DML will be logged.
If you can save a lot of time and resources by doing so, then why not do it,
unless you have a very good reason not to?

Back it up afterwards and you will be able to recover.  

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist



--
http://www.freelists.org/webpage/oracle-l