首页 1 2 3 4 5 6 7

oracle 恢复tab$,[20190225]删除tab$记录的恢复5.txt

[20190225]删除tab$记录的恢复5.txt

--//昨天下午看了链接https://blog.csdn.net/Enmotech/article/details/87834503,大概知道对方删除tab$记录的恢复方法.

--//实际上就是"狸猫换太子"的方法,利用好的数据库的对应块覆盖坏数据库的对应块,再启动数据库.然后利用删除前的tab$的备份

--//数据导入tab$,然后解决数据字典不一致的问题.

--//我当时测试的冷备份还在,也测试看看:

--//以下一些执行脚本直接使用源链接.https://blog.csdn.net/Enmotech/article/details/87834503

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

create table ORACHK001 tablespace system as select * from sys.tab$;

SYS@book> create table ORACHK001 tablespace system as select * from sys.tab$;

Table created.

SYS@book> select count(*) from sys.tab$;

COUNT(*)

----------

2966

SYS@book> select count(*) from orachk001;

COUNT(*)

----------

2965

--//这样建立的表不包含orachk001.

SYS@book> select * from sys.tab$ minus select * from orachk001;

...

SYS@book> select OBJ# , DATAOBJ# from sys.tab$ minus select OBJ# , DATAOBJ# from orachk001;

OBJ# DATAOBJ#

---------- ----------

91090 91090

SYS@book> insert into orachk001 select * from sys.tab$ where (OBJ#,DATAOBJ#) in ((91090,91090));

1 row created.

SYS@book> commit ;

Commit complete.

2.做一个冷备份:

--//关闭数据库略.

$ cp -r /mnt/ramdisk/book /home/oracle/backup/

--//重启数据库.

SYS@book> startup

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

Database opened.

SYS@book> delete from sys.tab$;

2966 rows deleted.

SYS@book> commit ;

Commit complete.

SYS@book> alter system checkpoint ;

System altered.

SYS@book> alter system checkpoint ;

System altered.

SYS@book> alter system checkpoint ;

System altered.

SYS@book> shutdown immediate ;

ORA-00957: duplicate column name

SYS@book> shutdown abort ;

ORACLE instance shut down.

--//再保留一份坏的备份.

$ mv /home/oracle/backup/book /home/oracle/backup/book_20190122_good

`/home/oracle/backup/book' -> `/home/oracle/backup/book_20190122_good'

$ cp -r /mnt/ramdisk/book /home/oracle/backup/

$ mv /home/oracle/backup/book /home/oracle/backup/book_20190122_bad

`/home/oracle/backup/book' -> `/home/oracle/backup/book_20190122_bad'

2.确定数据库open需要访问哪些核心基表呢？

--//先使用好的数据库启动确定对象:

SYS@book> startup mount

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

SYS@book> @ 10046on 12

old 1: alter session set events '10046 trace name context forever, level &1'

new 1: alter session set events '10046 trace name context forever, level 12'

Session altered.

SYS@book> alter database open ;

Database altered.

--//注我的测试如果使用upgrade,这样涉及的块少一些,但是不行.

SYS@book> @ 10046off

Session altered.

SYS@book> @ pp

TRACEFILE

--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_56704.trc

2,4,5,6,8,10,14,15,16,17,18,19,20,21,22,23,25,28,29,31,32,59,61,68,69,74,80,83,86,88,92,95,98,99,101,103,104,105,118,160,192,221,222,223,225,226,227,228

246,248,250,252,294,297,300,301,302,303,304,307,311,375,390,433,436,438,446,448,451,453,455,463,506,514,515,517,567,587,713,717,721,1297,1300,1302,1304

1306,1307,1309,1314,5541,5582,5780,5794,5797,5804,5814,6571,6731,7144,12939,12973,13003,13059,13273,13298,13604,14137,69043,75529,80805,83960,88118,88125

--//看看是那些对象:

SELECT a.OBJ#

,TAB#

,a.DATAOBJ#

,BOBJ#

,NAME

,DBMS_ROWID.ROWID_RELATIVE_FNO (a.ROWID) FILE_ID

,DBMS_ROWID.ROWID_BLOCK_NUMBER (a.ROWID) BLOCK_ID

FROM TAB$ a, obj$ b

WHERE a.obj# = b.obj#

AND A.OBJ# IN (

2,4,5,6,8,10,14,15,16,17,18,19,20,21,22,23,25,28,29,31,32,59,61,68,69,74,80,83,86,88,92,95,98,99,101,103,104,105,118,160,192,221,222,223,225,226,227,228,

246,248,250,252,294,297,300,301,302,303,304,307,311,375,390,433,436,438,446,448,451,453,455,463,506,514,515,517,567,587,713,717,721,1297,1300,1302,1304,

1306,1307,1309,1314,5541,5582,5780,5794,5797,5804,5814,6571,6731,7144,12939,12973,13003,13059,13273,13298,13604,14137,69043,75529,80805,83960,88118,88125

);

