Nologging Transactions

Owned by Jan Klinke
Last updated: Apr 22, 2024
4 min read

On this page we’ll clarify some facts about nologging transactions, how they affect your Standby database and how to fix the resulting corruption.

1. What is a nologging transaction

Every transaction done on primary database is written directly to the database datafiles. Together with the datafile change, the change vector of every transaction is then also written to the database online redo log files. Nologging transaction is a special type of transaction which completely bypasses writing to online redo part, only datafiles are changed. As a result, nologging transactions are never found in any redo or archived log.

In DR configurations, nologging transactions pose a risk, because nologging transaction are only written to primary database. Nologging transactions can’t be synchronized to standby database, because they are not part of any archivelog. As a result, when nologging transactions are performed on primary database, standby database will then contain inconsistent data (will be corrupted).

2. Detecting nologging transaction corruption

Dbvisit automatically detects nologging transactions (nologging transaction corruption) on your standby database during synchronization. This is an example of the warning message when nologging corruption is detected on your standby database:

image-20240318-182152.png
image-20240318-182208.png

If you observe such warning, you should verify the nologging corruption by running SQL directly in the database. On Standby database:

SQL> select file#, first_nonlogged_scn, to_char(first_nonlogged_time, 'YYYY-MM-DD:HH24:MI:SS') from v$datafile where first_nonlogged_scn > 0 /

On Primary database:

SQL> select file#, unrecoverable_change#, to_char(unrecoverable_time, 'YYYY-MM-DD:HH24:MI:SS') from v$datafile where unrecoverable_change# <> 0 /

Once you know which datafile contains the nologging corruption, you can use following command on standby (valid only for Oracle 12.2 onwards):

RMAN> validate datafile 12 nonlogged block; RMAN> select file#,block#, blocks ,object# from v$nonlogged_block;

This should give you and idea about nologging object id. You can then run on your primary:

It is not always possible to determine the corrupted object id

Once you know which objects are corrupted on your standby database, you can decide to fix the corruption or ignore it (for example when the corrupted objects are indexes which can be rebuilt in case standby database is activated).

You should never attempt switchover with nollogging corruption detected in your standby database as it may result in ORA-26040: Data block was loaded using the NOLOGGING option and in worst case scenario you can end up with two corrupted databases (primary and standby database will contain different data).

False Positive Nologging transactions

There are cases when nologging transactions have impossible date or SCN. For example date in the future:

or date way back in the past:

These false positive cases are typical for 19c PDB$SEED datafiles.

In such cases, ignore the contents of v$datafile and instead verify the non-logging corruption only via v$nonlogged_block:

Or completely ignore the warnings. For example if nologging transaction has date of 2016 and the standby database was created from the scratch in 2024, it’s impossible to have any such nologging transactions on standby. If you are not sure, contact Dbvisit support.

3. Fixing nologging transaction corruption

Once you detect nologging transactions, you must ensure that no nologging transactions can happen on your primary database anymore. This is done by setting your primary database into force logging mode:

Restart is not required. We recommend to monitor closely archivelog generation rate afterwards, as setting the force logging mode can generate extra amount of archivelogs.

Note that anybody with DBA privileges can easily change the force logging mode afterwards without your knowledge - for example application user with DBA rights. You should monitor database alert log to ensure force logging mode is preserved on your production database.

After setting the primary database to force logging mode, you can fix the nologging transactions. This can be done in several ways:

  • Running RMAN incremental backup SYNC (synchronize standby):

Oracle: Synchronize standby

  • Refreshing affected datafile:

Miscellaneous Oracle Functions | 5.4 Refresh One Data File

  • In cases when nologging transactions are wide-spread you could even decide to recreate standby database from the scratch

Once the procedure is completed, run again following select on standby:

This select should return no rows.