Drop tablespace xxx including contents，没有备份怎么办？

原创 Roger 2014-08-18

622

有朋友公司的开发库，被开发人员误操作了，什么操作呢？drop tablespace xxx including contents了。

更郁闷是没有备份，非归档，这简直是要命。这里简单测试下误删除表空间的恢复测试。

#### 创建测试表空间和测试表


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 文件丢失或损坏来处理了。思路类似。

##### 恢复步骤

     
[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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}100{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}100{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} 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，定位到我们需要抽取的表;


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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}100{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b} 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丢失的情况，因此抽取的表是没有表名称和列名称的，下面我们开始进行抽取：


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

接着导入数据：


[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 就行了。


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>

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Roger's Database Notes（共77篇）

非知名DBA、数据库爱好者、专注于Oracle/MySQL/PostgreSQL/NewSQL/国产数据库等

Drop tablespace xxx including contents，没有备份怎么办？

文章被以下合辑收录

评论