现在进入oracle12c时代；普遍用的oracle版本为10g以上。在oracle10g之后提供了一个回收的机制。所以恢复drop表的数据以及表很容易。当然需要打开回收机制以及是归档模式。若表被drop table tablename purge;这个是闪回技术恢复不了的。现如今中国技术达人（laoxiong）提供了odu工具，让这成为现实。大家可以关注他的博客。在此感谢(laoxiong)的贡献。
就在这几天，技术老大认为数据库有几张表没用（其实另外一个平台页面有用）；叫平台的兄弟清理掉；（其实那几张表；总共也就100多条数据；清不清真的没关系，没什么影响）。清理还是drop table tablename purge。下午发现一个平台打不开。叫我恢复下数据。虽然有备份；但还是用下odu工具来恢复下。
1. 先将表空间置于offline状态。
| SQL> alter tablespace zdgame2 offline; Tablespace altered. |
2. 然后需要使用logminer来查找被drop表的data object id：
| SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 CURRENT 3 INACTIVE SQL> select member from v$logfile where group#=2; MEMBER -------------------------------------------------- /u01/app/oradata/ora11g/redo02.log SQL> exec sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oradata/ora11g/redo02.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,to_char(timestamp,'yyyymmddhh24miss'),sql_redo from v$logmnr_contents where operation='DDL' and sql_redo like '%acct_module_role%' order by 2 --在这里得到drop表的时间结点 --20151125113452 SQL> select scn,timestamp,sql_redo from v$logmnr_contents where timestamp=to_date('2015-11-25 11:34:52','yyyy-mm-dd hh24:mi:ss') order by 1; 1.0790E+12 25-NOV-15 delete from "SYS"."OBJ$" where "OBJ#" = '68199' and "DATAOBJ#" = '68199' and "OW SCN TIMESTAMP ---------- --------- SQL_REDO -------------------------------------------------------------------------------- NER#" = '62' and "NAME" = 'ACCT_MODULE_ROLE' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('25-NOV-15', 'DD-MON-RR') and " MTIME" = TO_DATE('25-NOV-15', 'DD-MON-RR') and "STIME" = TO_DATE('25-NOV-15', 'D D-MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL a nd "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "S PARE3" = '62' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAP25AAI'; |
下面用odu工具来恢复数据；上传软件到oracle服务器上。并解压
从oracle获取控制信息
SQL> select ts#,file#,rfile#,name from v$datafile;
把查询结果贴到control.txt；
执行./odu
| [ **** odu]$ ./odu Oracle Data Unloader:Release 3.0.8 Copyright (c) 2008,2009 XiongJun. All rights reserved. Web: Email: loading default config....... byte_order little block_size 8192 db_timezone -7 client_timezone 8 data_path data charset_name ZHS16GBK ncharset_name AL16UTF16 output_format text lob_storage infile clob_byte_order little load control file 'config.txt' successful loading default control file ...... ts# fn rfn bsize blocks bf offset filename ---- ---- ---- ----- -------- -- ------ -------------------------------------------- 0 1 1 8192 89600 N 0 /u01/app/oradata/ora11g/system01.dbf 1 2 2 8192 76800 N 0 /u01/app/oradata/ora11g/sysaux01.dbf 2 3 3 8192 76160 N 0 /u01/app/oradata/ora11g/undotbs01.dbf 4 4 4 8192 14560 N 0 /u01/app/oradata/ora11g/users01.dbf 5 5 5 8192 2621440 N 0 /data/oradata/ora11g/******.dbf 6 6 6 8192 1310720 N 0 /data/oradata/ora11g/******.dbf load control file 'control.txt' successful loading dictionary data...... ODU> scan extent tablespace 5; scan extent start: 2015-11-25 11:52:21 scanning extent... scanning extent finished. scan extent completed: 2015-11-25 11:52:27 ODU> unload object 68199 sample --68199是上面查出来的结果 Unloading Object,object ID: 68199, Cluster: 0 output data is in file : 'data/ODU_0000068199.txt' Sample result: object id: 68199 tablespace no: 5 sampled 56 rows column count: 6 column 1 type: NUMBER column 2 type: VARCHAR2 column 3 type: VARCHAR2 column 4 type: VARCHAR2 column 5 type: NUMBER column 6 type: NUMBER COMMAND: unload object 68199 tablespace 5 column NUMBER VARCHAR2 VARCHAR2 VARCHAR2 NUMBER NUMBER ODU> unload object 68199 tablespace 5 column NUMBER VARCHAR2 VARCHAR2 VARCHAR2 NUMBER NUMBER Unloading Object,object ID: 68199, Cluster: 0 56 rows unloaded |
到data目录下看下是否恢复；数据都在。
| [ ××××× data]$ ll total 20 -rw-r--r-- 1 oracle dba 366 Nov 25 11:54 ODU_0000068199.ctl -rw-r--r-- 1 oracle dba 184 Nov 25 11:54 ODU_0000068199.sql --为建表语句 -rw-r--r-- 1 oracle dba 7264 Nov 25 11:54 ODU_0000068199.txt --恢复的数据 -rw-r--r-- 1 oracle dba 773 Nov 25 11:53 sample.txt |
先建表，在用sqlldr恢复数据即可。最后把先将表空间置于online状态。alter tablespace zdgame2 online;