Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2638688
  • 博文数量: 631
  • 博客积分: 10716
  • 博客等级: 上将
  • 技术积分: 8397
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-01 22:35
文章分类

全部博文(631)

文章存档

2020年(2)

2019年(22)

2018年(4)

2017年(37)

2016年(22)

2015年(1)

2013年(12)

2012年(20)

2011年(19)

2010年(20)

2009年(282)

2008年(190)

分类: Oracle

2019-02-13 11:35:24

--//上个星期做了truncate table的另类恢复,通过修改数据块的段号,再通过rowid定位收集数据,达到修复的目的.
--//实际上不能作为恢复的手段,主要不实用,不过通过学习了解oracle一些基本概念.
--//链接:blog.itpub.net/267265/viewspace-2156936/

--//而且上次涉及的块很少,这次我们反向思考,不修改块的段号,而修改数据字典,来恢复truncate表.
--//注意truncate还是使用reuse storage选项,存储空间不会回收.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t1 as select * from all_objects;
Table created.

SCOTT@test01p> create table t2 as select * from t1;
Table created.

SCOTT@test01p> select rowid,t1.* from t1 where rownum=1;
ROWID              OWNER OBJECT_NAME SUBOBJECT_NAME  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS T G S  NAMESPACE EDITION_NAME SHARING E O
------------------ ----- ----------- -------------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------ - - - ---------- ------------ ------- - -
AAAaSzAAJAAAAC7AAA SYS   I_OBJ1                             36             36 INDEX       2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29 VALID  N N N ##########              NONE      Y

SCOTT@test01p> @ rowid AAAaSzAAJAAAAC7AAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA     TEXT
---------- ---------- ---------- ---------- -------------------- ------- ----------------------------------------
    107699          9        187          0  0x24000BB           9,187   alter system dump datafile 9 block 187 ;

SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='T1';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    107699         107699

SCOTT@test01p> column PARTITION_NAME noprint
SCOTT@test01p> select * from dba_extents where owner=user and segment_name='T1';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- --------- ------- -------- ------- ------ ------------
SCOTT T1           TABLE        USERS                   0       9      184   65536      8            9
SCOTT T1           TABLE        USERS                   1       9      200   65536      8            9
SCOTT T1           TABLE        USERS                   2       9      208   65536      8            9
SCOTT T1           TABLE        USERS                   3       9      232   65536      8            9
SCOTT T1           TABLE        USERS                   4       9      240   65536      8            9
SCOTT T1           TABLE        USERS                   5       9      248   65536      8            9
SCOTT T1           TABLE        USERS                   6       9      256   65536      8            9
SCOTT T1           TABLE        USERS                   7       9      264   65536      8            9
SCOTT T1           TABLE        USERS                   8       9      272   65536      8            9
SCOTT T1           TABLE        USERS                   9       9      280   65536      8            9
SCOTT T1           TABLE        USERS                  10       9      288   65536      8            9
SCOTT T1           TABLE        USERS                  11       9      320   65536      8            9
SCOTT T1           TABLE        USERS                  12       9      328   65536      8            9
SCOTT T1           TABLE        USERS                  13       9      336   65536      8            9
SCOTT T1           TABLE        USERS                  14       9      344   65536      8            9
SCOTT T1           TABLE        USERS                  15       9      352   65536      8            9
SCOTT T1           TABLE        USERS                  16       9      384 1048576    128            9
SCOTT T1           TABLE        USERS                  17       9      512 1048576    128            9
SCOTT T1           TABLE        USERS                  18       9      640 1048576    128            9
SCOTT T1           TABLE        USERS                  19       9      768 1048576    128            9
SCOTT T1           TABLE        USERS                  20       9      896 1048576    128            9
SCOTT T1           TABLE        USERS                  21       9     1024 1048576    128            9
SCOTT T1           TABLE        USERS                  22       9     1152 1048576    128            9
SCOTT T1           TABLE        USERS                  23       9     1280 1048576    128            9
SCOTT T1           TABLE        USERS                  24       9     1408 1048576    128            9
SCOTT T1           TABLE        USERS                  25       9     1536 1048576    128            9
SCOTT T1           TABLE        USERS                  26       9     1664 1048576    128            9
SCOTT T1           TABLE        USERS                  27       9     1920 1048576    128            9
28 rows selected.

SCOTT@test01p> @ 10046on 12
Session altered.

SCOTT@test01p> truncate table t1 reuse storage;
Table truncated.

