ODU恢复测试一：truncate表恢复

1.准备环境：

使用ODU 为3.0.9 FOR winodws
C:\Users\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 19 15:51:09 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create user bamuta identified by bamuta ;

用户已创建。

SQL> alter user bamuta default tablespace users;

用户已更改。

SQL> grant connect,resource to bamuta;

授权成功。
SQL> grant dba to bamuta;

SQL> conn bamuta/bamuta;
已连接。
SQL> create table odu_test as select * from dba_objects;

表已创建。

SQL>
SQL> select count(*) from odu_test;

  COUNT(*)
----------
     72259

2.测试truncate表的恢复：

可以参考下面的文章：
http://www.laoxiong.net/odu_quick_start.html
http://www.laoxiong.net/odu_recover_truncated_table.html
2.1做truncate
SQL> truncate table odu_test;

表被截断。

SQL> alter system checkpoint;

系统已更改。

2.2 ODU使用过程：

offline表空间
SQL> select tablespace_name from user_tables where table_name='ODU_TEST';

TABLESPACE_NAME
------------------------------------------------------------
USERS

SQL> alter tablespace users offline;

表空间已更改。

让ODU能读到最新的数据字典。
SQL> alter system checkpoint;

系统已更改。

 

配置config.txt，如下：

byte_order little
block_size  8192
data_path   data
lob_path    lob
charset_name ZHS16GBK
ncharset_name AL32UTF8
output_format text
lob_storage file
clob_byte_order little
trace_level 1
delimiter |

SQL> select ts#,file_id fno#,file_id rfno#,file_name,value
  2  from V$tablespace t,dba_data_files d,v$parameter p
  3  where t.name=d.tablespace_name
  4  and p.name='db_block_size';

       TS#       FNO#      RFNO# FILE_NAME                                                                        VALUE
---------- ---------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         0          1          1 G:\APP\ADMINISTRATOR\ORADATA\TAN01\SYSTEM01.DBF                                  8192
         1          2          2 G:\APP\ADMINISTRATOR\ORADATA\TAN01\SYSAUX01.DBF                                  8192
         2          3          3 G:\APP\ADMINISTRATOR\ORADATA\TAN01\UNDOTBS01.DBF                                 8192
         4          4          4 G:\APP\ADMINISTRATOR\ORADATA\TAN01\USERS01.DBF                                   8192

SQL>

配置control，如下：

#ts  #fno   #rfno     filename                                          block_size
         0          1          1 G:\APP\ADMINISTRATOR\ORADATA\TAN01\SYSTEM01.DBF                                  8192
         1          2          2 G:\APP\ADMINISTRATOR\ORADATA\TAN01\SYSAUX01.DBF                                  8192
         2          3          3 G:\APP\ADMINISTRATOR\ORADATA\TAN01\UNDOTBS01.DBF                                 8192
         4          4          4 G:\APP\ADMINISTRATOR\ORADATA\TAN01\USERS01.DBF                                   8192

 

ODU使用：

 

ODU> open

 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ ----------------------------------------
----
   0    1    1  8192    89600 N       0 G:\APP\ADMINISTRATOR\ORADATA\TAN01\SYSTE
M01.DBF
   1    2    2  8192    76800 N       0 G:\APP\ADMINISTRATOR\ORADATA\TAN01\SYSAU
X01.DBF
   2    3    3  8192    65920 N       0 G:\APP\ADMINISTRATOR\ORADATA\TAN01\UNDOT
BS01.DBF
   4    4    4  8192     1280 N       0 G:\APP\ADMINISTRATOR\ORADATA\TAN01\USERS
01.DBF
load control file 'control.txt' successful
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ file_no: 1 block_no: 240
CLUSTER C_OBJ# file_no: 1 block_no: 144
CLUSTER C_OBJ# file_no: 1 block_no: 144
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found TABPART$'s obj# 576
found TABPART$'s dataobj#:576,ts#:0,file#:1,block#:3824,tab#:0
found INDPART$'s obj# 581
found INDPART$'s dataobj#:581,ts#:0,file#:1,block#:3872,tab#:0
found TABSUBPART$'s obj# 588
found TABSUBPART$'s dataobj#:588,ts#:0,file#:1,block#:3928,tab#:0
found INDSUBPART$'s obj# 593
found INDSUBPART$'s dataobj#:593,ts#:0,file#:1,block#:3968,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found LOB$'s obj# 80
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6
found LOBFRAG$'s obj# 609
found LOBFRAG$'s dataobj#:609,ts#:0,file#:1,block#:4096,tab#:0
ODU>

ODU> desc bamuta.odu_test

