Re: Tracking down NOLOGGING objects
Date: Mon, 1 Sep 2008 08:39:51 -0500
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.
On Sun, Aug 31, 2008 at 9:57 AM, Mark Brinsmead <pythianbrinsmead_at_gmail.com>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
> 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.