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;
set verify on

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 95237 

Cheers

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


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). 

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








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

Other related posts: