Re: Tracking down NOLOGGING objects

  • From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
  • To: Andrey.Kriushin <Andrey.Kriushin@xxxxxxxx>
  • Date: Mon, 1 Sep 2008 21:13:31 -0600

I'm not sure whether Oracle tracks NOLOGGING operations at the segment
level.  I've never really bothered to look.

But it *does* track them at the *block* level!  If you have a physical
standby, at least.  Blocks affected by non-logged operations will manifest
there as "corrupt" blocks.  Something like an RMAN "BACKUP VALIDATE" will be
sufficient to find them; they will be reported both in the RMAN output and a
dictionary view (V$BLOCK_CORRUPTION maybe).  From corrupt blocks it is a
short but tedious journey to segments.  Assuming the dictionary view doesn't
provide you with the segment name, that is.  (Sorry -- no documentation on
hand here -- just an aging memory.)

I know that this is not quite what Finn has been thinking of, but it might
need his needs.

On Mon, Sep 1, 2008 at 5:58 PM, Andrey.Kriushin <Andrey.Kriushin@xxxxxxxx>wrote:

> Hi Finn,
> Mark's comment on feasubility is complete. Nothing to add.
> However for pure theoretical investigation... At least we know now, that
> Oracle RDBMS doesn't keep a track of NOLOGGING operations at the segment
> level. Does it?
> I'd look at the definition of GV$SEGMENT_STATISTICS and x$ksolsfts. This
> x$-table keeps the runtime statistics (FTS_STATNAM), and if you see
> 'physical writes direct' there, it might be (or not - in case of PQO) an
> indication of direct load, probably with the NOLOGGING.
> BTW, it would be nice to see the version of the product (RDBMS) in your
> future posts.
> HTH,
> -- Andrey
> ...

-- Mark Brinsmead
Senior DBA,
The Pythian Group

Other related posts: