ITPub博客

首页 > Linux操作系统 > Linux操作系统 > odu truncate table

odu truncate table

原创 Linux操作系统 作者:g644516804 时间:2012-06-25 15:29:46 0 删除 编辑

下载odu测试软件http://www.oracleodu.com/cn/download

 安装odu

$gunzip ...

$tart -xvf ..

1. 创建表空间 user table

alter system checkpoint;

将更新写入数据字典中

2. 将system数据文件位置配置进ODU的control.txt文件:
可以预先查询数据字典设置内容:
select d.TS#        ts,
       d.FILE#      fno,
       d.FILE#      fno,
       d.NAME       filename,
       d.BLOCK_SIZE block_size
  from v$datafile d

cat control.txt

0       1       1       /oradata/STCSMES/system01.dbf   8192
2       3       3       /oradata/STCSMES/sysaux01.dbf   8192
4       4       4       /oradata/STCSMES/users01.dbf    8192

.............
注意:要将system表空间的第一个datafile写在第一行,否则会报:

can not get bootstrap$ address from SYSTEM tablespace

3. 进入odu界面

$./odu

$ODU>open

$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> desc lerry.t1


Object ID:110066
Storage(Obj#=110066 DataObj#=110066 TS#=10 File#=76 Block#=11 Cluster=0)

NO. SEG INT Column Name                    Null?     Type                      
--- --- --- ------------------------------ --------- ------------------------------
  1   1   1 OWNER                          NOT NULL  VARCHAR2(30)              
  2   2   2 TABLE_NAME                     NOT NULL  VARCHAR2(30)              
  3   3   3 TABLESPACE_NAME                          VARCHAR2(30)              
  4   4   4 CLUSTER_NAME                             VARCHAR2(30)              
  5   5   5 IOT_NAME                                 VARCHAR2(30)              
  6   6   6 STATUS                                   VARCHAR2(8)               
  7   7   7 PCT_FREE                                 NUMBER                    
  8   8   8 PCT_USED                                 NUMBER                    
  9   9   9 INI_TRANS                                NUMBER                    
 10  10  10 MAX_TRANS                                NUMBER                    
 11  11  11 INITIAL_EXTENT                           NUMBER                    
 12  12  12 NEXT_EXTENT                              NUMBER                    
 13  13  13 MIN_EXTENTS                              NUMBER                    
 14  14  14 MAX_EXTENTS                              NUMBER                    
 15  15  15 PCT_INCREASE                             NUMBER                    
 16  16  16 FREELISTS                                NUMBER                    
 17  17  17 FREELIST_GROUPS                          NUMBER                    
 18  18  18 LOGGING                                  VARCHAR2(3)               
 19  19  19 BACKED_UP                                VARCHAR2(1)               
 20  20  20 NUM_ROWS                                 NUMBER                    
 21  21  21 BLOCKS                                   NUMBER                    
 22  22  22 EMPTY_BLOCKS                             NUMBER                    
 23  23  23 AVG_SPACE                                NUMBER                    
 24  24  24 CHAIN_CNT                                NUMBER                    
 25  25  25 AVG_ROW_LEN                              NUMBER                    
 26  26  26 AVG_SPACE_FREELIST_BLOCKS                NUMBER                    
 27  27  27 NUM_FREELIST_BLOCKS                      NUMBER                    
 28  28  28 DEGREE                                   VARCHAR2(30)              
 29  29  29 INSTANCES                                VARCHAR2(30)              
 30  30  30 CACHE                                    VARCHAR2(15)              
 31  31  31 TABLE_LOCK                               VARCHAR2(8)               
 32  32  32 SAMPLE_SIZE                              NUMBER                    
 33  33  33 LAST_ANALYZED                            DATE                      
 34  34  34 PARTITIONED                              VARCHAR2(3)               
 35  35  35 IOT_TYPE                                 VARCHAR2(12)              
 36  36  36 TEMPORARY                                VARCHAR2(1)               
 37  37  37 SECONDARY                                VARCHAR2(1)               
 38  38  38 NESTED                                   VARCHAR2(3)               
 39  39  39 BUFFER_POOL                              VARCHAR2(7)               
 40  40  40 ROW_MOVEMENT                             VARCHAR2(8)               
 41  41  41 GLOBAL_STATS                             VARCHAR2(3)               
 42  42  42 USER_STATS                               VARCHAR2(3)               
 43  43  43 DURATION                                 VARCHAR2(15)              
 44  44  44 SKIP_CORRUPT                             VARCHAR2(8)               
 45  45  45 MONITORING                               VARCHAR2(3)               
 46  46  46 CLUSTER_OWNER                            VARCHAR2(30)              
 47  47  47 DEPENDENCIES                             VARCHAR2(8)               
 48  48  48 COMPRESSION                              VARCHAR2(8)               
 49  49  49 DROPPED                                  VARCHAR2(3)               

ODU> scan extent tablespace 10

scan extent start: 2012-06-25 15:28:45
scanning extent...
scanning extent finished.
scan extent completed: 2012-06-25 15:28:45

ODU> dump datafile 76 block 11
Block Header:
block type=0x23 (ASSM segment header block)
block format=0xa2 (oracle 10+)
block rdba=0x1300000b (file#=76, block#=11)
scn=0x0937.ad4a93f1, seq=1, tail=0x93f12301
block checksum value=0xa201=41473, flag=4
Data Segment Header:
  Extent Control Header
  -------------------------------------------------------------
  Extent Header:: extents: 1  blocks: 8
                  last map: 0x00000000  #maps: 0  offset: 2716
      Highwater:: 0x1300000f  (rfile#=76,block#=15)
                  ext#: 0  blk#: 6   ext size:8
      #blocks in seg. hdr's freelists: 0
      #blocks below: 6
      mapblk: 0x00000000   offset: 0
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x1300000f  ext#: 0      blk#: 6      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 6
  mapblk  0x00000000  offset: 0
  Level 1 BMB for High HWM block: 0x13000009
  Level 1 BMB for Low HWM block: 0x13000009
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x1300000a
  Last Level 1 BMB:  0x13000009
  Last Level 1I BMB:  0x1300000a
  Last Level 1II BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 110066  flag: 0x210000000
  Extent Map
  -------------------------------------------------------------
   0x13000009  length: 8

  Auxillary Map
  -------------------------------------------------------------
   Extent 0      :  L1 dba:  0x13000009 Data dba:  0x1300000c
  -------------------------------------------------------------

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

ODU> unload table lerry.t1 object 110066

Unloading table: T1,object ID: 110066
Unloading segment,storage(Obj#=110066 DataObj#=110066 TS#=10 File#=76 Block#=11 Cluster=0)
3 rows unloaded

---会在data目录下生气三个文件 .ctl .sql .txt

ODU> unload table lerry.t1

Unloading table: T1,object ID: 110066
Unloading segment,storage(Obj#=110066 DataObj#=110066 TS#=10 File#=76 Block#=11 Cluster=0)
3 rows unloaded

4. 利用sqlldr恢复数据

$sqlldr lerry/lerry control=LERRY_T1.ctl


SQL*Loader: Release 10.2.0.4.0 - Production on Mon Jun 25 15:41:45 2012

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

Commit point reached - logical record count 3

到此数据恢复成功

但是如果是利用odu测试版本 只能恢复一部分数据。。。

当出现如下情况:加上object之后数据不相同或是加上object之后data下没有LERRY_TT.三个文件时,可以去除object

ODU> unload table lerry.tt

Unloading table: TT,object ID: 110067
Unloading segment,storage(Obj#=110067 DataObj#=110068 TS#=0 File#=1 Block#=98785 Cluster=0)
0 rows unloaded

ODU> unload table lerry.tt object 110067

Unloading table: TT,object ID: 110067
Unloading segment,storage(Obj#=110067 DataObj#=110067 TS#=0 File#=1 Block#=98785 Cluster=0)
2 rows unloaded

在data下:

$cp ODU_0000110067.txt LERRY_TT.txt

将ODU_0000OBJECT_ID.txt. 拷贝成 LERRY_TT.txt

[oracle@app-rma data]$ sqlldr lerry/lerry control=LERRY_TT.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Jun 25 15:49:23 2012

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

Commit point reached - logical record count 2

 ODU正式版和试用版的区别:试用版仅用于测试、学习和验证,只能恢复SYSTEM表空间下的数据,对于其他表空间的数据,仅恢复少量的数据以验证数据可恢复。而正式版在获取LICENSE后能够恢复所有能够恢复的数据

http://www.laoxiong.net/odu_quick_start.html

http://www.laoxiong.net/odu_command_explain_part2.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24849178/viewspace-733744/,如需转载,请注明出处,否则将追究法律责任。

下一篇: odu 恢复drop table
请登录后发表评论 登录
全部评论

注册时间:2011-03-04

  • 博文量
    104
  • 访问量
    230631