如何通过修改数据字典来恢复被truncate的数据

在这篇文章里，我们演示了如何以修改数据字典并配合BBED的方式来恢复被truncate的数据，看这篇文章的朋友有几点一定要注意（免得被我误导了）：

1、如下的方法只适用于MSSM，ASSM要复杂很多；

2、恢复被truncate的数据用ODU就可以了，万万不要用我这里用到的方法；

3、 这里提到的方法没有任何实际意义，仅仅是出于研究的目的。

好了，我们来看一个实例，我是在9.2.0.6上做的测试：

SQL> conn sys/oracle@testdbaix as sysdba;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as SYS

SQL> create table test_truncate as select * from dba_users;

Table created

SQL> select username from test_truncate;

USERNAME

------------------------------

SYS

SYSTEM

......省略显示部分内容

QS_CB

QS_CS

29 rows selected

SQL> select object_id,data_object_id from dba_objects where object_name='TEST_TRUNCATE';

OBJECT_ID DATA_OBJECT_ID

---------- --------------

30207 30207

data_object_id通常是通过dump数据块或者以logmnr的方式得到。

SQL> select FILE#,BLOCK# from tab$ where obj#=30207;

FILE# BLOCK#

---------- ----------

1 50593

准备工作都做好了，现在我们来truncate：

SQL> truncate table test_truncate;

Table truncated

SQL> select count(*) from test_truncate;

COUNT(*)

----------

SQL> select username from test_truncate;

USERNAME

------------------------------

Truncate完后，我们来开始恢复的过程。

首先我们来修改数据字典：

SQL> update tab$ set dataobj#=30207 where obj#=30207;

1 row updated

SQL> update seg$ set hwmincr=30207 where file#=1 and block#=50593;

1 row updated

SQL> update obj$ set dataobj#=30207 where obj#=30207;

1 row updated

SQL> commit;

Commit complete

shutdown上述数据库，接着我们来用BBED修改上述段头（即file#=1 block#=50593）：

对于MSSM而言，有三个地方需要修改，分别是HWM，HWM下的block数量以及段头所指向的data object id，如下是完整的修改过程：

BBED> set file 1

FILE# 1

BBED> set block 50593

BLOCK# 50593

BBED> p ktech

struct ktech, 72 bytes @20

ub4 spare1_ktech @20 0x00000000

word tsn_ktech @24 0

ub4 lastmap_ktech @28 0x00000000

ub4 mapcount_ktech @32 0x00000000

ub4 extents_ktech @36 0x00000001

ub4 blocks_ktech @40 0x00000007

ub2 mapend_ktech @44 0x1020

struct hwmark_ktech, 32 bytes @48

ub4 extno_ktehw @48 0x00000000

ub4 blkno_ktehw @52 0x00000000

ub4 extsize_ktehw @56 0x00000007

ub4 blkaddr_ktehw @60 0x0040c5a2

ub4 mapblk_ktehw @64 0x00000000

ub4 offset_ktehw @68 0x00000000

ub4 flblks_ktehw @72 0x00000000

ub4 blkcnt_ktehw @76 0x00000000

struct locker_ktech, 8 bytes @80

ub2 kxidusn @80 0x0000

ub2 kxidslt @82 0x0000

ub4 kxidsqn @84 0x00000000

ub4 flag_ktech @88 0x00000000 (NONE)

BBED> set offset 60

OFFSET 60

BBED> modify /x 0040c5a3

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /dras21/testdb/system01.dbf (1)

Block: 50593 Offsets: 60 to 571 Dba:0x0040c5a1

------------------------------------------------------------------------

0040c5a3 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000001 00000000 00007600 40000000 0040c5a2 00000007 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> set offset 52

OFFSET 52

BBED> modify /x 00000001

File: /dras21/testdb/system01.dbf (1)

Block: 50593 Offsets: 52 to 563 Dba:0x0040c5a1

------------------------------------------------------------------------

00000001 00000007 0040c5a3 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000001 00000000 00007600 40000000 0040c5a2 00000007

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> p ktemh

struct ktemh, 16 bytes @92

ub4 count_ktemh @92 0x00000001

ub4 next_ktemh @96 0x00000000

ub4 obj_ktemh @100 0x00007600

ub4 flag_ktemh @104 0x40000000

BBED> set offset 100

OFFSET 100

BBED> modify /x 000075ff

File: /dras21/testdb/system01.dbf (1)

Block: 50593 Offsets: 100 to 611 Dba:0x0040c5a1

------------------------------------------------------------------------

000075ff 40000000 0040c5a2 00000007 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 1, Block 50593:

current = 0xe03d, required = 0xe03d

最后我们来看一下修改后的效果：

SQL>startup

ORACLE instance started.

Total System Global Area 505382744 bytes

Fixed Size 743256 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 1323008 bytes

Database mounted.

Database opened.

SQL >select count(*) from test_truncate;

COUNT(*)

----------

SQL >select username from test_truncate;

USERNAME

------------------------------

SYS

SYSTEM

......省略显示部分内容

QS_CB

QS_CS

29 rows selected

即我们已经恢复了上述被truncate掉的数据。

如何通过修改数据字典来恢复被truncate的数据

Categories:

Leave a comment

Categories

Monthly Archives

Search

Recent Entries

About this Entry

如何通过修改数据字典来恢复被truncate的数据

Categories:

Leave a comment

Categories

Monthly Archives

Search

Recent Entries

Tag Cloud

About this Entry