Re: nologging - on which object happened?

Andreas Kaltenrieder wrote:
> Hi all
>
> With "select file#, UNRECOVERABLE_CHANGE#, unrecoverable_time from v$datafile;",
> it's possible to see on which datafiles nologging-Operations happended. How
> can I find out, on which objects this occured?

You can't. At least, not by any simple query to a v$ or dba_ view, if that's what you want. v$datafile only keeps track of the most recent SCN and date at which a nologging operation occurred for a particular datafile. So if multiple nologging transactions occurred on a given object or within a given datafile, you'd only know about the most recent, and that only at a datafile level, not an object level.

However, during recovery, and assuming your datafile was restored from a backup prior to the nologging operation, you should see an error message in the alert log (exact message may be version dependent) noting that unrecoverable transactions affected block XYZ in datafile ABC leaving those blocks logically corrupt. Since you have block# and datafile# you could pin down the object involved and possibly take steps to recover the data (e.g. using import, etc.).

You may also be able to get fancy with some sort of auditing or trigger approach to generate a notification when nologging operations occur on specific objects, but that'd probably get messy quickly. You may even be able to use LogMiner - although the data won't be there, the statement that generated the nologging transaction might show up (but I've never bothered to look).

So the old caveat remains for nologging operations - only use them if you can afford to lose the data they contain, or take a full physical backup of the affected datafiles immediately afterwards (which still leaves you vulnerable during the time between the transaction and the successful completion of the backup). And they're usually a Very Bad Idea (tm) in a standby database environment.

Regards,
Sean Received on Wed Jan 08 2003 - 10:40:36 CST