Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Altering Data Using Oracle BBED
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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 2008

 

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.ÂL

 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.ÂLC.w»..

 01010102 c21fff02 c1152c01 0803c24e l ....Â...Á.,...ÂN

 

 <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.ÂLC.w»..

 01010102 c21fff02 c1152c01 0803c24e l ....Â...Á.,...ÂN

 

 <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

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.