ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How AUL (MyDUL) recover truncated table's data easily

How AUL (MyDUL) recover truncated table's data easily

原创 Linux操作系统 作者:fengjin821 时间:2009-06-13 15:24:36 0 删除 编辑

    

     I have seems somebody truncated their tables by mistaken several times, because of they connect to wrong database and run the script. without careful check. To save data, the first thing they need to do is stop the database, and take a backup of the tablespace where the tables locate. For truncation, Oracle just reformat the segment header, and other blocks remain untouched, so we are able to recover the data, however if the blocks are touched by oracle again, it will be formatted to hold new rows. Following is an example of how to recover the rows back, let's build a demo table first:

SQL> CREATE TABLE T_TRUNCATE AS SELECT * FROM TAB;

Table created.

SQL> ALTER SYSTEM CHECKPOINT;
SQL> TRUNCATE TABLE T_TRUNCATE;
SQL> ALTER SYSTEM CHECKPOINT;

    The truncated table will be assigned a new data object id, we could get it from the describe command output.

AUL> desc anysql.t_truncate

Storage(OBJ#=9976 OBJD=9977 TS=4 FILE=4 BLOCK=5235 CLUSTER=0)
No. SEQ INT Column Name                   Type
--- --- --- ----------------------------- ----------------
  1   1   1 TNAME                         VARCHAR2(30) NOT NULL
  2   2   2 TABTYPE                       VARCHAR2(7)
  3   3   3 CLUSTERID                     NUMBER

    To recover the rows, we need run scan extent command to generate the extent map to a text file (AULEXT.TXT), because segment header is formatted by Oracle.

AUL> SCAN EXTENT FILE 4
2006-12-18 21:32:10
2006-12-18 21:32:24

    The key is that we need to find out the old data object id, here I shutdown the database after truncate, no new data inputed after truncation. From the previous describe command output, we found that the new data object id is 9977, and segment header is (4,5235), the block next to segment header should be the old data block, we could get the old data object id from this block by ORADUMP command, if we created the table with several free list group, we need to skip more blocks. Following is the ORADUMP output:

AUL> ORADUMP FILE 4 BLOCK 5236


RDBA=0x01001474(4/5236)=16782452,type=0x06,fmt=0xa2,seq=0x02,flag=0x04
seg/obj=0x000026f8=9976,csc=0x0000.0006caf5,itc=3,typ=1 - DATA
FLG=0x32, fls=0, nxt=0x01001471(4/5233)=16782449
......

    We found that the old data object id is 9976, then we start recovery, without specify the old data object id, now rows should be returned:

AUL> unload table anysql.t_truncate;
2006-12-18 21:33:37
Unload BJD=9977 FILE=4 BLOCK=5235 CLUSTER=0 ...
2006-12-18 21:33:37

    Then we specify the old data object id, and do a recover again, all 14 rows are recovered as following:

AUL> unload table anysql.t_truncate object 9976;


2006-12-18 21:33:45
Unload BJD=9976 FILE=4 BLOCK=5235 CLUSTER=0 ...
P_MV_FACT_SALES|TABLE
TIME_DIM|TABLE
FACT_SALES|TABLE
MV_FACT_SALES|TABLE
......

    While database system is really complex, for this recovery, AUL cannot give you a 100% promise of data accuracy.

 

 

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

请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    505024