Extracting data directly from Oracle datafiles using Java.

I wrote a Java program today to scan Oracle datafiles for specific objects and then extract the data.  It first extracts the Block Header, Transaction Header, Data Header, Table Directory, Row Directory, Row Header, and finally the Row Data.

This allows me to extract any data I need direct from datafiles while bypassing audit and security at database level.  Following is example output running the program to scan for object 10 which is a clustered table containing USER$ and extracting data including usernames and password hashes.

Being able to directly access data in blocks allows more detailed analysis and auditing, so I should be able to  extract data for tables that have been truncated, extract deleted rows, or identify which blocks have changed since a specific SCN.

The source code can be found here :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html

The output still contains a bit of Hex data, so more work is needed to convert to proper char and date formats.

Block 209 Contains Object ID 10

Block Header start       :- 0

 Block Type 06-DATA          :- 06 

 Block Format                :- A2 

 Spare1                      :- 00 

 Spare2                      :- 00 

 Relative Block Address      :- 4194513

 SCN Base                    :- 954865

 SCN Wrap                    :- 0

 Sequence                    :- 01 

 Flag 01-NEW                 :- 06 

 CheckSum                    :- 10511

 Spare3                      :- 0

Transaction Header start  :- 20

 Type 01-DATA 02-INDEX       :- 01 

 Spare 1                     :- 00 

 Spare 2                     :- 00 

 Spare 3                     :- 00 

 Object ID                   :- 10

 Cleanout SCN Base           :- 954861

 Cleanout SCN Wrap           :- 0

 Spare 4                     :- 00 

 Spare 5                     :- 00 

 ITL Slots                   :- 2

 UNKNOWN Byte                :- 00 

 Flag 00-FREE                :- 03 

 ITL TX Feeelist Slot        :- 00 

 Next Block On Free List     :- 0

Data Header start        :- 92

 Flags                       :- 00 

 Number of Tables            :- 2

 Number of Rows              :- 43

 Offset to Freespace Start   :- -1

 Offset to Freespace End     :- 108

 Available Space             :- 4711

 Available Space after Commit:- 5519

Table Directory start    :-106

 Table                       :- 1

  Offset                      :- 0

  Number of Rows              :- 21

 Table                       :- 2

  Offset                      :- 21

  Number of Rows              :- 22

 Total Number of Rows        :- 43

Row Directory start      :-114

 Row Offset 1 :- 8074(92)

 Row Offset 2 :- 8005(92)

 Row Offset 3 :- 7829(92)

 Row Offset 4 :- 7740(92)

 Row Offset 5 :- 7650(92)

 Row Offset 6 :- 7559(92)

 Row Offset 7 :- 7473(92)

 Row Offset 8 :- 7294(92)

 Row Offset 9 :- 7192(92)

 Row Offset 10 :- 7089(92)

 Row Offset 11 :- 6987(92)

 Row Offset 12 :- 6809(92)

 Row Offset 13 :- 6709(92)

 Row Offset 14 :- 6609(92)

 Row Offset 15 :- 6504(92)

 Row Offset 16 :- 6412(92)

 Row Offset 17 :- 6220(92)

 Row Offset 18 :- 6116(92)

 Row Offset 19 :- 6021(92)

 Row Offset 20 :- 5842(92)

 Row Offset 21 :- 5733(92)

 Row Offset 22 :- 5951(92)

 Row Offset 23 :- 4882(92)

 Row Offset 24 :- 7762(92)

 Row Offset 25 :- 7672(92)

 Row Offset 26 :- 7581(92)

 Row Offset 27 :- 7495(92)

 Row Offset 28 :- 4711(92)

 Row Offset 29 :- 7214(92)

 Row Offset 30 :- 7111(92)

 Row Offset 31 :- 7009(92)

 Row Offset 32 :- 5391(92)

 Row Offset 33 :- 6731(92)

 Row Offset 34 :- 6631(92)

 Row Offset 35 :- 6526(92)

 Row Offset 36 :- 6434(92)

 Row Offset 37 :- 6242(92)

 Row Offset 38 :- 6138(92)

 Row Offset 39 :- 6043(92)

 Row Offset 40 :- 5864(92)

 Row Offset 41 :- 5755(92)

 Row Offset 42 :- 5647(92)

 Row Offset 43 :- 5562(92)

23 Row Header start  :-4974

 Flags :- 108 6C  Cluster Data

 Lock Status :- 00 

 Number of Columns :- 22

 Cluster Byte :- 01 

Column 1 Bytes 3 Data :- SYS

Column 2 Bytes 2 Data :- C1 02 

Column 3 Bytes 16 Data :- DCB748A5BC5390F2

Column 4 Bytes 1 Data :- 80 

Column 5 Bytes 2 Data :- C1 04 

Column 6 Bytes 7 Data :- xn03 1E 0B 08 7

Column 7 Bytes 7 Data :- xq02 13 0B 3'

Column 8 Bytes 7 Data :- xn03 1E 0C 07 9

Column 9 Bytes 7 Data :- xn03 1E 0C 07 9

Column 10 Bytes 1 Data :- 80 

Column 11 Bytes 255 Data :- NULL

Column 12 Bytes 2 Data :- C1 02 

Column 13 Bytes 255 Data :- NULL

Column 14 Bytes 255 Data :- NULL

Column 15 Bytes 1 Data :- 80 

Column 16 Bytes 1 Data :- 80 

Column 17 Bytes 22 Data :- DEFAULT_CONSUMER_GROUP

Column 18 Bytes 255 Data :- NULL

Column 19 Bytes 1 Data :- 80 

Column 20 Bytes 255 Data :- NULL

Column 21 Bytes 255 Data :- NULL

Column 22 Bytes 62 Data :-S:CDD630F4165A338BF851D4552897EB41EB6A9FCF7587B366260E0352A7C7

28 Row Header start  :-4803

 Flags :- 108 6C  Cluster Data

 Lock Status :- 02 

 Number of Columns :- 22

 Cluster Byte :- 05 

Column 1 Bytes 6 Data :- SYSTEM

Column 2 Bytes 2 Data :- C1 02 

Column 3 Bytes 16 Data :- EED9B65CCECDB2E9

Column 4 Bytes 1 Data :- 80 

Column 5 Bytes 2 Data :- C1 04 

Column 6 Bytes 7 Data :- xn03 1E 0B 08 8

Column 7 Bytes 7 Data :- xq02 13 0B 3'

Column 8 Bytes 7 Data :- xn03 1E 0C 07 9

Column 9 Bytes 7 Data :- xn03 1E 0C 07 9

Column 10 Bytes 1 Data :- 80 

Column 11 Bytes 255 Data :- NULL

Column 12 Bytes 2 Data :- C1 02 

Column 13 Bytes 255 Data :- NULL

Column 14 Bytes 255 Data :- NULL

Column 15 Bytes 1 Data :- 80 

Column 16 Bytes 1 Data :- 80 

Column 17 Bytes 22 Data :- DEFAULT_CONSUMER_GROUP

Column 18 Bytes 255 Data :- NULL

Column 19 Bytes 1 Data :- 80 

Column 20 Bytes 255 Data :- NULL

Column 21 Bytes 255 Data :- NULL

Column 22 Bytes 62 Data :- S:83FDD7C19ABAB64415514C28AD798DDAA3FE619B10B1AEA79E8463DC2566

If anyone wants more details on the Oracle block structure you can check :-

http://orafaq.com/papers/dissassembling_the_data_block.pdf

and 

http://www.v3rity.com/OracleForensicsDataBlock.pdf