oracle bbed恢复删除数据实例
发布时间：2021-12-28 09:53:10 所属栏目：教程 来源：互联网
导读：恢复己删除数据 一、创建模拟环境 复制代码 代码如下: 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
|
恢复己删除数据 一、创建模拟环境 复制代码 代码如下: 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 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
