Re: Tracking down NOLOGGING objects: resending
Date: Tue, 02 Sep 2008 10:55:31 -0500
(First email bounced and resending again..)
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..:-)
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
select 'alter system dump logfile '||chr(39)||member||chr(39) || ' layer 19 '
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;
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
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 495237
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).
> Received on Tue Sep 02 2008 - 10:55:31 CDT