(First email bounced and resending again..) AndreyConcrete 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); beginselect '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:53CHANGE #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 99999999select 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 bothin the RMAN output and a dictionary view (V$BLOCK_CORRUPTION maybe).