Re: Tracking down NOLOGGING objects: resending

(First email bounced and resending again..)

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

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

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;

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

OWNER SEGMENT_NAME SEGMENT_TY HDRFILE HDRBLOCK CURFILE CURBLK

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

Cheers

Riyaj
The Pythian Group www.pythian.com
Personal : http://orainternals.wordpress.com

> Andrey.Kriushin wrote:

--
http://www.freelists.org/webpage/oracle-l