Re: Tracking down NOLOGGING objects

  • From: "Finn Jorgensen" <finn.oracledba@xxxxxxxxx>
  • To: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
  • Date: Mon, 1 Sep 2008 08:39:51 -0500

Mark,

I appreciate your input and you are right force logging will fix the issue
and yes standby databases prompted this effort in the first place.

The reason for my looking for the objects is that I wrote a monitor to check
for datafiles that had recent nologging activity and then list the objects
that were not logged. This particular database, as can be seen, reports no
objects yet there's nologging activity. I want to be able to go back to the
app/dev guys and ask them if it's on purpose these objects are set to
nologging. It's possible I break (considerably slow down) some load process
if I just indiscriminently turn on force logging.

Looks like I'll have to test the sql*loader with unrecoverable/direct turned
on.

Finn

On Sun, Aug 31, 2008 at 9:57 AM, Mark Brinsmead
<pythianbrinsmead@xxxxxxxxx>wrote:

> Why bother to track down objects with NOLOGGING?  In the long run, it will
> have little affect, as this is only a default anyway -- you can also specify
> NOLOGGING with SQL*Loader and certain DML statements.
>
> If you want to be certain that this is not happening, just put your
> database in FORCE_LOGGING mode.  (ALTER DATABASE FORCE LOGGING if I recall
> correctly.)
>
> Your users than then request/specify unlogged operations until they are
> blue in the face (almost) all will be logged anyway.  (I haven't looked
> closely, though, at how FORCE_LOGGING interacts with DIRECT-PATH SQL-Loader.
> operations; I would expect, however, that they would either fail or -- more
> likely -- be silent changed to convertional path.)
>
> One of my all-time favorites is the NOLOGGING LOB segment.  It is
> (realitively) easy to unintentionally create LOB segments with NOLOGGING,
> and a number of DML operations (simple INSERTS and UPDATES, as I recall)
> that are not eligible for NOLOGGING operations on other segments 
> *are*unlogged on LOB segments.  Needless to say, this can be unhealthy for 
> your
> standby databases (which is doubtless why you are checking to begin with).
>
> Check out FORCE_LOGGING.  It ought to meet your needs.
>
>
>

Other related posts: