虽然10g及以上版本的Oracle数据库,提供了recyclebin(回收站)功能,可以找回被drop的表。但是仍然存在着很多8i、9i的库以及没有开启recyclebin功能、drop时直接purge操作等,这样的情况下,如果想找回被意外drop的表,常规的手段是通过备份来恢复。如果没有备份,那就没有办法来恢复了。不过ODU提供了一个可能,在没有备份的情况下,恢复被drop表的数据。

下面通过一个示例来演示如何使用ODU来恢复被drop的表。
首先创建一个测试表:

SQL> create table odu_test ( a number,b varchar2(10),c nvarchar2(30),d varchar2(20),e date,f timestamp,g binary_float,h binary_double);

Table created.

SQL> insert into odu_test select rownum,lpad('x',10),'NC测试' || rownum, 'ZHS测试'|| rownum,sysdate+dbms_random.value(0,100),systimestamp+dbms_random.value(0,100),rownum+dbms_random.value(0,10000),rownum+dbms_random.value(0,10000) from dba_objects where rownum<=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create table t1 as select * from odu_test;

Table created.
SQL> drop table odu_test purge;

Table dropped.

在发现重要的表被意外drop掉的时候,应该立即停止应用,offline那个表所在的表空间或关闭数据库。这里odu_test表是建在users表空间下,先将users表空间offline:

SQL> alter tablespace users offline;

Tablespace altered.

然后需要使用logminer来查找被drop表的data object id:

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> col member for a50
SQL> select member from v$logfile where group#=3;

MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/xty/redo03.log

SQL> exec sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/xty/redo03.log');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.
SQL> select scn,timestamp,sql_redo from v$logmnr_contents where operation='DDL' and sql_redo like '%odu_test%' order by 2 ;

SCN TIMESTAMP SQL_REDO
---------- ------------------- ----------------------------------------------------------------------
    681455 2009-05-08 11:20:50 create table odu_test ( a number,b varchar2(10),c nvarchar2(30),d varc
                               har2(20),e date,f timestamp,g binary_float,h binary_double);

    681521 2009-05-08 11:21:17 create table t1 as select * from odu_test;
    681567 2009-05-08 11:21:34 drop table odu_test purge;

SQL> select scn,timestamp,sql_redo from v$logmnr_contents where timestamp=to_date('2009-05-08 11:21:34','yyyy-mm-dd hh24:mi:ss') order by 1;

       SCN  SQL_REDO
----------  ----------------------------------------------------------------------
    681566  set transaction read write;
    681567  drop table odu_test purge;
    681569  Unsupported
    681570  Unsupported
    681570 
    681570 
    681570 
    681570  Unsupported
    681570 
    681570 
    681570 
    681570  Unsupported
    681570 
    681570 
    681570 
    681570  Unsupported
    681570 
    681570 
    681570 
    681570 
    681570 
    681570 
    681570  Unsupported
    681570  Unsupported
    681570 
    681570 
    681570 
    681570  Unsupported
    681570 
    681570 
    681570 
    681570  Unsupported
    681570 
    681570 
    681570 
    681571  Unsupported
    681572 
    681572  delete from "SYS"."OBJ$" where "OBJ#" = '52230' and "DATAOBJ#" = '5223
            0' and "OWNER#" = '57' and "NAME" = 'ODU_TEST' and "NAMESPACE" = '1' a
            nd "SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('2009-05-
            08 11:20:46', 'yyyy-mm-dd hh24:mi:ss') and "MTIME" = TO_DATE('2009-05-
            08 11:20:46', 'yyyy-mm-dd hh24:mi:ss') and "STIME" = TO_DATE('2009-05-
            08 11:20:46', 'yyyy-mm-dd hh24:mi:ss') and "STATUS" = '1' and "REMOTEO
            WNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID$" IS N
            ULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS NULL and "SP
            ARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'A
            AAAASAABAAAMzdAAS';

    681572 
    681573  commit;
    681574  set transaction read write;
    681574  Unsupported
    681576  commit;
    681577  set transaction read write;
    681579  Unsupported
    681581  commit;

SQL> exec sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

从SCN为681572的几行中,delete from ”SYS”.”OBJ$” where ”OBJ#” = ’52230′ and ”DATAOBJ#” = ’52230′ 可以看到被drop表的data object id为52230。

下面我们使用ODU来恢复这个被删除的表:

[oracle@xty odu]$ ./odu

Oracle Data Unloader:Release 2.6.0

Copyright (c) 2008,2009 XiongJun. All rights reserved.

Web: http://www.laoxiong.net
Email: magic007cn@gmail.com

loading default config.......

 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
   0    1    1  8192    62720 N       0 /u01/oradata/xty/system01.dbf
   1    2    2  8192    26240 N       0 /u01/oradata/xty/undotbs01.dbf
   2    3    3  8192    32000 N       0 /u01/oradata/xty/sysaux01.dbf
   4    4    4  8192      800 N       0 /u01/oradata/xty/users01.dbf
load control file 'control.txt' successful
loading dictionary data......

这里假设我们不知道这个表有多少列,每个列的数据类型,我们可以通过ODU的抽样来自动判断数据的类型:

ODU> scan extent tablespace 4;

scanning extent...
scanning extent finished.

ODU> unload object 52230 sample

Unloading Object,object ID: 52230, Cluster: 0
output data is in file : 'data/ODU_ODU_0000052230.txt'

Sample result:
  object id: 52230
  tablespace no: 4
  sampled 1056 rows
  column count: 8
  column    1  type: NUMBER
  column    2  type: VARCHAR2
  column    3  type: NVARCHAR2
  column    4  type: VARCHAR2
  column    5  type: DATE
  column    6  type: DATE
  column    7  type: BINARY_FLOAT
  column    8  type: BINARY_DOUBLE

COMMAND:
unload object 52230 tablespace 4 column NUMBER VARCHAR2 NVARCHAR2 VARCHAR2 DATE DATE BINARY_FLOAT BINARY_DOUBLE

可以看到,ODU比较准确地判断出了列类型,甚至连NVARCHAR类型都判断出来了。只是由于测试数据的原因,TIMESTAMP那一列按DATE类型进行了存储(只有7字节长),所以被判断成了DATE类型,但是在这里不影响数据的恢复。从输出的内容可以看到,可以在 'data/ODU_ODU_0000052230.txt' 中看到抽样的数据,同时可以在’data/sample.txt‘中看到更详细的抽样输出。
现在我们用ODU来恢复数据:

ODU> unload object 52230 tablespace 4 column NUMBER VARCHAR2 NVARCHAR2 VARCHAR2 DATE DATE BINARY_FLOAT BINARY_DOUBLE

Unloading Object,object ID: 52230, Cluster: 0

现在我们ONLINE USERS表空间,导入恢复的数据。
首先修改一下生成的SQL文件‘ODU_ODU_0000052230.sql’,并创建表:

SQL> CREATE TABLE "TEST"."T2"
  2  (
  3      "C0001" NUMBER ,
  4      "C0002" VARCHAR2(4000) ,
  5      "C0003" NVARCHAR2(2000) ,
  6      "C0004" VARCHAR2(4000) ,
  7      "C0005" DATE ,
  8      "C0006" DATE ,
  9      "C0007" BINARY_FLOAT ,
 10      "C0008" BINARY_DOUBLE 
 11  );

Table created.

修改一下生成的ODU_ODU_0000052230.ctl文件中导入数据的用户名和表名,然后使用sqlldr导入数据:

export NLS_LANG=american_america.zhs16gbk
[oracle@xty data]$ sqlldr test/test control=ODU_ODU_0000052230.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Fri May 8 12:19:34 2009

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

Commit point reached - logical record count 630
Commit point reached - logical record count 1260
Commit point reached - logical record count 1890
Commit point reached - logical record count 2520
Commit point reached - logical record count 3150
Commit point reached - logical record count 3780
Commit point reached - logical record count 4410
Commit point reached - logical record count 5040
Commit point reached - logical record count 5670
Commit point reached - logical record count 6300
Commit point reached - logical record count 6930
Commit point reached - logical record count 7560
Commit point reached - logical record count 8190
Commit point reached - logical record count 8820
Commit point reached - logical record count 9450
Commit point reached - logical record count 10000

对比数据,可以发现binary_double列存在精度上的差异,其他的数据完全匹配。如果数据导出为DMP文件格式,则不会受精度影响。至此数据已经完全恢复。

附:由于binary_float和binary_double是IEEE-754标准的数据类型,CPU直接支持这两种类型的表示和运算,比ORACLE的Number类型,速度更快,但是会导致精度上的差异。ODU默认输出精度是6位(也就是小数点后面6位数字),新版本(从2.6.1开始,截止到本文还没有发布)更改为10位精度。

,
Trackback

20 comments untill now

  1. 太强悍了,赞一个!

    老熊 Reply:

    崔大哥你太过奖了。

  2. cityvigil @ 2009-05-21 10:51

    HI:
    大侠 drop table可以用odu恢复
    那么 drop column 能否也可以恢复.
    :)

    老熊 Reply:

    这个不能恢复,因为在数据块中这一列的数据已经被删除了。

  3. cityvigil @ 2009-05-21 23:24

    了解 3Q!

  4. 如果是set unused column, 则是可以恢复.

    老熊 Reply:

    dcba大侠也来了。
    set unused column只是在数据字典里面对列做了个标记,数据还在块里面,是可以恢复。

  5. 非常感谢老熊给了我们提供这样的方法和理念,我今天试验了一下SQL> select scn,timestamp,sql_redo from
    v$logmnr_contents where operation=’DDL’
    and sql_redo like ‘%pim_card_test%’ order by 2 ;
    或者timestamp=to_date(‘2010-01-18′,’yyyy-mm-dd’)
    没有找到data object id;有没有找不到的情况

    老熊 Reply:

    @陶仪,
    你这里timestamp只指定了日期,而没有时间,显然精度不够。可以指定一个范围。

    另外,sql_redo这里,是否会存在大小写的问题。试试
    upper(sql_redo) like ‘%PIM_CARD_TEST%’

  6. 熊哥:
    CREATE TABLE “ODU_0000055967″
    (
    “C0001″ NUMBER ,
    “C0002″ VARCHAR2(4000) ,
    “C0003″ NVARCHAR2(2000) ,
    “C0004″ VARCHAR2(4000) ,
    “C0005″ DATE ,
    “C0006″ DATE ,
    “C0007″ BINARY_FLOAT ,
    “C0008″ BINARY_DOUBLE
    );
    恢复后,表的列名,都成了 C0001,C0002…之类的,是否能恢复成原来的列名?

    老熊 Reply:

    @寒, 因为表删除后,数据字典中已经没有了表的列信息,自然就没办法知道列的名称、类型、长度了。

  7. 刚刚测试了一下,可以利用LOGMNR找到DROP TABLE时刻的SCN ,然后将SYS.COL$闪回到DROP之前就可以找到列名了。

    SQL> SELECT NAME,TYPE# FROM SYS.COL$ AS OF SCN 3667628170 WHERE OBJ#=55967;

    NAME TYPE#
    ——————– ———-
    A 2
    B 1
    C 1
    D 1
    E 12
    F 180
    G 100
    H 101

    8 rows selected.

    老熊 Reply:

    @寒, 对的,人可以找到,但是ODU没办法使用logmnr呀。所以只需要修改一下生成的sql和ctl文件就可以了。

  8. 恩 是的。这个只能靠手工来了。

  9. 支持asm格式的文件吗?

    报找不到文件?

    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
    data_path data
    lob_path lob
    charset_name ZHS16GBK
    ncharset_name AL16UTF16
    output_format text
    lob_storage file
    clob_byte_order little
    trace_level 1
    delimiter |

    load control file ‘config.txt’ successful
    loading default control file ……

    can not open file +DG1/jyc/datafile/system.257.729092381!error message:No such f
    ile or directory.
    can not open file +DG1/jyc/datafile/undotbs1.260.729092487!error message:No such
    file or directory.
    can not open file +DG1/jyc/datafile/sysaux.258.729092435!error message:No such f
    ile or directory.
    can not open file +DG1/jyc/datafile/users.261.729092493!error message:No such fi
    le or directory.
    can not open file +DG1/jyc/datafile/example.259.729092471!error message:No such
    file or directory.
    can not open file +DG1/jyc/datafile/test.262.729092497!error message:No such fil
    e or directory.

    ts# fn rfn bsize blocks bf offset filename
    —- —- —- —– ——– — —— —————————————-
    —-
    load control file ‘control.txt’ successful
    loading dictionary data……

    ODU>

    老熊 Reply:

    @jyc, 目前的版本不支持ASM。

  10. 原来这样,感谢老熊,将asm拷到文件系统下就可以了。

    支持ASM的版本有计划出吗?

  11. 如果我直接进行drop,提示如下,那我该如何使用你的工具恢复?
    SQL> select scn,timestamp,sql_redo from v$logmnr_contents where operation=’DDL’ and sql_redo like ‘%T%’ order by 2;

    SCN TIMESTAMP
    ———- ——————-
    SQL_REDO
    ——————————————————————————–
    2786237 2011-04-09 23:01:45
    ALTER TABLE “SCOTT”.”T3″ RENAME TO “BIN$oH2e/YIdj0DgQMAKNA4ZKQ==$0″ ;

    2786248 2011-04-09 23:01:50
    ALTER TABLE “SCOTT”.”T2″ RENAME TO “BIN$oH2e/YIej0DgQMAKNA4ZKQ==$0″ ;

    2786259 2011-04-09 23:01:57
    ALTER TABLE “SCOTT”.”T1″ RENAME TO “BIN$oH2e/YIfj0DgQMAKNA4ZKQ==$0″ ;

    SCN TIMESTAMP
    ———- ——————-
    SQL_REDO
    ——————————————————————————–
    2786410 2011-04-09 23:02:32
    drop table “SCOTT”.”BIN$oH2e/YIdj0DgQMAKNA4ZKQ==$0″ purge;

    2786606 2011-04-09 23:02:32
    drop table “SCOTT”.”BIN$oH2e/YIej0DgQMAKNA4ZKQ==$0″ purge;

    2786650 2011-04-09 23:02:32
    drop table “SCOTT”.”BIN$oH2e/YIfj0DgQMAKNA4ZKQ==$0″ purge;

    6 rows selected.

  12. yanyumao @ 2015-12-31 15:03

    老熊好,如果是分区表被误DROP呢?多个分区,即意味着该表有多个DATA_OBJECT_ID,并且每个分区都在不同的表空间中。该情况是否要将每个分区看成是一个“小表”,按照恢复表的步骤逐个将每个分区恢复?

    老熊 Reply:

    是的。

Add your comment now