Reading Data From Oracle Data Files (without Connecting Database)

I attended the BGOUG Spring Conference last week as a speaker. While in BGOUG Conference, I had a chance to listen Kamil Stawiarski’s session about hacking Oracle. Kamil spoke about potential security issues of high level privileges such as create any index and create directory. At the end of the session, he also showed a tool written by himself. The tool (I do not remember its name right now), reads a datafile, searches for object ID (of a table) and parse and list the rows of the table.

I am aware that Oracle Support has a similar utility, and as I heard it’s called “DUL” (Data UnLoader). DUL is used to export data from data files in case there’s no possibility to open the database because of corruption. So the concept wasn’t new to me, but seeing the tool in action, made me want to write my own tool which is capable of extracting data from data files.

Yesterday, when I’m back home from Bulgaria, I have started to develop my own tool in “C” language. Why did I pick “C”? Because I though it would be easier to handle data block structure using “C” pointers. After about 5 hours, I saw that I forgot “C language” at all. I had to check almost everything. Funny thing is, although I forgot “C”, I still managed to develop a working tool using “C”, thanks to Internet and stackoverflow.com ­čÖé

Anyway, the tool still needs lots of improvements and it’s far from useable state but it helped me to improve my knowledge about how Oracle stores data internally. So I’m very happy to start developing it, and I’ll keep developing it as a side project.

extractor

Here you can see the output of my tool (I call it “extractor”). On the left side, you see the data from HR.DEPARTMENTS table, and on the right side, my tool shows the same data, reading directly from the data file containing the table. For now, it can only handle varchars and small numerics. I have to say that “numeric values” are stored in a complex(!?) structure, I need to work on it to handle all numerics (including negative ones).

Will it be a open source project? Oh no! It’s totally spaghetti code! On the other hand, I’ll share my findings about how data is stored in data files in my future blog posts.

Addition: I got a question about how I figure out the structure of data files. I do not have access to any official documentation about internal structures of Oracle. I use “alter system dump datafile block” command to get dump of block, it contains valuable information, then I compare that information with real block content using a hex editor (in fact, I added DBF file to XCode project so I can open it as HEX). I also use DUMP() command, so I don’t need to insert all possible variations to disks, I can see the corresponding byte array of a value. So basically, the information I discover may be totally wrong but I do my best ­čÖé

Please share
  • 1
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

12 Comments

  1. fernando silva

    Hi!

    And an Insert tool?
    Where you get information about datafile structures? trial and error?

    thanks
    fernando

    • Gokhan Atil

      Hi Fernando, Inserting? An interesting idea. I may try it! I added an explanation about how I get information about datafile structure. Thanks for reminding it!

  2. Your blog post reminds us ┬áDBAs and sysadmins that we must take extra care as to who has access to Oracle’s datafiles at the OS level — if data confidentiality means anything to us!

  3. Tengilimo─člu

    ├çok faydal─▒ bi payla┼č─▒m olmu┼č te┼čekk├╝rler. Ben de yap─▒cam ­čÖé Tamamlad─▒─č─▒n─▒zda devam─▒n─▒ da yazarsan─▒z ├žok sevinirim.

  4. Johanna

    Excellent┬áarticle┬áGokhan …!!!

    Just now I need to recover the data from some datafiles… but our database┬áis corrupted ,┬áwe lost the system datafile and our last backup is corrupted too !!! ­čÖü

    I don’t know how do it, and I don’t know if Oracle Support will help me, I never listen about “Data UnLoader” (DUL), what can I do??

    • Gokhan Atil

      Johanna, please contact Oracle support. They should help you.

  5. YANG KI

    Hi, Gokhan

    I happened to land your blog page while I am searching the problem you were solving.
    Is there any update after this?
    I really really interested in this topic.

    Thanks
    Yang

Leave Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.