OBJ# TAB# DATAOBJ# BOBJ# NAME FILE_ID BLOCK_ID

----- ---- -------- ----- ------------------------------ ------- --------

4 1 2 2 TAB$ 1 147

5 2 2 2 CLU$ 1 146

14 2 8 8 SEG$ 1 146

15 15 UNDO$ 1 145

16 2 6 6 TS$ 1 146

17 17 FILE$ 1 145

18 18 OBJ$ 1 147

19 3 2 2 IND$ 1 145

20 4 2 2 ICOL$ 1 145

21 5 2 2 COL$ 1 146

22 1 10 10 USER$ 1 147

23 23 PROXY_DATA$ 1 146

25 25 PROXY_ROLE_DATA$ 1 145

28 28 CON$ 1 145

31 1 29 29 CDEF$ 1 148

32 2 29 29 CCOL$ 1 147

59 59 BOOTSTRAP$ 1 147

61 61 OBJAUTH$ 1 148

68 68 SYN$ 1 148

69 69 VIEW$ 1 148

74 74 SEQ$ 1 149

80 6 2 2 LOB$ 1 149

83 7 2 2 COLTYPE$ 1 149

86 8 2 2 SUBCOLTYPE$ 1 149

88 9 2 2 NTAB$ 1 149

92 10 2 2 REFCON$ 1 149

95 11 2 2 OPQTYPE$ 1 149

98 98 PROPS$ 1 149

99 99 EDITION$ 1 149

101 101 FIXED_OBJ$ 1 149

103 103 MIGRATE$ 1 150

104 104 DEPENDENCY$ 1 150

105 105 ACCESS$ 1 150

118 118 SYSAUTH$ 1 150

160 160 TRIGGER$ 1 152

192 192 SQL$ 1 153

221 221 PROCEDURE$ 1 154

222 222 PROCEDUREINFO$ 1 154

223 223 ARGUMENT$ 1 154

225 225 IDL_UB1$ 1 155

226 226 IDL_CHAR$ 1 155

227 227 IDL_UB2$ 1 155

228 228 IDL_SB4$ 1 155

246 246 PROCEDUREJAVA$ 1 156

248 248 PROCEDUREC$ 1 156

250 250 PROCEDUREPLSQL$ 1 156

252 14 2 2 LIBRARY$ 1 156

294 294 RESOURCE_PLAN$ 1 158

297 297 RESOURCE_PLAN_DIRECTIVE$ 1 159

300 300 RESOURCE_STORAGE_POOL_MAPPING$ 1 159

301 301 RESOURCE_CAPABILITY$ 1 159

302 302 RESOURCE_INSTANCE_CAPABILITY$ 1 159

303 303 RESOURCE_IO_CALIBRATE$ 1 159

304 304 TSM_SRC$ 1 159

307 307 TSM_DST$ 1 159

311 311 SERVICE$ 1 160

