Re: Tracking down NOLOGGING objects

From: Riyaj Shamsudeen <>
Date: Tue, 02 Sep 2008 10:33:27 -0500
Message-ID: <>

  Concrete way to see which objects are causing nologging operation is to dump the logfile during that timeframe, for layer 19.   If you know timestamp at which nologging redo was generated, then you can find log file or archivelog file encompassing few minutes before and after that timestamp (using v$archived_log or v$log_history) and dump logfile for layer 19 alone..alter system dump logfile 'filename' layer 19;

Here is test case with a new table..I will probably blog about this..:-)

conn me/me
create table t ( a number) nologging tablespace users; alter system switch logfile;
insert /*+ append */ into t select object_id from dba_objects where rownum <11;
alter system switch logfile;

REM Now dump last log file..
REM Script by Riyaj
set serveroutput on size 1000000
  v_sqltext varchar2(255);
 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 immediate v_sqltext;

REM this should create a trace file with following redo record, if there are any nologging redo generated..

REDO RECORD - Thread:1 RBA: 0x0001a9.00000006.00f0 LEN: 0x0028 VLD: 0x01 SCN: 0x0000.ce521612 SUBSCN: 1 09/02/2008 11:23:53 CHANGE #1 INVLD AFN:4 DBA:0x01017408 BLKS:0x0001 SCN:0x0000.ce521612 SEQ: 1 OP:19.2 REM Convert DBA above to file, block.. file is 4 (AFN above) Hex 17408 => 95240
accept h_file_id prompt ' Enter file_id ==>' accept h_block_id prompt ' Enter block_id==>' set verify off
column owner format A10
column segment_name format A20
column segment_type format A10
column hdrfile format 9999
column curfile format 9999
column curblk format 99999999
column hdrblock format 99999999
select b.owner ,b.segment_name, b.segment_type,header_file hdrfile,header_block hdrblock ,a.file_id curfile,a.block_id curblk from
(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 and
      block_id + blocks > &&h_block_id)  a,
      dba_segments  b
      where a.owner=b.owner  and
       a.segment_name=b.segment_name and
       a.segment_type= b.segment_type;

set verify on

REM Now, you can see that block is for table T..


---------- -------------------- ---------- ------- --------- ------- 
ME         T                    TABLE            4     95239       4     


The Pythian Group
Personal :

Andrey.Kriushin wrote:
> Hi Mark,
> Perfect point. Thanks!
> Assuming that the one, not very familiar with the current operations on
> the database and having no other input to identify NOLOGGING, still
> tries to figure it out... And has a standby (as many of us do)...
> -- Andrey
> Mark Brinsmead wrote:
>> But it /does/ track them at the /block/ level! If you have a physical
>> standby, at least. Blocks affected by non-logged operations will
>> manifest there as "corrupt" blocks. Something like an RMAN "BACKUP
>> VALIDATE" will be sufficient to find them; they will be reported both
>> in the RMAN output and a dictionary view (V$BLOCK_CORRUPTION maybe).
> --

Received on Tue Sep 02 2008 - 10:33:27 CDT

Original text of this message