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

AskDBA.org Forum » DBA - Wiki

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

(2 posts)

Average Rating For This Topic:

Rate This Topic Yourself:

Started 11 months ago by Amit Bansal
Latest reply from amansharma

Tags:

Amit Bansal

Oracle DBA
Joined: Jun '08
Posts: 44

offline

Reputation:57

I was getting following errors while using Dbverify on a file

>dbv file=dc_users_01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.2.0 - Production on Thu Aug 27 11:09:04 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = dc_users_01.dbf
Block Checking: DBA = 96495368, Block Type = KTB-managed data block
data header at 0x10020aa7c
kdbchk: bad row offset slot 16 offs 58 fseo 1683 dtl 8168 bhs 104
Page 26376 failed with check code 6135

DBVERIFY - Verification complete

Total Pages Examined         : 256000
Total Pages Processed (Data) : 25740
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 1
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 664
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 229595
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3211169690 (1408.3211169690)

To find file# and block# I used dbms_utility package

select dbms_utility.data_block_address_file(&&rdba) RFN,
       dbms_utility.data_block_address_block(&&rdba) BL
from dual;  2    3
Enter value for rdba: 96495368
old   1: select dbms_utility.data_block_address_file(&&rdba) RFN,
new   1: select dbms_utility.data_block_address_file(96495368) RFN,
old   2:        dbms_utility.data_block_address_block(&&rdba) BL
new   2:        dbms_utility.data_block_address_block(96495368) BL

       RFN         BL
---------- ----------
        23      26376

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

SQL> select max(block_id) from dba_extents where file_id=23;

MAX(BLOCK_ID)
-------------
        25225

 select (25225*8192)/1024/1024 from dual;

(25225*8192)/1024/1024
----------------------
            197.070313

SQL> alter database datafile '/lmsdb/lms01/dc_users_axlmsdb_tblspc_01.dbf' resize 200m;

Database altered.

Running dbv again, gave a clean output.

Cheers
Amit
http://askdba.org/weblog/

Posted 11 months ago #

amansharma

mod
Joined: Aug '09
Posts: 1
offline
Reputation:1

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....

Posted 11 months ago #

RSS feed for this topic

Reply

You must log in to post.

AskDBA.org Forum

Subscribe to aSkDba.org Forum Posts

AskDBA.org Forum » DBA - Wiki

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

Reply