Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle BBED tips

Oracle Database Tips by Donald BurlesonJune 30, 2013

 

For using BBED in Oracle Forensics, see Oracle Internals - Using BBED Block Browser and Editor to Find Deleted Data.

 

Also see my notes on  the Oracle BBED Verify Command and BBED Editor Tools


Oracle has the BBED utility (block browser and editor) is is all releases of Oracle, from Oracle7 to Oracle10g.  The executable is removed in Oracle 11g, but you can nsearch in the ins_rdbms.mk makefile (located in $ORACLE_HOME/rdbms/lib directory), you will see the BBED entry.

Designed for internal use only, BBED can be used for several functions, both legitimate and illegal:

  • The original intent for BBED is for use by Oracle Technical support to browse, diagnose (and repair) data block corruption issues.
     

  • BBED is a great data block browser for those interested in examining the internal structures with data and index blocks.  However, the "alter system dump" command can also dump data block contents.  You should NEVER use BBED in EDIT Mode unless you are working with Oracle technical support.
     

  • Some DBA's use BBED to corrupt data and index blocks to test RMAN recovery from self-induced data corruptions.
     

  • Hackers might use BBED to break into an Oracle database.  Tools like BBED can be used to view data directly within their data block (bypassing the Oracle later), and because BBED writes directly the data block, BBED could be used by hackers to update a database without logging and auditing.

Using and linking BBED

This paper titled "disassembling the Oracle data block" has complete instructions for installing and using BBED.  This make command tells how to linkedit BBED:

make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/BBED $ORACLE_HOME/bin/BBED

This page shows the BBED command syntax and usage options.

BBED Safety tip:  When  using BBED, always stay in BROWSE mode, and only use BBED EDIT mode (with VER and REP) if you know what you are doing.

The BBED password

Because of the huge power of BBED, it's been designed such that only the worthy user can find it.  Experienced software engineers can find the BBED password in just a minute.  If you cannot extract the BBED password in 10 minutes, you are probably not experienced enough to safely use the BBED tool.

BEWARE of BBED's POWER

The BBED utility can easily corrupt a database and it should NEVER be used in a valuable database unless directed by Oracle technical support.

Using BBED:

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 one can take BBED from one version and use it on another version, but one is welcome to try.

[oracle@oralinux lib]$ make -f ins_rdbms.mk
$ORACLE_HOME/rdbms/lib/BBED

To confirm the creation, see if the BBED executable was created. In this example, the make command was executed in the rdbms/lib directory. BBED can be placed anywhere the DBA likes. 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>

Note that one will be prompted for a password. Virtually all of the references to BBED via a search on the Internet mention that if one is motivated enough to be using BBED in the first place, then one is clever enough to determine the password on your own. The password is blockedit. It will be seen 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.

The mileage may vary, but it is possible to use BBED.EXE that shipped with Oracle 8.1.6, which was about the last time the Windows version was included in the RDBMS software installation, and use that executable against later versions of Oracle datafiles.

Before going into syntax and examples, some preliminaries are in order. 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, the DBA is on his own. Do not use BBED on a production database unless one knows what one is doing. Do not use BBED on any database that one cannot afford to lose. Take a backup of any database on which this tool is going to be used.

If the DBA needs to recover data and finds herself completely stymied by every other effort made so far, this is the last resort. There may be bigger and better tools out there, but the "here and now" tool is BBED.

If this tool is needed to save/rescue/recover a production database, it would be in the DBA�s best interest to first take a cold backup and then take a copy of that backup as the test bed. In other words, do the work on files separate from the actual files. If the DBA is 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, or My Oracle Support as it is now known, 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.' The contents of the note are available inside a mailing list, and support agreements prevent publishing it within this book. (See http://www.freelists.org/post/oracle-l/is-it-the-bbed    )

Nonetheless, information can be gleaned 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. One 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 is HELP. Windows installations of Oracle still contain the message library even though BBED.EXE is no longer included.

Before one starts working directly with BBED, it is helpful to know one's 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.

The DBA needs 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 the Oracle8i days, but use of it may be new to the DBA. Information from several functions is combined in the one procedure which makes use of OUT parameters. Create one's own wrapper procedure around DBMS_ROWID.ROWID_INFO to make it reusable.

The analyst named Scott in the EMP table is going to be promoted to manager. The steps to alter the table data outside of SQL*Plus are:

