Re: Suppress writing Redo Log Data

I think this has been answered by now, but just in case not...

If a table is created with NOLOGGING set, then the DDL statement is not recorded in the redo logs, and none of the direct load inserts will be recorded either. If, having finished that process, you were immediately to do an 'update just_created_table set just_inserted_record to new_value where...' then that update WOULD be in the redo logs.

If you now have a failure of the database, and have to apply redo to recover, you're going to be in deep do-do, because the update will be replayed -it's just that it will be updating something that doesn't exist. The recovery operation is going to be fun, I think.

However, as you rightly point out, if after doing the create table statement (not logged) you took a backup (it doesn't have to be cold!!) then the new table will be in the backup 'image', and hence would be 'inside' the data files you restored to deal with the subsequent failure. Since all subsdequent 'normal' DML on that table would have been logged, the table is thus fully recoverable, since the redo stream can be applied to bring it right up-to-date.

Your big table is thus probably safe -provided you remember never to load data into it with SQL*Loader again (which as the documentation makes clear would revert to being not logged) -or do any of those other commands that were mentioned in my original post as genuinuely being not logged.

Two simple rules: one, if a table is made nologging (either with a create...as or alter statement), I would thoroughly recommend remembering to changing it back as soon as the initial load is completed. The onus should be on you to remember to *disable* logging if you do future loads; it shouldn't be left there as a default. Second. If you change the logging status of a table from logging to nologging, in order to more speedily carry out some load or other, always remember take a backup afterwards.

And as I said, the backup does not need to be cold -even an export with rows=y would do.

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------


<ashish25_at_my-deja.com> wrote in message news:8urp3t$718$1_at_nnrp1.deja.com...


> Hi Howard,

>    Even I agree with you that all conventional insert,update,delete are

> logged in the datadictionary.So if I create a table as create table as

> select unrecoverable there will be no logs generated.After that I can

> take a cold backup.All the dml statements after that will generate redo

> logs.So if I want to recover that table I will be able to.

>    Please clarify further??.I have a big table created as

> unrecoverable??

> Thanks,

> Ashish

>

>

> In article <3a1161f5_at_news.iprimus.com.au>,

>   "Howard J. Rogers" <howardjr_at_www.com> wrote:

> > Hi David,

> >

> > I think you may have the cart and the horse round the wrong way.

 NOLOGGING


> > doesn't suppress redo for normal inserts, updates and deletes, but

 only for


> > peculiar DML done under certain circumstances (eg, create table

 newemp as


> > select * from oldemp, SQL*Loader in direct path and so on).  Various

 DDL can


> > also be suppressed (such as create indexes, and indeed, create table).

> >

> > I only mention it because the doco for 8.1.5 states (as it has done

 since at


> > least 8.0.5):

> >

> > Quote ON:::::

> > Although you can set the NOLOGGING attribute for a table, partition,

 index,


> > or tablespace, no-logging mode does not apply to every operation

 performed


> > on the schema object for which you set the NOLOGGING attribute. Only

 the


> > following operations can make use of no-logging mode:

> >

> > direct load (SQL*Loader)

> > direct-load INSERT

> > CREATE TABLE ... AS SELECT

> > CREATE INDEX

> > ALTER TABLE ... MOVE PARTITION

> > ALTER TABLE ... SPLIT PARTITION

> > ALTER INDEX ... SPLIT PARTITION

> > ALTER INDEX ... REBUILD

> > ALTER INDEX ... REBUILD PARTITION

> > INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored

 out of


> > line

> > QUOTE OFF:::::::::::::::::::::::

> >

> > Regards

> > HJR

> > --

> > ----------------------------------------------------------------------

 -----


> > Opinions expressed are my own, and not those of Oracle Corporation

> > Oracle DBA Resources:

 http://www.geocities.com/howardjr2000


> > ----------------------------------------------------------------------

 -----


> >

> > "David Fitzjarrell" <oratune_at_aol.com> wrote in message

> > news:8urlvs$3s1$1_at_nnrp1.deja.com...

> > > In our last gripping episode insued_at_my-deja.com wrote:

> > > > Hello,

> > > >

> > > > Is it possible to tell Oracle *not* to write redo log information

 for
 certain


> > > > insert and update statements ? There is no need for the modified

 data
 to be


> > > > recovered after DB crash or DB shutdown, so the redo data is not

 really


> > > > necessary.

> > > >

> > > > I appreciate any help.

> > > >

> > > > Thanks,

> > > > Thomas Staudenmaier

> > > >

> > > > Sent via Deja.com http://www.deja.com/

> > > > Before you buy.

> > > >

> > >

> > > Not on a statement basis.  There is an option to CREATE TABLE/ALTER

> > > TABLE (also applies to CREATE/ALTER INDEX statements) that will

 prevent


> > > most redo log entries (inserts, deletes, updates) from being written

> > > but it is at the table/index level.  INVALID blocks and dictionary

> > > changes (add table columns, alter column storage parameters, drop

 table


> > > columns [in 8i]) will still be written to the redo logs but garden

> > > variety DML will not.

> > >

> > > Check the Oracle documentation for more on the NOLOGGING option for

> > > tables and indexes.

> > >

> > > --

> > > David Fitzjarrell

> > > Oracle Certified DBA

> > >

> > >

> > > Sent via Deja.com http://www.deja.com/

> > > Before you buy.

> >

> >

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.