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

╰+哭是因爲堅強的太久メ 2022-01-10 02:51 200阅读 0赞

\[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硬性编码取出对于值.不能保证你遇到类似问题,直接拿来使用.^\_^  
\--//何况真实的生产系统可能比我在测试环境遇到的问题更加复杂.

转载于:https://www.cnblogs.com/lfree/p/10441420.html