Sundaramurthy Blog

May 6, 2010

How to convert data block address to file number and block number

Filed under: Oracle Block,Oracle internals — sundar5 @ 10:33 pm

Because of a  host  crash,  I  had a corruption issue on my undo tablespace  in my database .
The alert log was showing the following error.

I wanted to find out the object that had caused the issue

SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
SMON: about to recover undo segment 212
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktbsdp1], [], [], [], [], [], [], []

Here is the steps
Note: some time alert log show object id also

1. Find out the segment in our case 212

Select usn,name from v$rollname where usn=212;
USN NAME

———- ——————————

212 _SYSSMU212$
2. segement Tabelspace was UNDOTBS3
3. alter system dump undo header “_SYSSMU212$” ;

********************************************************************************
Undo Segment:  _SYSSMU212$ (212)
********************************************************************************
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0      spare2: 0      #extents: 114    #blocks: 57359
last map  0x00000000  #maps: 0      offset: 4080
Highwater::  0x0301a93f  ext#: 112    blk#: 950    ext size: 1024
#blocks in seg. hdr’s freelists: 0
#blocks below: 0
mapblk  0x00000000  offset: 112
Unlocked
Map Header:: next  0x00000000  #extents: 114  obj#: 0      flag: 0x40000000
Extent Map
—————————————————————–
0x03000542  length: 7
0x030000d9  length: 8
0x03000c09  length: 128
0x03005a89  length: 128
0x03005189  length: 128

index  state cflags  wrap#    uel         scn            dba parent-xid    nub     stmt_num    cmt
————————————————————————————————
0x00    9    0x00  0x193ae  0x0015  0x08ff.296efb8e  0x03006e62  0x0000.000.00000000  0x00000001   0x00000000  1273026159
0x01    9    0x00  0x196fd  0x000f  0x08ff.296efb91  0x03006e62  0x0000.000.00000000  0x00000001   0x00000000  1273026159
0x29   10    0x90  0x19715  0x0071  0x08ff.296efae3 0x0300c7f8 0x0000.000.00000000  0x00005b3b   0x00000000  0

4. undo data block

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE( TO_NUMBER(‘0300c7f8‘, ‘XXXXXXXX’) ) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK( TO_NUMBER(‘0300c7f8‘, ‘XXXXXXXX’) ) BLOCK#
from dual;
2    3
FILE#     BLOCK#
———- ———-
12      51192

or we can use binary to finout the file and block number

0x0300c7f8

0=0000

3=0011

0=0000

0=0000

c=1100

7=0111

f=1111

8=1000

0000 0011 0000 0000 1100 0111 1111 1000

0000 0011 00   000000 11000111 1111 1000

0000 0011 00 =12

00 0000 1100 0111 1111 1000 = 51192

5. alter system dump datafile 12 block 51192;

* Rec #0x29 slt: 0x29  objn: 5462030(0x0053580e)  objd: 5462030 tblspc: 423(0x000001a7)
*       Layer:  11 (Row)   opc: 1   rci 0x28
undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0300c7f8.08a7.28
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0xee44d2d2  hdba: 0xee444e0c
itli: 2  ispac: 0  maxfr: 8098
tabn: 0 slot: 22(0x16) flag: 0x2c lock: 2 ckix: 0
ncol: 23 nnew: 10 size: 0
Vector content:
col  3: [ 5]  19 11 18 1f f
col  4: [ 9]  34 69 72 35 33 74 6f 72 79
col  5: [14]  3e 65 77 73 20 61 6e 64 20 4d 65 64 69 61
col  6: [ 5]  32 6c 6f 67 73
col  7: [19]  33 6f 6c 6c 61 72 6f 72 61 77 79 76 75 70 72 7c 7f 77 73
col  8: [ 9]  34 68 65 20 53 70 61 72 6b
col  9: [19]  54 79 73 78 71 72 64 20 53 74 61 35 36 36 31 33 38 35 72
col 10: [32]
54 4f 40 43 4c 45 64 40 40 49 43 40 48 45 4d 41 4e 4b 40 54 4f 40 4e 61 70
20 44 69 76 49 4e 65
col 11: *NULL*
col 12: *NULL*

7. select owner,object_name from dba_objects where object_id=5462030

OWNER                          OBJECT_NAME
—————————— ——————–
POC                            DIMTEST

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: