老熊的三分地-Oracle及数据恢复

虽然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位精度。