ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state

The customer was on 10.1.0.3. I'm reproducing the case on a 10.2.0.5 database for the purpose of this blogpost.

The database was already down for 4days and a SR1 was already raised with Oracle.

Their first solution was to restore the backup - that was a bit tricky as apparently... as usual in these situations, there was no backup (they thought there was but there wasn't)

At first I focused on getting the data out with DUDE which took about 4hours for about 40GB, producings lots of DMP's and DDL scripts.

Then a buddy of mine would take over and do the dirty work of rebuilding a new database and reload all data based on DUDE's output.

During the unload I had glanced at the SR1 call and noticed that the Oracle support engineer had done an effort to revive the crashed database by opening the 10G database using a 11G instance in upgrade mode.

Which at first sounded strange to me as that would introduce an extra complexity because of mis-matching dictionaries.

Once I had unloaded all data with DUDE (and most pressure was gone), I tried to open the database using an 11.2.x instance, making sure the COMPATIBLE parameter was set to 10.1.0.3 (otherwise you can't start your db with 10.1.0.3 anymore)

At least there was a cold backup of the database from just after the table moves, so I could play around a bit.

Now remember - this is an 11g instance running a 10g database with it's original dictionary - when comparing dictionaries (sql.bsq versus dpart.bsq), the column DEFMAXSIZE did not exist in 10g.

So instead of introducing the complexity of running the migrate scripts, I decided to just add the column and see what happens :

SQL> alter table partobj$ add defmaxsize number ;

Table altered.

SQL> alter index I_DEPENDENCY1 rebuild ;

Index altered.

Bonus !

During the actual case the above would also fail - it would complain about index I_WRI$_OPTSTAT_IND_OBJ#_ST being unusable.

This index seems related to the set of tables responsible for storing object statistics (index statistics in this case) - it seems logical that if you rebuild an index, oracle adds some stats in these tables.

If the related index is then not usable, it's only logical it complains.

So in fact I had to rebuild that index first :

SQL> alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild ;

Index altered.

And now I was able to rebuild all indexes without issues.

We could now start the db back in oracle 10g :

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 12 17:48:00 2013

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1015021568 bytes

Fixed Size 2101168 bytes

Variable Size 243269712 bytes

Database Buffers 734003200 bytes

Redo Buffers 35647488 bytes

Database mounted.

Database opened.

Double BONUS !

Now all that was left was to recompile the invalid objects :

SQL> select count(*) from dba_objects where status='INVALID' and owner='SYS' ;

496

SQL> @?/rdbms/admin/utlrp

SQL> select count(*) from dba_objects where status='INVALID' and owner='SYS' ;

COUNT(*)

----------

0

And voila - the db is back among the living !

Again, when I reproduced this on my test kit, I had no invalid objects left after utlrp ran the first time, but on the actual database I was left with invalid KU$ views which are related to metadata generation needed for exp/expdp utilities.

I noticed this when I tried to do a full export :

About to export the entire database ...

. exporting tablespace definitions

. exporting profiles

. exporting user definitions

. exporting roles

. exporting resource costs

. exporting rollback segment definitions

. exporting database links

. exporting sequence numbers

. exporting directory aliases

. exporting context namespaces

. exporting foreign function library names

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions

. exporting system procedural objects and actions

. exporting pre-schema procedural objects and actions

. exporting cluster definitions

EXP-00056: ORACLE error 24324 encountered

ORA-24324: service handle not initialized

EXP-00056: ORACLE error 24324 encountered

ORA-24324: service handle not initialized

EXP-00056: ORACLE error 24324 encountered

ORA-24324: service handle not initialized

EXP-00000: Export terminated unsuccessfully

Datapump as well as exp was tumbling down half way through ddl generation.

So the last step to do was regenerate these views :

-- re-install DataPump types and views

@?\rdbms\admin\catdph.sql

-- re-install tde_library packages

@?\rdbms\admin\prvtdtde.plb

-- re-install DataPump packages

@?\rdbms\admin\catdpb.sql

-- re-install DBMS DataPump objects

@?\rdbms\admin\dbmspump.sql

-- recompile invalid objects

@?\rdbms\admin\utlrp.sql

And finally we can do a full export and regenerate the database !

Final note - I also tested this starting on an 11gR2 (moving sys tables in 11gR2) - and it seems 11gR2, allthough bitching and moaning, does not crash after restart and allows you to rebuild the indexes.

ps - if you were thinking about using SKIP_UNUSABLE_INDEXES - that didn't work either on the dictionary indexes ;-)