使用ODU恢复分区表被truncate掉的分区测试
Dec032012
最近一个客户由于误操作，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表测试完成。当然恢复的前提是这部分数据块没有被复写。
使用ODU恢复分区表被truncate掉的分区测试：等您坐沙发呢！
发表评论
