[20231103]rename IDL_UB1$后使用bbed的恢复3.txt

[20231103]rename IDL_UB1$后使用bbed的恢复3.txt

–//上午解决rename IDL_UB1$后使用bbed的恢复问题，就是涉及到的5个索引4个需要修改，其中一个因为NULL值的缘故，不需要修改。
–//主要原因是rename是delete再insert obj$,反过来思考，如果修改时长度等长,我仅仅需要name等于原来的字符串就可以，当然有
–//一个前提，rename后在几个索引的位置不能发生变动。不然这样的改动量也是很大的，这样修复索引仅仅是修复i_obj2,i_obj5,
–//name等于原来的字符串就ok了，这样改动仅仅涉及3个块，自己还是测试验证看看。

1.环境：
–//先从冷备份恢复数据库，步骤略。
SYS@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

$ strings -t d /mnt/ramdisk/book/system01.dbf | grep ‘IDL_UB1\$’| awk ‘{printf “%6d %6d %20s\n”,int($1/8192), $1-int($1/8192)*8192, $2}’
243 6325 IDL_UB1$
351 5692 IDL_UB1$
375 5692 IDL_UB1$
17086 4802 IDL_UB1$
34025 5594 SELECT
34029 5278 COMMIT”SELECT
36154 5278 COMMIT”SELECT
95512 3371 IDL_UB1$l

–//注：显示的第一个字段对应数据块号，第二个字段对应相应数据块的偏移.
–//前面的测试我已经探察对应数据块，主要修改数据块243,351,375对应对象是obj$以及对应索引i_obj2,i_obj5.
–//注:前面的恢复测试说明我的疏漏,实际上rename是先delete,在insert,这样涉及到几个索引都要修改,因为rowid已经发生了变化,并
–//且记录在索引里面.参考前面的测试：[20231103]rename IDL_UB1$后使用bbed的恢复的后遗症.txt”

2.开始测试:
SYS@book> rename IDL_UB1$ to IDL_UB1Y;
Table renamed.
–//注：修改前后长度一样,我仅仅将$ -> Y,这样在两个索引段的位置即使变化，也在同一个数据块中，前后位置的差别。

SYS@book> alter system checkpoint;
System altered.

$ strings -t d /mnt/ramdisk/book/system01.dbf | grep ‘IDL_UB1Y’| awk ‘{printf “%6d %6d %20s\n”,int($1/8192), $1-int($1/8192)*8192, $2}’
351 3983 IDL_UB1Y
375 3983 IDL_UB1Y
86182 4750 IDL_UB1Y
–//仅仅涉及3个数据块.我仅仅修改里面的Y=>$,这样等于索引改动还在同一块中，修改的工作量也大大减少.
–//我仅仅需要修改数据块86182以及索引块(351,375)指向的行IDL_UB1Y => IDL_UB1$就ok了。
–//也就是 Y (ascii 0x59) 变成 $ (ascii 0x24).

3.使用bbed观察与修复:
–//dba = 1,86182

BBED> x /rnnncncntttnccnxnnncct dba 1,86182 *kdbr[18]
rowdata[0] @4736
———-
flag@4736: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4737: 0x02
cols@4738: 18

col 0[3] @4739: 225
col 1[3] @4743: 225
col 2[1] @4747: 0
col 3[8] @4749: IDL_UB1Y
col 4[2] @4758: 1
col 5[0] @4761: *NULL*
col 6[2] @4762: 2
col 7[7] @4765: 2013-08-24 11:37:39
col 8[7] @4773: 2023-11-07 08:45:45
col 9[7] @4781: 2023-11-07 08:45:45
col 10[2] @4789: 1
col 11[0] @4792: *NULL*
col 12[0] @4793: *NULL*
col 13[1] @4794: 0
col 14[0] @4796: *NULL*
col 15[1] @4797: 0
col 16[2] @4799: 2
~~~~~~~~~~~~~~~~~~~
col 17[1] @4802: 0

SYS@book> @bbvi 1 86182
BVI_COMMAND
—————————————————————————————————-
bvi -b 706002944 -s 8192 /mnt/ramdisk/book/system01.dbf
xxd -c16 -g 2 -s 706002944 -l 8192 /mnt/ramdisk/book/system01.dbf
dd if=/mnt/ramdisk/book/system01.dbf bs=8192 skip=86182 count=1 of=1_86182.dd conv=notrunc 2>/dev/null
od -j 706002944 -N 8192 -t x1 -v /mnt/ramdisk/book/system01.dbf
hexdump -s 706002944 -n 8192 -C -v /mnt/ramdisk/book/system01.dbf
alter system dump datafile ‘/mnt/ramdisk/book/system01.dbf’ block 86182;
6 rows selected.
–//使用bvi修改比较简单.

–//dba = 1,351
BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4] @3972
———-
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x12

data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1Y
col 2[2] @3992: 1
col 3[0] @3995: *NULL*
col 4[0] @3996: *NULL*
col 5[0] @3997: *NULL*
col 6[2] @3998: 2
col 7[1] @4001: 0
col 8[3] @4003: 225