1.      Get the ROWID if it is not already known

2.      Shutdown the database and take a cold backup

3.      Start bbed with a parameter file, being sure to include the relevant        datafile

4.      Find the data block address

5.   Find the offset where the string ANALYST begins and confirm the       data/location

6.      Change the mode to edit unless the parfile already includes that

7.      Modify the data

8.      Confirm the data change

9.      Apply the change

10.  Restart the database and look for the change

 

The chosen approach is that one already knows some things about the data, e.g., the record that one wants to change and the ROWID/dba information.  The dba will still be 4,32 for this example.

 

After shutting down the database and taking a cold backup while using the same parfile from before, a bbed session can be started. After it is started, navigate to dba 4,32 and set the offset to 0 so the DBA sees that she has a known starting position for the search/find operation to follow.

 

[oracle@oralinux bbed]$ bbed parfile=bbed.par

Password:

 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Aug 31 17:55:58 2015

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> set dba 4,32

        DBA             0x01000020 (16777248 4,32)

 

BBED> set offset 0

        OFFSET          0

 

The find command will dump multiple lines. Since one is searching for a character string, use the c flag.

 

BBED> find /c SCOTT

 File: /opt/app/oracle/oradata/ORCL2/users01.dbf (4)

 Block: 32               Offsets: 7864 to 8191           Dba:0x01000020

------------------------------------------------------------------------

 53434f54 5407414e 414c5953 5403c24c 430777bb 04130101 0102c21f ff02c115

 2c010803 c24e5305 434c4152 4b074d41 4e414745 5203c24f 280777b5 06090101

 0103c219 33ff02c1 0b2c0108 03c24d63 05424c41 4b45074d 414e4147 455203c2

 4f280777 b5050101 010103c2 1d33ff02 c11f2c01 0803c24d 37064d41 5254494e

 0853414c 45534d41 4e03c24d 630777b5 091c0101 0103c20d 3302c20f 02c11f2c

 010803c2 4c43054a 4f4e4553 074d414e 41474552 03c24f28 0777b504 02010101

 03c21e4c ff02c115 2c010803 c24c1604 57415244 0853414c 45534d41 4e03c24d

 630777b5 02160101 0103c20d 3302c206 02c11f2c 010803c2 4b640541 4c4c454e

 0853414c 45534d41 4e03c24d 630777b5 02140101 0102c211 02c20402 c11f2c01

 0803c24a 4605534d 49544805 434c4552 4b03c250 030777b4 0c110101 0102c209

 ff02c115 1006dbbf

 

 <32 bytes per line>

 

Dump the current offset and confirm that SCOTT was found.

 

BBED> dump /v dba 4,32 offset 7864 count 32

 File: /opt/app/oracle/oradata/ORCL2/users01.dbf (4)

 Block: 32      Offsets: 7864 to 7895  Dba:0x01000020

-------------------------------------------------------

 53434f54 5407414e 414c5953 5403c24c l SCOTT.ANALYST.?

 430777bb 04130101 0102c21f ff02c115 l C.w?......?..?

 

 <16 bytes per line>

 

The output tells the DBA that SCOTT begins at offset 7864 within the dba. Counting over six positions is where ANALSYT should begin. To confirm this, move the offset (explicitly, although one can add or subtract positions, such as +4 or -3) to 7870 and dump the contents again.

 

BBED> set offset 7870

        OFFSET          7870

 

BBED> d /v

 File: /opt/app/oracle/oradata/ORCL2/users01.dbf (4)

 Block: 32      Offsets: 7870 to 7901  Dba:0x01000020

-------------------------------------------------------

 414e414c 59535403 c24c4307 77bb0413 l ANALYST.?C.w?..

 01010102 c21fff02 c1152c01 0803c24e l ....?..?,...?

 

 <16 bytes per line>

 

Note the syntax used in the last dump command. If one is not sure of the location, one can always set it as done in the first dump. Now it is time to replace ANALYST with MANAGER, and that is done via the modify command. Now modify it and dump to confirm the change. Modifications can be made via one of several formats (same as find), so the easiest case for readability is finding and modifying via character strings, and that is what the /c does. Do not forget to change the EDIT mode if necessary (BBED> set mode edit).

 

