What if you could directly read and manipulate data at the block level? Oracle provides such a tool to do exactly that, but you have to build it yourself. The Block Browser and Editor tool, or bbed for short, is your ticket into the contents of data blocks within an Oracle database. What can you do with bbed? You can:
- Change data
- Recover damaged or deleted data
- Alter a file header
- Corrupt and uncorrupt a block
The ability to change data is what it sounds like. You can change any data anywhere. What does this imply? It also means you can change passwords any password. For example, you can change the password for sys.
Deleted a row and need to recover it? What happens to data when you or a user deletes it? Specifically, does the data really go away, or does something else take place? The answer is that something else takes place. Oracle marks the row (or rows) as deleted and makes the space available for use in the future. Using files in DOS, as an example, when a file was deleted, the first character of the file name was changed and the file became hidden to normal dir listings. Recovery tools could be used to show deleted files; youre only real work was to figure out what the missing first character was. Recovering data in Oracle using bbed is roughly the same thing you just have to find where the deleted row lives and reset some flags to make the row active again (assuming the row has not been overwritten yet).
On a larger scale, the same type of recovery can be done using data files. By setting values inside the file header (the file header block), you can make an older file become part of the current database.
The bbed utility also gives you the power to corrupt and uncorrupt a block (reset the corrupt block marker). Use of bbed for this purpose, although interesting, is not practical in that there are better (i.e., more established and approved) ways of repairing corrupt blocks. However, if you want to corrupt a block and test out your RMAN skills, this would be a fairly quick way to set up that lab environment.
All of the above can be done without having access to a database in terms of being logged in or having an active instance running (except for the RMAN recovery). In other words, if someone has access to bbed and access to your datafiles, that person has access to everything in your database. Everything. If that doesnt convince you to safeguard your Oracle datafiles from unauthorized users, what will?
Where and how do you get bbed? In UNIX, Oracle gives you the pieces needed to create the tool. You dont get bbed as a live or active executable as like what you get with exp or sqlplus. In older versions of Oracle on Windows, the executable was installed ready for use, but this is no longer the case. It didnt even have to be the RDBMS installation to get BBED.EXE (how it is named on Windows). Using an Oracle8i client installation, BBED.EXE is installed in $ORACLE_HOME/bin by default.
In a 32-bit installation on UNIX (refers to all *NIX variants), look for two object files in $ORACLE_HOME/rdbms/lib: sbbdpt.o and ssbbded.o. In a 64-bit installation, the files will be in the lib32 directory.
[oracle] ls -la *bb*.o -rw-r--r-- 1 oracle dba 1160 Nov 18 2003 sbbdpt.o -rw-r--r-- 1 oracle dba 848 Nov 18 2003 ssbbded.o
To create or make the executable, use the make command as shown.
[oracle] make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
Sample output from the make command is shown below.
Linking BBED utility (bbed) rm -f /u001/app/oracle/ora904/rdbms/lib/bbed gcc -o /u001/app/oracle/ora904/rdbms/lib/bbed -L/u001/app/oracle/ora904/rdbms/lib/ -L/u001/app/oracle/ora904/lib/ /u001/app/oracle/ora904/lib/s0main.o /u001/app/oracle/ora904/rdbms/lib/ssbbded.o /u001/app/oracle/ora904/rdbms/lib/sbbdpt.o `cat /u001/app/oracle/ora904/lib/ldflags` -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 - <some lines removed> lcommon9 -lgeneric9 -ltrace9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lclient9 -lvsn9 -lwtc9 -lcommon9 -lgeneric9 -lnls9 -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 `cat /u001/app/oracle/ora904/lib/sysliblist` -Wl,-rpath,/u001/app/oracle/ora904/lib:/lib:/usr/lib -lm `cat /u001/app/oracle/ora904/lib/sysliblist` -ldl -lm
Sample output from a 10.2.0.1 installation is shown below. The reason for showing it is to illustrate the difference between Oracle versions (9 and 10) in the flags. Put another way, there is no guarantee that you can take bbed from one version and use it on another version (youre welcome to try, of course).
[oracle@oralinux lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed Linking BBED utility (bbed) rm -f /opt/oracle/product/10.2.0/db_1/rdbms/lib/bbed gcc -o /opt/oracle/product/10.2.0/db_1/rdbms/lib/bbed - L/opt/oracle/product/10.2.0/db_1/rdbms/lib/ - L/opt/oracle/product/10.2.0/db_1/lib/ - L/opt/oracle/product/10.2.0/db_1/lib/stubs/ -L/usr/lib -lirc /opt/oracle/product/10.2.0/db_1/lib/s0main.o /opt/oracle/product/10.2.0/db_1/rdbms/lib/ssbbded.o /opt/oracle/product/10.2.0/db_1/rdbms/lib/sbbdpt.o `cat /opt/oracle/product/10.2.0/db_1/lib/ldflags` -lnsslb10 -lncrypt10 -lnsgr10 <some lines removed> -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10 - lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /opt/oracle/product/10.2.0/db_1/lib/sysliblist` -Wl,- rpath,/opt/oracle/product/10.2.0/db_1/lib -lm `cat /opt/oracle/product/10.2.0/db_1/lib/sysliblist` -ldl -lm - L/opt/oracle/product/10.2.0/db_1/lib
To confirm the creation, see if the bbed executable was created. In this example, the make command was executed in the rdbms/lib directory. You can place bbed anywhere youd like. Also, change the permissions if needed.
-rwxr-xr-x 1 oracle dba 434057 Aug 25 16:26 bbed
To confirm that the utility actually runs, invoke it. This example uses the 10g version, which shows release 220.127.116.11.0, and so does the 9.0.4 version. Aside from the change in the copyright, the release does not appear to have changed in quite some time.
[oracle@oralinux lib]$ ./bbed Password: BBED: Release 18.104.22.168.0 - Limited Production on Wed Aug 27 16:17:06 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED>
The Big Secret Behind bbed
Note that you will be prompted for a password. Virtually all of the references to bbed via a search on the Internet mention that if you are motivated enough to be using bbed in the first place, you are clever enough to determine the password on your own. Whatever. The password is blockedit. Youll see it as BLOCKEDIT in a hex dump file of bbed. Use xxd in /usr/bin to create a dump of bbed, and then look for BBED> in the file. A few lines up is BLOCKEDIT.
First and foremost is this: bbed is an undocumented and unsupported (from a customers perspective) utility. Unless being directed to use this tool by Oracle Support, you are on your own. Do not use bbed on a production database unless you know what you are doing. Do not use bbed on any database that you cannot afford to lose. Take a backup of any database on which you are going to use this tool.
If you need to recover data and find yourself completely stymied by every other effort made so far, this is your last resort. There may be bigger and better tools out there, but the here and now tool is bbed. Should you find yourself needing to use this tool to save/rescue/recover a production database, it would be in your best interest to first take a cold backup and then take a copy of that backup as your test bed. In other words, do your work on files separate from the actual files. If you are trying to restore data, transfer it from a rescue instance back into the production instance.
Oracle documentation for bbed (to include looking for it on MetaLink) is almost nowhere to be found in the public domain. MetaLink note 62015.1 contains (assuming it still exists within OSS) a note that BBED is a SUPPORT ONLY tool and should NOT be discussed with customers. (See http://www.freelists.org/archives/oracle-l/04-2004/msg01068.html)
Nonetheless, we can glean information about this tool (and others as well) from the message library that accompanies Oracle software. $ORACLE_HOME/rdbms/mesg contains a file named bbedus.msg. You can cat or vi the file and peruse its contents to obtain an idea of how the tool works. Within the message library (towards the end) is a listing of valid positional parameters, one of which being HELP. Windows installations of Oracle still contain the message library even though BBED.EXE is no longer included.
Before you start working directly with bbed, its helpful to know your way around data blocks in general, including how to get internal block information by row within a table. That, and other pieces of information commonly needed include the absolute file number, the full path and name of datafiles, datafile size in blocks, data block address, block number, block size, and the block type.
Youll need a reporting tool to output information about a block. There is more than one way to get this information, but the easiest is based on using the supplied PL/SQL built-in named DBMS_ROWID. This package (with ten functions and one procedure) has been available since at least Oracle8i days, but use of it may be new to you (how often have you dug into the internals of a data block?). Information from several functions is combined in the one procedure, which makes use of OUT parameters. Create your own wrapper procedure around DBMS_ROWID.ROWID_INFO to make it reusable. Lets look at what the procedure contains (Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)).
This procedure returns information about a ROWID, including its type (restricted or extended), and the components of the ROWID. This is a procedure, and it cannot be used in a SQL statement.
DBMS_ROWID.ROWID_INFO ( rowid_in IN ROWID, ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE', rowid_type OUT NUMBER, object_number OUT NUMBER, relative_fno OUT NUMBER, block_number OUT NUMBER, row_number OUT NUMBER);
A sample get_rowinfo wrapper procedure is shown below.
create or replace procedure get_rowinfo(rid in rowid) as sm varchar2(9) := 'SMALLFILE'; rid_t number; obj_n number; file_n number; block_n number; row_n number; begin DBMS_ROWID.ROWID_INFO(rid, rid_t, obj_n, file_n, block_n, row_n, sm); DBMS_OUTPUT.PUT_LINE('Type: ' || to_char(rid_t)); DBMS_OUTPUT.PUT_LINE('Data obj number: ' || to_char(obj_n)); DBMS_OUTPUT.PUT_LINE('Relative fno: ' || to_char(file_n)); DBMS_OUTPUT.PUT_LINE('Block number: ' || to_char(block_n)); DBMS_OUTPUT.PUT_LINE('Row number: ' || to_char(row_n)); end; /
Note how placeholder variables are used for the OUT parameters and can be directly referenced. The parameter list in the code also shows the file type (SMALLFILE) being passed in last in the list. The documentation shows this as the second parameter, and that is incorrect. A describe command issued against the package shows this to be the case as well.
Getting bbed installed is fairly simple, and being able to use the tool once the ultra-secret password is known, opens the door to many possibilities. Just being able to see how data is stored within a file is worthwhile in and of itself, as is how Oracle tags or marks data blocks with their current status. In a future article, well use a bare bones database and look at the contents of a datafile.