How to Create/Rebuild Bootstrap Indexes

1. 背景描述

   某客户服务器存储异常，服务器异常重启，Oracle alert日志报错 I_OBJ5 块损坏，I_OBJ5为Oracle bootstrap$对象无法直接rebuild重建.恢复优先级：
   1. 如果有备份并且归档archivelog连续且足够，建议优先使用备份集恢复。
   2. 没有备份时，建议停机全备后，使用本方案进行恢复。

2. 问题解释

How to Create/Rebuild Bootstrap Indexes (文档 ID 1587581.1)
Symptoms

The Following Indexes are Missing after Upgrade from 10gR2 to 11gR2:

SYS.I_OBJ1

SYS.I_OBJ2

SYS.I_OBJ3

SYS.I_OBJ4

SYS.I_OBJ5

SYS.I_USER1

SYS.I_USER2

This is Causing Some Data Dictionary to be Extremely Slow after the Upgrade .

Also Trying to Run Script dcore.bsq to recreate these objects gives the following Error :

ERROR at line 1:

ORA-00701: object necessary for warmstarting database cannot be altered

Also all DBA_REGISTRY components are is VALID

Changes

Upgrade from 10gR2 to 11gR2

Cause

The Database Instance Crashed for some reason during running the Upgrade Scripts ,

which Caused some upgrade steps to be missing .

Solution

1- Take the backup of the database .

2- start the database in upgrade mode , then execute the utlmmig.sql script.

$sqlplus "/as sysdba"

SQL> shutdown immediate

SQL> startup upgrade

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE;

SQL> @?/rdbms/admin/utlmmig.sql

Note

: the same steps can be used to recreate the indexes , this can be

confirmed by checking the LAST_DDL_TIME value in dba_objects before and

after executing the above action plan :

for example : select LAST_DDL_TIME from dba_objects where OBJECT_NAME='I_OBJ4';

3. 解决方案

重建脚本为非常规恢复操作，建议操作前创建 Oracle restore point还原点,出现其它异常可以回退。

# 1. 设置归档模式/flashback on
shutdown immediate
startup mount
alter database archivelog;
alter database flashback on;
archive log list;
select flashback_on from v$database;


# 2. 设置闪回恢复区参数
ALTER SYSTEM SET db_recovery_file_dest_size=10g SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;

show parameter db_recovery

# 3. 创建还原点                                                
create restore point upgrade_point guarantee flashback database;

set linesize 300 pagesize 9999
col name for a20
col RESTORE_POINT_TIME for a40
col TIME for a40
select * from v$restore_point;

# 4. 执行重建脚本
shutdown immediate
startup upgrade
ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE;
@?/rdbms/admin/utlmmig.sql
startup
select LAST_DDL_TIME from dba_objects where OBJECT_NAME='I_OBJ4';


# 5. 删除 restore point或闪回

### 正常

drop restore point upgrade_point; 

set linesize 300 pagesize 9999
col name for a20
col RESTORE_POINT_TIME for a40
col TIME for a40
select * from v$restore_point;

### 异常
su - oracle
sqlplus / as sysdba
shutdown immediate
startup mount
flashback database to restore point upgrade_point;
alter database open;