如何最大程度抢救数据（数据文件丢失-非归档）
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 如何最大程度抢救数据（数据文件丢失-非归档）
|
1 2 3 4 5 6 7 8 |
今天一朋友问到一个比较有意思的问题： 如果一个表空间，其中一个数据文件丢失，在无备份和非归档的情况下，如何最大程度的 把表空间里面的数据给抢救出来？ 我当时直接的回答是，使用exp进行逻辑导出，当时对方直接说exp是不行的，真是这样吗？ 这篇文章就的目的就是为了解答这个问题，首先准备下测试环境： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
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' |
|
1 2 |
情景我们已经模拟出来来，现在问题是我们要如何去最大限度的抢救killdb1，killdb2里面的数据 （虽然会丢失部分数据）。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
'###### 第一种方式 ######' [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. |
|
1 2 3 4 5 |
++++++ roger表空间不足，我需要扩容一下 ++++++ SQL > alter database datafile 5 resize 20m; Database altered. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[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. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
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 |
|
1 2 3 4 |
通过上面的查询，我们可以看出，exp在抽取数据的时候，是根据extent来的，换句话说 在exp导出表killdb1时，只是抽取了前面0~15个extent，虽然extent id 17，18，20是完好的。 也就是说使用exp进行导出的话，实际上是丢失了这3个extent部分的数据的。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 |
'###### 第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条。++++++ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
'###### 第三种方式：使用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 |
|
1 2 3 4 5 |
可以发现，对于低版本的ODU，似乎还存在一些问题，高版本的ODU应该是ok的，我这里没测试。 目前我暂时所能想到的就这几种方式，如果谁还有更好的方法，请告诉我，谢谢! 总的来说，我个人认为第2种方式比较好，当然使用新版的ODU也比较省事，直接scan一下，然后unload，imp就完了。 |
4 Responses to “如何最大程度抢救数据（数据文件丢失-非归档）”
学习。
其中139的记算是不是 16*8+11
to zhouyf： 是的
SELECT 16*8*(50094/708) FROM dual;
这里面的数字分别代表什么啊 55555555555555555
50094/708表示，每个块有多少记录
16表示有多少个扩展
8表示一个扩展有多少个块
所以就可以得到前16个扩展大约会含有 9056.5条记录
Leave a Reply
You must be logged in to post a comment.