Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 17, 2021 - 7:56 am UTC

Last updated: November 22, 2021 - 2:07 am UTC

Version: 19.12

Viewed 1000+ times

You Asked

Hello team,

RMAN report unrecoverable is showing that we have NOLOGGING operations in the database. Is there a way to find out which query performed the NOLOGGING operation?

Thanks for your help.


and Connor said...

You cannot run a "command" as nologging, you can only set an object as nologging.

So check dba_indexes, dba_tables and if needed, the equivalent partition views to see if anything has NOLOGGING set.

If nothing has, it would mean that someone did:

- alter/create object as nologging
- perform an operation
- alter it to logging

In that instance, you'd probably need to do some auditing to discover that sequence of events. Here's a method to do that

https://connor-mcdonald.com/2019/04/24/grab-all-the-ddl/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 18, 2021 - 7:14 am UTC

Thank you for the answer. There are indeed nologging objects in this database. Do you know any cases where nologging objects are not avoidable or can be tolerated?
Connor McDonald
November 19, 2021 - 12:43 am UTC

There is nothing wrong with NOLOGGING as long as you understand the implications.

You've already seen with RMAN that it can tell you about such operations. In a nutshell, the reason NOLOGGING operations are best tracked carefully is that ideally you want to backup the impacted datafile(s) as quickly as possible after the NOLOGING operation has been done (to ensure you don't lose data in the event of a disaster).

For example, I've worked on a data warehouse system where all bulk load operations will done in nologging mode, with some automation that monitored v$datafile and the like to launch backups via RMAN.

My "default" position is always have logging until you can't :-)

"alter database force logging" is the easiest way to do that

A reader, November 19, 2021 - 3:39 pm UTC

Thank you very much for the answer!
Connor McDonald
November 22, 2021 - 2:07 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.