SCOTT@test01p> @ 10046off
Session altered.

2.首先看看truncate后执行什么命令:

SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='T1';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    107699         107701

--//可以发现truncate后data_object_id=107701发生变化.

SCOTT@book> alter system checkpoint;
System altered.

D:\> egrep  -i "^insert|^update|^delete" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_6320.trc  > b.txt

--//显示b.txt内容看看:

delete from compression_stat$ where dataobj# = :1 and ts# = :2
delete from superobj$ where subobj# = :1
delete from tab_stats$ where obj#=:1

UPDATE tab$
   SET ts# = :2
      ,file# = :3
      ,block# = :4
      ,bobj# = DECODE (:5, 0, NULL, :5)
      ,tab# = DECODE (:6, 0, NULL, :6)
      ,intcols = :7
      ,kernelcols = :8
      ,clucols = DECODE (:9, 0, NULL, :9)
      ,audit$ = :10
      ,flags = :11
      ,pctfree$ = :12
      ,pctused$ = :13
      ,initrans = :14
      ,maxtrans = :15
      ,rowcnt = :16
      ,blkcnt = :17
      ,empcnt = :18
      ,avgspc = :19
      ,chncnt = :20
      ,avgrln = :21
      ,analyzetime = :22
      ,samplesize = :23
      ,cols = :24
      ,property = :25
      ,degree = DECODE (:26, 1, NULL, :26)
      ,instances = DECODE (:27, 1, NULL, :27)
      ,dataobj# = :28
      ,avgspc_flb = :29
      ,flbcnt = :30
      ,trigflag = :31
      ,spare1 = :32
      ,spare2 = DECODE (:33, 0, NULL, :33)
      ,spare4 = :34
      ,spare6 = :35
 WHERE obj# = :1

UPDATE seg$
   SET type# = :4
      ,blocks = :5
      ,extents = :6
      ,minexts = :7
      ,maxexts = :8
      ,extsize = :9
      ,extpct = :10
      ,user# = :11
      ,iniexts = :12
      ,lists = DECODE (:13, 65535, NULL, :13)
      ,groups = DECODE (:14, 65535, NULL, :14)
      ,cachehint = :15
      ,hwmincr = :16
      ,spare1 = DECODE (:17, 0, NULL, :17)
      ,scanhint = :18
      ,bitmapranges = :19
 WHERE ts# = :1 AND file# = :2 AND block# = :3

UPDATE obj$
   SET obj# = :4
      ,type# = :5
      ,ctime = :6
      ,mtime = :7
      ,stime = :8
      ,status = :9
      ,dataobj# = :10
      ,flags = :11
      ,oid$ = :12
      ,spare1 = :13
      ,spare2 = :14
      ,spare3 = :15
      ,signature = :16
      ,spare7 = :17
      ,spare8 = :18
      ,spare9 = :19
 WHERE     owner# = :1
       AND name = :2
       AND namespace = :3
       AND remoteowner IS NULL
       AND linkname IS NULL
       AND subname IS NULL

--//为了显示的需要,我格式化sql语句,这样显示更好看一些.
--//只要是对tab$,seg$,obj$进行了修改.

3.通过修改数据字典看看:
--//首先确定原来的段号,一般如果表没有move或者truncate,开始object_id=data_object_id.也可以转储块看看.
--//使用bbed查看也是一样的.
BBED> p /d dba 9,188 ktbbh.ktbbhsid.
union ktbbhsid, 4 bytes                     @24
   ub4 ktbbhsg1                             @24       107699
   ub4 ktbbhod1                             @24       107699
--//数据段号=107699

UPDATE tab$ set dataobj#=107699  where obj#=107699;
UPDATE seg$ set hwmincr=107699   where hwmincr=107701;
--//好像seg$记录段头,没有注意这个表以前.

SCOTT@test01p> @ pt2 "select * from sys.seg$ where  hwmincr=107701"
ROW_NUM    COL_NUM COL_NAME             COL_VALUE
------- ---------- -------------------- ------------
      1          1 FILE_x0023_          9
                 2 BLOCK_x0023_         186
                 3 TYPE_x0023_          5
                 4 TS_x0023_            3
                 5 BLOCKS               1664
                 6 EXTENTS              28
                 7 INIEXTS              8
                 8 MINEXTS              1
                 9 MAXEXTS              2147483645
                10 EXTSIZE              128
                11 EXTPCT               0
                12 USER_x0023_          109
                13 LISTS                0
                14 GROUPS               0
                15 BITMAPRANGES         2147483645
                16 CACHEHINT            0
                17 SCANHINT             0
                18 HWMINCR              107701
                19 SPARE1               4194561
