Some time ago I wrote a simple tool to learn about Oracle data block internals – ODBV.
The series of articles can be found here: http://blog.ora-600.pl/?s=odbv&submit= and the github repo is here: https://github.com/ora600pl/odbv
This is not a production tool but during the last session in Birmingham at UKOUG_TECH17 – where I was doing a presentation using this tool – I came to the conclusion that with a little bit of work it could be used to trace ghost data in a database.
What is ghost data? This is very simple – each time we delete something or truncate or move, Oracle database is not removing data from our datafile – the blocks are "marked" for reuse and are not associated with any logical object in a database, but our data is still there.
These days we have to think about security a lot – that’s why when you move a table to encrypted tablespace you might want to be sure, that you leave no trace of important data behind.
I propose the v2 of ODBV (written in Python3) – this is a demo version and it requires a lot to be actually useful, but for now, it works like this:
ora-600:odbv2 inter$ python3.6 odbv2.py ODBV v2 by Kamil Stawiarski (@ora600pl | www.ora-600.pl) ODBV v2 requires cx_Oralce - you pip to install it Enter interactive mode: $ python3 odbv2.py -i Or do a batch processing: $ python3 -f file_with_path_to_dbfs -c user/pass@ip:port/service -o file.html Usage for interactive mode: to add a datafile for parsing and visualizing: ODBV> add file <<path to a datafile>> to generate html visualization report: ODBV> make html file_name.html to connect to Oracle for dictionary data: get dict user/password@ip:port/service Default block size is 8192 - to change it to N: ODBV> set blocksize N
As you can see you can use this tool in interactive or batch mode. This tool requires a cx_Oralce package for Python3 to connect to Oracle database – it is necessary to get DBA_OBJECTS contents to map blocks to existing objects and show the rest of the blocks that contents TABLE DATA or INDEX DATA as ghost blocks.
So let’s check how it works.
First of all let’s create a secured tablespace, encrypted with AES256:
SQL> administer key management create keystore '/u01/wallet' identified by "Dupiszcze"; Zmieniono magazyn kluczy. SQL> administer key management set keystore open identified by "Dupiszcze"; Zmieniono magazyn kluczy. SQL> administer key management set key identified by "Dupiszcze" with backup; Zmieniono magazyn kluczy. SQL> create tablespace secure_data 2 datafile size 1m 3 autoextend on next 1m 4 maxsize 1g 5 encryption using 'AES256' 6 default storage (encrypt); Utworzono przestrzen tabel.
I have 2 tables in my tablespace TBS_DUPA which is not encrypted:
SQL> select table_name 2 from dba_tables 3 where tablespace_name='TBS_DUPA'; TABLE_NAME -------------------------------------------------------------------------------- EMPLOYEES_SEC EMPLOYEES_TMP2
Now let’s move table EMPLOYEES_SEC to a new, highly secure tablespace 🙂
SQL> alter table hr.EMPLOYEES_SEC move tablespace secure_data; Zmieniono tabele.
Now I’ll prepare a file for batch processing with ODBVv2:
[oracle@rokoko ~]$ echo "/u01/app/oracle/oradata/DUPA12C/datafile/o1_mf_tbs_dupa_f34nwbwh_.dbf" > listfile.odbv [oracle@rokoko ~]$ echo "/u01/app/oracle/oradata/DUPA12C/datafile/o1_mf_secure_d_f3qgqznm_.dbf" >> listfile.odbv
After removing unnecessary data I have a list of datafiles for my tool to process.
Now let’s start a tool:
[oracle@rokoko ~]$ python3.6 odbv2.py -f listfile.odbv -c system/oracle@localhost:1521/dupa12c -o map.html [oracle@rokoko ~]$ ls -al map.html -rw-r--r--. 1 oracle oinstall 534540 12-21 14:58 map.html
The output is an HTML file which you can check out here: map.html
As you can see in a regular tablespace you can find a map of database blocks with details about existing and deleted rows – ghost data blocks are marked black because no entry in a dictionary could be found.
Blocks in a secure tablespace are black because they are encrypted so I can’t match them with anything without a wallet and encryption key.
Next step will be to create a pattern recognition to guess which ghost blocks, belonged to which table previously.
As you can see this is just a demo and it needs a lot of work but I think you got the idea 😉
ODBV v2 can be found in here: https://github.com/ora600pl/odbv