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; you’re 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
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 doesn’t
convince you to safeguard your Oracle datafiles from unauthorized users, what
Where and how do you get bbed?
In UNIX, Oracle gives you the pieces needed to create the tool. You don’t 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 didn’t 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
[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/
/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
-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 (you’re welcome to try, of course).
[[email protected] 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/lib/stubs/ -L/usr/lib -lirc
/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
rpath,/opt/oracle/product/10.2.0/db_1/lib -lm `cat
/opt/oracle/product/10.2.0/db_1/lib/sysliblist` -ldl -lm –
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 you’d 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.
[[email protected] 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. You’ll 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 customer’s 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
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.”
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, it’s 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.
You’ll 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. Let’s look at what
the procedure contains (Oracle® Database
PL/SQL Packages and Types
Reference 10g Release 2 (10.2)).
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, we’ll use a bare
bones database and look at the contents of a datafile.