How to Find Objects Creating nologging Changes

In an Oracle-l thread, this question was raised: how can you find objects creating nologging changes?

First, what is a ‘nologging’ change?

The redo logging mechanism plays critical role in media recovery. Media recovery relies on archivelog files generated to roll the database forward. A standby database or dataguard recovery also relies on archivelog files. It is possible to do DML changes with minimal logging, i.e., nologging changes, also known as direct mode inserts. For example, insert /*+ append */ can be used to populate the rows into a table without generating much redo. This can invalidate the standby database and might trigger rebuilding some or all parts of the standby database.

nologging changes generates minimal redo, since the blocks are pre-formatted and written to disk directly. A redo record is generated, invalidating a range of affected blocks. This invalidation redo record size is far smaller, for e.g. hundreds of blocks can be invalidated using just a single redo record. Of course, recovery is severely affected as the changes performed with nologging operations cannot be reapplied/recovered.

Internals of nologging changes

Since nologging is all about redo records, dumping the redo log file or archivelog file is a concrete way to see what happens under the hood. Let’s consider an example to explain the internals of nologging changes. We will create a table, insert rows, and closely review the redo records.

create table t4 ( a number) nologging tablespace users;
REM switch log file so that we wil have smaller log files to dump.
alter system switch logfile;
REM Direct mode or nologging inserts
insert /*+ append */ into t4 select object_id from dba_objects;
commit;
alter system switch logfile;

The previous online redo log file contains nologging changes from table t4 above. We could dump that log file in its entirety, but instead, to limit trace file size, we will dump just a layer specific to direct mode changes alone. Layer 19 is for nologging changes. The following script will dump the last online redo log file for layer 19.

set serveroutput on size 1000000
declare
   v_sqltext varchar2(255);
begin
  -- Find name of latest but one log file.
  select 'alter system dump logfile '||chr(39)||
               member||chr(39) || ' layer 19 '
  into v_sqltext
  from
  v$log  lg, v$logfile lgfile
  where lg.group# = lgfile.group# and
  lg.sequence# =
      (select sequence#-1 from v$log where status='CURRENT' )
  and rownum <2;
  dbms_output.put_line ('Executing :'||v_sqltext);
  --Execute above SQL to dump log file and print it to see.
  execute immediate v_sqltext;
end;
/

alter system dump logfile 'D:\ORACLE\ORADATA\ORCL11G\REDO02.LOG' layer 19

This command generated a trace file in the user_dump_dest directory and a few redo records from that trace file, printed below:

...
REDO RECORD - Thread:1 RBA: 0x0000b0.0000000f.00d4 LEN: 0x0034 VLD: 0x01
SCN: 0x0000.00486397 SUBSCN:  1 09/03/2008 10:54:38
CHANGE #1 INVLD AFN:4 DBA:0x01038824 BLKS:0x0001 OBJ:72852 SCN:0x0000.00486397 SEQ:  1 OP:19.2
Direct Loader invalidate block range redo entry

REDO RECORD - Thread:1 RBA: 0x0000b0.00000014.0118 LEN: 0x0034 VLD: 0x01
SCN: 0x0000.0048639a SUBSCN:  1 09/03/2008 10:54:38
CHANGE #1 INVLD AFN:4 DBA:0x01038832 BLKS:0x0001 OBJ:72852 SCN:0x0000.0048639a SEQ:  1 OP:19.2
Direct Loader invalidate block range redo entry
...

Explanation of redo records

Let’s review following two lines.

CHANGE #1 INVLD AFN:4 DBA:0x01038832 BLKS:0x0001 OBJ:72852 SCN:0x0000.0048639a SEQ:  1 OP:19.2
Direct Loader invalidate block range redo entry

OP:19.2 indicates that the layer is 19 for this change vector and the opcode is 2. This vector also specifies that BLKS=1 and DBA as 1038832. Essentially, this change vector says “Invalidate the range of blocks starting at block DBA 0x01038832 for 1 block”. This could be for a range of blocks too.

For nologging changes, a block-range-invalidation redo is generated, block-formatted, populated with rows, and written directly to disk. But standby databases and media recovery rely on redo records to replay these changes. When recovery code encounters the above block-range-invalidation redo record, it throws a warning to the alert log and simply marks that range of blocks as invalid. Any attempt to access those blocks will throw an ORA-1578 block corruption error.

Just to reiterate, standby and media recovery are affected by nologging changes. But the current database is still fine, and there is no corruption in primary database. If we backup the primary database again or rebuild that tablespace in the standby database, we avoid corruption. The point is that, if there is a business need to have nologging changes, consider taking a backup immediately after nologging changes. Of course, if there is a standby database involved, then that tablespace needs to be re-copied from production.

Back to our problem

We need to find the object causing nologging changes. Field OBJ:72852 shows object_id in decimal. We can query dba_objects to find object_name.

select owner, object_name from dba_objects where
object_id=72852 or data_object_id=72852
SQL> /
OWNER                          OBJECT_NAM
------------------------------ ----------
CBQT                           T4

In essence, the procedure to find object_name causing nologging generation is:

1. Find the archivelog containing nologging changes. If the timestamp is known, then v$archived_log can be queried to find the archivelog filename.
2. Dump the archivelog file for layer 19: alter system dump logfile 'filename' layer 19;
3. Locate the redo record with opcode 19.2.
4. Query dba_objects for that object_id.

More information about redo internals can be found in Redo Internals and Tuning by Redo Reduction [PDF].

It is preferable to enable force logging in production databases to avoid nologging changes altogether: alter database enable force logging;.

Oracle version: 11.1.0.6, XP platform.