Object ID:73639
Storage(Obj#=73639 DataObj#=73640 TS#=4 File#=4 Block#=130Cluster=0)

NO. SEG INT Column Name                    Null?     Type

--- --- --- ------------------------------ --------- ---------------------------
---
  1   1   1 OWNER                                    VARCHAR2(30)

  2   2   2 OBJECT_NAME                              VARCHAR2(128)

  3   3   3 SUBOBJECT_NAME                           VARCHAR2(30)

  4   4   4 OBJECT_ID                                NUMBER

  5   5   5 DATA_OBJECT_ID                           NUMBER

  6   6   6 OBJECT_TYPE                              VARCHAR2(19)

  7   7   7 CREATED                                  DATE

  8   8   8 LAST_DDL_TIME                            DATE

  9   9   9 TIMESTAMP                                VARCHAR2(19)

 10  10  10 STATUS                                   VARCHAR2(7)

 11  11  11 TEMPORARY                                VARCHAR2(1)

 12  12  12 GENERATED                                VARCHAR2(1)

 13  13  13 SECONDARY                                VARCHAR2(1)

 14  14  14 NAMESPACE                                NUMBER

 15  15  15 EDITION_NAME                             VARCHAR2(30)

ODU> scan extent tablespace 4

scan extent start: 2012-07-19 16:38:50
scanning extent...
scanning extent finished.
scan extent completed: 2012-07-19 16:38:51

ODU> dump datafile 4 block 130
Block Header:
block type=0x23 (ASSM segment header block)
block format=0xa2 (oracle 10)
block rdba=0x01000082 (file#=4, block#=130)
scn=0x0000.00116e62, seq=3, tail=0x6e622303
block checksum value=0xb769=46953, flag=4
Data Segment Header:
  Extent Control Header
  -------------------------------------------------------------
  Extent Header:: extents: 1  blocks: 8
                  last map: 0x00000000  #maps: 0  offset: 2716
      Highwater:: 0x01000083  (rfile#=4,block#=131)
                  ext#: 0  blk#: 3   ext size:8
      #blocks in seg. hdr's freelists: 0
      #blocks below: 0
      mapblk: 0x00000000   offset: 0
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01000083  ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  Level 1 BMB for High HWM block: 0x01000080
  Level 1 BMB for Low HWM block: 0x01000080
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x01000081
  Last Level 1 BMB:  0x01000080
  Last Level 1I BMB:  0x01000081
  Last Level 1II BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 73640  flag: 0x21000000
0
  Extent Map
  -------------------------------------------------------------
   0x01000080  length: 8

  Auxillary Map
  -------------------------------------------------------------
   Extent 0      :  L1 dba:  0x01000080 Data dba:  0x01000083
  -------------------------------------------------------------

   Second Level Bitmap block DBAs
  -------------------------------------------------------------
   DBA 1:   0x01000081

ODU>
ODU> dump datafile 4 block 131 header
Block Header:
block type=0x06 (table/index/cluster segment data block)
block format=0xa2 (oracle 10)
block rdba=0x01000083 (file#=4, block#=131)
scn=0x0000.00116d84, seq=2, tail=0x6d840602
block checksum value=0x37c5=14277, flag=4
Data Block Header Dump:
 Object id on Block? Y
 seg/obj: 0x11fa7=73639  csc: 0x00.116d7d  itc: 3  flg: E  typ: 1 (data)
     brn: 0  bdba: 0x1000080 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00116d7d
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
Data Block Dump:
================
flag=0x0 --------
ntab=1
nrow=88
frre=-1
fsbo=0xc2
ffeo=0x432
avsp=0x370
tosp=0x370
ODU> unload table bamuta.odu_test object 73639

Unloading table: ODU_TEST,object ID: 73639
Unloading segment,storage(Obj#=73639 DataObj#=73639 TS#=4 File#=4 Block#=130 Cluster=0)
72259 rows unloaded

ODU>

恢复验证：

SQL> alter tablespace users online;

表空间已更改。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Users\Administrator>sqlldr bamuta/bamuta control=D:\Downloads\odu\data\ODU_0000073639.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 7月 19 16:45:47 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-941:  在描述表 "ODU_0000073639" 时出错
ORA-04043: 对象 "ODU_0000073639" 不存在

这是因为默认是要导入ODU_0000073639导，需要，创建，如下：

SQL> @ODU_0000073639.sql;

表已创建。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

D:\Downloads\odu\data>sqlldr bamuta/bamuta control=ODU_0000073639.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 7月 19 17:01:07 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

达到提交点 - 逻辑记录计数 5302
达到提交点 - 逻辑记录计数 10604
达到提交点 - 逻辑记录计数 15906
达到提交点 - 逻辑记录计数 21208
达到提交点 - 逻辑记录计数 26510
达到提交点 - 逻辑记录计数 31812
达到提交点 - 逻辑记录计数 37114
达到提交点 - 逻辑记录计数 42416
达到提交点 - 逻辑记录计数 47718
达到提交点 - 逻辑记录计数 53020
达到提交点 - 逻辑记录计数 58322
达到提交点 - 逻辑记录计数 63624
达到提交点 - 逻辑记录计数 68706
达到提交点 - 逻辑记录计数 72259

 

SQL> select count(*) from odu_test;

  COUNT(*)
----------
         0

SQL> select count(*) from ODU_0000073639;

  COUNT(*)
----------
     72259

SQL>
数据已经恢复到了表0000073639，可以轻松导入到odu_test了；

 

SQL> insert into odu_test  select * from ODU_0000073639;

已创建72259行。

SQL> commit;

提交完成。