列删除的恢复测试 – 不要模仿
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 列删除的恢复测试 – 不要模仿
|
1 |
首先重申，这个测试是玩的，大家不要借鉴！测试源于群里一个网友的提问。 |
|
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 |
SQL> show user USER is ROGER SQL> drop table t; Table dropped. SQL> create table t(a number, b varchar2(6)); Table created. SQL> insert into t values(1, 'roger'); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> commit; Commit complete. SQL> select * from t; A B ---------- ------ 1 roger 1 roger 1 roger SQL> conn roger/roger Connected. SQL> drop table t3; Table dropped. SQL> create table t3 as select owner,object_name,object_type from dba_objects; Table created. SQL> create table t4 as select * from t3; Table created. SQL> select owner,object_name,object_id 2 from dba_objects 3 where object_name in ('T3','T4'); OWNER OBJECT_NAME OBJECT_ID ------------------------------ -------------------- ---------- ROGER T3 54360 ROGER T4 54361 SQL> analyze table roger.t3 compute statistics; Table analyzed. SQL> analyze table roger.t4 compute statistics; Table analyzed. |
|
1 |
开始在作了10046 trace发现了如下操作： |
|
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 |
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6), intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12, pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20, avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26), instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32, spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1 update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15, spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null) and (subname=:12 or subname is null and :12 is null) delete com$ where obj#=:1 and col#=:2 delete from sys.col_usage$ where obj#= :1 and intcol#= :2 delete from objauth$ where obj#=:1 and col#=:2 delete from col$ where obj#=:1 and intcol#=:2 -- delete from idl_ub1$ where obj#=:1 delete from idl_char$ where obj#=:1 delete from idl_ub2$ where obj#=:1 delete from idl_sb4$ where obj#=:1 delete from error$ where obj#=:1 delete from superobj$ where subobj# = :1 delete from tab_stats$ where obj#=:1 |
|
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 |
SQL> select * from com$ where obj#=54360; no rows selected SQL> select * from sys.col_usage$ where obj#=54360; no rows selected SQL> select count(*) from objauth$ where obj#=54360; COUNT(*) ---------- 0 SQL> select count(*) from col$ where obj#=54360; COUNT(*) ---------- 3 SQL> select count(*) from idl_ub1$ where obj#=54360; COUNT(*) ---------- 0 SQL> select count(*) from idl_char$ where obj#=54360; COUNT(*) ---------- 0 SQL> select count(*) from idl_ub2$ where obj#=54360; COUNT(*) ---------- 0 SQL> select count(*) from idl_sb4$ where obj#=54360; COUNT(*) ---------- 0 SQL> select count(*) from error$ where obj#=54360; COUNT(*) ---------- 0 SQL> select count(*) from tab_stats$ where obj#=54360; COUNT(*) ---------- 0 SQL> select count(*) from superobj$; COUNT(*) ---------- 0 |
|
1 |
经过对比发现，对于列的删除，tab$ 只是改变了如下几个列： |
|
1 2 3 4 5 6 |
SQL> select COLS,INTCOLS,KERNELCOLS,AVGSPC,AVGRLN from tab$ where obj# in(54360,54361); COLS INTCOLS KERNELCOLS AVGSPC AVGRLN ---------- ---------- ---------- ---------- ---------- 3 3 3 834 41 2 2 2 2272 33 |
|
1 |
对于 obj$ 而言，如下3个列发生了改变： |
|
1 2 3 4 5 6 |
SQL> select ctime,mtime,stime,spare2 from obj$ where obj# in(54360,54361); CTIME MTIME STIME SPARE2 ------------------- ------------------- ------------------- ---------- 2012-02-23 04:43:37 2012-02-23 04:43:37 2012-02-23 04:43:37 1 2012-02-23 04:43:46 2012-02-23 04:44:38 2012-02-23 04:44:38 2 |
|
1 |
对于 col$ 的删除。 |
|
1 2 3 4 5 6 |
SQL> select property from col$ where obj# in(54360,54361) and intcol#=2; PROPERTY ---------- 14336 0 |
|
1 2 |
另外还涉及到几个索引，都是object_id 小于56的，不要随便动，下面我通过一个最简单的方式进行操作。 为了安全起见，我备份了一下数据库，如下： |
|
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 |
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /home/ora10g/archivelog Oldest online log sequence 235 Next log sequence to archive 237 Current log sequence 237 [ora10g@killdb archivelog]$ ls -ltr | head -200 | awk '{print $9}'| xargs rm -rf [ora10g@killdb ~]$ dbv file=/home/ora10g/oradata/roger/roger02.dbf DBVERIFY: Release 10.2.0.5.0 - Production on Thu Feb 23 19:57:20 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger02.dbf Page 548 is marked corrupt Corrupt block relative dba: 0x01800224 (file 6, block 548) Completely zero block found during dbv: Page 50403 is marked corrupt Corrupt block relative dba: 0x0180c4e3 (file 6, block 50403) Completely zero block found during dbv: Block Checking: DBA = 25217508, Block Type = KTB-managed data block data header at 0xb7f5907c kdbchk: row count in table index incorrect Page 51684 failed with check code 6125 DBVERIFY - Verification complete Total Pages Examined : 64000 Total Pages Processed (Data) : 57061 Total Pages Failing (Data) : 1 Total Pages Processed (Index): 5978 Total Pages Failing (Index): 0 Total Pages Processed (Other): 839 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 120 Total Pages Marked Corrupt : 2 Total Pages Influx : 0 Highest block SCN : 2549808 (0.2549808) RMAN> run { 2> set maxcorrupt for datafile 6 to 2; 3> backup full as compressed backupset database plus archivelog; } executing command: SET MAX CORRUPT Starting backup at 23-FEB-12 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=230 recid=206 stamp=775116934 input archive log thread=1 sequence=231 recid=207 stamp=775468322 input archive log thread=1 sequence=232 recid=208 stamp=775545128 input archive log thread=1 sequence=233 recid=209 stamp=775548250 input archive log thread=1 sequence=234 recid=210 stamp=775945249 input archive log thread=1 sequence=235 recid=211 stamp=775975475 input archive log thread=1 sequence=236 recid=212 stamp=776029642 input archive log thread=1 sequence=237 recid=213 stamp=776029875 input archive log thread=1 sequence=238 recid=214 stamp=776029937 input archive log thread=1 sequence=239 recid=215 stamp=776030013 input archive log thread=1 sequence=240 recid=216 stamp=776030070 input archive log thread=1 sequence=241 recid=217 stamp=776030315 channel ORA_DISK_1: starting piece 1 at 23-FEB-12 channel ORA_DISK_1: finished piece 1 at 23-FEB-12 piece handle=/home/ora10g/product/10.2/dbs/0qn42i3b_1_1 tag=TAG20120223T195835 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:37 Finished backup at 23-FEB-12 Starting backup at 23-FEB-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00005 name=/home/ora10g/oradata/roger/roger01.dbf input datafile fno=00002 name=/home/ora10g/oradata/roger/undotbs01.dbf input datafile fno=00006 name=/home/ora10g/oradata/roger/roger02.dbf input datafile fno=00001 name=/home/ora10g/oradata/roger/system01.dbf input datafile fno=00003 name=/home/ora10g/oradata/roger/sysaux01.dbf input datafile fno=00004 name=/home/ora10g/oradata/roger/users01.dbf input datafile fno=00008 name=/home/ora10g/oradata/roger/sqlt_01.dbf input datafile fno=00007 name=/home/ora10g/oradata/roger/test1.dbf channel ORA_DISK_1: starting piece 1 at 23-FEB-12 channel ORA_DISK_1: finished piece 1 at 23-FEB-12 piece handle=/home/ora10g/product/10.2/dbs/0rn42i4g_1_1 tag=TAG20120223T195912 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45 channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 23-FEB-12 channel ORA_DISK_1: finished piece 1 at 23-FEB-12 piece handle=/home/ora10g/product/10.2/dbs/0sn42i7p_1_1 tag=TAG20120223T195912 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 23-FEB-12 Starting backup at 23-FEB-12 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=242 recid=218 stamp=776030460 channel ORA_DISK_1: starting piece 1 at 23-FEB-12 channel ORA_DISK_1: finished piece 1 at 23-FEB-12 piece handle=/home/ora10g/product/10.2/dbs/0tn42i7s_1_1 tag=TAG20120223T200100 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 23-FEB-12 |
|
1 |
上面的2个坏块不用管，是以前做测试留下的，没有处理，回头有空再处理了，下面继续： |
|
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 |
SQL> select obj#,name from obj$ where name in('I_TAB1','I_OBJ3','I_COL1','I_COL2','I_COL3'); OBJ# NAME ---------- ------------------------------ 38 I_OBJ3 46 I_COL2 33 I_TAB1 47 I_COL3 45 I_COL1 SQL> update tab$ set cols=3 where obj#=54361; 1 row updated. SQL> update tab$ set intcols=3 where obj#=54361; 1 row updated. SQL> update tab$ set KERNELCOLS=3 where obj#=54361; 1 row updated. SQL> update tab$ set AVGSPC=834 where obj#=54361; 1 row updated. SQL> update tab$ set avgrln=41 where obj#=54361; 1 row updated. SQL> update obj$ set mtime='2012-02-23 04:43:46' where obj#=54361; 1 row updated. SQL> update obj$ set stime='2012-02-23 04:43:46' where obj#=54361; 1 row updated. SQL> update obj$ set spare2=1 where obj#=54361; 1 row updated. SQL> insert into col$ 2 values 3 (54361, 4 3, 5 3, 6 19, 7 0, 8 'OBJECT_TYPE', 9 1, 10 19, 11 0, 12 '', 13 '', 14 0, 15 '', 16 '', 17 3, 18 14336, 19 852, 20 1, 21 0, 22 0, 23 19, 24 '', 25 '', 26 ''); 1 row created. SQL> commit; Commit complete. SQL> set lines 80 SQL> desc roger.t3 Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) OBJECT_TYPE VARCHAR2(19) SQL> desc roger.t4 Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) OBJECT_TYPE VARCHAR2(19) SQL> select count(*) from t3; COUNT(*) ---------- 50918 SQL> select count(*) from t4; COUNT(*) ---------- 50918 SQL> set lines 120 SQL> col object_name for a20 SQL> col owner for a20 SQL> select * from t3 where rownum <2; OWNER OBJECT_NAME OBJECT_TYPE -------------------- -------------------- ------------------- SYS ICOL$ TABLE SQL> select * from t4 where rownum <2; OWNER OBJECT_NAME OBJECT_TYPE -------------------- -------------------- ------------------- SYS ICOL$ SQL> select * from t4 where rownum <10; OWNER OBJECT_NAME OBJECT_TYPE -------------------- -------------------- ------------------- SYS ICOL$ SYS I_USER1 SYS CON$ SYS UNDO$ SYS C_COBJ# SYS I_OBJ# SYS PROXY_ROLE_DATA$ SYS I_IND1 SYS I_CDEF2 9 rows selected. |
|
1 2 3 4 5 6 |
虽然成功恢复了这个删除的列，但是实际上该列的数据已经没了，因为drop column以后，该列的数据已经 从datafile里面清除了，通过修改数据字典的方式，只是一个欺骗的作用，貌似ODU也不能恢复这种情况。 不过数据库使用闪回的话，应该是可以的，当然是闪回整个db。 补充：对于还没重用的情况，数据其实还在的，看下面的测试： |
|
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 |
SQL> conn roger/roger Connected. SQL> create table drop_col(a varchar2(4),b varchar2(4)); Table created. SQL> insert into drop_col values('AA','BB'); 1 row created. SQL> commit; Commit complete. SQL> alter table drop_col drop column b; Table altered. SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# 2 from drop_col; FILE# BLK# ---------- ---------- 5 29960 SQL> alter system dump datafile 5 block 29960; System altered. SQL> select dump('AA',16) from dual; DUMP('AA',16) ------------------- Typ=96 Len=2: 41,41 SQL> select dump('BB',16) from dual; DUMP('BB',16) ------------------- Typ=96 Len=2: 42,42 |
|
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 |
BBED> set file 5 block 29960 FILE# 5 BLOCK# 29960 BBED> find /c BB File: /home/ora10g/oradata/roger/roger01.dbf (5) Block: 29960 Offsets: 8186 to 8191 Dba:0x01407508 ------------------------------------------------------------------------ 42420106 8268 <32 bytes per line> BBED> p rowdata ub1 rowdata[0] @8179 0x2c ub1 rowdata[1] @8180 0x02 ub1 rowdata[2] @8181 0x01 ub1 rowdata[3] @8182 0x02 ub1 rowdata[4] @8183 0x41 ub1 rowdata[5] @8184 0x41 ub1 rowdata[6] @8185 0x02 ub1 rowdata[7] @8186 0x42 ub1 rowdata[8] @8187 0x42 |
在这种情况下，应该是可以直接把rowdata里面的数据给抽取出来的，前面的02是行头，需要跳过。
Leave a Reply
You must be logged in to post a comment.