当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

使用ODU恢复分区表被truncate掉的分区测试

最近一个客户由于误操作,TRUNCATE了一个分区表的某些分区,需要恢复。

这个案例需要使用老熊写的ODU软件恢复,关于ODU软件的下载和使用方法,详见老熊的BLOG http://www.laoxiong.net/

以下是在OEL5.7 X86_64,ORACLE 10.2.0.5.7版本环境下做的实验。
1.创建测试表

SQL> create table dbdream(id number,name varchar2(10),test varchar2(20))
2  partition by list (id)
3  (
4  partition part1 values(1),
5  partition part2 values(2),
6  partition part3 values(3),
7  partition part4 values(default));

Table created.

2.向测试表中插入测试数据

SQL> insert into dbdream values(1,'dbdream','is test');

1 row created.
SQL> insert into dbdream values(2,'stream','yes');

1 row created.
SQL> insert into dbdream values(3,'wind','no');

1 row created.
SQL> insert into dbdream values(4,'chunfeng','ttt');

1 row created.
SQL> commit;

Commit complete.

3.查询/验证测试数据

SQL> select * from dbdream;

ID NAME       TEST
---------- ---------- --------------------
1 dbdream    is test
2 stream     yes
3 wind       no
4 chunfeng   ttt

SQL> select * from dbdream partition(part1);

ID NAME       TEST
---------- ---------- --------------------
1 dbdream    is test

SQL> c/part1/part2
1* select * from dbdream partition(part2)
SQL> /

ID NAME       TEST
---------- ---------- --------------------
2 stream     yes

SQL> c/part2/part3
1* select * from dbdream partition(part3)
SQL> /

ID NAME       TEST
---------- ---------- --------------------
3 wind       no

SQL> c/part3/part4
1* select * from dbdream partition(part4)
SQL> /

ID NAME       TEST
---------- ---------- --------------------
4 chunfeng   ttt

4.TRUNCATE分区PRAT2

SQL> alter table dbdream truncate partition part2;

Table truncated.
SQL> select * from dbdream;

ID NAME       TEST
---------- ---------- --------------------
1 dbdream    is test
3 wind       no
4 chunfeng   ttt

SQL> select * from dbdream partition (part2);

no rows selected

5.解压ODU软件

[oracle@10205 ~]$ ls
odu_308_linux_x86.tar.tar
[oracle@10205 ~]$ tar -xvf odu_308_linux_x86.tar.tar
odu/
odu/odu
odu/control.txt
odu/config.txt
odu/data/

6.登录ODU,验证是否可以正常登录

[oracle@10205 odu]$ ./odu
Oracle Data Unloader:Release 3.0.8
Copyright (c) 2008,2009 XiongJun. All rights reserved.
Web: http://www.laoxiong.net
Email: magic007cn@gmail.com
loading default config.......
byte_order little
block_size  8192
db_timezone -7
client_timezone 8
data_path   data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order little
load control file 'config.txt' successful
loading default control file ......
ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
load control file 'control.txt' successful
loading dictionary data......

ODU>

7.查询数据库数据文件存放路径及名字

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orac/system01.dbf
/u01/app/oracle/oradata/orac/undotbs01.dbf
/u01/app/oracle/oradata/orac/sysaux01.dbf
/u01/app/oracle/oradata/orac/users01.dbf

8.查询分区表DBDREAM的PART2分区的OBJECT ID

SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID from dba_objects where

OBJECT_NAME='DBDREAM' AND SUBOBJECT_NAME='PART2';
OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID
-------------------- ------------------------------ ----------
DBDREAM              PART2                               46250

9.编辑ODU的control.txt文件
control.txt文件里面存放ODU需要读取的数据库的数据文件信息,格式如下:

[oracle@10205 odu]$ cat control.txt
#ts #fno   #rfno     filename                                          block_size
0       0       0       /u01/app/oracle/oradata/orac/system01.dbf
0       0       0       /u01/app/oracle/oradata/orac/users01.dbf