375 375 RLS$ 1 163

390 390 RADM_FPTM$ 1 163

433 433 XS$SESSIONS 1 165

436 436 XS$SESSION_ROLES 1 165

438 438 XS$SESSION_APPNS 1 165

446 1 444 444 HISTGRM$ 1 166

448 448 HIST_HEAD$ 1 166

451 451 AUX_STATS$ 1 166

453 453 TAB_STATS$ 1 166

455 455 IND_STATS$ 1 166

463 463 ASSOCIATION$ 1 167

506 506 OPTSTAT_HIST_CONTROL$ 1 3337

514 514 ID_GENS$ 1 3337

515 515 OID$ 1 3337

517 17 2 2 TYPE_MISC$ 1 3337

567 567 KOPM$ 1 3339

587 587 PARTOBJ$ 1 3341

713 713 STREAMS$_CAPTURE_PROCESS 1 4396

717 717 STREAMS$_APPLY_PROCESS 1 4396

721 721 STREAMS$_PROPAGATION_PROCESS 1 4396

1297 1297 SYS_FBA_FA 1 7913

1300 1300 SYS_FBA_TSFA 1 7913

1302 1302 SYS_FBA_BARRIERSCN 1 7913

1304 1304 SYS_FBA_TRACKEDTABLES 1 7913

1306 1306 SYS_FBA_PARTITIONS 1 7913

1307 1307 SYS_FBA_USERS 1 7913

1309 1309 SYS_FBA_DL 1 7913

1314 1314 REGISTRY$ 1 7913

5541 5541 DAM_CONFIG_PARAM$ 1 9951

5582 5582 INVALIDATION_REGISTRY$ 1 9953

5780 5780 LOC$ 1 9962

5794 5794 AQ$_QUEUE_TABLES 1 9964

5797 5797 AQ$_QUEUES 1 9964

5804 5804 AQ$_QUEUE_TABLE_AFFINITIES 1 9964

5814 5814 AQ$_SCHEDULES 1 9964

6571 6571 WRH$_UNDOSTAT 1 13332

6731 6731 WRM$_SNAPSHOT 1 13344

7144 7144 REPCAT$_REPPROP 1 13367

12939 12939 AQ$_SCHEDULER$_EVENT_QTAB_L 1 22500

12973 12973 AQ$_SCHEDULER$_REMDB_JOBQTAB_L 1 22503

13003 13003 AQ$_SCHEDULER_FILEWATCHER_QT_L 1 22505

13059 13059 AQ$_ALERT_QT_L 1 22508

13273 13273 AQ_EVENT_TABLE 1 22517

13298 13298 AQ$_AQ_PROP_TABLE_L 1 22518

13604 13604 AQ$_SYS$SERVICE_METRICS_TAB_L 1 31491

14137 14137 AQ$_WM$EVENT_QUEUE_TABLE_L 1 31513

69043 69043 XDB$ROOT_INFO 1 31548

75529 75529 SDO_TOPO_METADATA_TABLE 1 65971

80805 80805 AQ$_MGMT_NOTIFY_QTABLE_L 1 74061

83960 83960 AQ$_MGMT_LOADER_QTABLE_L 1 77949

88118 87516 AQ$_STREAMS_QUEUE_TABLE_L 1 94221

88125 87487 AQ$_ORDERS_QUEUETABLE_L 1 94222

108 rows selected.

--//一般同版本的数据库.这些rdba地址一般都是一致的，也就是找1个好版本的数据库对应块覆盖坏数据库对应块.

--//使用bbed的copy命令覆盖.

SELECT DISTINCT

'copy file 101 block '

|| block_id

|| ' to file '

|| FILE_ID

|| ' block '

|| BLOCK_ID c100

