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

来源：这里教程网时间：2026-03-03 12:59:29 作者：

[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 $ grep "TABLE ACCESS" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_56704.trc | cut -d" " -f7 | cut -f2 -d=| sort -n | uniq | xargs -s 165 -P3 | sed 's/ /,/g' 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/* /mnt/ramdisk/book/ */ 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-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 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 /*+ full(tab$) */ count(*) from tab$ where BOBJ#=2; COUNT(*) ---------- 17 --//我个人认为安全起见,最好禁用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硬性编码取出对于值.不能保证你遇到类似问题,直接拿来使用.^_^ --//何况真实的生产系统可能比我在测试环境遇到的问题更加复杂.

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推