Re: Database with NOLOGGING everywhere

Tom,

   The first part of your message was only half-right, although you have (pretty much) self-corrected in the quotation from the documentation.

   It is not only table creation that will be unlogged, but also any DIRECT PATH modification. The main cases of that are, as cited, INSERT /*+APPEND*/ and SQL*Loader with direct path. When working with LOBs, you will find that a number of other operations (simple inserts, and I think also updates) are also done by direct path, and can therefore also be unlogged. (Its a few years since I last researched the specifics of that, so I apologize for the vagueness.)

   If you want to do point-in-time recovery, it has been my experience that LOBs and NO-LOGGING do not mix. Not nicely, anyway. (Note, also, that unless otherwise specified, your LOB segments will inherit their default LOGGING mode from the TABLESPACE, not from the table definition. That is, if you have a tablespace in NO LOGGING mode, and create in it a table with the LOGGING OPTION, any LOB segments without an explicit storage clause will be created in NO LOGGING mode. At least, that was the behaviour in 9iR2.)

On Mon, Apr 7, 2008 at 6:10 AM, Mercadante, Thomas F (LABOR) < Thomas.Mercadante_at_labor.state.ny.us> wrote:

> Mike,
>
> (Sorry about the first "premature" send!)
>
> NOLOGGING is not really what you think it is. This attribute is *only*
> enforced when you create the table brand new. Once the table exists, all
> updates are logged. So whomever set this up is not really acomplishing what
> he/she thought they were doing.
>
> If you are not using Rman, you should be. There is nothing like a good
> backup to help one sleep at night.
>
> From the Docs:
> Purpose
>
> The *logging_clause* lets you specify whether creation of a database
> object will be logged in the redo log file (LOGGING) or not (NOLOGGING).
>
> This clause also specifies whether subsequent Direct Loader (SQL*Loader)
> and direct-path INSERT operations against the object are logged (LOGGING)
> or not logged (NOLOGGING).
>
> Tom
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Kline.Michael
> *Sent:* Sunday, April 06, 2008 2:09 PM
> *To:* undisclosed-recipients
> *Subject:* Database with NOLOGGING everywhere
>
> I've got a 1.08TB warehouse that is pretty much all "batch mode".
>
> Because of that "batching" they have almost 80-95% of the production
> tables set to NOLOGGING, including many at the tablespace level.
>
> We normally rely on shadow copies, but the database is archivelog.
>
> Given all the NOLOGGING, what could I possibly hope to "recover" if I ever
> had to use those archivelogs????
>
> The shadow copy is going to naturally be a COLD backup, I would think that
> would make the archivelogs practically useless.
>
> Mind you, I may be able to copy a datafile from the shadow copy, and then
> apply archive logs, but again, with all the NOLOGGING defined, what can I
> hope for in the recovery? I guess I can pick up the table creates and all,
> but the data would have to be all repopulated through the batch runs?
>
> I inherited this one.
>
> This is at 9.2.0.6, but when we do the next conversion, I think I'm going
> to push for a "more normal" archivelog + LOGGING configuration, RMAN backups
> and some shadow copy for other things that database has to do.
>
> Thoughts? Comments?
>
> Looking for someone who may have been caught in this situation and how you
> managed. That good old "Been there done that, ended up doing …" type
> thing. Been very fortunate thus far.
>
> Michael Kline
>
>
>
> LEGAL DISCLAIMER
> The information transmitted is intended solely for the individual or
> entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, retransmission, dissemination or other use
> of or taking action in reliance upon this information by persons or entities
> other than the intended recipient is prohibited. If you have received this
> email in error please contact the sender and delete the material from any
> computer.
>
> SunTrust and Seeing beyond money are federally registered service marks of
> SunTrust Banks, Inc.
> [ST:XCL]
>

-- 
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs

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