oracle bbed恢复删除数据实例_oracle

恢复己删除数据
一、创建模拟环境

复制代码 代码如下:

SQL> create table hr.xifenfei (id number,name varchar2(20)) tablespace xff;
Table created.
SQL> insert into hr.xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into hr.xifenfei values(2,'xff');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
SQL> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from hr.xifenfei;
ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHy3AACAAAAISAAA 2 530 0
AAAHy3AACAAAAISAAB 2 530 1
查询file#，block，后面恢复要用
SQL> delete from hr.xifenfei where id=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 

二、bbed恢复删除数据

复制代码 代码如下:

[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Mon Aug 22 01:52:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0×00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> set dba 2,530
DBA 0×00800212 (8389138 2,530)
BBED> find /c xff
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
————————————————————————
7866662c 000202c1 02087869 66656e66 65690106 80e2
<32 bytes per line>
BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
——————————————————-
7866662c 000202c1 02087869 66656e66 l xff,……xifenf
65690106 80e2 l ei….
<16 bytes per line>
BBED> dump /v offset 8160
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8160 to 8191 Dba:0×00800212
——————————————————-
0000003c 020202c1 03037866 662c0002 l …<......xff,..
02c10208 78696665 6e666569 010680e2 l ....xifenfei....
<16 bytes per line>
BBED> dump /v offset 8164
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8164 to 8191 Dba:0×00800212
——————————————————-
020202c1 03037866 662c0002 02c10208 l ……xff,……
78696665 6e666569 010680e2 l xifenfei….
<16 bytes per line>
BBED> dump /v offset 8162
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8162 to 8191 Dba:0×00800212
——————————————————-
003c0202 02c10303 7866662c 000202c1 l .<......xff,....
02087869 66656e66 65690106 80e2 l ..xifenfei....
<16 bytes per line>
BBED> dump /v offset 8163
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
——————————————————-
3c020202 c1030378 66662c00 0202c102 l <......xff,.....
08786966 656e6665 69010680 e2 l .xifenfei....
<16 bytes per line>
通过尝试，推断出来3c的offset
BBED> modify /x 2c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
————————————————————————
2c020202 c1030378 66662c00 0202c102 08786966 656e6665 69010680 e2
<32 bytes per line>
修改3c为2c
BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9
 

三、核对结果

复制代码 代码如下:

SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff

说明：
1）如果数据未删除：row flag的值为 32+8+4=44或者0x2c
2）如果数据被删除：row flag的值为 32+16+8+4=60或者0x3c

找回被删除数据

创建模拟表数据

复制代码 代码如下:

SQL> create table t_xifenfei(id number,name varchar2(10));

Table created.

SQL> insert into t_xifenfei values(1,'xifenfei');

1 row created.

SQL> insert into t_xifenfei values(2,'XIFENFEI');

1 row created.

SQL> commit;

Commit complete.
dump数据块
SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> select   rowid,id,name,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from chf.t_xifenfei;

ROWID                      ID NAME          REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ---------- ---------- ----------
AAASdmAAEAAAACvAAA          1 xifenfei            4        175          0
AAASdmAAEAAAACvAAB          2 XIFENFEI            4        175          1

SQL> alter system dump datafile 4 block 175;

System altered.
dump文件内容
block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  78 69 66 65 6e 66 65 69
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 8]  58 49 46 45 4e 46 45 49
end_of_block_dump
2012-05-01 05:09:29.287714 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
 

删除表数据

复制代码 代码如下:

SQL> delete from t_xifenfei;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> alter system dump datafile 4 block 175;

System altered.
dump文件内容
block_row_dump:
tab 0, row 0, @0x1f89
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f7a
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
2012-05-01 05:13:35.214357 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
通过对比这两次的dump文件发现
1.数据内容被删除,并不是真正删除,而是给其增加了一个标识位(fd:---D----)
2.fb:--H-FL--(head of row piece+first data piece+last data piece )
  其有8个选项每个选项的值分别对应bitmask即32+8+4=44 or 0x2c
3.如果一个row被delete了,那么row flag就会更新,bitmask里的deleted被设置为16.
  此时row flag为：32+16+8+4 = 60 or 0x3c.
4.如果我们要找回来被删除的数据,只需要把3c改为2c即可
 

关闭数据库

复制代码 代码如下:

SQL> select * from chf.t_xifenfei;

no rows selected

SQL> select name from v$datafile where file#=4;

NAME
------------------------------------------------
/tmp/user01.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
bbed修改数据
BBED> set filename '/tmp/user01.dbf'
        FILENAME        /tmp/user01.dbf

BBED> set block 175
        BLOCK#          175

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set mode edit
        MODE            Edit

