非歸檔庫誤刪表空間後的資料恢復

westzq1984發表於2013-07-27
資料恢復
前幾天同事問我，非歸檔庫誤刪除的表空間，資料檔案還在，有沒有辦法恢復資料
想了一下，完整的加回去肯定是不可能，但是應該是有辦法把資料給搞出來的

處理的思路為：
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章