[20160405]利用bbed修改跳过损坏的索引.txt

[20160405]利用bbed修改跳过损坏的索引.txt

--oracle的启动通过system的第一块的rdba(kcvfhrdb)
http://blog.itpub.net/267265/viewspace-2016219/
http://blog.itpub.net/267265/viewspace-2022857/

--如果前obj#<=59对象损坏,不允许重建,假设某个索引损坏,是否可以跳过索引启动数据库呢?自己做一个测试.
--以sys.undo$的索引i_undo1为例做测试:
--测试参考链接:
-- http://www.htz.pw/2014/11/21/change-bootstrap-table-with-bbed-to-skip-corrupt-block-on-i_undo1.html

1.环境:
SCOTT@book> @ &r/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

SCOTT@book> select rowid,a.* from sys.bootstrap$ a where a.sql_text like '%UNDO%';
ROWID                   LINE#       OBJ# SQL_TEXT
------------------ ---------- ---------- ------------------------------------------------------------
AAAAA7AABAAAAIJAAH         15         15 CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30)
                                          NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"B
                                         LOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACT
                                         SQN" NUMBER,"UNDOSQN" NUMBER,"INST#" NUMBER,"STATUS$" NUMBER
                                          NOT NULL,"TS#" NUMBER,"UGRP#" NUMBER,"KEEP" NUMBER,"OPTIMAL
                                         " NUMBER,"FLAGS" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPA
                                         RE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),
                                         "SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
                                          STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2
                                         147483645 PCTINCREASE 0 OBJNO 15 EXTENTS (FILE 1 BLOCK 224))

AAAAA7AABAAAAIJAAI         34         34 CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRAN
                                         S 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENT
                                         S 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FI
                                         LE 1 BLOCK 320))

AAAAA7AABAAAAIJAAJ         35         35 CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INITRANS 2 MA
                                         XTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MA
                                         XEXTENTS 2147483645 PCTINCREASE 0 OBJNO 35 EXTENTS (FILE 1 B
                                         LOCK 328))

AAAAA7AABAAAAIKAAH         16         16 CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME" VARCHAR2(30) N
                                         OT NULL,"OWNER#" NUMBER NOT NULL,"ONLINE$" NUMBER NOT NULL,"
                                         CONTENTS$" NUMBER NOT NULL,"UNDOFILE#" NUMBER,"UNDOBLOCK#" N
                                         UMBER,"BLOCKSIZE" NUMBER NOT NULL,"INC#" NUMBER NOT NULL,"SC
                                         NWRP" NUMBER,"SCNBAS" NUMBER,"DFLMINEXT" NUMBER NOT NULL,"DF
                                         LMAXEXT" NUMBER NOT NULL,"DFLINIT" NUMBER NOT NULL,"DFLINCR"
                                          NUMBER NOT NULL,"DFLMINLEN" NUMBER NOT NULL,"DFLEXTPCT" NUM
                                         BER NOT NULL,"DFLOGGING" NUMBER NOT NULL,"AFFSTRENGTH" NUMBE
                                         R NOT NULL,"BITMAPPED" NUMBER NOT NULL,"PLUGGED" NUMBER NOT
                                         NULL,"DIRECTALLOWED" NUMBER NOT NULL,"FLAGS" NUMBER NOT NULL
                                         ,"PITRSCNWRP" NUMBER,"PITRSCNBAS" NUMBER,"OWNERINSTANCE" VAR
                                         CHAR2(30),"BACKUPOWNER" VARCHAR2(30),"GROUPNAME" VARCHAR2(30
                                         ),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" VARCHAR2(1000),"S
                                         PARE4" DATE) STORAGE (  OBJNO 16 TABNO 2) CLUSTER C_TS#(TS#)

SCOTT@book> @ &r/rowid AAAAA7AABAAAAIJAAI
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
        59          1        521          8 1,521                alter system dump datafile 1 block 521 ;

--从定义也可以看出i_undo1索引在dba=1,320.
SCOTT@book> select * from dba_extents where owner='SYS' and segment_name='I_UNDO1';
OWNER  SEGMENT_NAME          SEGMENT_TYPE       TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ --------------------  ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS    I_UNDO1               INDEX              SYSTEM                   0          1        320      65536          8            1

2.破坏索引:
SCOTT@book> @ &r/bbvi 1 320
BVI_COMMAND
-----------------------------------------------------
bvi -b 2621440 -s 8192 /mnt/ramdisk/book/system01.dbf

--关闭数据库,顺便往开头写入一些垃圾数据.我的测试仅仅10 A2=>AA AA

SYS@book> startup
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

--可以发现虽然我仅仅修改2个字节,oracle一样启动正常.

$ dd if=/dev/zero of=/mnt/ramdisk/book/system01.dbf bs=8192 count=8 seek=320 conv=notrunc
8+0 records in
8+0 records out
65536 bytes (66 kB) copied, 9.6634e-05 seconds, 678 MB/s

SYS@book> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 321)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
Process ID: 24813
Session ID: 232 Serial number: 3
--这次报错.

