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:
- Recover damaged or deleted data
- 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 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 doesn't convince you to safeguard your Oracle datafiles
from unauthorized users, what will?
Installing bbed
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
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 (you?re 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 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
2.0.0.0.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 2.0.0.0.0 - Limited Production on Wed Aug 27 16:17:06 2015
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.
Caveats
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
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.?
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.
Helpful Tools
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)).
ROWID_INFO Procedure
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.
Syntax
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.
In
Closing
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.
