sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle BBED >

案例:oracle bbed恢复之通过bbed工具恢复数据表中意外删除的数据

时间:2016-05-02 21:51   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

数据库某表中的数据意外删除,通过bbed恢复删除的数据

一、创建模拟环境
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.

二、oracle 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

三、检查核对bbed恢复结果
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
 

-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享,转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:oracle bbed恢复之通过bbed工具恢复数据表中意外删除的数据

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/122.html

Oracle研究中心

关键词:

通过bbed工具恢复数据表中意外删除的数据