19 rows selected.
--//
update obj$ set dataobj#=107699 where obj#=107699;

--//执行以上3条update命令以sys用户执行.还原回来原来的数据段号.
SYS@test01p> alter system flush shared_pool;
System altered.

SYS@test01p> alter system flush shared_pool;
System altered.

SYS@test01p> UPDATE tab$ set dataobj#=107699  where obj#=107699;
1 row updated.

SYS@test01p> UPDATE seg$ set hwmincr=107699   where hwmincr=107701;
1 row updated.

SYS@test01p> update obj$ set dataobj#=107699 where obj#=107699;
1 row updated.

SYS@test01p> commit ;
Commit complete.

SYS@test01p> alter system flush shared_pool;
System altered.

4.检查是否能正常显示:
SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@test01p> select rowid,t1.* from t1 where rowid='AAAaSzAAJAAAAC7AAA';
ROWID              OWNER                OBJECT_NAME          SUBOBJECT_NAME        OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE          CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS               T G S  NAMESPACE EDITION_NAME         SHARING       E O
------------------ -------------------- -------------------- -------------------- ---------- -------------- -------------------- ------------------- ------------------- ------------------- -------------------- - - - ---------- -------------------- ------------- - -
AAAaSzAAJAAAAC7AAA SYS                  I_OBJ1                                            36             36 INDEX                2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29 VALID                N N N ##########                      NONE            Y

--//可以发现使用rowid可以查询对应记录.如果写成如下:
SCOTT@test01p> select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB';
select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB'
*
ERROR at line 1:
ORA-08103: object no longer exists

D:\tools\bbed>oerr ora 8103
08103, 00000, "object no longer exists"
// *Cause:  The object has been deleted by another user since the operation
//          began, or a prior incomplete recovery restored the database to
//          a point in time during the deletion of the object.
// *Action: Delete the object if this is the result of an incomplete
//          recovery.

--//你可以发现报错ora-08103,因为数据段头的段号我没有修改,现在是107701.做一个转储就能确定:

SCOTT@test01p> alter system dump datafile 9 block 186 ;
System altered.

--//检查转储:
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 28     #blocks: 1664  
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x024000bb  ext#: 0      blk#: 3      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 0     

*** 2018-06-30 21:01:17.135
      Disk Lock:: Locked by xid:  0x0004.00c.00006195
  --------------------------------------------------------
  Low HighWater Mark : 
      Highwater::  0x024000bb  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: 0x024000b8
  Level 1 BMB for Low HWM block: 0x024000b8
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x024000b9
  Last Level 1 BMB:  0x02400781
  Last Level II BMB:  0x024000b9
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 28   obj#: 107701 flag: 0x10000000
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ===>obj#: 107701
  Inc # 0 
  Extent Map
  -----------------------------------------------------------------
   0x024000b8  length: 8     
   0x024000c8  length: 8     
   0x024000d0  length: 8     
   0x024000e8  length: 8     
   0x024000f0  length: 8     
   0x024000f8  length: 8     
   0x02400100  length: 8     
   0x02400108  length: 8     
   0x02400110  length: 8     
   0x02400118  length: 8     
   0x02400120  length: 8     
   0x02400140  length: 8     
   0x02400148  length: 8     
   0x02400150  length: 8     
   0x02400158  length: 8     
   0x02400160  length: 8     
   0x02400180  length: 128   
   0x02400200  length: 128   
   0x02400280  length: 128   
   0x02400300  length: 128   
   0x02400380  length: 128   
   0x02400400  length: 128   
   0x02400480  length: 128   
   0x02400500  length: 128   
   0x02400580  length: 128   
   0x02400600  length: 128   
   0x02400680  length: 128   
   0x02400780  length: 128   
  
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x024000b8 Data dba:  0x024000bb
   Extent 1     :  L1 dba:  0x024000b8 Data dba:  0x024000c8
   Extent 2     :  L1 dba:  0x024000d0 Data dba:  0x024000d1
   Extent 3     :  L1 dba:  0x024000d0 Data dba:  0x024000e8
   Extent 4     :  L1 dba:  0x024000f0 Data dba:  0x024000f1
   Extent 5     :  L1 dba:  0x024000f0 Data dba:  0x024000f8
   Extent 6     :  L1 dba:  0x02400100 Data dba:  0x02400101
   Extent 7     :  L1 dba:  0x02400100 Data dba:  0x02400108
   Extent 8     :  L1 dba:  0x02400110 Data dba:  0x02400111
   Extent 9     :  L1 dba:  0x02400110 Data dba:  0x02400118
   Extent 10    :  L1 dba:  0x02400120 Data dba:  0x02400121
   Extent 11    :  L1 dba:  0x02400120 Data dba:  0x02400140
   Extent 12    :  L1 dba:  0x02400148 Data dba:  0x02400149
   Extent 13    :  L1 dba:  0x02400148 Data dba:  0x02400150
   Extent 14    :  L1 dba:  0x02400158 Data dba:  0x02400159
   Extent 15    :  L1 dba:  0x02400158 Data dba:  0x02400160
   Extent 16    :  L1 dba:  0x02400180 Data dba:  0x02400182
   Extent 17    :  L1 dba:  0x02400200 Data dba:  0x02400202
   Extent 18    :  L1 dba:  0x02400280 Data dba:  0x02400282
   Extent 19    :  L1 dba:  0x02400300 Data dba:  0x02400302
   Extent 20    :  L1 dba:  0x02400380 Data dba:  0x02400382
   Extent 21    :  L1 dba:  0x02400400 Data dba:  0x02400402
   Extent 22    :  L1 dba:  0x02400480 Data dba:  0x02400482
   Extent 23    :  L1 dba:  0x02400500 Data dba:  0x02400502
   Extent 24    :  L1 dba:  0x02400580 Data dba:  0x02400582
   Extent 25    :  L1 dba:  0x02400600 Data dba:  0x02400602
   Extent 26    :  L1 dba:  0x02400680 Data dba:  0x02400682
   Extent 27    :  L1 dba:  0x02400780 Data dba:  0x02400782
  --------------------------------------------------------
  
   Second Level Bitmap block DBAs 
   --------------------------------------------------------
   DBA 1:   0x024000b9
