今天一朋友问到一个比较有意思的问题：
如果一个表空间，其中一个数据文件丢失，在无备份和非归档的情况下，如何最大程度的
把表空间里面的数据给抢救出来？
我当时直接的回答是，使用exp进行逻辑导出，当时对方直接说exp是不行的，真是这样吗？
这篇文章就的目的就是为了解答这个问题，首先准备下测试环境：
SQL > select file_id,file_name,bytes/1024/1024,tablespace_name,AUTOEXTENSIBLE
2 from dba_data_files
3 order by 1;
FILE_ID FILE_NAME BYTES/1024/1024 TABLESPACE_NAME AUT
------- ------------------------------------------ --------------- ----------------- ---
1 /home/ora10g/oradata/roger/system01.dbf 450 SYSTEM YES
2 /home/ora10g/oradata/roger/undotbs01.dbf 925 UNDOTBS1 YES
3 /home/ora10g/oradata/roger/sysaux01.dbf 260 SYSAUX YES
4 /home/ora10g/oradata/roger/users01.dbf 5 USERS YES
5 /home/ora10g/oradata/roger/roger01.dbf 10 ROGER NO
6 /home/ora10g/oradata/roger/roger02.dbf 10 ROGER NO
7 /home/ora10g/oradata/roger/roger03.dbf 10 ROGER NO
7 rows selected.
SQL > create user roger identified by roger default tablespace roger;
User created.
SQL > grant connect,resource,dba to roger;
Grant succeeded.
SQL > conn roger/roger
Connected.
SQL > create table killdb1
2 as select * from sys.dba_objects;
Table created.
SQL > create table killdb2
2 as select * from killdb1;
Table created.
SQL > begin
2 for i in 1..100 loop
3 insert /*+ append */into killdb2 select * from killdb2;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table ROGER.KILLDB2 by 128 in tablespace ROGER
ORA-06512: at line 3
SQL > analyze table killdb1 compute statistics;
Table analyzed.
SQL > analyze table killdb2 compute statistics;
Table analyzed.
SQL > conn /as sysdba
Connected.
SQL > select distinct dbms_rowid.rowid_relative_fno(rowid) file#
2 from roger.killdb1
3 union all
4 select distinct dbms_rowid.rowid_relative_fno(rowid) file#
5 from roger.killdb2;
FILE#
----------
6
5
7
6
5
7
6 rows selected.
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup mount
ORA-00000: normal, successful completion
SQL > conn /as sysdba
Connected to an idle instance.
SQL > startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 79693032 bytes
Database Buffers 83886080 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL > alter database noarchivelog;
Database altered.
SQL > alter database open;
Database altered.
SQL > !rm /home/ora10g/oradata/roger/roger03.dbf
SQL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup
ORA-00000: normal, successful completion
SQL > conn /as sysdba
Connected to an idle instance.
SQL > startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 79693032 bytes
Database Buffers 83886080 bytes
Redo Buffers 2920448 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
SQL > select status from v$instance;
STATUS
------------
MOUNTED
SQL > alter database datafile 7 offline drop;
Database altered.
SQL > alter database open;
Database altered.
SQL > select count(*) from roger.killdb1;
select count(*) from roger.killdb1
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'情景我们已经模拟出来来，现在问题是我们要如何去最大限度的抢救killdb1，killdb2里面的数据
（虽然会丢失部分数据）。
'###### 第一种方式 ######'
[ora10g@killdb ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[ora10g@killdb ~]$ exp roger/roger file=killdb1.dmp tables=killdb1 grants=n indexes=n
Export: Release 10.2.0.5.0 - Production on Wed Nov 2 20:52:53 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
About to export specified tables via Conventional Path ...
. . exporting table KILLDB1
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
Export terminated successfully with warnings.
[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y
Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:55:43 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing 'ROGER's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "KILLDB1" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "
""SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER,"
" "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTA"
"MP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED"
"" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 M"
"AXTRANS 255 STORAGE(INITIAL 6291456 NEXT 1048576 FREELISTS 1 FREELIST GROUP"
"S 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace ROGER
Import terminated successfully with warnings.++++++ roger表空间不足，我需要扩容一下 ++++++
SQL > alter database datafile 5 resize 20m;
Database altered.[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y
Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:56:32 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing 'ROGER's objects into SYSTEM
. . importing table "KILLDB1" 9500 rows imported
Import terminated successfully without warnings.SQL > conn /as sysdba
Connected.
SQL > select count(*) from system.killdb1;
COUNT(*)
----------
9500
SQL > select * from SYSSEGOBJ
2 where obj# in ( select object_id
3 from dba_objects
4 where object_name='KILLDB1' and owner='ROGER'
5 );
OBJ# FILE# BLOCK# TYPE PCTFREE$ PCTUSED$
---------- ----- ------ ------ -------- --------
52059 5 11 TABLE 10 40
SQL > select file#,BLOCK#,BLOCKS,EXTENTS
2 from seg$
3 where file#=5 and block#=11;
FILE# BLOCK# BLOCKS EXTENTS
---------- ---------- ---------- ----------
5 11 768 21
SQL > col owner for a10
SQL > select a.owner,
2 a.segment_name,
3 a.initial_extent,
4 b.file_id,
5 a.extents,
6 b.extent_id,
7 b.blocks,
8 a.HEADER_FILE,
9 a.HEADER_BLOCK
10 from dba_segments a, dba_extents b
11 where a.owner = b.owner
12 and a.segment_name = b.segment_name
13 and a.owner = 'ROGER'
14 and b.segment_name = 'KILLDB1'
15 order by 6;
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 0 8 5 11
ROGER KILLDB1 65536 5 21 1 8 5 11
ROGER KILLDB1 65536 5 21 2 8 5 11
ROGER KILLDB1 65536 5 21 3 8 5 11
ROGER KILLDB1 65536 5 21 4 8 5 11
ROGER KILLDB1 65536 5 21 5 8 5 11
ROGER KILLDB1 65536 5 21 6 8 5 11
ROGER KILLDB1 65536 5 21 7 8 5 11
ROGER KILLDB1 65536 5 21 8 8 5 11
ROGER KILLDB1 65536 5 21 9 8 5 11
ROGER KILLDB1 65536 5 21 10 8 5 11
ROGER KILLDB1 65536 5 21 11 8 5 11
ROGER KILLDB1 65536 5 21 12 8 5 11
ROGER KILLDB1 65536 5 21 13 8 5 11
ROGER KILLDB1 65536 5 21 14 8 5 11
ROGER KILLDB1 65536 5 21 15 8 5 11
ROGER KILLDB1 65536 7 21 16 128 5 11
ROGER KILLDB1 65536 6 21 17 128 5 11
ROGER KILLDB1 65536 5 21 18 128 5 11
ROGER KILLDB1 65536 7 21 19 128 5 11
ROGER KILLDB1 65536 6 21 20 128 5 11
21 rows selected.
SQL > select owner,
2 TABLE_NAME,
3 NUM_ROWS,
4 BLOCKS,
5 EMPTY_BLOCKS,
6 AVG_SPACE,
7 AVG_ROW_LEN
8 from DBA_TAB_STATISTICS
9 where table_name = 'KILLDB1'
10 and owner = 'ROGER';
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ------------------------------ ---------- ---------- ------------ ---------- -----------
ROGER KILLDB1 50094 708 60 861 97
SQL > select 16*8*(50094/708) from dual;
16*8*(50094/708)
----------------
9056.54237通过上面的查询，我们可以看出，exp在抽取数据的时候，是根据extent来的，换句话说
在exp导出表killdb1时，只是抽取了前面0~15个extent，虽然extent id 17，18，20是完好的。
也就是说使用exp进行导出的话，实际上是丢失了这3个extent部分的数据的。
'###### 第2种方法：基于 rowid 进行数据抢救 ######'
++++++ 表 killdb1 的block分布情况如下：++++++
SQL > col owner for a10
SQL > select a.owner,
2 a.segment_name,
3 a.initial_extent,
4 b.file_id,
5 a.extents,
6 b.extent_id,
7 b.blocks,
8 a.HEADER_FILE,
9 a.HEADER_BLOCK
10 from dba_segments a, dba_extents b
11 where a.owner = b.owner
12 and a.segment_name = b.segment_name
13 and a.owner = 'ROGER'
14 and b.segment_name = 'KILLDB1'
15 order by 6;
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 0 8 5 11
ROGER KILLDB1 65536 5 21 1 8 5 11
ROGER KILLDB1 65536 5 21 2 8 5 11
ROGER KILLDB1 65536 5 21 3 8 5 11
ROGER KILLDB1 65536 5 21 4 8 5 11
ROGER KILLDB1 65536 5 21 5 8 5 11
ROGER KILLDB1 65536 5 21 6 8 5 11
ROGER KILLDB1 65536 5 21 7 8 5 11
ROGER KILLDB1 65536 5 21 8 8 5 11
ROGER KILLDB1 65536 5 21 9 8 5 11
ROGER KILLDB1 65536 5 21 10 8 5 11
ROGER KILLDB1 65536 5 21 11 8 5 11
ROGER KILLDB1 65536 5 21 12 8 5 11
ROGER KILLDB1 65536 5 21 13 8 5 11
ROGER KILLDB1 65536 5 21 14 8 5 11
ROGER KILLDB1 65536 5 21 15 8 5 11
ROGER KILLDB1 65536 7 21 16 128 5 11
ROGER KILLDB1 65536 6 21 17 128 5 11
ROGER KILLDB1 65536 5 21 18 128 5 11
ROGER KILLDB1 65536 7 21 19 128 5 11
ROGER KILLDB1 65536 6 21 20 128 5 11
21 rows selected.
select 139+128 from dual; = 267
select 139+2*128 from dual; = 395
select 139+3*128 from dual; = 523
select 139+4*128 from dual; = 651
select 139+5*128 from dual; = 779
SQL > select object_id
2 from dba_objects
3 where object_name='KILLDB1' and owner='ROGER';
OBJECT_ID
----------
52059
SQL > select 139+128 from dual;
139+128
----------
267
SQL > select 139+3*128 from dual;
139+3*128
----------
523
SQL > select 139+4*128 from dual;
139+4*128
----------
651
SQL > select 139+5*128 from dual;
139+5*128
----------
779
SQL > set lines 200
SQL > SELECT dbms_rowid.rowid_create(1,52059,5,139,0) FROM dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAACLAAA
SQL > select dbms_rowid.rowid_create(1,52059,5,267,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAELAAA
SQL > select dbms_rowid.rowid_create(1,52059,5,395,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAGLAAA
SQL > select dbms_rowid.rowid_create(1,52059,5,523,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAILAAA
SQL > select dbms_rowid.rowid_create(1,52059,5,651,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAKLAAA
SQL > select dbms_rowid.rowid_create(1,52059,5,779,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAMLAAA
SQL > create table t1
2 as select * from roger.killdb1 where rowid < 'AAAMtbAAFAAAACLAAA';
Table created.
SQL > select count(*) from t1;
COUNT(*)
----------
9500 ====== 这个就是我们使用exp所能抽取的9500条数据 ======
SQL > insert into t1
2 select *
3 from (select *
4 from roger.killdb1
5 where rowid < 'AAAMtbAAFAAAAELAAA'
6 union all
7 select *
8 from roger.killdb1
9 where rowid < 'AAAMtbAAFAAAAGLAAA'
10 and rowid >='AAAMtbAAFAAAAELAAA'
11 union all
12 select *
13 from roger.killdb1
14 where rowid < 'AAAMtbAAFAAAAKLAAA'
15 and rowid >= 'AAAMtbAAFAAAAGLAAA'
16 union all
17 select *
18 from roger.killdb1
19 where rowid < 'AAAMtbAAFAAAAMLAAA'
20 and rowid >= 'AAAMtbAAFAAAAKLAAA');
18347 rows created.
SQL > commit;
Commit complete.
SQL > select count(*) from t1;
COUNT(*)
----------
27847
++++++ 我们可以看到，这样可以抢救出27847条数据，远大于9500条。++++++'###### 第三种方式：使用ODU进行数据抽取 ######'
[ora10g@killdb data]$ ls -ltr
total 1036
-rw-r--r-- 1 ora10g oinstall 1036288 Nov 3 01:07 ROGER_KILLDB1.txt
-rw-r--r-- 1 ora10g oinstall 416 Nov 3 01:07 ROGER_KILLDB1.sql
-rw-r--r-- 1 ora10g oinstall 618 Nov 3 01:07 ROGER_KILLDB1.ctl
SQL > @ /home/ora10g/odu/odu/data/ROGER_KILLDB1.sql
Table created.
SQL > !
[ora10g@killdb ~]$ cd /home/ora10g/odu/odu/data
[ora10g@killdb data]$ sqlldr system/oracle control=ROGER_KILLDB1.ctl
SQL*Loader: Release 10.2.0.5.0 - Production on Thu Nov 3 00:50:33 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 6492
Commit point reached - logical record count 9478
Commit point reached - logical record count 9479
[ora10g@killdb data]$ exit
exit
SQL > select count(*) from system.killdb1;
COUNT(*)
----------
9479可以发现，对于低版本的ODU，似乎还存在一些问题，高版本的ODU应该是ok的，我这里没测试。
目前我暂时所能想到的就这几种方式，如果谁还有更好的方法，请告诉我，谢谢!
总的来说，我个人认为第2种方式比较好，当然使用新版的ODU也比较省事，直接scan一下，然后unload，imp就完了。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
