Data block dump and RDBA conversion
Original link:
http://www.eygle.com/internal/how_to_dump_datablock.htm
Tuesday, 2004-08-31 17:51
Eygle
Many times we need to dump (DUMP) Oracle data blocks in further study to study their content, Oracle provides a good way, we will briefly explain the following example:
[Oracle @ Jumper Udump] $ SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.3.0 - Production on Tue Aug 31 17:01:27 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.3.0 - Production
With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.3.3.0 - PRODUCTION
SQL> Select RowID, Deptno, DName, Loc from Scott.dept;
Rowid Deptno DName Loc
------------------------------------------------ -----
Aaadz7aabaaagk6aaa 10 Accounting New York
Aaadz7aabaaagk6aab 20 Research Dallas
Aaadz7aabaaagk6aac 30 Sales Chicago
Aaadz7aabaaagk6aad 40 Operations Boston
SQL> SELECT FILE_ID, Block_ID, Blocks from DBA_EXTENTS WHERE Segment_Name = 'DEPT';
FILE_ID BLOCK_ID BLOCKS
---------- --------------------
1 25273 8
SQL> ALTER SYSTEM DUMP DATAFILE 1 block min 25273 block max 25274;
SYSTEM altered.
SQL>!
[Oracle @ Jumper Udump] $ LS -L
Total 4
-rw-r ----- 1 Oracle DBA 3142 AUG 31 17:04 HSJF_ORA_13674.TRC
[Oracle @ Jumper Udump] $ More Hsjf_ora_13674.TRC
/opt/oracle/admin/hsjf/udump/HSJF_ORA_13674.TRC
Oracle9i Enterprise Edition Release 9.2.0.3.3.0 - Production
With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.3.3.0 - PRODUCTION
Oracle_Home = /opt/oracle/product/9.2.0
SYSTEM NAME: Linux
Node name: jumper.hurray.com.cn
RELEASE: 2.4.18-14
Version: # 1 WED SEP 4 13:35:50 EDT 2002Machine: i686
Instance name: HSJF
Redo thread mounted by this instance: 1
Oracle Process Number: 9
UNIX Process PID: 13674, Image: Oracle@jumper.hurray.com.cn (TNS V1-V3)
*** 2004-08-31 17: 04: 27.820
*** session ID: (8.3523) 2004-08-31 17: 04: 27.819
Start Dump Data Blocks TSN: 0 File #: 1 minblk 25273 MaxBLK 25274
Buffer TSN: 0 RDBA: 0x004062B9 (1/25273)
SCN: 0x0000.0057C70D SEQ: 0x01 flg: 0x04 tail: 0xc70d1001
FRMT: 0x02 Chkval: 0x12e3 Type: 0x10 = DATA Segment Header - Unlimited
Extent Control HEADER
-------------------------------------------------- ---------------
Extent Header :: Spare1: 0 spare2: 0 #Extents: 1 #blocks: 7
Last Map 0x00000000 # Maps: 0 Offset: 4128
Highwater :: 0x004062bb ext #: 0 BLK #: 1 EXT SIZE: 7
#blocks in seg. HDR's FreeElists: 1
#blocks Below: 1
Mapblk 0x00000000 offset: 0
Unlocked
Map header :: Next 0x00000000 #EXTents: 1 Obj #: 13947 Flag: 0x40000000
Extent Map
-------------------------------------------------- ---------------
0x004062BA Length: 7
NFL = 1, NFB = 1 TYP = 1 nxf = 0 CCNT = 1
Seg Lst :: flg: used lhd: 0x004062ba ltl: 0x004062ba
Buffer TSN: 0 RDBA: 0x004062BA (1/25274)
SCN: 0x0000.0131909B SEQ: 0x07 flg: 0x04 tail: 0x909b0607
FRMT: 0x02 Chkval: 0xA8E7 TYPE: 0X06 = Trans Data
Block header dump: 0x004062ba
Object ID on block? Y
SEG / OBJ: 0x367B CSC: 0x00.131909A ITC: 2 FLG: o Typ: 1 - Data
FSL: 0 fnx: 0x0 Ver: 0x01
ITL XID UBA FLAG LCK SCN / FSC
0x01 0x0001.02a.000003f3 0x0080000b.0188.08 c --- 0 SCN 0x0000.0057C70E
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 FSC 0x0000.00000000Data_block_dump, Data HEADER AT 0XADB505C
================
TSIZ: 0x1fa0
HSIZ: 0x1a
PBL: 0x0ADB505C
BDBA: 0x004062BA
76543210
Flag = --------
NTAB = 1
nrow = 4
FRRE = -1
FSBO = 0x1a
FSEO = 0x1f44
AVSP = 0x1f2a
TOSP = 0x1f2a
0xE: PTI [0] nrow = 4 OFFS = 0
0x12: Pri [0] OFFS = 0x1f86
0x14: PRI [1] OFFS = 0x1f70
0x16: PRI [2] OFFS = 0x1f5c
0x18: PRI [3] OFFS = 0x1f44
Block_row_dump:
Tab 0, Row 0, @ 0x1f86
TL: 26 FB: --H-FL - lb: 0x0 Cc: 3
COL 0: [2] C1 0B
Col 1: [10] 41 43 43 4F 55 4E 54 49 4E 47
COL 2: [8] 4E 45 57 20 59 4F 52 4B
Tab 0, Row 1, @ 0x1f70
TL: 22 FB: --H-FL - LB: 0x0 CC: 3
COL 0: [2] C1 15
COL 1: [8] 52 45 53 45 41 52 43 48
COL 2: [6] 44 41 4C 4C 41 53
Tab 0, Row 2, @ 0x1f5c
TL: 20 fb: --h-fl - lb: 0x0 cc: 3
COL 0: [2] C1 1F
COL 1: [5] 53 41 4C 45 53
COL 2: [7] 43 48 49 43 41 47 4F
Tab 0, ROW 3, @ 0x1f44
TL: 24 FB: --H-FL - lb: 0x0 cc: 3
COL 0: [2] C1 29
COL 1: [10] 4F 50 45 52 41 54 49 4F 4e 53
COL 2: [6] 42 4F 53 54 4F 4E
END_OF_BLOCK_DUMP
End Dump Data Blocks TSN: 0 File #: 1 minblk 25273 Maxblk 25274
One question that many people often put forward is how RDBA conversion? RDBA: 0x004062BA (1/25274) We introduced this example. RDBA has three changes from Oracle6-> Oracle7-> Oracle8: In Oracle6, RDBA 6-bit 2-based number, that is, the data block can only have 2 ^ 6 = 64 data files (remove all 0 and all 1, actually only 62 files) in Oracle7, RDBA The file number is increased to 10 digits. In order to be backward compatible, take 4 digits as the high position of the file number from the high position of the Block number. This is no need to change from 6-> 7. The number of data files is expanded. 1022 (removed all 0 and all 1), in Oracle7, the RowID format is: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb.rrr.ffff In Oracle8, the file number is still represented by 10 bits, just no longer need to replace, in order to backward, and introduce The relative file number (RFILE #), so from Oracle7 to Oracle8, RowID still does not need to change. In Oracle8i, Oracle introduced DataObj #, RowID's format becomes: OooooOOFFFBBBBBSSS, Oracle is further set to table space through DataObj # further, Thus, the number of data files per table space can be in theory 1022 examples: in Oracle6:, for example: File 8, Block 56892
26 BLOCK == 56892
vv vvvvvvvv vvvvvvvv vvvvvvvv
00100000 00000000 1101110 00111100
^^^^^^
6-digit file number == 8
In Oracle7:
For example: File 255, Block 56892
11111100 11000000 11011110 00111100
F C C 0 D E 3 C
/ _____ // ___ // _______________________ /
| | | | |
| | Block = 0xDE3C = 56892
/ _____________
| /
V v
0011 111111 = 0xff = 255 - Note that the high and low positions are replaced to get the correct file #
In Oracle8:
For example: File 255, Block 56892
11111100 11000000 11011110 00111100
F C C 0 D E 3 C
/ _____ // ___ // _______________________ /
| | | | |
| | Block = 0xDE3C = 56892
/ _____________
| /
V v
0011 1111 0011 = 03f3 = 1011 - this is the relative file number
For examples in our test: RDBA: 0x004062BA (1/25274) is: 0000 0000 0100 0000 0110 0010 1011 1010 Top 10 in RFILE #: 0000 0000 01 = 1 After 22 bits Block #: 00 0000 0110 0010 1011 1010 = 25274
