[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/* /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-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 /*+ 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

相關文章