FROM (SELECT a.OBJ#

,TAB#

,a.DATAOBJ#

,BOBJ#

,NAME

,DBMS_ROWID.ROWID_RELATIVE_FNO (a.ROWID) FILE_ID

,DBMS_ROWID.ROWID_BLOCK_NUMBER (a.ROWID) BLOCK_ID

FROM TAB$ a, obj$ b

WHERE a.obj# = b.obj# AND A.OBJ# IN (

2,4,5,6,8,10,14,15,16,17,18,19,20,21,22,23,25,28,29,31,32,59,61,68,69,74,80,83,86,88,92,95,98,99,101,103,104,105,118,160,192,221,222,223,225,226,227,228,

246,248,250,252,294,297,300,301,302,303,304,307,311,375,390,433,436,438,446,448,451,453,455,463,506,514,515,517,567,587,713,717,721,1297,1300,1302,1304,

1306,1307,1309,1314,5541,5582,5780,5794,5797,5804,5814,6571,6731,7144,12939,12973,13003,13059,13273,13298,13604,14137,69043,75529,80805,83960,88118,88125

));

C100

------------------------------------------------

copy file 101 block 149 to file 1 block 149

copy file 101 block 163 to file 1 block 163

copy file 101 block 9953 to file 1 block 9953

copy file 101 block 9962 to file 1 block 9962

copy file 101 block 22505 to file 1 block 22505

copy file 101 block 22508 to file 1 block 22508

copy file 101 block 22517 to file 1 block 22517

copy file 101 block 31491 to file 1 block 31491

copy file 101 block 159 to file 1 block 159

copy file 101 block 9951 to file 1 block 9951

copy file 101 block 22500 to file 1 block 22500

copy file 101 block 94221 to file 1 block 94221

copy file 101 block 150 to file 1 block 150

copy file 101 block 4396 to file 1 block 4396

copy file 101 block 7913 to file 1 block 7913

copy file 101 block 13332 to file 1 block 13332

copy file 101 block 94222 to file 1 block 94222

copy file 101 block 74061 to file 1 block 74061

copy file 101 block 156 to file 1 block 156

copy file 101 block 3339 to file 1 block 3339

copy file 101 block 13344 to file 1 block 13344

copy file 101 block 145 to file 1 block 145

copy file 101 block 165 to file 1 block 165

copy file 101 block 13367 to file 1 block 13367

copy file 101 block 22503 to file 1 block 22503

copy file 101 block 31548 to file 1 block 31548

copy file 101 block 65971 to file 1 block 65971

copy file 101 block 153 to file 1 block 153

copy file 101 block 166 to file 1 block 166

copy file 101 block 9964 to file 1 block 9964

copy file 101 block 77949 to file 1 block 77949

copy file 101 block 147 to file 1 block 147

copy file 101 block 146 to file 1 block 146

copy file 101 block 152 to file 1 block 152

copy file 101 block 155 to file 1 block 155

copy file 101 block 158 to file 1 block 158

copy file 101 block 3341 to file 1 block 3341

copy file 101 block 22518 to file 1 block 22518

copy file 101 block 31513 to file 1 block 31513

copy file 101 block 148 to file 1 block 148

copy file 101 block 154 to file 1 block 154

copy file 101 block 160 to file 1 block 160

copy file 101 block 167 to file 1 block 167

copy file 101 block 3337 to file 1 block 3337

44 rows selected.

3.执行以上生成bbed的脚本.

--//关闭好的数据库:

$ /bin/cp -r /home/oracle/backup/book_20190122_bad

BBED> info

File# Name Size(blks)

----- ---- ----------

1 /mnt/ramdisk/book/system01.dbf 0

101 /home/oracle/backup/book_20190122_good/system01.dbf 0

201 /home/oracle/backup/book_20190122_bad/system01.dbf 0

bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/copy_bbed.txt

--//启动数据库:

SYS@book> alter database open upgrade ;

alter database open upgrade

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [], []

Process ID: 56160

Session ID: 274 Serial number: 3

--//说明:实际上面的脚本虽然覆盖对应块,利用相同版本的数据库对应位置都不变的假设.但是忽略一个问题就是行迁移的问题,如果对应

