上传解压ODU软件
ODU软件：https://elanjie.lanzout.com/idu6G3lzsdef
[oracle@db ~]$ ls -rtl total 1064 drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Videos drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Templates drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Public drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Pictures drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Music drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Downloads drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Documents drwxr-xr-x 2 oracle oinstall 6 Jan 5 2024 Desktop -rw-r--r-- 1 oracle oinstall 1089442 Mar 31 13:36 odu_308_linux_x86.tar.tar [oracle@db ~]$ tar -xvf odu_308_linux_x86.tar.tar odu/ odu/odu odu/control.txt odu/config.txt odu/data/ [oracle@db ~]$ cd odu/ [oracle@db odu]$ ls config.txt control.txt data odu [oracle@db odu]$ ./oduOracle Data Unloader:Release 3.0.8Copyright (c) 2008,2009 XiongJun. All rights reserved.Web: http://www.laoxiong.net Email: magic007cn@gmail.comloading 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 littleload control file 'config.txt' successful loading default control file ......ts# fn rfn bsize blocks bf offset filename ---- ---- ---- ----- -------- -- ------ -------------------------------------------- load control file 'control.txt' successful loading dictionary data......ODU> exit
模拟生成测试数据：
创建表空间以及用户： create tablespace elan datafile '/u01/app/oracle/oradata/orcl11g/elan01.dbf' size 10M; create user elan identified by 123456 default tablespace elan; grant connect,resource to elan; ALTER USER elan QUOTA UNLIMITED ON elan;创建表并插入数据： create table ceshi(id int,name varchar2(10)); begin for i in 1..20000loopinsert into ceshi values(i,'testvalue'); end loop;commit; end; /
模拟使用delete误删数据
SQL> select count(*)from ceshi;COUNT(*) ----------20000SQL>SQL> SQL> delete ceshi where ID > 8000;12000 rows deleted.SQL> SQL> commit;Commit complete.SQL> select count(*) from ceshi;COUNT(*) ----------8000
使用ODU工具恢复被误删除数据：
此时如果是生产环境需要立即做checkpoint和将所在表的表空间离线，避免数据覆盖： 查询出当前数据文件信息： SQL> set lines 1000 SQL> col name for a100 SQL> select ts#,file#,rfile#,name from v$datafile;TS# FILE# RFILE# NAME ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------0 12 1 /u01/oradata/ORA19C/elanpdb1system01.dbf1 13 4 /u01/oradata/ORA19C/elanpdb1sysaux01.dbf2 14 6 /u01/oradata/ORA19C/elanpdb1undotbs01.dbf4 15 15 /u01/oradata/ORA19C/elan01.dbf修改ODU的control文件 [oracle@db odu]$ vi control.txt [oracle@db odu]$ [oracle@db odu]$ [oracle@db odu]$ [oracle@db odu]$ [oracle@db odu]$ cat control.txt #ts #fno #rfno filename block_size0 12 1 /u01/oradata/ORA19C/elanpdb1system01.dbf1 13 4 /u01/oradata/ORA19C/elanpdb1sysaux01.dbf2 14 6 /u01/oradata/ORA19C/elanpdb1undotbs01.dbf4 15 15 /u01/oradata/ORA19C/elan01.dbf修改ODU的config.txt [oracle@db odu]$ vi config.txt [oracle@db odu]$ [oracle@db odu]$ [oracle@db odu]$ [oracle@db odu]$ cat config.txt 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 unload_deleted yes [oracle@db odu]$新增：unload_deleted yes
加载unload数据字典
ODU> unload dict CLUSTER C_USER# file_no: 1 block_no: 208 TABLE OBJ$ file_no: 1 block_no: 240 CLUSTER C_OBJ# file_no: 1 block_no: 144 CLUSTER C_OBJ# file_no: 1 block_no: 144 found IND$'s obj# 19 found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3 found TABPART$'s obj# 812 found TABPART$'s dataobj#:812,ts#:0,file#:1,block#:5232,tab#:0 found INDPART$'s obj# 817 found INDPART$'s dataobj#:817,ts#:0,file#:1,block#:5272,tab#:0 found TABSUBPART$'s obj# 824 found TABSUBPART$'s dataobj#:824,ts#:0,file#:1,block#:11856,tab#:0 found INDSUBPART$'s obj# 829 found INDSUBPART$'s dataobj#:829,ts#:0,file#:0,block#:0,tab#:0 found IND$'s obj# 19 found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3 found LOB$'s obj# 109 found LOB$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6 found LOBFRAG$'s obj# 845 found LOBFRAG$'s dataobj#:845,ts#:0,file#:1,block#:5320,tab#:0 ODU> unload table elan.ceshiUnloading table: CESHI,object ID: 23288 Unloading segment,storage(Obj#=23288 DataObj#=23288 TS#=4 File#=15 Block#=130 Cluster=0) 20000 rows unloaded
查看恢复的数据信息：
[oracle@db odu]$ cd data [oracle@db data]$ ls ELAN_CESHI.ctl ELAN_CESHI.sql ELAN_CESHI.txt [oracle@db data]$ ls -rtl total 312 -rw-r--r-- 1 oracle oinstall 76 Mar 31 14:14 ELAN_CESHI.sql -rw-r--r-- 1 oracle oinstall 273 Mar 31 14:14 ELAN_CESHI.ctl -rw-r--r-- 1 oracle oinstall 308894 Mar 31 14:14 ELAN_CESHI.txt .sql：建表 DDL .ctl：SQL*Loader 控制文件 .txt：数据文本ELAN_CESHI.sql 为建表语句 [oracle@db data]$ cat ELAN_CESHI.sql CREATE TABLE "ELAN"."CESHI" ("ID" NUMBER ,"NAME" VARCHAR2(10) );
恢复数据
修改控制文件，导入表ELAN"."CESHI_BAK [oracle@db data]$ cat ELAN_CESHI.ctl -- --Generated by ODU,for table "ELAN"."CESHI" -- OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000) LOAD DATA INFILE 'ELAN_CESHI.txt' "STR X'0a'" APPEND INTO TABLE "ELAN"."CESHI_BAK" FIELDS TERMINATED BY X'7c' TRAILING NULLCOLS ("ID" ,"NAME" CHAR(10) ) [oracle@db data]$创建表： Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0SQL> CREATE TABLE "ELAN"."CESHI_BAK" ("ID" NUMBER ,"NAME" VARCHAR2(10) ); 2 3 4 5Table created.SQL>导入数据： [oracle@db data]$ sqlldr elan/123456@elanpdb1 control=ELAN_CESHI.ctl log=test.logSQL*Loader: Release 19.0.0.0.0 - Production on Tue Mar 31 14:36:27 2026 Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.LRM-00104: '-1' is not a legal integer for 'errors' SQL*Loader-114: Error in OPTIONS statement [oracle@db data]$ [oracle@db data]$ [oracle@db data]$ vi ELAN_CESHI.ctl [oracle@db data]$ [oracle@db data]$ [oracle@db data]$ [oracle@db data]$ cat test.logSQL*Loader: Release 19.0.0.0.0 - Production on Tue Mar 31 14:36:27 2026 Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.LRM-00104: '-1' is not a legal integer for 'errors' SQL*Loader-114: Error in OPTIONS statement [oracle@db data]$ [oracle@db data]$ 报错，将ctl文件中-1位置删除，重新导入[oracle@db data]$ sqlldr elan/123456@elanpdb1 control=ELAN_CESHI.ctl log=test.logSQL*Loader: Release 19.0.0.0.0 - Production on Tue Mar 31 14:38:15 2026 Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Path used: Conventional Commit point reached - logical record count 20000Table "ELAN"."CESHI_BAK":20000 Rows successfully loaded.Check the log file:test.log for more information about the load. [oracle@db data]$数据导入成功 查看数据： SQL> select count(*) from ceshi_bak;COUNT(*) ----------20000SQL>