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

  • This topic is empty.
Viewing 1 reply thread
  • 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….

Viewing 1 reply thread
  • You must be logged in to reply to this topic.