Easy conversion of Datablock address(DBA) to file# and block#

Home Forums DBA – Wiki Easy conversion of Datablock address(DBA) to file# and block#

This topic contains 1 reply, has 0 voices, and was last updated by  Amit Bansal 8 years, 1 month ago.

  • Author
    Posts
  • #1898

    Amit Bansal
    Keymaster

    I was getting following errors while using Dbverify on a file

    <br />
    &gt;dbv file=dc_users_01.dbf blocksize=8192<br />
    <br />
    DBVERIFY: Release 10.2.0.2.0 - Production on Thu Aug 27 11:09:04 2009<br />
    <br />
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.<br />
    <br />
    DBVERIFY - Verification starting : FILE = dc_users_01.dbf<br />
    <strong>Block Checking: DBA = 96495368, Block Type = KTB-managed data block<br />
    data header at 0x10020aa7c</strong><br />
    <strong>kdbchk: bad row offset slot 16 offs 58 fseo 1683 dtl 8168 bhs 104<br />
    Page 26376 failed with check code 6135</strong><br />
    <br />
    DBVERIFY - Verification complete<br />
    <br />
    Total Pages Examined         : 256000<br />
    Total Pages Processed (Data) : 25740<br />
    Total Pages Failing   (Data) : 1<br />
    Total Pages Processed (Index): 1<br />
    Total Pages Failing   (Index): 0<br />
    Total Pages Processed (Other): 664<br />
    Total Pages Processed (Seg)  : 0<br />
    Total Pages Failing   (Seg)  : 0<br />
    Total Pages Empty            : 229595<br />
    Total Pages Marked Corrupt   : 0<br />
    Total Pages Influx           : 0<br />
    Highest block SCN            : 3211169690 (1408.3211169690)<br />
    

    To find file# and block# I used dbms_utility package

    <br />
    select dbms_utility.data_block_address_file(&amp;&amp;rdba) RFN,<br />
    dbms_utility.data_block_address_block(&amp;&amp;rdba) BL<br />
    from dual;  2    3<br />
    Enter value for rdba: 96495368<br />
    old   1: select dbms_utility.data_block_address_file(&amp;&amp;rdba) RFN,<br />
    new   1: select dbms_utility.data_block_address_file(96495368) RFN,<br />
    old   2:        dbms_utility.data_block_address_block(&amp;&amp;rdba) BL<br />
    new   2:        dbms_utility.data_block_address_block(96495368) BL<br />
    <br />
    RFN         BL
    <hr class="bbcode_rule" />
    
    <hr class="bbcode_rule" />
    23      26376<br />
    

    On further analysis , found that the block number 26376 did not contain any object and maximum block used was 25225

    <br />
    SQL&gt; select max(block_id) from dba_extents where file_id=23;<br />
    <br />
    MAX(BLOCK_ID)
    <hr class="bbcode_rule" />
    25225<br />
    <br />
    select (25225*8192)/1024/1024 from dual;<br />
    <br />
    (25225*8192)/1024/1024
    <hr class="bbcode_rule" />
    197.070313<br />
    <br />
    SQL&gt; alter database datafile '/lmsdb/lms01/dc_users_axlmsdb_tblspc_01.dbf' resize 200m;<br />
    <br />
    Database altered.<br />
    

    Running dbv again, gave a clean output.

  • #2062

    amansharma
    Participant

    Good one! DBMs-Util is a good package.I was going to add this as well in the blog post but than just felt lazy and left it :-).

    Are the comments working on my blog now?

    Aman….

You must be logged in to reply to this topic.