End dump data blocks tsn: 3 file#: 9 minblk 186 maxblk 186
--//注意看下划线内容.

--//我前面提到执行计划TABLE ACCESS BY ROWID RANGE,注意范围这个关键字.这样要访问段头,因为是truncate表后
--//降低了高水位标识,并且我没有修改数据段号,即使我修改数据段号=107699.
--//oracle认为要访问的数据块在高水位之上,一样没有结果集.
--//而前面的执行计划是TABLE ACCESS BY USER ROWID.直接通过rowid访问块.越过了数据段头的访问.

5.有了以上思路,应该可以恢复全部记录.
--//实际上就是当段头损坏.

SCOTT@test01p> CREATE TABLE t1new tablespace lfree  AS SELECT * FROM t1 where  1=0;
Table created.

--//网上找到的脚本,参考链接:http://www.xifenfei.com/3898.html
--//修改owner以及对应表名.

set serveroutput on
set concat off
DECLARE
 nrows number;
 rid rowid;
 dobj number;
 ROWSPERBLOCK number;
BEGIN
 ROWSPERBLOCK:=736;  --估算最大的一个块中记录条数,8K最多736条记录1块.
 nrows:=0; 

 select data_object_id  into dobj
 from dba_objects
 where owner = 'SCOTT'
 and object_name = 'T1'
-- and subobject_name = ''  Add this condition if table is partitioned
 ;

 for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
           from dba_extents
           where owner = 'SCOTT'
             and segment_name = 'T1'
-- and partition_name = '
' Add this condition if table is partitioned
-- and file_id != This condition is only used if a datafile needs to be skipped due to ORA-376 (A)
          order by extent_id)
 loop
   for br in i.block_id..i.totblocks loop
    for j in 1..ROWSPERBLOCK loop
    begin
      rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
      insert into t1new
      select /*+ ROWID(A) */ *
      from t1 A
      where rowid = rid;
      if sql%rowcount = 1 then nrows:=nrows+1; end if;
      if (mod(nrows,10000)=0) then commit; end if;
    exception when others then null;
    end;
    end loop;
  end loop;
 end loop;
 COMMIT;
 dbms_output.put_line('Total rows:'||to_char(nrows));
END;
/ 

--//再执行前必须修改dba=9,184 到 9,186块中的段号=107699,不然
--//select * from dba_extents where owner=user and segment_name='T1';没有显示.无法恢复.

SCOTT@test01p> @ 10to16 107701
10 to 16 HEX      REVERSE16
----------------- ----------------------------------
000000000001a4b5  0xb5a40100-00000000