10.编辑ODU的config.txt文件
config.txt文件存放ODU的参数,本案例参数配置如下,各参数详细说明,详见老熊的BLOG。

[oracle@10205 odu]$ cat config.txt

byte_order little
block_size  8192
db_timezone -7
client_timezone 8
data_path   data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format dmp
lob_storage infile
clob_byte_order little

11.登录ODU

上述文件正确配置后,登录ODU会看到ODU的提示。

[oracle@10205 odu]$ ./odu

Oracle Data Unloader:Release 3.0.8
Copyright (c) 2008,2009 XiongJun. All rights reserved.
Web: http://www.laoxiong.net
Email: magic007cn@gmail.com
loading default config.......
byte_order little
block_size  8192
db_timezone -7
client_timezone 8
data_path   data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format dmp
lob_storage infile
clob_byte_order little
load control file 'config.txt' successful
loading default control file ......
ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
0    1    1  8192    48640 N       0 /u01/app/oracle/oradata/orac/system01.dbf
4    4    4  8192      640 N       0 /u01/app/oracle/oradata/orac/users01.dbf
load control file 'control.txt' successful
loading dictionary data......

12.使用ODU抽取数据字典

ODU> unload dict

CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$'s obj# 266
found TABPART$'s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0
found INDPART$'s obj# 271
found INDPART$'s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0
found TABSUBPART$'s obj# 278
found TABSUBPART$'s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0
found INDSUBPART$'s obj# 283
found INDSUBPART$'s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$'s obj# 151
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$'s obj# 299
found LOBFRAG$'s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0

ODU>
    13.使用ODU扫描数据文件

scan命令用于扫描数据文件中的segment以及extent。主要作用在于没有SYSTEM表空间时的数据恢复,以及TRUNCATE表和DROP表之后的数据恢复。

ODU> scan extent

scan extent start: 2012-11-29 20:11:48
scanning extent...
scanning extent finished.
scan extent completed: 2012-11-29 20:11:48

14.抽取数据

ODU> unload table scott.dbdream object 46250

Unloading table: DBDREAM,object ID: 46248
Unloading segment,storage(Obj#=0 DataObj#=46250 TS#=0 File#=0 Block#=0 Cluster=0)

1 rows unloaded

现在,被TRUNCATE掉的分区PART2的数据就保存在ODU安装目录的data目录下。

[oracle@10205 odu]$ cd data
[oracle@10205 data]$ ls
SCOTT_DBDREAM.dmp

15.恢复被TRUNCATE的数据
只需要使用IMP命令将上面的DMP文件导入到数据库即可完成恢复。

[oracle@10205 data]$ imp scott/tiger file=SCOTT_DBDREAM.dmp ignore=y full=y

Import: Release 10.2.0.5.0 - Production on ?1129 20:13:46 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V08.01.07 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                      "DBDREAM"          1 rows imported
Import terminated successfully without warnings.

16.验证数据

SQL> select * from dbdream;

ID NAME       TEST
---------- ---------- --------------------
1 dbdream    is test
2 stream     yes
3 wind       no
4 chunfeng   ttt

SQL> select * from dbdream partition(part2);

ID NAME       TEST
---------- ---------- --------------------
2 stream     yes

至此,恢复TRUNCATE表测试完成。当然恢复的前提是这部分数据块没有被复写。

本文固定链接: http://www.dbdream.com.cn/2012/12/%e4%bd%bf%e7%94%a8odu%e6%81%a2%e5%a4%8d%e5%88%86%e5%8c%ba%e8%a1%a8%e8%a2%abtruncate%e6%8e%89%e7%9a%84%e5%88%86%e5%8c%ba%e6%b5%8b%e8%af%95/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2012年12月03日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 使用ODU恢复分区表被truncate掉的分区测试 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

使用ODU恢复分区表被truncate掉的分区测试:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter