Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

How to find objects creating nologging changes?

Posted by Riyaj Shamsudeen on September 11, 2008

In an Oracle-l thread, a question was raised: How to find objects creating nologging changes?

What is a ‘nologging’ change?

Redo logging mechanism plays critical role in media recovery. Media recovery relies on archivelog files generated to roll forward the database. Standby database or dataguard recovery also relies on archivelog files. It is possible to do DML changes with minimal logging, aka nologging changes or direct mode inserts. For example, insert /*+ append */ can be used to populate the rows in to a table without generating much redo. This can invalidate 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 can NOT be reapplied / recovered.

Internals of nologging changes

Since nologging is all about redo records, dumping redo log file or archivelog file is a concrete way to see what happens under the hood. Let’s consider an example to explain internals of nologging changes.

We will create a table, insert rows, and review redo records closely.

  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;
  alter system switch logfile;

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

 set serveroutput on size 1000000
   v_sqltext varchar2(255);
   -- Find name of latest but one log file.
   select 'alter system dump logfile '||chr(39)||
                member||chr(39) || ' layer 19 '
   into v_sqltext
   v$log  lg, v$logfile lgfile
   where = 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;

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

Above command generated a trace file in user_dump_dest directory and 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 layer is 19 for this change vector and 2 is the opcode. This vector also specifies that BLKS=1 and DBA as 1038832. Essentially this change vector says “Invalidate 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 generated, block formatted, populated with rows and written directly to disk. But, standby database and media recovery rely on redo records to replay these changes. When recovery code encounters above direct loader invalidate redo record, it throws a warning to alert log and simply mark that range of blocks as invalid. Any attempt to access those blocks will throw ORA-1578 block corruption error.

Just to reiterate, standby and media recovery are affected by nologging changes. But, 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 standby database, we avoid corruption. 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 need 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, procedure to find object_name causing nologging generation is:

    1. Find archivelog containing nologging changes. If timestamp is known, then v$archived_log can be queried to find archivelog file name.
    2. Dump archivelog file for layer 19: alter system dump logfile ‘filename’ layer 19;
    3. Locate 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 doc