SCOTT@test01p> @ 10to16 107699
10 to 16 HEX      REVERSE16
----------------- -----------------------------------
000000000001a4b3  0xb3a40100-00000000

--//修改0xb5a40100 => 0xb3a40100

--//修改dba=9,184
BBED> set dba 9,185
        DBA             0x024000b9 (37748921 9,185)
--//注:我使用windwos的bbed,修改块存在1个数据块偏移.后面不再说明.实际上修改9,184.

BBED> set offset 0
        OFFSET          0

BBED> find /x b5a40100 top
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
 Block: 185                                                  Offsets:  192 to  201                                               Dba:0x024000b9
------------------------------------------------------------------------------------------------------------------------------------------------
 b5a40100 f6f4a501 0000

 <64 bytes per line>

BBED> find
BBED-00212: search string not found

BBED> modify /x b3a4 offset 192
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
 Block: 185                                                  Offsets:  192 to  201                                               Dba:0x024000b9
------------------------------------------------------------------------------------------------------------------------------------------------
 b3a40100 f6f4a501 0000

 <64 bytes per line>

BBED> sum apply dba 9,185
Check value for File 9, Block 185:
current = 0xa553, required = 0xa553

--//修改dba=9,185
BBED> set dba 9,186
        DBA             0x024000ba (37748922 9,186)

BBED> set offset 0
        OFFSET          0

BBED> find /x b5a40100 top
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
 Block: 186                                                  Offsets:  104 to  113                                               Dba:0x024000ba
------------------------------------------------------------------------------------------------------------------------------------------------
 b5a40100 01000000 0000

 <64 bytes per line>

BBED> f
BBED-00212: search string not found

BBED> modify /x b3a4 offset 104
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
 Block: 186                                                  Offsets:  104 to  113                                               Dba:0x024000ba
------------------------------------------------------------------------------------------------------------------------------------------------
 b3a40100 01000000 0000

 <64 bytes per line>

BBED> sum apply dba 9,186
Check value for File 9, Block 186:
current = 0x4215, required = 0x4215

--//修改dba=9,186
BBED> sum apply dba 9,186
Check value for File 9, Block 186:
current = 0x4215, required = 0x4215

BBED> set dba 9,187
        DBA             0x024000bb (37748923 9,187)

BBED> set offset 0
        OFFSET          0

BBED> find /x b5a40100 top
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
 Block: 187                                                  Offsets:  272 to  281                                               Dba:0x024000bb
------------------------------------------------------------------------------------------------------------------------------------------------
 b5a40100 00000010 b800

 <64 bytes per line>

BBED> f
BBED-00212: search string not found


BBED> modify /x b3a4 offset 272
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
 Block: 187                                                  Offsets:  272 to  281                                               Dba:0x024000bb
------------------------------------------------------------------------------------------------------------------------------------------------
 b3a40100 00000010 b800

 <64 bytes per line>

BBED> sum apply dba 9,187
Check value for File 9, Block 187:
current = 0x6ac0, required = 0x6ac0

--//OK现在修改完成.select * from dba_extents where owner=user and segment_name='T1';也能正常显示.
--//执行修复脚本,等,脚本很慢,主要块有点多,而且不管什么块都做这样的操作,
--//另外每块假设的记录值736有点高,估计200比较合适.对于当前的表.

SCOTT@test01p> alter system flush shared_pool;
System altered.

SCOTT@test01p> select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB';
no rows selected
--//rowid range 查询一样没有结果,验证了我前面的判断.

SCOTT@test01p> @ aa.txt
Total rows:89859
PL/SQL procedure successfully completed.
--//我这里大约5分钟,有点慢...^_^.

SCOTT@test01p> select * from t1new minus select * from t2;
no rows selected

SCOTT@test01p> select * from t2 minus select * from t1new;
no rows selected

SCOTT@test01p> select count(*) from t1new;
  COUNT(*)
----------
     89859

--//OK没有丢失数据.

6.总结:
--//这样方法不适合一般的恢复,仅仅作为探究与学习.
--//从测试可以看出做好备份才是关键.
--//另外出现问题,如果真是那种没有备份没有归档的数据库,如果真出现这样的问题.
--//最好的方法停库,做一个冷备份.如果不行,直接拷贝对应表空间的数据文件也是一个可行的方法.
--//或者先给truncate的表改名,建立同名表在不同表空间,避免覆盖,最大程度恢复truncate的数据.

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

阅读(3064) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~