RE: Will dump block make dirty block written to disk?

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Sun, 12 Mar 2006 23:14:48 +0100
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF3F5545@MSXVS04.trivadis.com>

Kamus

>But, can anyone prove if that block is dirty, will
>DUMP action cause the DBWn to write this block
>into disk or not?

"prove" is big word... I'll limit myself to show you that in my case the dirty block is not written to disk.

SQL> CREATE TABLE t (n NUMBER) TABLESPACE test_8k;

SQL> INSERT INTO t VALUES (42);

SQL> COMMIT; SQL> ALTER SYSTEM CHECKPOINT; SQL> INSERT INTO t VALUES (666);

SQL> SELECT n,

  2         dump(n,16) n_hex,
  3         dbms_rowid.rowid_relative_fno(rowid) rfile_nr,
  4         dbms_rowid.rowid_block_number(rowid) block_nr,
  5         dbms_rowid.rowid_row_number(rowid) row_nr

6 FROM t;

         N N_HEX                  RFILE_NR   BLOCK_NR     ROW_NR
---------- -------------------- ---------- ---------- ----------
        42 Typ=2 Len=2: c1,2b            9         10          0
       666 Typ=2 Len=3: c2,7,43          9         10          1

SQL> SELECT name FROM v$datafile WHERE rfile# = 9;

NAME

/u00/oradata/A1020/test_8k.dbf

SQL> ALTER SYSTEM DUMP DATAFILE '/u00/oradata/A1020/test_8k.dbf' BLOCK 10;

Now, to check the data in the file, you can either use a dump at OS level or BBED. Since BBED knows the format of the blocks, I'll use it... As you can see only a single row, the first one, is shown.

BBED> info

 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     9  /u00/oradata/A1020/test_8k.dbf                                    1280
    11  /u00/oradata/A1020/test_8k_assm.dbf                               1280
    15  /u00/oradata/A1020/undotbs2.dbf                                  12800

BBED> set dba 9,10
        DBA             0x0240000a (37748746 9,10)

BBED> x /*r rowdata
rowdata[0]                                  @8182

flag_at_8182: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock_at_8183: 0x01
cols_at_8184:    1

col 0[2] @8185: 0xc1 0x2b

tailchk                                     @8188

BBED-00210: no row at this offset

In the trace file I have the following data (notice that only one row, the first one, is available).

block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 2b
end_of_block_dump

Of course if I execute another "ALTER SYSTEM CHECKPOINT" I see the following output.

BBED> x /*r rowdata

rowdata[0]                                  @8175

flag_at_8175: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock_at_8176: 0x02
cols_at_8177:    1

col 0[3] @8178: 0xc2 0x07 0x43

rowdata[7]                                  @8182

flag_at_8182: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock_at_8183: 0x01
cols_at_8184:    1

col 0[2] @8185: 0xc1 0x2b

tailchk                                     @8188

BBED-00210: no row at this offset

HTH
Chris

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

Received on Sun Mar 12 2006 - 16:14:48 CST