3.我简单修改bootstrap$的标识为3c.

BBED> set dba 1,521
        DBA             0x00400209 (4194825 1,521)

BBED> p  *kdbr[8]
rowdata[4533]
-------------
ub1 rowdata[4533]                           @5823     0x2c

BBED> x /rnnc
rowdata[4533]                               @5823
-------------
flag@5823: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5824: 0x01
cols@5825:    3

col    0[2] @5826: 34
col    1[2] @5829: 34
col  2[196] @5832: CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K
                   NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))

BBED> assign dba 1,521 offset 5823 = 0x3c;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 rowdata[0]                              @5823     0x3c

--这样标识删除.
BBED> sum apply
Check value for File 1, Block 521:
current = 0x42fa, required = 0x42fa

BBED> verify   dba 1,521
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 521

Block Checking: DBA = 4194825, Block Type = KTB-managed data block
data header at 0x7f71a13b1244
kdbchk: the amount of space used is not equal to block size
        used=6760 fsc=0 avsp=1156 dtl=8120
Block 521 failed with check code 6110

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

--先不管这些不一致看看是否可以进入.

SYS@book> startup
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

SYS@book> select /*+ index(undo$ i_undo1) */* from undo$ where name='SYSTEM';

SYS@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  44y8fh2x00vqx, child number 0
-------------------------------------
select /*+ index(undo$ i_undo1) */* from undo$ where name='SYSTEM'
Plan hash value: 3995376916
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| UNDO$ |      1 |    63 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / UNDO$@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"='SYSTEM')

--说明已经不再使用索引i_undo1.
--这样虽然破坏数据的一致性, obj$,ind$的相关信息没有删除,至少数据库能启动,
--dbv检查也可以发现索引破坏.

$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Apr 5 08:32:12 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
Page 320 is marked corrupt
Corrupt block relative dba: 0x00400140 (file 1, block 320)
Completely zero block found during dbv:

Page 321 is marked corrupt
Corrupt block relative dba: 0x00400141 (file 1, block 321)
Completely zero block found during dbv:

Page 322 is marked corrupt
Corrupt block relative dba: 0x00400142 (file 1, block 322)
Completely zero block found during dbv:

Page 323 is marked corrupt
Corrupt block relative dba: 0x00400143 (file 1, block 323)
Completely zero block found during dbv:

Page 324 is marked corrupt
Corrupt block relative dba: 0x00400144 (file 1, block 324)
Completely zero block found during dbv:

Page 325 is marked corrupt
Corrupt block relative dba: 0x00400145 (file 1, block 325)
Completely zero block found during dbv:

Page 326 is marked corrupt
Corrupt block relative dba: 0x00400146 (file 1, block 326)
Completely zero block found during dbv:

Page 327 is marked corrupt
Corrupt block relative dba: 0x00400147 (file 1, block 327)
Completely zero block found during dbv:

Block Checking: DBA = 4194825, Block Type = KTB-managed data block
data header at 0x7fafd934a044
kdbchk: the amount of space used is not equal to block size
        used=6760 fsc=0 avsp=1156 dtl=8120
Page 521 failed with check code 6110

DBVERIFY - Verification complete

Total Pages Examined         : 98560
Total Pages Processed (Data) : 64815
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 13670
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3938
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 16129
Total Pages Marked Corrupt   : 8
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 342809922 (3.342809922)

4.补充测试修复1,521一致性.
--做这个不是我擅长的,还是自己测试看看.
BBED> verify   dba 1,521
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 521

Block Checking: DBA = 4194825, Block Type = KTB-managed data block
data header at 0x7fc784424244
kdbchk: the amount of space used is not equal to block size
        used=6760 fsc=0 avsp=1156 dtl=8120
Block 521 failed with check code 6110

-- dtl - used  = 8120-6760= 1360 = 0x550

BBED> assign kdbh.kdbhavsp=1360
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbhavsp                                @78       1360

BBED> assign kdbh.kdbhtosp=1360
sb2 kdbhtosp                                @80       1360

BBED> sum apply
Check value for File 1, Block 521:
current = 0x42fa, required = 0x42fa

BBED> verify   dba 1,521
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 521

Block Checking: DBA = 4194825, Block Type = KTB-managed data block
data header at 0x1d33e44
kdbchk: space available on commit is incorrect
        tosp=1360 fsc=0 stb=2 avsp=1360
Block 521 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

--依旧有问题.还要1360+2=1362.

BBED> assign kdbh.kdbhtosp=1362;
sb2 kdbhtosp                                @80       1362
BBED> assign kdbh.kdbhtosp=1362;
sb2 kdbhtosp                                @80       1362

BBED> sum apply
Check value for File 1, Block 521:
current = 0x42f8, required = 0x42f8

BBED> verify   dba 1,521
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 521

DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

-- 这种测试是万不得以而为之,千万不要在生产系统测试.在测试的过程中参考连接
-- http://www.htz.pw/2014/11/21/change-bootstrap-table-with-bbed-to-skip-corrupt-block-on-i_undo1.html
-- 再次感谢.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2075271/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2075271/