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@xxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx]*On Behalf Of *Jared Still > *Sent:* Friday, August 19, 2005 1:25 PM > *To:* cmarquez@xxxxxxxxxxxxxxxx > *Cc:* oracle-l@xxxxxxxxxxxxx > *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@xxxxxxxxxxxxxxxx > 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@xxxxxxxxxxxxx on behalf of Jared Still > > Sent: Fri 8/19/2005 3:31 PM > > To: joelgarry@xxxxxxxxxxxxxxx > > Cc: oracle-l@xxxxxxxxxxxxx > > Subject: Re: ORA-1578...block corrupted...error is normal...a > > block...had a NOLOGGING...operation performed against > > > > On 8/19/05, Joel Garry <joelgarry@xxxxxxxxxxxxxxx> 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