[20190130]删除tab$记录的恢复.txt
--//网上提到许多删除tab$的案例,主要原因在于没有从官方正规渠道下载oracle版本,还有一些来自工具里面带有一些脚本删除tab$记录.
--//首先我并不知道许多人的恢复方法,仅仅简单提到恢复数据字典,我想到既然是删除,反向的操作就是恢复.也就是恢复tab$记录.
--//在我开始尝试时,我发现遇到的问题比原来想像的要复杂.tab$是CLUSTER C_OBJ#的一个表.本身cluster table的结果就与普通
--//堆表的结构不一样,还有可能遇到行链接和行迁移的情况.
--//这也是我最近许多帖子关于cluster table等方面的内容.
--//自己也尝试恢复看看.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> column SQL_TEXT format a100
SCOTT@book> select * from (select * from SYS.BOOTSTRAP$ order by line#) where rownum<=5;
LINE# OBJ# SQL_TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
-1 -1 8.0.0.0.0
0 0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO
0 EXTENTS (FILE 1 BLOCK 128))
2 2 CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL
136K NEXT 200K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 144))
SIZE 800
3 3 CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT
1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1 BLOCK 168))
4 4 CREATE TABLE TAB$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT
NULL,"BLOCK#" NUMBER NOT NULL,"BOBJ#" NUMBER,"TAB#" NUMBER,"COLS" NUMBER NOT NULL,"CLUCOLS" NUMBER,"
PCTFREE$" NUMBER NOT NULL,"PCTUSED$" NUMBER NOT NULL,"INITRANS" NUMBER NOT NULL,"MAXTRANS" NUMBER NO
T NULL,"FLAGS" NUMBER NOT NULL,"AUDIT$" VARCHAR2(38) NOT NULL,"ROWCNT" NUMBER,"BLKCNT" NUMBER,"EMPCN
T" NUMBER,"AVGSPC" NUMBER,"CHNCNT" NUMBER,"AVGRLN" NUMBER,"AVGSPC_FLB" NUMBER,"FLBCNT" NUMBER,"ANALY
ZETIME" DATE,"SAMPLESIZE" NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"INTCOLS" NUMBER NOT NULL,"KERNE
LCOLS" NUMBER NOT NULL,"PROPERTY" NUMBER NOT NULL,"TRIGFLAG" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,
"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 4 TA
BNO 1) CLUSTER C_OBJ#(OBJ#)
--//sys.tab$表是cluster table C_OBJ#下的一个表.OBJ#=2
SELECT ROWNUM -1 rn , a.*
FROM ( SELECT *
FROM dba_objects
WHERE owner = 'SYS' AND data_object_id = 2
ORDER BY object_id) a;
RN OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
-- ----- ----------- ---------- --------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
0 SYS C_OBJ# 2 2 CLUSTER 2013-08-24 11:37:35 2013-08-24 11:37:35 2013-08-24:11:37:35 VALID N N N 5
1 SYS TAB$ 4 2 TABLE 2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID N N N 1
2 SYS CLU$ 5 2 TABLE 2013-08-24 11:37:35 2013-08-24 11:37:35 2013-08-24:11:37:35 VALID N N N 1
3 SYS IND$ 19 2 TABLE 2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID N N N 1
4 SYS ICOL$ 20 2 TABLE 2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID N N N 1
5 SYS COL$ 21 2 TABLE 2013-08-24 11:37:35 2013-08-24 11:52:40 2013-08-24:11:37:35 VALID N N N 1
6 SYS LOB$ 80 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:47:37 2013-08-24:11:37:36 VALID N N N 1
7 SYS COLTYPE$ 83 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:47:37 2013-08-24:11:37:36 VALID N N N 1
8 SYS SUBCOLTYPE$ 86 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID N N N 1
9 SYS NTAB$ 88 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID N N N 1
10 SYS REFCON$ 92 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID N N N 1
11 SYS OPQTYPE$ 95 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID N N N 1
12 SYS ICOLDEP$ 114 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID N N N 1
13 SYS VIEWTRCOL$ 174 2 TABLE 2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID N N N 1
14 SYS LIBRARY$ 252 2 TABLE 2013-08-24 11:37:39 2013-08-24 11:37:39 2013-08-24:11:37:39 VALID N N N 1
15 SYS ASSEMBLY$ 253 2 TABLE 2013-08-24 11:37:39 2013-08-24 11:37:39 2013-08-24:11:37:39 VALID N N N 1
16 SYS ATTRCOL$ 512 2 TABLE 2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID N N N 1
17 SYS TYPE_MISC$ 517 2 TABLE 2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID N N N 1
18 rows selected.
--//可以发现tab$仅仅是cluster table中的1个.而是是第1个表(从0算起,0是cluster table)
2.如何tab$表被全部删除,自然导致数据库无法启动:
--//如何修复呢?
--//CLUSTER C_OBJ#的段头在dba 1,144.
SCOTT@book> select * from dba_extents where owner='SYS' and segment_name='C_OBJ#';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- --------- ------- -------- ------- ------ ------------
SYS C_OBJ# CLUSTER SYSTEM 0 1 144 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 1 1 152 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 2 1 160 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 3 1 3336 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 4 1 4392 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 5 1 5424 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 6 1 6152 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 7 1 7264 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 8 1 7912 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 9 1 8464 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 10 1 8496 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 11 1 8520 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 12 1 8552 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 13 1 8560 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 14 1 8600 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 15 1 8624 65536 8 1
SYS C_OBJ# CLUSTER SYSTEM 16 1 8704 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 17 1 9856 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 18 1 13312 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 19 1 17792 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 20 1 22400 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 21 1 31488 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 22 1 65920 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 23 1 73984 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 24 1 77824 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 25 1 86016 1048576 128 1
SYS C_OBJ# CLUSTER SYSTEM 26 1 94208 1048576 128 1
27 rows selected.
--//system的表空间是mssm,bbed可以查看这种文件的段头.这些信息记录在
BBED> p /d dba 1,144 ktetb
struct ktetb[0], 8 bytes @108
ub4 ktetbdba @108 4194449
ub4 ktetbnbk @112 7
--//这里不算段头,记录数据部分.仅仅占7块.
struct ktetb[1], 8 bytes @116
ub4 ktetbdba @116 4194456
ub4 ktetbnbk @120 8
struct ktetb[2], 8 bytes @124
ub4 ktetbdba @124 4194464
ub4 ktetbnbk @128 8
struct ktetb[3], 8 bytes @132
ub4 ktetbdba @132 4197640
ub4 ktetbnbk @136 8
struct ktetb[4], 8 bytes @140
ub4 ktetbdba @140 4198696
ub4 ktetbnbk @144 8
..
struct ktetb[25], 8 bytes @308
ub4 ktetbdba @308 4280320
ub4 ktetbnbk @312 128
struct ktetb[26], 8 bytes @316
ub4 ktetbdba @316 4288512
ub4 ktetbnbk @320 128
--//我程序中使用的别名如下:
$ alias zdate='date +'\''%Y/%m/%d %T'\'''
$ export RLWRAP=$(which rlwrap)
$ type rlbbed
rlbbed is a function
rlbbed ()
{
cd /home/oracle/bbed;
$RLWRAP -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=cmd.par
}
--//关于bbed配置看相关文档.我下面有一些代码使用cut直接取对应位置的数据,可能width(宽度)设置要与我脚本保持一致.
$ cat cmd.par
set count 64
set width 160
$ cat bbed.par
blocksize=8192
listfile=$HOME/bbed/filelist.txt
mode=edit
PASSWORD=blockedit
SPOOL=Y
--//filelist.txt文件通过select file#||' '||name c100 from v$dbfile order by file#;生成.
--//首先确定扫描那些块的脚本.
/bin/rm /home/oracle/zzz430/bbed/scan*.txt
cd /home/oracle/zzz430/bbed
echo "process 1 start : `zdate` scan dba 1,144 , create scan1.txt about ktetbdba,ktetbnbk"
echo "p /d dba 1,144" ktetb | rlbbed | egrep 'ktetbdba|ktetbnbk' | cut -c8-16,55- |tr " " "=" | paste -d ";" - - > scan1a.txt
high_water=`echo p /d dba 1,144 ktech.hwmark_ktech.blkno_ktehw | rlbbed | grep blkno_ktehw | cut -c60- | tr -d " " `
sed "\$s/ktetbnbk=.*$/ktetbnbk=$high_water/" scan1a.txt > scan1.txt
read -p "process 1 finish: `zdate`,enter continue..."
--//结果保存scan1.txt
--//也就是从dba=4194449开始,扫描7块. 从4194456开始,扫描8块....如此到结束。
--//注意仅仅扫描到高水位下的块.
3.分析扫描块的情况:
BBED> set dba 4194449
DBA 0x00400091 (4194449 1,145)
BBED> p /d kdbt[1]
struct kdbt[1], 4 bytes @110
sb2 kdbtoffs @110 8
sb2 kdbtnrow @112 7
--//按照前面的查询，仅仅kdbt[1]记录相关表sys.tab$的记录信息，从偏移8(即*kdbr[8])开始,共有7条记录.
BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[8]
rowdata[7430] @7884
-------------
flag@7884: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7885: 0x00
cols@7886: 31
col 0[2] @7888: 2
col 1[1] @7891: 0
col 2[2] @7893: 1
col 3[3] @7896: 144
col 4[2] @7900: 2
col 5[2] @7903: 4
col 6[2] @7906: 14
col 7[2] @7909: 1
col 8[1] @7912: 0
col 9[1] @7914: 0
col 10[1] @7916: 0
col 11[1] @7918: 0
col 12[3] @7920: 529
col 13[38] @7924: --------------------------------------
col 14[3] @7963: 7789
col 15[3] @7967: 1442
col 16[1] @7971: 0
col 17[1] @7973: 0
col 18[1] @7975: 0
col 19[2] @7977: 32
col 20[1] @7980: 0
col 21[1] @7982: 0
col 22[7] @7984: 2017-02-03 22:00:18
col 23[3] @7992: 7789
col 24[0] @7996: *NULL*
col 25[0] @7997: *NULL*
col 26[2] @7998: 14
col 27[2] @8001: 14
col 28[3] @8004: 1024
col 29[1] @8008: 0
col 30[1] @8010: 0
..
BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[14]
rowdata[6684] @7138
-------------
flag@7138: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7139: 0x02
cols@7140: 31
ckix@7141: 7
col 0[2] @7142: 2
col 1[1] @7145: 0
col 2[2] @7147: 1
col 3[3] @7150: 144
col 4[2] @7154: 2
col 5[2] @7157: 3
col 6[2] @7160: 34
col 7[2] @7163: 1
col 8[1] @7166: 0
col 9[1] @7168: 0
col 10[1] @7170: 0
col 11[1] @7172: 0
col 12[3] @7174: 529
col 13[38] @7178: --------------------------------------
col 14[3] @7217: 5066
col 15[3] @7221: 1442
col 16[1] @7225: 0
col 17[1] @7227: 0
col 18[1] @7229: 0
col 19[2] @7231: 91
col 20[1] @7234: 0
col 21[1] @7236: 0
col 22[7] @7238: 2017-02-22 22:00:13
col 23[3] @7246: 5066
col 24[0] @7250: *NULL*
col 25[0] @7251: *NULL*
col 26[2] @7252: 34
col 27[2] @7255: 34
col 28[3] @7258: 1024
col 29[1] @7262: 0
col 30[1] @7264: 0
--//获取tab$的记录偏移以及数量.使用scan2.sh脚本.
echo "process 2 start : `zdate` scan block , get kdbtnrow,kdbtoffs ang grep kdbtnrow=0"
cat scan1.txt | while read i
do
eval $i
#echo $ktetbdba $ktetbnbk
for ((j=1; j<=$ktetbnbk ; j++))
do
echo -n "dba=$ktetbdba;" >> scan2a.txt
echo "p /d dba $ktetbdba offset 0 kdbt[1]" | rlbbed | egrep 'kdbtoffs|kdbtnrow' | cut -c8-16,55- |tr " " "=" | paste -d ";" - - >> scan2a.txt
ktetbdba=$[ ktetbdba + 1 ]
#echo $ktetbdba
done
done
grep -v kdbtnrow=0 scan2a.txt > scan2.txt
read -p "process 2 finish: `zdate`,enter continue..."
..
--//生成scan2a.txt,过滤掉kdbtnrow=0.结果保存scan2.txt
$ head scan2.txt
dba=4194449;kdbtoffs=8;kdbtnrow=7
dba=4194450;kdbtoffs=8;kdbtnrow=5
dba=4194451;kdbtoffs=9;kdbtnrow=6
dba=4194452;kdbtoffs=10;kdbtnrow=9
dba=4194453;kdbtoffs=10;kdbtnrow=10
dba=4194454;kdbtoffs=10;kdbtnrow=10
dba=4194455;kdbtoffs=10;kdbtnrow=10
dba=4194456;kdbtoffs=10;kdbtnrow=10
dba=4194457;kdbtoffs=10;kdbtnrow=10
dba=4194458;kdbtoffs=7;kdbtnrow=7
3.生成修改记录flag偏移的脚本:
echo "process 3 start : `zdate` scan block , create bbed'script scan3_bbed.txt for modify delete of flag and create scan4a.txt about block of ckix "
cat scan2.txt | while read i
do
eval $i
begin=$kdbtoffs
end=$[ kdbtoffs + kdbtnrow -1 ]
# echo $dba $kdbtoffs $kdbtnrow $begin $end
kdbr_size=`echo map dba $dba| rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//" -e "s/].*$//" `
while [ $begin -le $end ]
do
kdbr_off=`echo p dba $dba offset 0 kdbr | rlbbed | grep "\[$begin\]" | cut -c55-`
if [ $kdbr_off -gt $kdbr_size ]
then
echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | grep KDRHFD | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k
#echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k
do
eval $k
#echo $dba $offset $value
value=`printf "0x%x" $(( value - 0x10 )) `
echo "assign /x dba $dba offset $offset = $value " >> scan3_bbed.txt
done
# if not found ckix@ and found flag=0x7c , then ckix_value=0, and do not process chained row.
echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | grep "^ckix@" | sed -e "s/^ckix/dba=$dba;/" -e 's/@.*:/ckix_value=/' | tr -d " " >> scan4a.txt
echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^ckix@" > /dev/null
if [ $? -eq 1 ]
then
echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^^flag@.*: *0x7c" > /dev/null
if [ $? -eq 0 ]
then
echo "dba=$dba;ckix_value=0" >> scan4a.txt
fi
fi
fi
begin=$[ begin + 1 ]
done
done
read -p "process 3 finish: `zdate`,enter continue..."
--//记录的偏移量如果小于kdbr_size,这些是覆盖的记录可能无法恢复,必须跳过.
--//注意仅仅过滤包含KDRHFD标识的记录需要修改,生成修改偏移的脚本保存在scan3_bbed.txt.
--//修改flag 使用当前值 减去 0x10.
--//并且保存dba,ckix位置的脚本在scan4a.txt,注意使用注解部分,仅仅过滤KDRHFD标志的记录.(写脚本时tab$记录还没有删除)
--//注:如果过滤不存在ckix,使用ckix_value=0替代,如果存在行迁移,跳过不处理.
$ head scan3_bbed.txt
assign /x dba 4194449 offset 7884 = 0x5c
assign /x dba 4194449 offset 7756 = 0x5c
assign /x dba 4194449 offset 7632 = 0x5c
assign /x dba 4194449 offset 7512 = 0x5c
assign /x dba 4194449 offset 7388 = 0x5c
assign /x dba 4194449 offset 7266 = 0x5c
assign /x dba 4194449 offset 7138 = 0x5c
assign /x dba 4194450 offset 509 = 0x5c
assign /x dba 4194450 offset 7882 = 0x5c
assign /x dba 4194450 offset 7756 = 0x5c
--//注:实际上正常恢复大部分值是0x6c,删除前是0x7c.
$ head scan4a.txt
dba=4194449;ckix_value=0
dba=4194449;ckix_value=1
dba=4194449;ckix_value=2
dba=4194449;ckix_value=4
dba=4194449;ckix_value=5
dba=4194449;ckix_value=6
dba=4194449;ckix_value=7
dba=4194450;ckix_value=1
dba=4194450;ckix_value=3
dba=4194450;ckix_value=4
4.修改mref_offset标识:
echo "process 4 start : `zdate` create bbed's scan4_bbed.txt for modify cluster of mref of value "
sort scan4a.txt | uniq > scan4b.txt
cat scan4b.txt | while read i
do
eval $i
#echo $dba $ckix_value
echo -n "dba=$dba;" >> scan4c.txt
echo "x /rn dba $dba *kdbr[$ckix_value]" | rlbbed | egrep "^kref@|^mref@" | sed -e "s/@/_offset=/" -e "s/:/;value=/" | tr -d " " | paste -d ";" - - >> scan4c.txt
done
sed -e 's/;$/;mref_offset=0;value=0/' -e 's/value=/valuek=/' scan4c.txt > scan4.txt
cat scan4.txt | while read i
do
eval $i
# echo $dba $kref_offset $valuek $mref_offset $value
if [ $mref_offset -eq 0 ]
then
mref_offset=$[ $kref_offset+ 2 ]
fi
valuem=$[ value + 1 ]
if [ $valuem -lt $valuek ]
then
echo "assign dba $dba offset $mref_offset = $valuem" >> scan4m_bbed.txt
else
echo "assign dba $dba offset $mref_offset = $valuek" >> scan4k_bbed.txt
fi
done
read -p "process 4 finish: `zdate`,enter continue..."
--//注:如果没有mref表示的情况其值等于0.实际上这步可以不做修复.对于读取tab$表没有问题的.
--//不做,verify类似如下错误.
Block Checking: DBA = 4288536, Block Type = KTB-managed data block
data header at 0x7f865724125c
kdbchk: key comref count wrong
keyslot=8
Block 94232 failed with check code 6121
5.建立sum apply脚本:
echo "process 5 start : create bbed's scan5_bbed.txt for sum apply"
sed -e 's/^dba=/sum apply dba /' -e 's/;.*$//' scan2.txt > scan5_bbed.txt
read -p "process 5 finish: `zdate`,enter continue..."
6.注意问题.
--//恢复记录后,tab$的索引I_TAB1与表tab$存在不一致的情况.
SYS@book> select rowid,a.* from SYS.BOOTSTRAP$ a where a.sql_text like '%I_TAB1%';
ROWID LINE# OBJ# SQL_TEXT
------------------ ---------- ---------- ------------------------------------------------------------
AAAAA7AABAAAAILAAJ 33 33 CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAX
TRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAX
EXTENTS 2147483645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BL
OCK 312))
--//必须禁用这个索引.
SYS@book> @ rowid AAAAA7AABAAAAILAAJ
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
59 1 523 9 0x40020B 1,523 alter system dump datafile 1 block 523 ;
BBED> x /rnnc dba 1,523 *kdbr[9]
rowdata[1269] @4910
-------------
flag@4910: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4911: 0x01
cols@4912: 3
col 0[2] @4913: 33
col 1[2] @4916: 33
col 2[189] @4919: CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483
645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))
--//设置flag=3c,表示删除.
assign /x dba 1,523 offset 4910= 0x3c
7.剩下的就是测试.但愿OK.
--//太长,另外写一篇blog具体操作过程.另外对于已经出问题的系统,可能要设置_system_trig_enabled=false,job_queue_processes=0启动数据库.可能还有一些
--//细节需要注意.