--//记录发生了行迁移或者链接.对应链接块还是相同位置吗?正好我的测试tab$ obj#=4的记录发生了行迁移.我看了几个库,obj#=4这条记录都发生

--//行迁移(8K的数据块),而且迁移的块居然都一样,对应rdba= 1,31497,是巧合还是什么原因...

--//也许对方的好的数据库这里不存在行迁移问题.

--//我前面测试发生行链接的情况,注意检查修改flag不是0x6c的记录.

$ grep -v "0x6c $" scan3_bbed.txt

assign /x dba 4194451 offset 7349 = 0x20

assign /x dba 4197642 offset 7888 = 0x20

assign /x dba 4207636 offset 7087 = 0x20

assign /x dba 4225801 offset 3621 = 0x4c

assign /x dba 4225801 offset 4436 = 0x4c

assign /x dba 4288537 offset 7717 = 0x4c

--//换算看看:

4194451= set dba 1,147

4197642= set dba 1,3338

4207636= set dba 1,13332

4225801= set dba 1,31497

4288537= set dba 1,94233

BBED> set dba 1,147

DBA 0x00400093 (4194451 1,147)

BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[14]

rowdata[6848] @7349

-------------

flag@7349: 0x20 (KDRHFH)

lock@7350: 0x02

cols@7351: 0

nrid@7352:0x00407b09.1

--//发生了行迁移.行号=1

BBED> set dba 0x00407b09

DBA 0x00407b09 (4225801 1,31497)

BBED> p kdbt[1]

struct kdbt[1], 4 bytes @110

sb2 kdbtoffs @110 10

sb2 kdbtnrow @112 2

--//对应的行号要加偏移10,就是10+1.

BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[11]

rowdata[815] @4436

------------

flag@4436: 0x5c (KDRHFL, KDRHFF, KDRHFD, KDRHFC)

lock@4437: 0x02

cols@4438: 0

ckix@4439: 8

--//ckix=8

BBED> x /rn *kdbr[8]

rowdata[950] @4571

------------

flag@4571: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@4572: 0x00

cols@4573: 1

kref@4574: 1

hrid@4576:0x00400093.8

nrid@4582:0x00400094.0

col 0[2] @4590: 4

--//obj#正好就是4.与错误ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [],[]对上

BBED> assign offset 4436 =0x4c

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub1 rowdata[0] @4436 0x4c

BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[11]

rowdata[815] @4436

------------

flag@4436: 0x4c (KDRHFL, KDRHFF, KDRHFC)

lock@4437: 0x02

cols@4438: 31

ckix@4439: 8

hrid@4440:0x00400093.5

col 0[2] @4446: 2

col 1[1] @4449: 0

col 2[2] @4451: 1

col 3[3] @4454: 144

col 4[2] @4458: 2

col 5[2] @4461: 1

col 6[2] @4464: 37

col 7[2] @4467: 1

col 8[1] @4470: 0

col 9[1] @4472: 0

col 10[1] @4474: 0

col 11[1] @4476: 0

col 12[3] @4478: 529

col 13[38] @4482: --------------------------------------

col 14[3] @4521: 2965

col 15[3] @4525: 1442

col 16[1] @4529: 0

col 17[1] @4531: 0

col 18[1] @4533: 0

col 19[3] @4535: 137

col 20[1] @4539: 0

col 21[1] @4541: 0

col 22[7] @4543: 2019-01-14 22:00:14

col 23[3] @4551: 2965

col 24[0] @4555: *NULL*

col 25[0] @4556: *NULL*

col 26[2] @4557: 37

col 27[2] @4560: 37

col 28[3] @4563: 1024

col 29[1] @4567: 0

col 30[1] @4569: 0

BBED> sum apply

Check value for File 1, Block 31497:

current = 0xaeb2, required = 0xaeb2

--//也可以追加执行:copy file 101 block 31497 to file 1 block 31497,我个人不建议这样做,要根据实际需要修改对应块的信息.这样问题少一些.

