非归档库误删表空间后的数据恢复

前几天同事问我，非归档库误删除的表空间，数据文件还在，有没有办法恢复数据
想了一下，完整的加回去肯定是不可能，但是应该是有办法把数据给搞出来的

处理的思路为：
1.虽然表空间被DROP，但是DROP只是从数据字典中删除了相关的记录，实际数据还是存在的
2.重构数据字典，恢复这些被删除的表空间上对象的信息
3.恢复数据字典中，表空间以及数据文件的记录
4.只要能在seg$中，将对象的段头所在的块，指向被删除的数据文件上，就可以读取数据
5.另外，还有一个重点，obj$.dataobj#,如果对象的dataobj#与块上的dataobj#不同，将报告ORA-8103

考虑了几种方法：
1.如果REDO没有被覆盖，考虑使用LOGMNR，但是可惜10g必须要补充日志，否则一些DML语句挖掘不处理
2.直接flashback 数据字典，可惜SYS下的对象不支持
3.自己重建相关的数据字典，发现这是个不可能的事情

再失败多次后，使用如下办法搞定：
1.在数据库上重建相关对象（没有数据，只有结构）
2.更改TS$,FILE$，在数据字典中恢复被删除的表空间和数据文件
3.更改这些重建的对象的obj$.dataobj#字段为以前的段的dataobj#
4.更改seg$中的这些重建的对象的段头块的位置，指向以前的旧位置
5.由于控制文件中还有被删除的表空间数据文件信息，所以需要重建控制文件，加入被删除的表空间的相关数据文件
6.使用_allow_resetlogs_corruption参数，强制open resetlogs打开数据库
7.导出数据

具体过程：

测试环境为10g,主要考虑的3种数据段：表/表分区/表子分区

1.表空间被DROP后，数据字典的中记录也被DELETE了。所以需要重造数据字典。所以，需要第一时间停止业务，使用闪回备份一些重要的数据字典
CREATE TABLE RECO_DBA_SEGMENTS AS SELECT * FROM DBA_SEGMENTS AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS') WHERE TABLESPACE_NAME IN ('T2','T3');
CREATE TABLE RECO_DBA_OBJECTS AS SELECT * FROM DBA_OBJECTS AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_SEG$ AS SELECT * FROM SEG$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_TS$ AS SELECT * FROM TS$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_TSQ$ AS SELECT * FROM TSQ$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_FILE$ AS SELECT * FROM FILE$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_obj$ AS SELECT * FROM OBJ$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_tab$ AS SELECT * FROM tab$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_tabpart$ AS SELECT OBJ#,DATAOBJ#,BO#,PART#,HIBOUNDLEN ,TS#,FILE#,BLOCK#,PCTFREE$,PCTUSED$,INITRANS,MAXTRANS,FLAGS,ANALYZETIME,SAMPLESIZE ,ROWCNT,BLKCNT,EMPCNT,AVGSPC,CHNCNT,AVGRLN,SPARE1,SPARE2,SPARE3,BHIBOUNDVAL  FROM tabpart$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');
CREATE TABLE RECO_tabsubpart$ AS SELECT OBJ#,DATAOBJ#,POBJ#,SUBPART#,FLAGS,TS#,FILE#,BLOCK#,PCTFREE$,PCTUSED$,INITRANS,MAXTRANS,ANALYZETIME,SAMPLESIZE ,ROWCNT,BLKCNT,EMPCNT,AVGSPC,CHNCNT,AVGRLN,SPARE1,SPARE2,SPARE3,HIBOUNDLEN ,BHIBOUNDVAL  FROM tabsubpart$ AS OF TIMESTAMP TO_TIMESTAMP('2013-07-27 13:35:47','YYYY-MM-DD HH24:MI:SS');

2.如果想要重构所有的相关数据字典数据，我觉得这是不可能的事情。所以，还需要准备相关表的DDL语句。如果没有，那么只有闪回来的数据字典中，提取相关DDL

3.修改创建对象的DDL，表空间指定为一个已经存在的表空间，重建这些表。索引等就不需要了

4.不要创建新的表空间，否则这会覆盖旧的TS# & FILE#，增加恢复的难度

5.对于DROP表空间操作，TS$,FILE$不是进行的DELETE，而是UPDATE状态位。先还原这两个对象
DELETE TS$ WHERE NAME IN ('T2','T3');
INSERT INTO TS$ SELECT * FROM RECO_TS$ WHERE NAME IN ('T2','T3');
DELETE file$ WHERE ts# IS NULL;
INSERT INTO file$ SELECT * FROM reco_file$ WHERE ts# NOT IN (SELECT ts# FROM file$ WHERE ts# IS not NULL);

