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

I will rerun my tests and post the results to the list.

If there is a flaw in my testing procedure someone here is sure to spot it.

Won't be able to do so until this afternoon however.

Jared

On 8/19/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> Jared, I'm confused about your email below - your quote from the manual is
> incomplete, the entire phrase (from
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_510a.htm#SQLRF01209)
> is:
> *"logging_clause*
>
> Specify whether the creation of the index will be logged (LOGGING) or not
> logged (NOLOGGING) in the redo log file. This setting also determines
> whether subsequent Direct Loader (SQL*Loader) and direct-path INSERToperations against the index are logged or not logged.
> LOGGING is the default."
> So, the manual is saying that the NO/LOGGING clause will control the
> creation of the index and also control direct-path INSERTs. If I understand
> correctly, you are saying your example shows that direct-path INSERTs *are*
> logged even if the index is created with NOLOGGING, so you are saying the
> manual is wrong - is that correct?
> Thanks,
> Brandon
>
> -----Original Message-----
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]*On Behalf Of *Jared Still
> *Sent:* Friday, August 19, 2005 1:25 PM
> *To:* cmarquez_at_collegeboard.org
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: ORA-1578...block corrupted...error is normal...a
> block...had a NOLOGGING...operation performed against
>
> The manual might help here.
>
> From the 'CREATE INDEX' entry:
>
> Specify whether the creation of the index will be logged (LOGGING) or not
> logged (NOLOGGING) in the redo log file.
>
> The creation of the index will not be logged. Subsequent inserts will be
> logged.
>
> It isn't too hard to test this out.
>
> Create a table nologging.
> Create an index nologging.
>
> Check your session redo size.
>
> select
> name.name name,
> stat.value
> from v$mystat stat, v$statname name
> where
> stat.statistic# = name.statistic#
> and name.name = 'redo size';
>
> Use 'insert /*+ append */' to create a lot of rows in the table.
>
> Check your redo size again.
>
> It will be clear that there is redo generated for the index.
> You can verify the amount of redo generated is not from the table
> by doing the same exercise wihout the index. The redo will
> be minimal.
>
> You can also verify this by the following procedure:
>
> create the table and index (no logging)
>
> check redo size
>
> do the mass insert
>
> backup the tablespace
>
> insert a new row
>
> restore the datafile
>
> see if the new row is in index and table
>
> HTH
>
> Jared
>
>
> On 8/19/05, Marquez, Chris <cmarquez_at_collegeboard.org > wrote:
> >
> > All,
> >
> > After talking to a co-DBA we believe we *did* have [create] nologging
> > operations within the redo logs applied to the recovered database.
> >
> > Also, I just tested out our database in constant recovery mode (poor
> > man's standby).
> > We have 33 indexes created *and* defined with nologging, *before* the
> > database was restored and recovered (applying logs) to our standby server.
> > It seems that we can select from indexes that were "CREATED" with NOLOGGING
> > and *still* are "DEFINED as LOGGING='NO". When we select using the index on
> > the database (in read only mode) we find the index and data are fine...no
> > errors.
> > We have applied two weeks worth of arch log (transactions) against these
> > indexes "DEFINED as LOGGING='NO".
> >
> > So now that statement or questions is that;
> > "CREATED" with NOLOGGING, must be entirely different than "DEFINED
> > [ALTER?] as LOGGING='NO" (dba_indexes).
> > Or rather NOLOGGING only has negative recovery impact on CREATED DDL?
> >
> > Chris Marquez
> > Oracle DBA
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org on behalf of Jared Still
> > Sent: Fri 8/19/2005 3: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
> >
> >
> >
> >
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>

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

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