Force logging and NOLOGGING clauses

Today we had an odd case whereby we could see a large number of indexes that were reporting they had been created with a NOLOGGING clause, however the DB is in FORCE LOGGING mode. Time for a quick test to see what was going on and whether we should actually be worried or not about our standby.

First test – create a table with NOLOGGING clause while FORCE LOGGING is on:

SQL> select force_logging from v$database;

FORCE_LOGGING
-----------------------
YES


SQL> create table kate_test (person int) nologging;

Table created.

SQL> select table_name, logging from dba_tables where table_name='KATE_TEST';

TABLE_NAME   LOG
-----------  ---
KATE_TEST    NO

 

Hmm. So Oracle reports it as not being logged. How can we check this? Redo log mining…

Looking for my table name in the latest redo log reveals the following:

nolog.png

So it looks like it was logged, even though it says it wasn’t. Let’s make sure of the behaviour when FORCE LOGGING is off to guarantee this isn’t a red herring.

SQL> select force_logging from v$database;

FORCE_LOGGING
-----------------------
NO


SQL> create table neil_test (person int) nologging;

Table created.

SQL> select table_name, logging from dba_tables where table_name='NEIL_TEST';

TABLE_NAME LOG
----------- ---
NEIL_TEST   NO

And a quick check of the redo logs:

neil.png

Bingo. Nothing there.

Summary

If you create any object when FORCE LOGGING is turned on it WILL write it to redo, but for some reason the LOGGING column will show as ‘NO’ on the object tables. Strange and unexpected behaviour but it does mean we can relax about our standby now.

Leave a Reply