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

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

--oracle的启动通过system的第一块的rdba(kcvfhrdb)

--如果前obj#<=59对象损坏,不允许重建,假设某个索引损坏,是否可以跳过索引启动数据库呢?自己做一个测试.

--以sys.undo$的索引i_undo1为例做测试:

--测试参考链接:

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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf

Page 320 is marked corrupt

Corrupt block relative dba: 0x00400140 (file 1, block 320)