BBED> modify /c MANAGER

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

 File: /opt/app/oracle/oradata/ORCL2/users01.dbf (4)

 Block: 32               Offsets: 7870 to 7901           Dba:0x01000020

------------------------------------------------------------------------

 4d414e41 47455203 c24c4307 77bb0413 01010102 c21fff02 c1152c01 0803c24e

 

 <32 bytes per line>

 

BBED> d /v

 File: /opt/app/oracle/oradata/ORCL2/users01.dbf (4)

 Block: 32      Offsets: 7870 to 7901  Dba:0x01000020

-------------------------------------------------------

 4d414e41 47455203 c24c4307 77bb0413 l MANAGER.?C.w?..

 01010102 c21fff02 c1152c01 0803c24e l ....?..?,...?

 

 <16 bytes per line>

 

Now perform a sum, which is used to check or set the block's checksum value, and apply the change.

 

BBED> sum

Check value for File 4, Block 32:

current = 0x26b5, required = 0x32ae

 

BBED> sum apply

Check value for File 4, Block 32:

current = 0x32ae, required = 0x32ae

 

So far, so good. Assuming the change has been made in the data block, name two ways one knows of right now to check the value  but not using the dump command. One is using SQL*Plus, but within bbed, one could print the row data. SCOTT's row number is still 8 (or 7 recalling that bbed starts at 0), so a combination of the following could be used:

 

BBED> p *kdbr[7]

rowdata[235]

------------

ub1 rowdata[235]                            @7856     0x2c

 

BBED> x /rnccntnnn

rowdata[235]                                @7856

------------

flag@7856: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@7857: 0x01

cols@7858:    8

 

col    0[3] @7859: 7788

col    1[5] @7863: SCOTT

col    2[7] @7869: MANAGER

col    3[3] @7877: 7566

col    4[7] @7881: 19-APR-87

col    5[2] @7889: 3000

col    6[0] @7892: *NULL*

col    7[2] @7893: 20

 

 Oracle BBED Restoring a File with BBED

Oracle Database Tips by Donald Burleson

Being able to plug in an older version of a file into the current instance could be a huge save in terms of recovery. At one point in time, a file has certain values related to its state, and that state is consistent with other files in the database. When the state is off, Oracle informs the DBA about it right away via one or more error messages, especially the one about a file needing more media recovery. In a normal recovery scenario where media loss has occurred, the recovery consists of restoring a backed up copy of one or more datafiles and then applying archived redo to bring the file to a consistent state.

 

In the bbed recovery scenario, one will not be applying redo, but rather will be jumping the state of the file from a point in the past to a point in time consistent with the rest of the database. The specific part of the file to be edited is the file header. Information will be needed from three structs: kcvfhckp, kcvfhcpc, and kcvfhccc. Published elsewhere are decode tables for many of these items, and these are generally easy to interpret. Kernel-related codes begin with a k, fh looks like file header, and the remainder are related to checkpoints. Specifically, good values from the following are needed:

  • kscnbas - last change SCN

  • kcvcptim - time of the last change

  • kcvfhcpc - checkpoint count

  • kcvfhccc - a checkpoint checker value, which is one less than kcvfhcpc

For file headers, one is interested in the output of the kcvfh struct. The output of ?p kcvfh? is long, but it is interesting to browse through the output. It is also interesting how the name of the database (ORCL2 here) appears spelled out.

 

BBED> p kcvfh

struct kcvfh, 676 bytes                     @0

   struct kcvfhbfh, 20 bytes                @0

      ub1 type_kcbh                         @0        0x0b

      ub1 frmt_kcbh                         @1        0xa2

      ub1 spare1_kcbh                       @2        0x00

      ub1 spare2_kcbh                       @3        0x00

      ub4 rdba_kcbh                         @4        0x01800001

      ub4 bas_kcbh                          @8        0x00000000

      ub2 wrp_kcbh                          @12       0x0000

      ub1 seq_kcbh                          @14       0x01

      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)

      ub2 chkval_kcbh                       @16       0x8f50

      ub2 spare3_kcbh                       @18       0x0000

   struct kcvfhhdr, 76 bytes                @20

      ub4 kccfhswv                          @20       0x00000000

      ub4 kccfhcvn                          @24       0x0a200100

      ub4 kccfhdbi                          @28       0x266ecc46

      text kccfhdbn[0]                      @32      O

      text kccfhdbn[1]                      @33      R

      text kccfhdbn[2]                      @34      C

      text kccfhdbn[3]                      @35      L

      text kccfhdbn[4]                      @36      2

 

