Oracle BBED修改数据块进而修改数据

介绍：

利用bbed修改数据块进而修改数据，没什么实际利用价值，可以了解一下，bbed属于生产中危险操作，谨慎使用。

建立测试表：

SQL> create table dbhang (id number,name varchar2(20));
Table created.
SQL> insert into dbhang values(1,'baoyuhang');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dbhang;
ID NAME
1 baoyuhang

需求：现将数据baoyuhang修改为bbbyuhang


查看该行记录处于的文件号，块号，行号：
select
rowid,
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno
from dbhang;

ROWID		      REL_FNO	 BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAVoxAABAAAW8BAAA	    1	   93953	  0

准备BBED参数文件：

查询数据文件：
SQL> select file# || ' ' ||name || ' ' || bytes from v$datafile;

​                  FILE#||''||NAME||''||BYTES
1 /oracle/app/oradata/prod/system01.dbf786432000
2 /oracle/app/oradata/prod/sysaux01.dbf545259520
3 /oracle/app/oradata/prod/undotbs01.dbf94371840
4 /oracle/app/oradata/prod/users01.dbf5242880
5 /oracle/app/oradata/prod/example01.dbf328335360
6 /oracle/app/oradata/prod/dbhang01.dbf52428800
7 /oracle/app/oradata/prod/dbhang02.dbf52428800



将以上查询的信息保存在文本中。

[oracle@server1 ~]$ cat datafile.txt 
1 /oracle/app/oradata/prod/system01.dbf 786432000
2 /oracle/app/oradata/prod/sysaux01.dbf 545259520
3 /oracle/app/oradata/prod/undotbs01.dbf 94371840
4 /oracle/app/oradata/prod/users01.dbf 5242880
5 /oracle/app/oradata/prod/example01.dbf 328335360
6 /oracle/app/oradata/prod/dbhang01.dbf 52428800
7 /oracle/app/oradata/prod/dbhang02.dbf 52428800

创建BBED参数文件：

[oracle@server1 ~]$ cat bbed.par 
blocksize=8192
listfile=/home/oracle/datafile.txt   //指定文本文件
mode=edit    //编辑模式

bbed进行数据定位：

[oracle@server1 ~]$ bbed parfile=/home/oracle/bbed.par 
Password: 
BBED: Release 2.0.0.0.0 - Limited Production on Thu Jul 30 05:59:45 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> 


指定1号文件93953号块：
BBED> set dba 1,93953 offset 0
	DBA            	0x00416f01 (4288257 1,93953)
	OFFSET         	0

定位数据：（确定数据在block中的偏移量）
BBED> find /c baoyuhang
 File: /oracle/app/oradata/prod/system01.dbf (1)
 Block: 93953            Offsets: 8179 to 8191          Dba:0x00416f01
 62616f79 7568616e 6704066c 38 
 <32 bytes per line>

dump查看一下offset 8179信息：
BBED> dump /v dba 1,93953 offset 8179 count 128
 File: /oracle/app/oradata/prod/system01.dbf (1)
 Block: 93953   Offsets: 8179 to 8191  Dba:0x00416f01
 62616f79 7568616e 6704066c 38       l baoyuhang..l8


Offsets: 8179 to 8191    代表这一行的地址。
也是说baoyuhang属于8179-8191 

b:8179
a:8180
o:8181


修改block将bao替换为bbb：
BBED> modify /c bbb dba 1,93953 offset 8179
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oracle/app/oradata/prod/system01.dbf (1)
 Block: 93953            Offsets: 8179 to 8191           Dba:0x00416f01
 62626279 7568616e 670106ba 3c 
 <32 bytes per line>


再次查看修改后的数据：
BBED> dump /v dba 1,93953 offset 8179 count 128
 File: /oracle/app/oradata/prod/system01.dbf (1)
 Block: 93953   Offsets: 8179 to 8191  Dba:0x00416f01

 62626279 7568616e 6704066c 38       l bbbyuhang..l8


应用变更：
BBED> sum dba 1,93953 apply
Check value for File 1, Block 93953:
current = 0x29a2, required = 0x29a2

查询修改后的数据：

SQL> startup;
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size		    2257800 bytes
Variable Size		  541068408 bytes
Database Buffers	  281018368 bytes
Redo Buffers		    6586368 bytes
Database mounted.
Database opened.

SQL> select * from dbhang;
ID NAME
1 bbbyuhang

修改完毕.

修改完毕

其他有关bbed的案例：

Oracle BBED工具介绍与安装

Oracle BBED利用copy命令恢复已删除的记录

Oracle BBED修改数据块进而修改数据

Oracle BBED全库跳过丢失的归档继续恢复

Oracle BBED单个数据文件跳过丢失的归档继续恢复

Oracle BBED单个数据文件跳过所有归档恢复

Oracle BBED将offline的数据文件改为online案例

本文地址：https://blog.csdn.net/baoyuhang0/article/details/108980528