6.将obj$中的dataobj#修改为以前旧的dataobj#，否则读取到以前的段时，会由于dataobj#不同而报告ORA-8103
update obj$ set dataobj#=52967 where obj#=53009;
update obj$ set dataobj#=52966 where obj#=53008;
update obj$ set dataobj#=52965 where obj#=53007;
update obj$ set dataobj#=52964 where obj#=53006;
update obj$ set dataobj#=52988 where obj#=53030;
update obj$ set dataobj#=52987 where obj#=53029;
update obj$ set dataobj#=52986 where obj#=53028;
update obj$ set dataobj#=52985 where obj#=53027;
update obj$ set dataobj#=52984 where obj#=53026;
update obj$ set dataobj#=52983 where obj#=53025;
update obj$ set dataobj#=52982 where obj#=53024;
update obj$ set dataobj#=52981 where obj#=53023;
update obj$ set dataobj#=52980 where obj#=53022;
update obj$ set dataobj#=52979 where obj#=53021;
update obj$ set dataobj#=52978 where obj#=53020;
update obj$ set dataobj#=52977 where obj#=53019;
update obj$ set dataobj#=52976 where obj#=53018;
update obj$ set dataobj#=52975 where obj#=53017;
update obj$ set dataobj#=52974 where obj#=53016;
update obj$ set dataobj#=52973 where obj#=53015;
update obj$ set dataobj#=52989 where obj#=53031;
update obj$ set dataobj#=52995 where obj#=53032;

7.将seg$中的段首块的位置，修改为以前旧的位置,这样就可以读取到被删除表空间上的数据段
DELETE FROM seg$ WHERE (file#,block#,blocks,EXTENTS) IN (
SELECT header_file,header_block,blocks,EXTENTS FROM dba_segments WHERE
(OWNER,SEGMENT_NAME,nvl(PARTITION_NAME,'a'))IN (SELECT OWNER,SEGMENT_NAME,nvl(PARTITION_NAME,'a') FROM RECO_DBA_SEGMENTS)
);
INSERT INTO SEG$
SELECT * FROM reco_seg$ WHERE (file#,block#,blocks,EXTENTS) IN (
SELECT header_file,header_block,blocks,EXTENTS FROM reco_dba_segments WHERE segment_type NOT LIKE 'INDEX%');

8.更新tab$,tabpart$,tabsubpart$,这3个表相当于是obj$ seg$的对应关系表
update tabpart$ set file#=6 ,block#=11,ts#=7,dataobj#=52964 where obj#=53006;
update tabpart$ set file#=6 ,block#=19,ts#=7,dataobj#=52965 where obj#=53007;
update tabpart$ set file#=6 ,block#=27,ts#=7,dataobj#=52966 where obj#=53008;
update tabpart$ set file#=6 ,block#=35,ts#=7,dataobj#=52967 where obj#=53009;
update tabsubpart$ set file#=6 ,block#=43,ts#=7,dataobj#=52973 where obj#=53015;
update tabsubpart$ set file#=6 ,block#=51,ts#=7,dataobj#=52974 where obj#=53016;
update tabsubpart$ set file#=6 ,block#=59,ts#=7,dataobj#=52975 where obj#=53017;
update tabsubpart$ set file#=6 ,block#=67,ts#=7,dataobj#=52976 where obj#=53018;
update tabsubpart$ set file#=6 ,block#=75,ts#=7,dataobj#=52977 where obj#=53019;
update tabsubpart$ set file#=6 ,block#=83,ts#=7,dataobj#=52978 where obj#=53020;
update tabsubpart$ set file#=6 ,block#=91,ts#=7,dataobj#=52979 where obj#=53021;
update tabsubpart$ set file#=6 ,block#=99,ts#=7,dataobj#=52980 where obj#=53022;
update tabsubpart$ set file#=6 ,block#=107,ts#=7,dataobj#=52981 where obj#=53023;
update tabsubpart$ set file#=6 ,block#=115,ts#=7,dataobj#=52982 where obj#=53024;
update tabsubpart$ set file#=6 ,block#=123,ts#=7,dataobj#=52983 where obj#=53025;
update tabsubpart$ set file#=6 ,block#=131,ts#=7,dataobj#=52984 where obj#=53026;
update tabsubpart$ set file#=6 ,block#=139,ts#=7,dataobj#=52985 where obj#=53027;
update tabsubpart$ set file#=6 ,block#=147,ts#=7,dataobj#=52986 where obj#=53028;
update tabsubpart$ set file#=6 ,block#=155,ts#=7,dataobj#=52987 where obj#=53029;
update tabsubpart$ set file#=6 ,block#=163,ts#=7,dataobj#=52988 where obj#=53030;
update tab$ set file#=6 ,block#=171,ts#=7,dataobj#=52989 where obj#=53031;
update tab$ set file#=6 ,block#=179,ts#=7,dataobj#=52995 where obj#=53032;