Here is the test case. A new tablespace (BB), user (BB) and table (EMP) where the table has three rows and three columns from Scott's EMP table is created. The datafile is bb01.dbf, created with a size of 100K. A backup exists where the three rows are in place. The current instance has had a deletion of all three rows and the object is to restore the data by recovering the older datafile. Therefore, get a new list of files for use within the parameter file, and the copy of bb01.dbf will be the older version. Only two files are really needed - one with a good SCN state, and the older file, but all were listed anyway.

 

1 /opt/app/oracle/oradata/ORCL2/system01.dbf 513802240

2 /opt/app/oracle/oradata/ORCL2/undotbs01.dbf 36700160

3 /opt/app/oracle/oradata/ORCL2/sysaux01.dbf 272629760

4 /opt/app/oracle/oradata/ORCL2/users01.dbf 5242880

5 /opt/app/oracle/oradata/ORCL2/example01.dbf 104857600

6 /opt/app/oracle/oradata/ORCL2/bb01.dbf 106496

 

Replace the file and issue a startup command. One should see the error related to the ?bad? file.

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area  922746880 bytes

Fixed Size                  1222624 bytes

Variable Size             281020448 bytes

Database Buffers          633339904 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/opt/app/oracle/oradata/ORCL2/bb01.dbf'

 

The current checkpoint number is not necessarily needed, but since it can be pulled directly from the database, see what it is. It will also show up in the file itself courtesy of bbed. The current SCN is at 689110 and the bad file is at 685758.

 

SQL> select distinct checkpoint_change# from v$datafile;

 

CHECKPOINT_CHANGE#

------------------

            689110

 

SQL> select change# from v$recover_file;

 

   CHANGE#

----------

    685758

 

Start a bbed session and print the kcvfhckp struct. The first few lines are shown and the values of interest are the SCN and last time.

 

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes                   @484

   struct kcvcpscn, 8 bytes                 @484

      ub4 kscnbas                           @484      0x000a83d6

      ub2 kscnwrp                           @488      0x0000

   ub4 kcvcptim                             @492      0x2799048e

 

The hexadecimal value 0x000a83d6 should convert to 689110 in base 10 (decimal). Use the scientific calculator on Windows, something on the Internet, or get the decimal value manually. The fun way is by hand. The significant part of the hex value is a83d6. Convert hex to binary where each hex character is xxxx in binary. Now there is:

 

a

8

3

d

6

1010

1000

0011

1101

0110

 

Binary 1010100001111010110 is 689110, so that is good. Now print kcvfhcpc and kcvfhccc. The SYSTEM datafile is used by setting dba 1,1.

 

BBED> p kcvfhcpc

ub4 kcvfhcpc                                @140      0x0000004a

 

BBED> p kcvfhccc

ub4 kcvfhccc                                @148      0x00000049

 

One other, albeit final complication in all of this is taking into account the byte ordering of data on the platform the server is running on. It is big endian versus little endian. The database is running Oracle Enterprise Linux on a PC, so that makes it little endian. The order of the first two values has to be reversed (pair by pair). To summarize the changes to be made to the older file, see the table below.

 

 

Attribute

kscnbas

kcvcptim

kcvfhcpc

kcvfhccc

Value

000a83d6

(d6830a00)

2799048e

(8e049927)

4a

49

Offset

484

492

140

148

 

Use the modify command with /x for hexadecimal editing and a dba of file 6, block 1.

 

When finished with the four modify statements, perform a 'sum dba x,1 apply? where ?x? is your file number. If after applying the changes one receives an error upon startup, check the SCN values output from:

 

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE; and

SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# 

FROM V$LOG V1, V$LOGFILE V2

WHERE V1.GROUP# = V2.GROUP#;

 

If the SCN for the recovered file is wildly different from the others, the byte ordering of the hex value during the modify command might have been reversed.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  922746880 bytes

Fixed Size                  1222624 bytes

Variable Size             281020448 bytes

Database Buffers          633339904 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01122: database file 6 failed verification check

ORA-01110: data file 6: '/opt/app/oracle/oradata/ORCL2/bb01.dbf'

ORA-01207: file is more recent than control file - old control file

     


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster