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