SYS@book> @bbvi 1 351
BVI_COMMAND
—————————————————–
bvi -b 2875392 -s 8192 /mnt/ramdisk/book/system01.dbf

–//dba = 1,375
BBED> x /rncnnnnccn dba 1,375 *kd_off[37]
rowdata[4] @3972
———-
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x12

data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1Y
col 2[2] @3992: 1
col 3[2] @3995: 2
col 4[1] @3998: 0
col 5[0] @4000: *NULL*
col 6[0] @4001: *NULL*
col 7[0] @4002: *NULL*
col 8[3] @4003: 225

SYS@book> @bbvi 1 375
BVI_COMMAND
——————————————————
bvi -b 3072000 -s 8192 /mnt/ramdisk/book/system01.dbf

BBED> sum apply dba 1,86182
Check value for File 1, Block 86182:
current = 0x3af3, required = 0x3af3

BBED> sum apply dba 1,351
Check value for File 1, Block 351:
current = 0x9706, required = 0x9706

BBED> sum apply dba 1,375
Check value for File 1, Block 375:
current = 0x2f2d, required = 0x2f2d
–//使用完成后，使用x命令检查过程略.

verify dba 1,86182
verify dba 1,351
verify dba 1,375

4.验证是否正确:

SYS@book> alter system flush buffer_cache;
System altered.

SYS@book> alter system flush shared_pool;
System altered.

SYS@book> select rowid from sys.obj$ where name=’IDL_UB1$’;
ROWID
——————
AAAAASAABAAAVCmAAS

SYS@book> @ rowid AAAAASAABAAAVCmAAS
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
———- ———- ———- ———- ——————– ——————– —————————————-
18 1 86182 18 0x4150A6 1,86182 alter system dump datafile 1 block 86182

–//注：在测试过程遇到1个小问题,alter system flush buffer_cache; dba=1,86182块被重写还原(不知代为什么).后面我关闭数据库修改OK.

SYS@book> analyze table idl_ub1$ validate structure cascade;
Table analyzed.

SYS@book> analyze table obj$ validate structure cascade;
analyze table obj$ validate structure cascade
*
ERROR at line 1:
ORA-08100: index is not valid – see trace file for diagnostics

$ oerr ora 8100
08100, 00000, “index is not valid – see trace file for diagnostics”
// *Cause: Validate Index detected an inconsistency in its argument index
// *Action: Send trace file to your customer support representative

–//还是不行!!
–//最后我发现连删除的索引也要按照顺序排序.

BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713] @5681
————-
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17

data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1$
col 2[2] @5701: 1
col 3[0] @5704: *NULL*
col 4[0] @5705: *NULL*
col 5[0] @5706: *NULL*
col 6[2] @5707: 2
col 7[1] @5710: 0
col 8[3] @5712: 225

BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4] @3972
———-
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x12

data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1$
col 2[2] @3992: 1
col 3[0] @3995: *NULL*
col 4[0] @3996: *NULL*
col 5[0] @3997: *NULL*
col 6[2] @3998: 2
col 7[1] @4001: 0
col 8[3] @4003: 225

–//kd_off[36]指向的删除的记录,kd_off[37]指向正常的记录.两个除了flag,keydata[6]不同外,其它一样.
–//$ ascii=24,ascii 23 对应字符 #.
–//我必须把指向kd_off[36]的记录的col 1[8] @5692: IDL_UB1$修改为col 1[8] @5692: IDL_UB1#.这样保证顺序一致.

–//修改后的样子如下：
BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713] @5681
————-
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17

data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1#
col 2[2] @5701: 1
col 3[0] @5704: *NULL*
col 4[0] @5705: *NULL*
col 5[0] @5706: *NULL*
col 6[2] @5707: 2
col 7[1] @5710: 0
col 8[3] @5712: 225

–//dba = 1,375 也是一样,不然 analyze table obj$ validate structure cascade;还会报错.
BBED> x /rncnnnnccn dba 1,375 *kd_off[36]
rowdata[1713] @5681
————-
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17

data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1#
col 2[2] @5701: 1
col 3[2] @5704: 2
col 4[1] @5707: 0
col 5[0] @5709: *NULL*
col 6[0] @5710: *NULL*
col 7[0] @5711: *NULL*
col 8[3] @5712: 225

SYS@book> alter system flush shared_pool;
System altered.

SYS@book> alter system flush buffer_cache;
System altered.

SYS@book> analyze table obj$ validate structure cascade;
Table analyzed.

$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 – Production on Tue Nov 7 09:48:34 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY – Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 97280
Total Pages Processed (Data) : 64260
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13456
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4206
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 15358
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 392040706 (3.392040706)
–//这样算下来工作量也不小.

总结:
–//1.修改对应数据块(86182))以及索引块(351,375)的name=IDL_UB1$.
–//2.修改删除索引对应数据块原来的IDL_UB1$->IDL_UB1#,保证顺序一致.这样analyze table obj$ validate structure cascade;ok.
–//3.我觉得第2步可以不做,这样就无法通过analyze table obj$ validate structure cascade;罢了,不会影响使用.
–//4.不建议采用这样的方式恢复，直接修改数据字段是最高效的方法。