[20160413利用bbed修改跳过损坏的索引.txt
--前一阵子做过利用bbed修改跳过损坏的索引,这次测试看看破坏索引SYS.I_OBJ1看看.
1.环境:
SYS@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
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME
---------- -------------- ------ -------------------- ------------------- ------- -------------------
3 3 SYS I_OBJ# INDEX VALID 2013-08-24 11:37:35
40 40 SYS I_OBJ5 INDEX VALID 2013-08-24 11:37:35
38 38 SYS I_OBJ3 INDEX VALID 2013-08-24 11:37:35
36 36 SYS I_OBJ1 INDEX VALID 2013-08-24 11:37:35
37 37 SYS I_OBJ2 INDEX VALID 2013-08-24 11:37:35
39 39 SYS I_OBJ4 INDEX VALID 2013-08-24 11:37:35
62 92377 SYS I_OBJAUTH1 INDEX VALID 2016-04-13 11:03:55
63 63 SYS I_OBJAUTH2 INDEX VALID 2013-08-24 11:37:35
445 445 SYS I_OBJ#_INTCOL# INDEX VALID 2013-08-24 11:37:43
1082 92375 SYS I_OBJTYPE INDEX VALID 2016-04-13 11:02:08
10 rows selected.
SYS@book> alter index SYS.I_OBJ1 unusable;
alter index SYS.I_OBJ1 unusable
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
--这种方式不行1
SYS@book> column PARTITION_NAME noprint
SYS@book> select * from dba_extents where owner='SYS' and segment_name='I_OBJ1';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS I_OBJ1 INDEX SYSTEM 0 1 336 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 1 1 8672 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 2 1 12072 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 3 1 18136 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 4 1 24104 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 5 1 34504 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 6 1 36296 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 7 1 38192 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 8 1 40096 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 9 1 41496 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 10 1 42880 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 11 1 42952 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 12 1 45200 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 13 1 45280 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 14 1 47776 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 15 1 47848 65536 8 1
SYS I_OBJ1 INDEX SYSTEM 16 1 50432 1048576 128 1
17 rows selected.
SYS@book> select rowid,a.* from sys.bootstrap$ a where a.sql_text like '%I_OBJ1%';
ROWID LINE# OBJ# SQL_TEXT
------------------ ---------- ---------- ------------------------------------------------------------
AAAAA7AABAAAAILAAB 36 36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFRE
E 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 102
4K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36
EXTENTS (FILE 1 BLOCK 336))
SYS@book> @ &r/rowid AAAAA7AABAAAAILAAB
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
59 1 523 1 1,523 alter system dump datafile 1 block 523 ;
SYS@book> @ &r/bbvi 1 336
BVI_COMMAND
-----------------------------------------------------
bvi -b 2752512 -s 8192 /mnt/ramdisk/book/system01.dbf
2.破坏索引看看:
$ dd if=/dev/zero of=/mnt/ramdisk/book/system01.dbf bs=8192 count=8 seek=336 conv=notrunc
--注意不要忘记conv=notrunc参数,不然文件会被截断!!!也不要在生产系统做这样的测试.
$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Apr 13 11:18:32 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
Page 336 is marked corrupt
Corrupt block relative dba: 0x00400150 (file 1, block 336)
Completely zero block found during dbv:
Page 337 is marked corrupt
Corrupt block relative dba: 0x00400151 (file 1, block 337)
Completely zero block found during dbv:
Page 338 is marked corrupt
Corrupt block relative dba: 0x00400152 (file 1, block 338)
Completely zero block found during dbv:
Page 339 is marked corrupt
Corrupt block relative dba: 0x00400153 (file 1, block 339)
Completely zero block found during dbv:
Page 340 is marked corrupt
Corrupt block relative dba: 0x00400154 (file 1, block 340)
Completely zero block found during dbv:
Page 341 is marked corrupt
Corrupt block relative dba: 0x00400155 (file 1, block 341)
Completely zero block found during dbv:
Page 342 is marked corrupt
Corrupt block relative dba: 0x00400156 (file 1, block 342)
Completely zero block found during dbv:
Page 343 is marked corrupt
Corrupt block relative dba: 0x00400157 (file 1, block 343)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 98560
Total Pages Processed (Data) : 64843
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 14205
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3932
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 15572
Total Pages Marked Corrupt : 8
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 343125708 (3.343125708)
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
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-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 337)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
Process ID: 21984
Session ID: 232 Serial number: 3
SYS@book> startup upgrade
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-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 337)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
Process ID: 22054
Session ID: 232 Serial number: 3
3.修复看看:
BBED> set dba 1,523
DBA 0x0040020b (4194827 1,523)
BBED> p *kdbr[1]
rowdata[3681]
-------------
ub1 rowdata[3681] @7322 0x2c
BBED> x /rnnc
rowdata[3681] @7322
-------------
flag@7322: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7323: 0x01
cols@7324: 3
col 0[2] @7325: 36
col 1[2] @7328: 36
col 2[208] @7331: CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (
INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTE NTS (FILE 1
BLOCK 336))
BBED> assign dba 1,523 offset 7322 = 0x3c;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 rowdata[0] @7322 0x3c
BBED> x /rnnc
rowdata[3681] @7322
-------------
flag@7322: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@7323: 0x01
cols@7324: 0
BBED> sum apply dba 1,523;
Check value for File 1, Block 523:
current = 0x7e6b, required = 0x7e6b
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-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Process ID: 22174
Session ID: 232 Serial number: 3
-- 这次就没有上次这么幸运了.
SYS@book> startup upgrade
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> create pfile='/tmp/aaa.ora' from spfile ;
File created.
--加入*.EVENT="38003 trace name context forever, level 10"再启动.
4.继续恢复:
SYS@book> startup upgrade pfile='/tmp/aaa.ora'
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> show parameter event
NAME TYPE VALUE
-------------- ------- ------------------------------------------
event string 38003 trace name context forever, level 10
xml_db_events string enable
SYS@book> select object_id,data_object_id,OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME
---------- -------------- ------ -------------------- ------------------- ------- -------------------
3 3 SYS I_OBJ# INDEX VALID 2013-08-24 11:37:35
40 40 SYS I_OBJ5 INDEX VALID 2013-08-24 11:37:35
38 38 SYS I_OBJ3 INDEX VALID 2013-08-24 11:37:35
36 36 SYS I_OBJ1 INDEX VALID 2013-08-24 11:37:35
37 37 SYS I_OBJ2 INDEX VALID 2013-08-24 11:37:35
39 39 SYS I_OBJ4 INDEX VALID 2013-08-24 11:37:35
62 92377 SYS I_OBJAUTH1 INDEX VALID 2016-04-13 11:03:55
63 63 SYS I_OBJAUTH2 INDEX VALID 2013-08-24 11:37:35
445 445 SYS I_OBJ#_INTCOL# INDEX VALID 2013-08-24 11:37:43
1082 92375 SYS I_OBJTYPE INDEX VALID 2016-04-13 11:02:08
10 rows selected.
SYS@book> @?/rdbms/admin/utlmmig.sql
....
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."BOOTSTRAP$TMPSTR"."LINE#")
ORA-06512: at line 150
$ cp utlmmig.sql utlmmig.sql_xxx
--删除not null继续看看.
create table bootstrap$tmpstr
( line# number , /* statement order id */
obj# number not null, /* object number */
sql_text varchar2(4000) not null) /* statement */
/
SYS@book> @?/rdbms/admin/utlmmig.sql_xxx
View created.
insert into bootstrap$mig select * from bootstrap$tmpstr
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."BOOTSTRAP$MIG"."LINE#")
--不能插入NULL.
SYS@book> select * from bootstrap$tmpstr where line# is null ;
LINE# OBJ# SQL_TEXT
---------- ---------- ------------------------------------------------------------
92416 create unique index i_obj1 on obj$(obj#, owner#, type#) PCTF
REE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT
1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OB
JNO 92416 EXTENTS (FILE 1 BLOCK 9136))
--前面标识的line#=36
SYS@book> update ( select * from bootstrap$tmpstr where line# is null ) set line#=36;
1 row updated.
SYS@book> commit ;
Commit complete.
--从错误处保存另外一个文件utlmmig.sql_yyy.sql,最好在原来的目录里面,继续执行.
/*****************************************************************************/
/* Step 6 - Insert the new object entries in bootstrap$mig
*/
/*****************************************************************************/
insert into bootstrap$mig select * from bootstrap$tmpstr;
commit;
...
SYS@book> @ @?/rdbms/admin/utlmmig.sql_yyy
Commit complete.
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> quit
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.
--OK,启动成功了.
SYS@book> select object_id,data_object_id,OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME
---------- -------------- ------ -------------------- ------------------- ------- -------------------
3 3 SYS I_OBJ# INDEX VALID 2013-08-24 11:37:35
62 92377 SYS I_OBJAUTH1 INDEX VALID 2016-04-13 11:03:55
63 63 SYS I_OBJAUTH2 INDEX VALID 2013-08-24 11:37:35
445 445 SYS I_OBJ#_INTCOL# INDEX VALID 2013-08-24 11:37:43
1082 92375 SYS I_OBJTYPE INDEX VALID 2016-04-13 11:02:08
92416 92416 SYS I_OBJ1 INDEX VALID 2016-04-13 11:51:25
92417 92417 SYS I_OBJ2 INDEX VALID 2016-04-13 11:51:25
92418 92418 SYS I_OBJ3 INDEX VALID 2016-04-13 11:51:25
92419 92419 SYS I_OBJ4 INDEX VALID 2016-04-13 11:51:25
92420 92420 SYS I_OBJ5 INDEX VALID 2016-04-13 11:51:25
10 rows selected.
--这些对象OBJECT_ID(指I_OBJ%)已经大于59.
SYS@book> select * from scott.dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--已经修复,实际上真正的问题比这个要复杂.
5.检查:
$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Apr 13 12:09:49 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
Page 336 is marked corrupt
Corrupt block relative dba: 0x00400150 (file 1, block 336)
Completely zero block found during dbv:
Page 337 is marked corrupt
Corrupt block relative dba: 0x00400151 (file 1, block 337)
Completely zero block found during dbv:
Page 338 is marked corrupt
Corrupt block relative dba: 0x00400152 (file 1, block 338)
Completely zero block found during dbv:
Page 339 is marked corrupt
Corrupt block relative dba: 0x00400153 (file 1, block 339)
Completely zero block found during dbv:
Page 340 is marked corrupt
Corrupt block relative dba: 0x00400154 (file 1, block 340)
Completely zero block found during dbv:
Page 341 is marked corrupt
Corrupt block relative dba: 0x00400155 (file 1, block 341)
Completely zero block found during dbv:
Page 342 is marked corrupt
Corrupt block relative dba: 0x00400156 (file 1, block 342)
Completely zero block found during dbv:
Page 343 is marked corrupt
Corrupt block relative dba: 0x00400157 (file 1, block 343)
Completely zero block found during dbv:
Block Checking: DBA = 4194827, Block Type = KTB-managed data block
data header at 0x7f6f05946044
kdbchk: the amount of space used is not equal to block size
used=4379 fsc=0 avsp=3525 dtl=8120
Page 523 failed with check code 6110
DBVERIFY - Verification complete
Total Pages Examined : 101120
Total Pages Processed (Data) : 65920
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 16032
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3941
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 15219
Total Pages Marked Corrupt : 8
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 343226074 (3.343226074)
SYS@book> @ &r/dfb10 4194827
old 2: dbms_utility.data_block_address_file(&1) rfile#,
new 2: dbms_utility.data_block_address_file(4194827) rfile#,
old 3: dbms_utility.data_block_address_block(&&1) block#
new 3: dbms_utility.data_block_address_block(4194827) block#
RFILE# BLOCK#
---------- ----------
1 523
old 1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(&1)||' block '||
new 1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(4194827)||' block '||
old 2: dbms_utility.data_block_address_block(&&1) ||' ;' text
new 2: dbms_utility.data_block_address_block(4194827) ||' ;' text
TEXT
------------------------------------------------------------
alter system dump datafile 1 block 523 ;
--存在一些错误,不过应该不影响使用,这样情况最好备份,导出在导入新机器.
SYS@book> select rowid,a.* from sys.bootstrap$ a where upper(a.sql_text) like '%I_OBJ%';
ROWID LINE# OBJ# SQL_TEXT
------------------ ---------- ---------- ------------------------------------------------------------
AAAWkIAABAAAG0DAAG 3 3 CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1
BLOCK 168))
AAAWkIAABAAAG0DAAP 36 92416 create unique index i_obj1 on obj$(obj#, owner#, type#) PCTF
REE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT
1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OB
JNO 92416 EXTENTS (FILE 1 BLOCK 9136))
AAAWkIAABAAAG0DAAQ 37 92417 create unique index i_obj2 on obj$(owner#, name, namespace,r
emoteowner, linkname, subname, type#, spare3, obj#) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT 104
8576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO
92417 EXTENTS (FILE 1 BLOCK 10032))
AAAWkIAABAAAG0DAAR 38 92418 create index i_obj3 on obj$(oid$) PCTFREE 10 INITRANS 2 MAXT
RANS 255 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 92418 EXTENTS (FIL
E 1 BLOCK 15888))
AAAWkIAABAAAG0DAAS 39 92419 create index i_obj4 on obj$(dataobj#, type#, owner#) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT 10
48576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO
92419 EXTENTS (FILE 1 BLOCK 21040))
AAAWkIAABAAAG0DAAT 40 92420 create unique index i_obj5 on obj$(spare3, name, namespace,
type#, owner#, remoteowner, linkname, subname, obj#) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT 10
48576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO
92420 EXTENTS (FILE 1 BLOCK 22776))
6 rows selected.