9.重建控制文件
alter system  set "_allow_resetlogs_corruption"=true scope=spfile;
shutdown immediate;
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "O10204" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/o10204/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/o10204/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/o10204/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/o10204/system01.dbf',
  '/u01/app/oracle/oradata/o10204/undotbs01.dbf',
  '/u01/app/oracle/oradata/o10204/sysaux01.dbf',
  '/u01/app/oracle/oradata/o10204/users01.dbf',
  '/u01/app/oracle/oradata/o10204/t1.dbf',
  '/u01/app/oracle/oradata/o10204/t2.dbf',
  '/u01/app/oracle/oradata/o10204/t3.dbf',
  '/u01/app/oracle/oradata/o10204/t4.dbf'
CHARACTER SET ZHS16GBK
;
alter database open resetlogs;

10.检查数据
SQL> select count(*) from ctais2.t2;

  COUNT(*)
----------
        23

SQL> select count(*) from ctais2.t3;

  COUNT(*)
----------
        23

SQL> select count(*) from ctais2.ts_partition;

  COUNT(*)
----------
         4

SQL> select count(*) from ctais2.ts_subpartition;

  COUNT(*)
----------
        16



PS：测试创建的对象
create tablespace T1 datafile '/u01/app/oracle/oradata/o10204/t1.dbf' size 100M reuse;
create tablespace T2 datafile '/u01/app/oracle/oradata/o10204/t2.dbf' size 100M reuse;
create tablespace T3 datafile '/u01/app/oracle/oradata/o10204/t3.dbf' size 100M reuse;
create tablespace T4 datafile '/u01/app/oracle/oradata/o10204/t4.dbf' size 100M reuse;

conn ctais2/oracle

CREATE TABLE ts_partition(
    ID integer,
    birth DATE,
    DATA VARCHAR2(20)
)
PARTITION BY RANGE(birth)
(  
PARTITION p1 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE t2,
PARTITION p2 VALUES LESS THAN(TO_DATE('2008-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE t2,
PARTITION p3 VALUES LESS THAN(TO_DATE('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE t2,
PARTITION p4 VALUES LESS THAN (MAXVALUE)TABLESPACE t2
);

insert into ts_partition values(1,TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), '1111');
insert into ts_partition values(2,TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2222');
insert into ts_partition values(3,TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '3333');
insert into ts_partition values(4,TO_DATE('2008-09-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '4444');
commit;

create table ts_subpartition  
(  
    ID integer,
    birth DATE,
    city varchar2(10),
    DATA VARCHAR2(20)
)  
partition by range (birth)  
SUBPARTITION BY list (city)  
SUBPARTITION TEMPLATE (
   SUBPARTITION sp1 VALUES ('a','b','c','d') tablespace t3,
   SUBPARTITION sp2 VALUES ('e','f','g','h') tablespace t3,
   SUBPARTITION sp3 VALUES ('i','j','k','l') tablespace t3,
   SUBPARTITION sp4 VALUES ('m','n','o','p') tablespace t3
)
(   
PARTITION p1 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION p2 VALUES LESS THAN(TO_DATE('2008-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION p3 VALUES LESS THAN(TO_DATE('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);  

insert into ts_subpartition values(11,TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'a', '1111');
insert into ts_subpartition values(12,TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'b', '2222');
insert into ts_subpartition values(13,TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'c', '3333');
insert into ts_subpartition values(14,TO_DATE('2008-09-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'd', '4444');
insert into ts_subpartition values(21,TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'e', '1111');
insert into ts_subpartition values(22,TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'f', '2222');
insert into ts_subpartition values(23,TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'g', '3333');
insert into ts_subpartition values(24,TO_DATE('2008-09-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'h', '4444');
insert into ts_subpartition values(31,TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'i', '1111');
insert into ts_subpartition values(32,TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'j', '2222');
insert into ts_subpartition values(33,TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'k', '3333');
insert into ts_subpartition values(34,TO_DATE('2008-09-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'l', '4444');
insert into ts_subpartition values(41,TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'm', '1111');
insert into ts_subpartition values(42,TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'n', '2222');
insert into ts_subpartition values(43,TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'o', '3333');
insert into ts_subpartition values(44,TO_DATE('2008-09-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), 'p', '4444');
commit;

-- 表索引约束都在要删除的表空间上
create table t1 tablespace t2 as select * from dba_users;
create index idx_t1 on t1(username) tablespace t3;
alter table t1 add constraints pk_t1 primary key (user_id) using index tablespace t4;

-- 表在保留的表空间上，其他在删除的表空间上
create table t2 tablespace t1 as select * from dba_users;
create index idx_t2 on t2(username) tablespace t3;
alter table t2 add constraints pk_t2 primary key (user_id) using index tablespace t4;

-- 表在删除的表空间商，其他在保留的表空间上
create table t3 tablespace t2 as select * from dba_users;
create index idx_t3 on t3(username) tablespace t1;
alter table t3 add constraints pk_t3 primary key (user_id) using index tablespace t1;

drop tablespace t2 including contents cascade constraints;
drop tablespace t3 including contents cascade constraints;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8242091/viewspace-767275/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-767275/