BBED> map
 File: /tmp/user01.dbf (0)
 Block: 175                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0     

 struct ktbbh, 72 bytes                     @20    

 struct kdbh, 14 bytes                      @100   

 struct kdbt[1], 4 bytes                    @114   

 sb2 kdbr[2]                                @118   

 ub1 freespace[8036]                        @122   

 ub1 rowdata[30]                            @8158  

 ub4 tailchk                                @8188  

BBED> p *kdbr[0]
rowdata[15]
-----------
ub1 rowdata[15]                             @8173     0x3c

BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8158     0x3c

BBED> m /x 2c offset 8158
 File: /tmp/user01.dbf (0)
 Block: 175              Offsets: 8158 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c630202 c1030858 4946454e 46454932 630202c1 02087869 66656e66 65690106
 b47e

 <32 bytes per line>

BBED>  m /x 2c offset 8173
 File: /tmp/user01.dbf (0)
 Block: 175              Offsets: 8173 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c630202 c1020878 6966656e 66656901 06b47e

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 175:
current = 0x4d13, required = 0x4d13

启动数据库验证

复制代码 代码如下:

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xifenfei;

        ID NAME
---------- ----------
         1 xifenfei
         2 XIFENFEI

时间： 2024-09-03 10:06:12

oracle bbed恢复删除数据实例_oracle的相关文章

Oracle误删除表数据后的数据恢复详解_oracle

Oracle误删除表数据后的恢复详解   测试环境: SYSTEM:IBM AIX 5L                         Oracle Version:10gR2   1. undo_retention参数的查询与修改 使用show parameter undo命令查看当前的数据库参数undo_retention设置. 显示如下: SQL> show parameter undo NAME                                 TYPE        V

Oracle导出excel数据_oracle

推荐阅读:Oracle读取excel数据 oracle导出excel(非csv)的方法有两种,1.使用sqlplus spool,2.使用包体 现将网上相关代码整理后贴出以备不时之需: 使用sqlplus: 使用sqlplus需要两个文件:sql脚本文件和格式设置文件. 去除冗余信息,main.sql --main.sql 注意,需要在sqlplus下运行 非plsql命令行下 set linesize 200 set term off verify off feedback off pages

Oracle读取excel数据_oracle

推荐阅读:Oracle导出excel数据 废话不多说了,直接给大家奔入主题了. --解析excel,转换成table,可供查询,支持xls.xlsx --首先修改这个Type,长度改为4000. CREATE OR REPLACE TYPE XYG_PUB_DATA_UPLOAD_Obj AS OBJECT( SOURCE_TYPE VARCHAR2(240)--EXCEL/TXT ,BATCH_CODE VARCHAR2 (480 BYTE)--批的Code,Excel用,因为一个Excel可

利用Oracle数据库发送邮件的实例代码_oracle

--发送邮件的主过程如下所述: Procedure send_mail_ (p_From Varchar2, --邮件发送人 p_Fromuser Varchar2, --发件人昵称 p_Touser Varchar2, --接受人昵称 p_To Varchar2, --邮件接收人 p_Cc Varchar2, --邮件抄送人 p_Subject Varchar2, --邮件标题 p_Message Varchar2, --邮件内容 p_User Varchar2, --邮件验证用户 p_Mai

[20150522]bbed与数据块检查和.txt

[20150522]bbed与数据块检查和.txt --我现在基本拿bbed学习,基本是拿bbed查看,而使用bvi修改数据.我感觉这种方便1写. --实际上使用bbed的好处就是修改数据块检查和不一致,而使用bbed修改很简单仅仅需要执行sum apply就ok了. --对比dbv与bbed确定检查和位置. 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER -------

[20150527]bbed与数据块检查和2.txt

[20150527]bbed与数据块检查和2.txt http://blog.itpub.net/267265/viewspace-1666781/ --我现在基本拿bbed学习,基本是拿bbed查看,而使用bvi修改数据.我感觉这种方便1写. --实际上使用bbed的好处就是修改数据块检查和不一致,而使用bbed修改很简单仅仅需要执行sum apply就ok了. --对比dbv与bbed确定检查和位置. --实际上既然检查和在16,17字节,只要清零,加上dbv就很容易确定要修改的内容. 1.

[20170412]bbed隐藏数据记录.txt

[20170412]bbed隐藏数据记录.txt --上午做了bbed恢复修改记录(不等长)的情况,http://blog.itpub.net/267265/viewspace-2137082/ --下午做一个隐藏数据记录的情况,实际上这样做有点多余,就是删除命令,看看bbed是否可以完成. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER --------------------

[20160526]bbed修改数据记录(不等长).txt

[20160526]bbed修改数据记录(不等长).txt --以前做的测试,有点乱,当时没有很好的理解快速提交.而且做的很乱,链接如下: http://blog.itpub.net/267265/viewspace-1193074/ --今天重复测试看看: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ ------

[20150527]bbed解决数据文件大小问题.txt

[20150527]bbed解决数据文件大小问题.txt --模拟一个数据文件大小不一致的问题. 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86