重建 oracle Bootstrap Indexes

郑全

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';