14 Responses to “How to find objects creating nologging changes?”

  1. Kumar said

    Hi Riyaz:
    I have couple of questions.
    1. Why only a few blocks are marked as invalid when the entire object is created using the nologging. Should not all blocks become invalid. In your example, you created the object with no logging and also inserted records with direct mode. But since the object is created with nologging would not all the records you insert have the nologgin attribute and should not all the blocks be invalid even if you did not use the append hint (not just the ones that were inserted using the append hint).

    Also, you have this comment “Of course, if there is a standby database involved, then that tablespace need to be re-copied from production.”

    So you sync your standby ; identify the objects (and their tablespaces) that have nologging set and transport them using the transportable tablespaces?

    Thank you
    – Kumar

  2. Hi Kumar
    Thanks for reading my blog.
    1. So, the question is: If the insert statement does not specify /*+ append */ hint, does that still
    use direct mode [aka nologging ] inserts, if the table property is set to nologging?
    Let me clarify that with few examples.
    a. If the table is populated during table creation itself using CTAS format:
    “create table t1 nologging as select * from t2”
    then rows are populated using direct mode inserts.
    b. If the rows are inserted with out specifying /*+ append */ hint in to the table, then conventional
    mode inserts are performed. Nologging property at table level doesn’t matter in this case.
    This will use conventional mode inserts.
    c. If the insert statement specifies /*+ append */ hint AND if the table property is set to
    nologging mode then rows are populated using direct mode.
    d. If the table property is set to logging mode, then insert statement resorts to conventional mode
    regardless of whether append hint is specified or not.

    Of course, if force logging is set at table level, then inserts will use conventional mode and all
    changes will generate redo. This is why standby database or data guardis guaranteed only if
    primary has force logging property set.

    2. As to the question of your standby, tablespace(s) [ more accurately datafiles ] that suffered
    nologging changes and they must be recopied. v$datafile has fields such as unrecoverable_change# and unrecoverable_time and these fields can be used to find latest nologging changes. Just because
    a table has nologging attribute set does not mean that all changes to that table will use direct mode

    Better yet, force logging if standby needed to be golden.

    Thanks & Cheers

  3. Taral Desai said

    Hi Riyaj,

    I have 2 questions regarding this.

    1. Will delete with nologging will act as same.

    2. When i tried your example on 10.2.03 on solaris sparc 64 bit i got this
    REDO RECORD – Thread:1 RBA: 0x00008c.00000015.012c LEN: 0x0034 VLD: 0x01
    SCN: 0x090d.bb0f91a3 SUBSCN: 1 04/21/2009 16:04:24
    CHANGE #1 INVLD AFN:4 DBA:0x010002e0 BLKS:0x000d SCN:0x090d.bb0f91a3 SEQ: 1 OP:19.2
    Direct Loader invalidate block range redo entry

    REDO RECORD – Thread:1 RBA: 0x00008c.00000015.0160 LEN: 0x0034 VLD: 0x01
    SCN: 0x090d.bb0f91a3 SUBSCN: 1 04/21/2009 16:04:24
    CHANGE #1 INVLD AFN:4 DBA:0x010002ee BLKS:0x0002 SCN:0x090d.bb0f91a3 SEQ: 1 OP:19.2
    Direct Loader invalidate block range redo entry

    How do i find object_id in this

  4. Hi Taral
    Thanks for reading my blog.
    So, in this case, range of 13 blocks starting with DBA 010002e0 were invalidated in the first redo record.

    We can convert this to a file_id and block_id using the SQL below:

    undef dba
    dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('&&dba', 'XXXXXXXXX'))||','||
    dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&dba', 'XXXXXXXXX'))
    from dual
    Enter value for dba: 010002e0
    We can find object_name using following SQL:
    select  owner, segment_name, segment_type, file_id,block_id from dba_extents
    where file_id = &&h_file_id and
         block_id  &&h_block_id
    old   2: where file_id = &&h_file_id and
    new   2: where file_id = 4 and
    Enter value for h_block_id: 736
    old   3:      block_id <= &&h_block_id and
    new   3:      block_id  &&h_block_id
    new   4:   block_id + blocks > 736
    OWNER                          SEGMENT_NAME                     SEGMENT_TYPE          FILE_ID   BLOCK_ID
    ------------------------------ -------------------------------- ------------------ ---------- ----------
    SYS                            I_SQL_VERSION$_VERSION#          INDEX                       4        736
  5. Taral Desai said

    Hello Riyaj,

    Thanks for answer. Few more question please
    1. What about delete. Will delete with nologging will act as same.
    DELETE FROM t4 NOLOGGING where rownum <1000;
    How to find that delete is doing this in no logging mode. i.e. generating less redo

    The reason i am asking this is because we need to delete around 700GB data from one table which contains blob/clob. And i think this will going to take hell lot of time and can’t be achieve in one go. So delete some row then commit. We suggested CTAS and other methods but customer want to delete that the verdict. So, no choice.

    2. How do you find layers information. i.e layer 19 is used for specific task. Is there any list available.

  6. Hi Taral
    delete will produce redo. For the delete, rows are marked as deleted. But, undo change vectors will keep prior image of the row piece and will add these change vectors in to undo blocks. For delete statements, this modification to undo blocks is a substantial portion of redo size, if we do breakup of redo.

    But, by design, LOB columns that are stored out of line uses different type of undo mechanism– meaning they don’t modify undo blocks– LOB block versioning used. Delete modifies row piece and undo index block generating little bit of redo. So, if all these blob column values are stored out of line, then redo generation may not be as big as you would imagine, for deletes.

    I guess, my question back to you :-)How much of that 700GB is stored out of line? Have you tested for say 10000 rows and measured redo size? Also, what version of Software?

    For the list of layers, I had that list. I just can’t find it 😦 Google desktop has been removed from my laptop and I can’t seem to find anything anymore 😦


  7. Taral Desai said

    Thanks you very much Riyaj .

    We are using 10.2.3 on solaris sparc 64-bit.

    Table is created using “STORAGE IN ROW CHUNK 8192”. And when i query data(We have file_size column in table so from that.

    COUNT(*) ‘<4096’
    1 7777380 < 4096
    2 44993596 < 8192
    3 34133414 < 16384
    4 879905 32768

    Also, can you please explain i know we can get redo information from v$mystat. But confused about RAC. Whenever i query “select distinct sid from gv$mystat” i get different sid’s. So, how to measure redo generation in RAC.

    I am using

    SELECT s.sid, s.serial#, s.username,, ms.value FROM gv$statname sn, gv$mystat ms, gv$session s WHERE MS.SID = s.sid AND ms.statistic# = sn.statistic# ORDER BY 4;

  8. Taral
    If the lob column length exceeds ~4000 bytes, then lob will be stored out-of-line. So, in your case, 1% of rows are stored in line. For delete statement, LOBS stored out of lines tend to generate less redo. I would think that redo generation for delete will be actual rows, rather than lob column values. Of course, Test it..

    If you want to query redo information, then it is probably better to use one session for delete and another session to query redo size from first session. Following script can be used to query:

    1. To query current sid : select sid from v$mystat where rownum <2;
    2. From a different session, find redo size using following script.

    undefine sid
    accept sid prompt ‘Enter sid :’
    select, ses.value from
    v$sesstat ses , v$statname stat
    where stat.statistic#=ses.statistic# and
    and lower( like ‘redo size’
    Remember that redo size statistics is cumulative and so need to delete redo size from prior sample.

    To answer your question about your query, you are missing inst_id join..

    SELECT s.sid, s.serial#, s.username,, ms.value FROM gv$statname sn, gv$mystat ms, gv$session s
    WHERE MS.SID = s.sid AND ms.statistic# = sn.statistic#
    and sn.inst_id = ms.inst_id and s.inst_id=ms.inst_id
    and like ‘%redo%’
    ORDER BY 4;


  9. Taral Desai said

    Thanks You so very much Sir, I think i got my answer but will ask if some doubts.

  10. […] es ganz genau wissen will und des Englischen mächtig ist, erfährt in diesem Artikel von Riyaj Shamsudeen und dem dort angehängten Dokument viele Einzelheiten. Es ist anwendungsspezifisch zu bestimmen, […]

  11. Jim said


    If I have a table that is
    created with LOGGING,
    then it load some data,
    then change to NOLOGGING, (alter table … nologging…).
    then loading some more data that is nologging ,
    then change to LOGGING again. (alter table … logging..)

    Does it means in the same table, some blocks are marked as NOLOGGING and not recoverable ?

    When I take a hotbackup of the database and later on I want to do the point-in-time recovery, does oracle try to recover all blocks in the table, or just recover the blocks that is changed after the last hotbackup ? And if It happen to find some blocks are nologging during the recovery step, it will just bypass them ?

    After the point in time recovery I bring up the databaes and try to do full scan of the table, if I see “ORA-26040: Data block was loaded using the NOLOGGING option”, does it meas these blocks are the ‘bypass’ blocks during the point-in-time recovery ? All blocks prior the hotback are okay ?


    • Jim
      Thanks for reading my blog.
      Nologging pertains to redo log records, not datafile blocks. During nologging inserts, datafile blocks are formatted, populated with rows, and written directly to the disk. Oracle will generate an invalidation redo record marking range of blocks are invalid for the blocks written.
      This “marking” is in the redo records (meaning redo log files), not in the data files. This change matters, only if you have to do a recovery before performing another backup.
      Let me clarify this with two scenarios:

      Scenario #1:
      1. Backup was taken at 10AM including the tablespace ts1.
      2. At 10:30 AM You perform nologging inserts in to a table in the ts1 tablespace. So, Oracle will pre-format those blocks, populate rows and writes them directly to the disk. Let us say file 21, blocks 200-300 were loaded using this method. At 10:45AM the inserts completed.
      3. An invalidation redo was generated marking file 21, blocks 200-300 as invalid (aka corrupt).
      4. At 11:30 AM, somebody removed the datafile 21. So, you had to restore the data file from the 10AM backup and recover the datafile.
      5. In this case, after recovery, accessing the table loaded with nologging at 10:30AM will fail with corruption error. Why? redo records marked those blocks as invalid during recovery.

      Scenario #1:
      (1,2,3) are same Scenario #1.
      4. At 11:00 AM you took another hot backup of that tablespace.
      5. At 11:30 AM, somebody removed the datafile 21. So, you had to restore the data file from the 11AM backup and recover the datafile.
      6. In this case, after recovery, accessing the table loaded with nologging at 10:30AM is fine. No corruption. Since you took the hot backup of the datafile including the blocks loaded with direct mode and since there wasn’t any invalidation redo after the hot backup, these blocks are valid and NOT corrupt.

      So, actual blocks in the datafile are fine. Only when you have to do recovery and had to use invalidation redo for the recovery, blocks will be marked corrupt.

      This is why after a nologging insert, you must take a backup of that tablespace.

      Hope that clarifies!


  12. going the wholesale way…

    […]How to find objects creating nologging changes? « Oracle database internals by Riyaj[…]…

  13. […] media recovery to check for objects impacted by nologging operations. This is covered in depth here by Oracle Expert […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: