Drop tablespace xxx including contents，没有备份怎么办？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
有朋友公司的开发库，被开发人员误操作了，什么操作呢？drop tablespace xxx including contents了。
更郁闷是没有备份，非归档，这简直是要命。这里简单测试下误删除表空间的恢复测试。
#### 创建测试表空间和测试表
|
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 |
SQL> show user USER is "SYS" SQL> select table_name,owner from dba_tables where tablespacE_name='T_TEST'; no rows selected SQL> conn roger/roger Connected. SQL> create table t_tbs as select * from dba_objects where rownum < 10; Table created. SQL> alter table t_tbs move tablespace t_Test; Table altered. SQL> select table_name,owner from dba_tables where tablespacE_name='T_TEST'; TABLE_NAME OWNER ------------------------------ ------------------------------ T_TBS ROGER SQL> select count(1) from t_tbs; COUNT(1) ---------- 9 SQL> alter system checkpoint; System altered. SQL> drop tablespace t_test; drop tablespace t_test * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option SQL> drop tablespace t_test including contents; Tablespace dropped. SQL> select table_name,owner from dba_tables where tablespacE_name='T_TEST'; no rows selected SQL> select tablespace_name,file_name from dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ ---------------------------------------------------------------------- STREAMS_TBS /home/ora10g/oradata/roger/streams01.dbf UNDOTBS2 /home/ora10g/oradata/roger/undotbs2.dbf SYSTEM /home/ora10g/oradata/roger/system02.dbf USERS /home/ora10g/oradata/roger/users01.dbf SYSAUX /home/ora10g/oradata/roger/sysaux01.dbf UNDOTBS1 /home/ora10g/oradata/roger/undotbs01.dbf SYSTEM /home/ora10g/oradata/roger/system01.dbf ROGER /home/ora10g/oradata/roger/roger01.dbf TEST_BIG /home/ora10g/oradata/roger/test_big.dbf 9 rows selected. SQL> ! [ora10g@killdb ~]$ ls -ltr /home/ora10g/oradata/roger/ total 4209076 -rw-r--r-- 1 ora10g oinstall 0 Aug 11 2013 user_01.dbf -rw-r----- 1 ora10g oinstall 5251072 Jan 3 2014 test0104.dbf -rw-r----- 1 ora10g oinstall 52429312 Aug 11 19:56 redo01.log -rw-r----- 1 ora10g oinstall 10493952 Aug 16 05:50 temp01.dbf -rw-r----- 1 ora10g oinstall 52429312 Aug 16 06:53 redo02.log -rw-r----- 1 ora10g oinstall 10493952 Aug 17 23:09 users01.dbf -rw-r----- 1 ora10g oinstall 52436992 Aug 17 23:09 undotbs2.dbf -rw-r----- 1 ora10g oinstall 204480512 Aug 17 23:09 undotbs01.dbf -rw-r----- 1 ora10g oinstall 20979712 Aug 17 23:09 test_big.dbf -rw-r----- 1 ora10g oinstall 5251072 Aug 17 23:09 system02.dbf -rw-r----- 1 ora10g oinstall 555753472 Aug 17 23:09 system01.dbf -rw-r----- 1 ora10g oinstall 2621448192 Aug 17 23:09 sysaux01.dbf -rw-r----- 1 ora10g oinstall 104865792 Aug 17 23:09 streams01.dbf -rw-r----- 1 ora10g oinstall 524296192 Aug 17 23:09 roger01.dbf -rw-r----- 1 ora10g oinstall 10493952 Aug 17 23:10 t_test.dbf -rw-r----- 1 ora10g oinstall 52429312 Aug 17 23:10 redo03.log -rw-r----- 1 ora10g oinstall 7389184 Aug 17 23:11 control03.ctl -rw-r----- 1 ora10g oinstall 7389184 Aug 17 23:11 control02.ctl -rw-r----- 1 ora10g oinstall 7389184 Aug 17 23:11 control01.ctl |
类似这样的情况，oracles数据字典内的信息已经被清掉了，常规的方法肯定是不行的。这里我用DUL来进行抽取。
这样的case，我们就当是假设system 文件丢失或损坏来处理了。思路类似。
##### 恢复步骤
|
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 |
[ora10g@killdb dul]$ ./dul Data UnLoader: 10.2.0.5.32 - Internal Only - on Sun Aug 17 23:25:18 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: Recreating file "dul.log" Found db_id = 2525832133 Found db_name = ROGER DUL> scan database; Scanning tablespace 1, data file 2 ... 0 segment header and 0 data blocks tablespace 1, data file 2: 24959 blocks scanned Scanning tablespace 2, data file 3 ... 4506 segment header and 189288 data blocks tablespace 2, data file 3: 319999 blocks scanned Scanning tablespace 4, data file 4 ... 11 segment header and 29 data blocks tablespace 4, data file 4: 1279 blocks scanned Scanning tablespace 6, data file 5 ... 65 segment header and 37785 data blocks tablespace 6, data file 5: 63999 blocks scanned Scanning tablespace 0, data file 6 ... 10 segment header and 510 data blocks tablespace 0, data file 6: 639 blocks scanned Scanning tablespace 5, data file 7 ... 0 segment header and 0 data blocks tablespace 5, data file 7: 6399 blocks scanned Scanning tablespace 8, data file 8 ... 96 segment header and 51 data blocks tablespace 8, data file 8: 12799 blocks scanned Scanning tablespace 10, data file 9 ... 2 segment header and 1127 data blocks tablespace 10, data file 9: 1279 blocks scanned Scanning tablespace 11, data file 1024 ... 3 segment header and 2127 data blocks tablespace 11, data file 1024: 2559 blocks scanned Reading EXT.dat 6555 entries loaded and sorted 6555 entries Reading SEG.dat 4693 entries loaded Reading COMPATSEG.dat 0 entries loaded Reading SCANNEDLOBPAGE.dat DUL: Warning: Increased the size of DC_SCANNED_LOB_PAGES from 10000 to 32768 entries 10394 entries loaded and sorted 10394 entries DUL> SCAN TABLES; DUL: Warning: Recreating file "seen_tab.dat" DUL: Warning: Recreating file "seen_col.dat" Scanning tables with segment header Analyzing segment: data object id 587 segment header at ( file 3 block 11) heap organized table Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice 1 1094 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 2 1094 9 0 0 0 100 100 0 0 0 0 0 0 0 0 1 0 0 3 1094 34 4 100 100 4 0 0 0 0 0 0 0 0 0 11 0 0 4 1094 2 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 5 1090 2 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 6 1090 34 4 100 100 4 0 0 0 0 0 0 0 0 0 11 0 0 7 1090 608 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100 8 3 63 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100 "100" "281474976710849" "ALLSTAT" "2" "35" "ALLSTAT" "lob_type=1, byte_length=1, inline=1 Lobid=0x00000001000000033904" "100" "281474976710784" "EIFEXTENSIONPATH" "2" "36" "EIFEXTENSIONPATH" "lob_type=1, byte_length=1, inline=1 Lobid=0x00000001000000033905" "100" "281474976710921" "" "2" "7" "" "lob_type=1, byte_length=1, inline=1 Lobid=0x00000001000000033906" "100" "281474976710719" "NLS_LANG" "2" "36" "NLS_LANG" "lob_type=1, byte_length=1, inline=1 Lobid=0x00000001000000033907" "100" "281474976710856" "_TRKCALLS" "2" "6" "_TRKCALLS" "lob_type=1, byte_length=1, inline=1 Lobid=0x00000001000000033908" UNLOAD TABLE OBJNO587 ( COL001 NUMBER, COL002 NUMBER, COL003 VARCHAR2(34) , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(34), COL007 LOB INFORMATION , COL008 LOB INFORMATION ) STORAGE( DATAOBJNO 587 ); Analyzing segment: data object id 588 segment header at ( file 3 block 19) DUL: Warning: Segment skipped, no extents in scanned extent map Analyzing segment: data object id 589 segment header at ( file 3 block 27) index organized segment keydata = 0 No rows found in this table Analyzing segment: data object id 590 segment header at ( file 3 block 35) DUL: Warning: Segment skipped, no extents in scanned extent map Analyzing segment: data object id 591 segment header at ( file 3 block 43) index organized segment keydata = 0 No rows found in this table Analyzing segment: data object id 592 segment header at ( file 3 block 51) DUL: Warning: Segment skipped, no extents in scanned extent map Analyzing segment: data object id 593 segment header at ( file 3 block 59) index organized segment keydata = 0 No rows found in this table Analyzing segment: data object id 594 segment header at ( file 3 block 67) index organized segment keydata = 6 Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice 1 1094 6 0 0 0 0 0 0 0 0 0 0 0 0 0 100 100 0 2 1094 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 3 1094 9 0 0 0 100 100 0 0 0 0 0 0 0 0 1 0 0 4 1094 2 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 "000042C3.000C.0003" "101" "281474976710700" "2" "00004224.0000.0003" "101" "281474976710701" "2" "000042C8.0002.0003" "101" "281474976710702" "2" "00004226.0006.0003" "101" "281474976710703" "2" "000042C6.0007.0003" "101" "281474976710704" "2" UNLOAD TABLE OBJNO594 ( COL001 ROWID, COL002 NUMBER, COL003 NUMBER , COL004 NUMBER ) ORGANIZATION INDEX STORAGE( DATAOBJNO 594 ); 。。。。。。。。。。 UNLOAD TABLE OBJNO58237 ( COL001 VARCHAR2(18), COL002 VARCHAR2(30), COL003 VARCHAR2(28) , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(18), COL007 DATE , COL008 DATE, COL009 VARCHAR2(19), COL010 VARCHAR2(7), COL011 VARCHAR2(1) , COL012 VARCHAR2(1), COL013 VARCHAR2(1) ) STORAGE( DATAOBJNO 58237 ); Analyzing segment: data object id 58239 heap organized table DUL: Error: hi water extent does not exist No rows found in this table Analyzing segment: data object id 58240 heap organized table DUL: Error: hi water extent does not exist No rows found in this table DUL> |
搜索关键字file 9，定位到我们需要抽取的表;
|
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 |
Analyzing segment: data object id 58406 segment header at ( file 9 block 1163) heap organized table Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice 1 9 3 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 2 9 16 0 100 100 0 0 0 0 0 0 0 0 0 0 22 0 0 3 9 0 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 9 2 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 5 9 2 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 6 9 7 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 7 9 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0 8 9 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0 9 9 19 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 10 9 5 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 11 9 1 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 12 9 1 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 13 9 1 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0 "SYS" "ICOL$" "" "20" "2" "TABLE" "15-APR-2010 AD 13:14:44" "15-APR-2010 AD 13:25:22" "2010-04-15:13:14:44" "VALID" "N" "N" "N" "SYS" "I_USER1" "" "44" "44" "INDEX" "15-APR-2010 AD 13:14:44" "15-APR-2010 AD 13:14:44" "2010-04-15:13:14:44" "VALID" "N" "N" "N" "SYS" "CON$" "" "28" "28" "TABLE" "15-APR-2010 AD 13:14:44" "15-APR-2010 AD 13:31:16" "2010-04-15:13:14:44" "VALID" "N" "N" "N" "SYS" "UNDO$" "" "15" "15" "TABLE" "15-APR-2010 AD 13:14:44" "15-APR-2010 AD 13:14:44" "2010-04-15:13:14:44" "VALID" "N" "N" "N" "SYS" "C_COBJ#" "" "29" "29" "CLUSTER" "15-APR-2010 AD 13:14:44" "15-APR-2010 AD 13:14:44" "2010-04-15:13:14:44" "VALID" "N" "N" "N" UNLOAD TABLE OBJNO58406 ( COL001 VARCHAR2(3), COL002 VARCHAR2(16), COL003 CHAR , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(7), COL007 DATE , COL008 DATE, COL009 VARCHAR2(19), COL010 VARCHAR2(5), COL011 VARCHAR2(1) , COL012 VARCHAR2(1), COL013 VARCHAR2(1) ) STORAGE( DATAOBJNO 58406 ); |
由于是类似system丢失的情况，因此抽取的表是没有表名称和列名称的，下面我们开始进行抽取：
|
1 2 3 4 5 6 7 |
DUL> UNLOAD TABLE OBJNO58406 ( COL001 VARCHAR2(3), COL002 VARCHAR2(16), COL003 CHAR 2 , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(7), COL007 DATE , COL008 DATE, COL009 VARCHAR2(19), COL010 VARCHAR2(5), COL011 VARCHAR2(1) 3 4 , COL012 VARCHAR2(1), COL013 VARCHAR2(1) ) 5 STORAGE( DATAOBJNO 58406 ); . unloading table OBJNO58406 9 rows unloaded DUL> exit |
接着导入数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[ora10g@killdb dul]$ imp roger/roger file=dump001.dmp full=y Import: Release 10.2.0.5.0 - Production on Sun Aug 17 23:40:35 2014 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:V07.00.07 via conventional path Warning: the objects were exported by Bernard's DUL, not by you . importing Bernard's DUL's objects into ROGER . importing Bernard's DUL's objects into ROGER . . importing table "OBJNO58406" 9 rows imported Import terminated successfully without warnings. |
假设我们知道表结构的话，那么就方便了，不然是比较麻烦的。这里假设知道，那么直接create as 就行了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> conn roger/roger Connected. SQL> create table t_tbs_tmp as select * from dba_objects where 1=2; Table created. SQL> insert into t_tbs_tmp select * from OBJNO58406; 9 rows created. SQL> commit; Commit complete. SQL> select count(1) from t_tbs_tmp; COUNT(1) ---------- 9 SQL> |
2 Responses to “Drop tablespace xxx including contents，没有备份怎么办？”
看着你的网站一天天进步 很不错 继续加油 也欢迎你来见证我的进步
solars.biz
Leave a Reply
You must be logged in to post a comment.