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

[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.

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
-- 再次感谢.