--//遗漏一些verify步骤.可以参考后面dba=1,94237的修复过程,基本步骤一样的.

SYS@book> startup mount

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

SYS@book> alter database open upgrade ;

Database altered.

SYS@book> shutdown immediate ;

ORA-00957: duplicate column name

--//关机出现问题.

4.恢复tab$的中对应表orachk001的相关记录.

--//对方通过日志确定在块中位置,我这些早删除了.通过别的方式确定看看:

$ strings -t d system01.dbf | grep ORACHK

43852415 ORACHK001

43917951 ORACHK001

706004707 ORACHK001

--//43852415/8192 = 5353.07800292968750000000

--//43917951/8192 = 5361.07800292968750000000

--//706004707/8192 = 86182.21520996093750000000

--//前面2块dba=1,5353 1,5361对应的都是索引跳过.

BBED> set dba 1,86182

DBA 0x004150a6 (4280486 1,86182)

BBED> find /c ORACHK

File: /mnt/ramdisk/book/system01.dbf (1)

Block: 86182 Offsets: 1764 to 1827 Dba:0x004150a6

------------------------------------------------------------------------------------------------------------------------------------------------

4f524143 484b3030 3102c102 ff02c103 07787701 1610100b 07787701 1610100b 07787701 1610100b 02c102ff ff0180ff 02c10702 c1020180 2c001204 c30a0b57

<64 bytes per line>

BBED> p kdbr

sb2 kdbr[0] @86 1752

sb2 kdbr[1] @88 1680

sb2 kdbr[2] @90 7837

sb2 kdbr[3] @92 7769

sb2 kdbr[4] @94 7689

sb2 kdbr[5] @96 7585

sb2 kdbr[6] @98 7515

sb2 kdbr[7] @100 9

sb2 kdbr[8] @102 7344

--//kdbr记录的是相对偏移,感觉对应的记录是kdbr[1].而且可以确定对象是obj$.

BBED> x /rnnncncntttnccnxnnncct *kdbr[1]

rowdata[0] @1748

----------

flag@1748: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1749: 0x01

cols@1750: 18

col 0[4] @1751: 91090

col 1[4] @1756: 91090

col 2[1] @1761: 0

col 3[9] @1763: ORACHK001

col 4[2] @1773: 1

col 5[0] @1776: *NULL*

col 6[2] @1777: 2

col 7[7] @1780: 2019-01-22 15:15:10

col 8[7] @1788: 2019-01-22 15:15:10

col 9[7] @1796: 2019-01-22 15:15:10

col 10[2] @1804: 1

col 11[0] @1807: *NULL*

col 12[0] @1808: *NULL*

col 13[1] @1809: 0

col 14[0] @1811: *NULL*

col 15[2] @1812: 6

col 16[2] @1815: 1

col 17[1] @1818: 0

--//可以确定obj#=91090

BBED> dump /v offset 1751

File: /mnt/ramdisk/book/system01.dbf (1)

Block: 86182 Offsets: 1751 to 1814 Dba:0x004150a6

-----------------------------------------------------------------------------------------------------------

