首先重申，这个测试是玩的，大家不要借鉴！测试源于群里一个网友的提问。
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.
开始在作了10046 trace发现了如下操作：
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
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
经过对比发现，对于列的删除，tab$ 只是改变了如下几个列：
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
对于 obj$ 而言，如下3个列发生了改变：
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
对于 col$ 的删除。
SQL> select property from col$ where obj# in(54360,54361) and intcol#=2;
PROPERTY
----------
14336
0
另外还涉及到几个索引，都是object_id 小于56的，不要随便动，下面我通过一个最简单的方式进行操作。 为了安全起见，我备份了一下数据库，如下：
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
上面的2个坏块不用管，是以前做测试留下的，没有处理，回头有空再处理了，下面继续：
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.
虽然成功恢复了这个删除的列，但是实际上该列的数据已经没了，因为drop column以后，该列的数据已经 从datafile里面清除了，通过修改数据字典的方式，只是一个欺骗的作用，貌似ODU也不能恢复这种情况。 不过数据库使用闪回的话，应该是可以的，当然是闪回整个db。 补充：对于还没重用的情况，数据其实还在的，看下面的测试：
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
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是行头，需要跳过。
发表回复