Re: Suppress writing Redo Log Data

From: David Fitzjarrell <oratune_at_aol.com>
Date: Tue, 14 Nov 2000 16:34:45 GMT
Message-ID: <8urpj5$7a9$1@nnrp1.deja.com>

In our last gripping episode ashish25_at_my-deja.com wrote:
> 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
>

The DML for that table was not logged in the redo log therefore if you rely on the redo logs to recover that table you are out of luck. If you take a cold backup of the database, or even an export of the database, you should be able to recover the table without any problems. You just can't restore it from the redo logs.

> 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.
>

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Nov 14 2000 - 10:34:45 CST