04c30a0b 5b04c30a 0b5b0180 094f5241 43484b30 303102c1 02ff02c1 03077877 l ....[....[...ORACHK001........xw

01161010 0b077877 01161010 0b077877 01161010 0b02c102 ffff0180 ff02c107 l ......xw......xw................

<32 bytes per line>

--//扫描04c30a0b5b看看.

$ xxd -c 64 -g 1 system01.dbf | grep '7c .. .. .. 04 c3 0a 0b 5b'

2e03bec0:05 01 80 01 80 ff ff 02 c1 02 01 80 01 80 01 80 01 80 01 80 01 80 7c 02 24 01 04 c3 0a 0b 5b 01 80 02 c1 02 03 c2 19 19 ff ff 02 c1 26 ff 02 c1 0b 02 c1 29 02 c1 02 03 c2 03 38 02 c1 02 26 2d

~~~~~~~~~~~~~~~~~~~~~~~~~

--//7c是删除标识,说明一下如果找不到,可能修改-c 后面的参数(最多9次可以定位),有可能被分开了2行.

--//0x2e03bec0=771997376 771997376/8192 = 94237.9609375,确定删除的记录就是dba=1,94237.

BBED> set dba 1,94237

DBA 0x0041701d (4288541 1,94237)

BBED> x /rnn *kdbr[1]

rowdata[2605] @8140

-------------

flag@8140: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@8141: 0x00

cols@8142: 1

kref@8143: 38

mref@8145: 37

hrid@8147:0x0041701d.1

nrid@8153:0x0041701d.1

col 0[4] @8159: 91090

--//cluster table 表0信息.

BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[3]

rowdata[2359] @7894

-------------

flag@7894: 0x7c (KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC)

lock@7895: 0x02

cols@7896: 0

ckix@7897: 1

BBED> assign offset 7894=0x6c

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub1 rowdata[0] @7894 0x6c

BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[3]

rowdata[2359] @7894

-------------

flag@7894: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@7895: 0x02

cols@7896: 36

ckix@7897: 1

col 0[4] @7898: 91090

col 1[1] @7903: 0

col 2[2] @7905: 1

col 3[3] @7908: 2424

col 4[0] @7912: *NULL*

col 5[0] @7913: *NULL*

col 6[2] @7914: 37

col 7[0] @7917: *NULL*

col 8[2] @7918: 10

col 9[2] @7921: 40

col 10[2] @7924: 1

col 11[3] @7927: 255

col 12[2] @7931: 1

col 13[38] @7934: --------------------------------------

col 14[0] @7973: *NULL*

col 15[0] @7974: *NULL*

col 16[0] @7975: *NULL*

col 17[0] @7976: *NULL*

col 18[0] @7977: *NULL*

col 19[0] @7978: *NULL*

col 20[0] @7979: *NULL*

col 21[0] @7980: *NULL*

col 22[0] @7981: *NULL*

col 23[0] @7982: *NULL*

col 24[0] @7983: *NULL*

col 25[0] @7984: *NULL*

col 26[2] @7985: 37

col 27[2] @7988: 37

col 28[6] @7991: 536870912

col 29[1] @7998: 0

col 30[3] @8000: 176

col 31[0] @8004: *NULL*

col 32[0] @8005: *NULL*

col 33[0] @8006: *NULL*

col 34[0] @8007: *NULL*

col 35[7] @8008: 2019-01-22 07:15:10

--//OK了.

BBED> assign offset 8145=38

ub1 rowdata[0] @8145 0x26

BBED> x /rnn *kdbr[1]

rowdata[2605] @8140

-------------

flag@8140: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)

lock@8141: 0x00

cols@8142: 1

kref@8143: 38

mref@8145: 38

hrid@8147:0x0041701d.1

nrid@8153:0x0041701d.1

col 0[4] @8159: 91090

BBED> p ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x0005

ub2 kxidslt @70 0x0018

ub4 kxidsqn @72 0x00000751

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x00c0050f

ub2 kubaseq @80 0x04eb

ub1 kubarec @82 0x13

ub2 ktbitflg @84 0x2002 (KTBFUPB)

union _ktbitun, 2 bytes @86

sb2 _ktbitfsc @86 238

ub2 _ktbitwrp @86 0x00ee

ub4 ktbitbas @88 0x17745f5f

--//设置 ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=0.

BBED> assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=0

sb2 _ktbitfsc @86 0

BBED> sum apply

Check value for File 1, Block 94237:

current = 0xea46, required = 0xea46

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/system01.dbf

BLOCK = 94237

Block Checking: DBA = 4288541, Block Type = KTB-managed data block

data header at 0xfbae5c

kdbchk: the amount of space used is not equal to block size

used=2669 fsc=0 avsp=5307 dtl=8096

Block 94237 failed with check code 6110

--//dtl-used-fsc=avsp

--//8096-2669-0 = 5427

BBED> p kdbh

struct kdbh, 14 bytes @92

ub1 kdbhflag @92 0x00 (NONE)

sb1 kdbhntab @93 6

sb2 kdbhnrow @94 49

sb2 kdbhfrre @96 -1

sb2 kdbhfsbo @98 136

sb2 kdbhfseo @100 5443

sb2 kdbhavsp @102 5307

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sb2 kdbhtosp @104 5553

BBED> assign kdbh.kdbhavsp=5427

sb2 kdbhavsp @102 5427

BBED> sum apply

Check value for File 1, Block 94237:

current = 0xebce, required = 0xebce

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/system01.dbf

BLOCK = 94237

Block Checking: DBA = 4288541, Block Type = KTB-managed data block

data header at 0xfbae5c

kdbchk: space available on commit is incorrect

tosp=5553 fsc=0 stb=4 avsp=5427

Block 94237 failed with check code 6111

--//tops=fsc+stb+avsp= 0+4+5427 = 5431

BBED> assign kdbh.kdbhtosp=5431

sb2 kdbhtosp @104 5431

BBED> sum apply

Check value for File 1, Block 94237:

current = 0xeb48, required = 0xeb48

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/system01.dbf

BLOCK = 94237

--//OK现在修复了.

5.修复tab$:

--//事先写好脚本,避免手忙脚乱的...^_^.

select * from orachk001 where not exists (select 1 from tab$ where tab$.obj#=orachk001.obj#);

SYS@book> insert into tab$ select * from orachk001 where not exists (select 1 from tab$ where tab$.obj#=orachk001.obj#);

2669 rows created.

SYS@book> commit;

Commit complete.

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@book> startup open read only ;

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

Database opened.

SYS@book> select * from sys.tab$ minus select * from orachk001;

no rows selected

SYS@book> select * from orachk001 minus select * from sys.tab$;

no rows selected

--//ok!!

6.数据字典不一致的问题.

--//我这里没有数据字典不一致的问题.因为我是从原来的库对应块拷贝回来的.

SYS@book> @desc SYS.AQ$SCHEDULER$_EVENT_QTAB

--//可以正常执行的.

--//我自己有1个疑问,对方没有禁用tab$索引I_TAB1.这样会有什么问题呢?

--//实际上tab$的索引是不一致的.

SYS@book> select * from tab$ where BOBJ#=2;

no rows selected

SYS@book> select count(*) from tab$ where BOBJ#=2;

COUNT(*)

----------

--//我个人认为安全起见,最好禁用tab$的索引.另外我发现索引很小,应该可以尝试使用bbed修复索引.

SYS@book> select count(*) from orachk001 where BOBJ# is not null ;

COUNT(*)

----------

139

7.后记:

--//我个人认为这样修复,后续的问题一定很多.

--//实际上第一次听别人提起这个问题时,我第一反应就是恢复tab$,当然我开始并不知道tab$是CLUSTER C_OBJ#的一个表(^_^,如果是普

--//通表,我可能早完成了).本身cluster table的结果就与普通堆表的结构不一样,断断续续花了差不多1个月了解cluster table,而且

--//里面还遇到行迁移或者链接问题.

--//还有bbed 如果ckix=0,mref=0不显示的问题.

--//还有就是延迟提交导致的问题,等等许多细节几乎导致我放弃恢复测试.

--//前面写bash shell脚本也是一样,断断续续写了一个星期,

--//使用bash shell写脚本实际上执行效率很低的操作,通过这个恢复,了解许多oracle cluster table许多相关知识,

--//还有一些细节的处理,许多编码是使用cut硬性编码取出对于值.不能保证你遇到类似问题,直接拿来使用.^_^

--//何况真实的生产系统可能比我在测试环境遇到的问题更加复杂.