如何最大程度的 把表空间里面的数据给抢救出来
今天一朋友问到一个比较有意识的问题：
如果一个表空间，其中一个数据文件丢失，在无备份和非归档的情况下，如何最大程度的
把表空间里面的数据给抢救出来？
我当时直接的回答是，使用exp进行逻辑导出，当时对方直接说exp是不行的，真是这样吗？
这篇文章就的目的就是为了解答这个问题，首先准备下测试环境：
SQL> select file_id,file_name,bytes/1024/1024,tablespace_name,AUTOEXTENSIBLE
2 from dba_data_files 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>
SQL> conn roger/roger
Connected.
SQL> create table killdb1 as select * from sys.dba_objects;
Table created.
SQL> create table killdb2 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>
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.
[ora10g@killdb ~]$
[ora10g@killdb ~]$
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.
[ora10g@killdb ~]$
SQL> conn /as sysdba
Connected.
SQL>
SQL> select count(*) from system.killdb1;
COUNT(*)
----------
9500
SQL>
SQL> select * from SYSSEGOBJ where obj# in(select object_id from dba_objects
2 where object_name='KILLDB1' and owner='ROGER');
OBJ# FILE# BLOCK# TYPE PCTFREE$ PCTUSED$
---------- ---------- ---------- ------- ---------- ----------
52059 5 11 TABLE 10 40
SQL> select file#,BLOCK#,BLOCKS,EXTENTS from seg$ where file#=5 and block#=11;
FILE# BLOCK# BLOCKS EXTENTS
---------- ---------- ---------- ----------
5 11 768 21
SQL>
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
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
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
SQL>
通过上面的查询，我们可以看出，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
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
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 from dba_objects where object_name='KILLDB1' and
2 owner='ROGER';
OBJECT_ID
----------
52059
SQL>
SQL> select 139+128 from dual;
select 139+2*128 from dual;
139+128
----------
267
SQL> select 139+3*128 from dual;
139+2*128
----------
395
SQL>
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;
select dbms_rowid.rowid_create(1,52059,5,395,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,523,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,651,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,779,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAELAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAGLAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAILAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAKLAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAMLAAA
SQL>
SQL>
SQL> create table t1 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
SQL> ---我们可以看到，这样可以抢救出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
[ora10g@killdb data]$
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
可以发现，对于低版本的OUD，似乎还存在一些问题，高版本的odu应该是ok的，我这里没测试。
目前我暂时所能想到的就这几种方式，如果谁还有更好的方法，请告诉我，谢谢，总的来说，
我个人认为第2种方式比较好，当然使用新版的ODU也比较省事，直接scan一下，然后unload，imp就完了。
