在Oracle使用的过程中,经常遭遇一些核心index出现异常，导致数据库无法正常使用,特别是在数据库open的情况下,因为出现这些bootstrap$中的部分index是无法通过设置event38003进行重建,从而导致数据库无法正常使用,最常见异常index 有:I_ICOL1, I_TS1, I_CDEF1, I_CDEF2, I_CDEF3, I_CDEF4, I_PROXY_DATA$, I_IND1, I_TS#, I_UNDO1, I_UNDO2, I_COBJ#, I_USER1, I_USER2, I_CON1, I_CON2, I_FILE1, I_FILE2, I_FILE#_BLOCK#, I_USER#, I_OBJ#, I_PROXY_ROLE_DATA$_1, I_PROXY_ROLE_DATA$_2, I_CCOL1, I_CCOL2, I_TAB1, I_COL1, I_COL2, I_COL3, I_OBJ1, I_OBJ2, I_OBJ3, I_OBJ4, I_OBJ5
重建的index异常报错有:ORA-00701,ORA-01410,ORA-08102,ORA-08103,ORA-01578,ORA-01499,ORA-00600等各类错误
因为处理细节复杂,无法一一描述出来.这里假设Oracle数据库一个非常核心的表OBJ$中的I_OBJ1异常,现在准备重建它,大概步骤如下:
1. 假设异常index信息（I_OBJ1）
SQL> alter index sys.i_obj1 rebuild;
alter index sys.i_obj1 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> SELECT SQL_TEXT FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%obj%';
SQL_TEXT
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXT
RANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC
TINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))
2. 创建获取异常index所属表
SQL> select table_name from dba_indexes where owner='SYS' AND INDEX_NAME='I_OBJ1';
TABLE_NAME
--------------------------------------------------------------------------------
OBJ$
3. 获取obj$ ddl 语句
SQL> set long 10000
SQL> set pages 1000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."OBJ$"
( "OBJ#" NUMBER NOT NULL ENABLE,
"DATAOBJ#" NUMBER,
"OWNER#" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(128) NOT NULL ENABLE,
"NAMESPACE" NUMBER NOT NULL ENABLE,
"SUBNAME" VARCHAR2(128),
"TYPE#" NUMBER NOT NULL ENABLE,
"CTIME" DATE NOT NULL ENABLE,
"MTIME" DATE NOT NULL ENABLE,
"STIME" DATE NOT NULL ENABLE,
"STATUS" NUMBER NOT NULL ENABLE,
"REMOTEOWNER" VARCHAR2(128),
"LINKNAME" VARCHAR2(128),
"FLAGS" NUMBER,
"OID$" RAW(16),
"SPARE1" NUMBER,
"SPARE2" NUMBER,
"SPARE3" NUMBER,
"SPARE4" VARCHAR2(1000),
"SPARE5" VARCHAR2(1000),
"SPARE6" DATE,
"SIGNATURE" RAW(16),
"SPARE7" NUMBER,
"SPARE8" NUMBER,
"SPARE9" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
4. 获取obj$相关index 语句
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'SYS') FROM dba_indexes where owner='SYS' AND TABLE_NAME='OBJ$';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'SYS')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYS"."I_OBJ5" ON "SYS"."OBJ$" ("SPARE3", "NAME", "NAMESPA
CE", "TYPE#", "OWNER#", "REMOTEOWNER", "LINKNAME", "SUBNAME", "OBJ#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
CREATE INDEX "SYS"."I_OBJ4" ON "SYS"."OBJ$" ("DATAOBJ#", "TYPE#", "OWNER#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
CREATE INDEX "SYS"."I_OBJ3" ON "SYS"."OBJ$" ("OID$")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
CREATE UNIQUE INDEX "SYS"."I_OBJ2" ON "SYS"."OBJ$" ("OWNER#", "NAME", "NAMESPA
CE", "REMOTEOWNER", "LINKNAME", "SUBNAME", "TYPE#", "SPARE3", "OBJ#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
CREATE UNIQUE INDEX "SYS"."I_OBJ1" ON "SYS"."OBJ$" ("OBJ#", "OWNER#", "TYPE#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
5. 创建obj$img表和相关index
SQL> CREATE TABLE "SYS"."OBJ$IMG"
2 ( "OBJ#" NUMBER NOT NULL ENABLE,
3 "DATAOBJ#" NUMBER,
4 "OWNER#" NUMBER NOT NULL ENABLE,
5 "NAME" VARCHAR2(128) NOT NULL ENABLE,
6 "NAMESPACE" NUMBER NOT NULL ENABLE,
7 "SUBNAME" VARCHAR2(128),
8 "TYPE#" NUMBER NOT NULL ENABLE,
9 "CTIME" DATE NOT NULL ENABLE,
10 "MTIME" DATE NOT NULL ENABLE,
11 "STIME" DATE NOT NULL ENABLE,
12 "STATUS" NUMBER NOT NULL ENABLE,
13 "REMOTEOWNER" VARCHAR2(128),
14 "LINKNAME" VARCHAR2(128),
15 "FLAGS" NUMBER,
16 "OID$" RAW(16),
17 "SPARE1" NUMBER,
18 "SPARE2" NUMBER,
19 "SPARE3" NUMBER,
20 "SPARE4" VARCHAR2(1000),
21 "SPARE5" VARCHAR2(1000),
22 "SPARE6" DATE,
23 "SIGNATURE" RAW(16),
24 "SPARE7" NUMBER,
25 "SPARE8" NUMBER,
26 "SPARE9" NUMBER
27 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
28 NOCOMPRESS LOGGING
29 STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
30 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
31 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
32 TABLESPACE "SYSTEM"
33 ;
Table created.
SQL>
SQL> CREATE UNIQUE INDEX "SYS"."I_OBJ_5IMG" ON "SYS"."OBJ$IMG" ("SPARE3", "NAME", "NAMESPACE", "TYPE#", "OWNER#", "REMOTEOWNER", "LINKNAME", "SUBNAME", "OBJ#")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "SYSTEM";
Index created.
SQL> CREATE INDEX "SYS"."I_OBJ4IMG" ON "SYS"."OBJ$IMG" ("DATAOBJ#", "TYPE#", "OWNER#")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "SYSTEM";
Index created.
SQL> CREATE INDEX "SYS"."I_OBJ3IMG" ON "SYS"."OBJ$IMG" ("OID$")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "SYSTEM";
Index created.
SQL> CREATE UNIQUE INDEX "SYS"."I_OBJ2IMG" ON "SYS"."OBJ$IMG" ("OWNER#", "NAME", "NAMESPACE", "REMOTEOWNER", "LINKNAME", "SUBNAME", "TYPE#", "SPARE3", "OBJ#")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
3 STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "SYSTEM";
Index created.
SQL> CREATE UNIQUE INDEX "SYS"."I_OBJ1IMG" ON "SYS"."OBJ$IMG" ("OBJ#", "OWNER#", "TYPE#")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "SYSTEM"
7 ;
Index created.
6. 获取bootstrap$语句
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','BOOTSTRAP$','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','BOOTSTRAP$','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."BOOTSTRAP$"
( "LINE#" NUMBER NOT NULL ENABLE,
"OBJ#" NUMBER NOT NULL ENABLE,
"SQL_TEXT" VARCHAR2(4000) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 57344 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
7. 创建bootstrap$img表
SQL> CREATE TABLE "SYS"."BOOTSTRAP$IMG"
2 ( "LINE#" NUMBER NOT NULL ENABLE,
3 "OBJ#" NUMBER NOT NULL ENABLE,
4 "SQL_TEXT" VARCHAR2(4000) NOT NULL ENABLE
5 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
6 NOCOMPRESS LOGGING
7 STORAGE(INITIAL 57344 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
8 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
9 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
10 TABLESPACE "SYSTEM"
11 /
Table created.
8.插入数据
insert into obj$img select * from obj$;
insert into bootstrap$img select * from bootstrap$;
commit;
9. 删除bootstrap$img对象名
delete from bootstrap$img where obj# in
(select obj# from obj$
where name in ('OBJ$', 'I_OBJ1', 'I_OBJ2', 'I_OBJ3', 'I_OBJ4', 'I_OBJ5',
'BOOTSTRAP$'));
commit;
10. 插入新创建对象
insert into bootstrap$img select * from bootstrap$tmpstr;
commit;
11. 关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
12. bbed修改相关block值
[oracle@oel6 ~]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 7 04:54:50 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/u01/app/oracle/oradata/XIFENFEI/system01.dbf'
FILENAME /u01/app/oracle/oradata/XIFENFEI/system01.dbf
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set block 1
BLOCK# 1
BBED> set mode edit
MODE Edit
BBED> set count 32
COUNT 32
BBED> m /x e81d
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
Block: 1 Offsets: 196 to 227 Dba:0x00000000
------------------------------------------------------------------------
e81d4000 12000000 00000000 41bad632 15bad632 01000000 00000000 00000000
<32 bytes per line>
BBED> m /x 5200 offset +2
File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
Block: 1 Offsets: 198 to 229 Dba:0x00000000
------------------------------------------------------------------------
52001200 00000000 000041ba d63215ba d6320100 00000000 00000000 00000000
<32 bytes per line>
BBED> d offset 96
File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
Block: 1 Offsets: 196 to 227 Dba:0x00000000
------------------------------------------------------------------------
e81d5200 12000000 00000000 41bad632 15bad632 01000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 1:
current = 0x5fbf, required = 0x5fbf
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/XIFENFEI/system01.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
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
BBED> exit
13. 启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 663945216 bytes
Fixed Size 2291808 bytes
Variable Size 369100704 bytes
Database Buffers 289406976 bytes
Redo Buffers 3145728 bytes
Database mounted.
Database opened.
14. 验证I_OBJ1 index重建情况
SQL> SELECT OBJECT_ID,DATA_OBJECT_ID,CREATED FROM DBA_OBJECTS WHERE OBJECT_NAME='I_OBJ1';
OBJECT_ID DATA_OBJECT_ID CREATED
---------- -------------- ---------
77120 77120 06-AUG-14
SQL> !date
Thu Aug 6 05:29:25 CST 2014
SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME='I_OBJ1';
HEADER_FILE HEADER_BLOCK
----------- ------------
1 77296
SQL> SELECT SQL_TEXT FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%obj%';
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------
create unique index i_obj1 on obj$(obj#, owner#, type#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 M
AXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77120 EXTENTS (FILE 1 BLOCK 77296))
create unique index i_obj2 on obj$(owner#, name, namespace,remoteowner, linkname, subname, type#, spare3, obj#) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE ( INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77119 EXTENTS (FILE 1 BLOCK 77288))
create index i_obj3 on obj$(oid$) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 P
CTINCREASE 0 OBJNO 77118 EXTENTS (FILE 1 BLOCK 77280))
create index i_obj4 on obj$(dataobj#, type#, owner#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXE
XTENTS 2147483645 PCTINCREASE 0 OBJNO 77117 EXTENTS (FILE 1 BLOCK 77272))
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 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77116 EXTENTS (FILE 1 BLOCK 77264))
不同版本对应的基表和index结构不一样(本篇blog使用的是12c做试验),但是本方法支持数据库从Oracle 7到Oracle 12c对于bootstrap$中核心index处理.
因为重建bootstrap$中的核心index是一个复杂而且风险非常大的事情,在你无法确定风险或者无法正常完成此类操作之时,如有需要请联系我
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
