brspace error during tbreorg

[vince_laurent]

I created a few temp tablespaces to reorg my data too, then I would drop the old ones, create new LMTS ones, then drop the temp.

My command line is:

brspace -f tbreorg -a reorg -i psapods2i -m offline -n psapods2d -s psapodsd -t "*"

Then

brspace -f tbreorg -a reorg -i psapodsi -m offline -n psapodsd -s psapods2d -t "*"

But I am seeing TONS of errors like this:

Tue Apr 22 23:48:23 2008

Some indexes or index [sub]partitions of table SAPR3./BIC/B0000342000 have been marked unusable

Tue Apr 22 23:48:23 2008

Some indexes or index [sub]partitions of table SAPR3./BIC/B0000343000 have been marked unusable

Tue Apr 22 23:48:24 2008

Some indexes or index [sub]partitions of table SAPR3./BIC/B0000344000 have been marked unusable

What am I mssing using the brtools?

Thanks

Vince

oracle 10.2.0.2 on HP-Itanium

Edited by: Vince Laurent on Apr 23, 2008 6:58 AM

Added OS and DB info

[Former Member]

Hi,

you specify an index, index partition, or index subpartition that has been marked UNUSABLE

try to set SKIP_UNUSABLE_INDEXES session parameter TRUE.

use ALTER SESSION command to set this parameter.

regards,

kaushal

[vince_laurent]

I am not setting it - the reorg is causing it, no?

How to I make them usable again? What made them unusable?

Vince

[stefan_koehler]

Hello Vince,

> How to I make them usable again? What made them unusable?

Taking my crystal ball and starting guessing:

• Maybe you have created your partitioned indexes with nologging and your database was recovered

• Maybe you have block corruption

• Maybe the ranges in the partition are invalid

> Some indexes or index subpartitions of table SAPR3./BIC/B0000342000 have been marked unusable

Try to identify the indexes and check why they are unusable:

> SQL> SELECT * from ALL_OBJECTS where STATUS = 'INVALID';

But you should not ignore this error..

Regards

Stefan

[vince_laurent]

I ran the SQL and it returned no indexes, partitions, or sub partitions. Just some public synonyms. I ran it as SYS then as SAPR3. Still the same.

Where these bad items go?

-vince

[stefan_koehler]

Hello Vince,

oh sorry my mistake, please run this query:

SQL> SELECT INDEX_NAME, PARTITION_NAME from ALL_IND_PARTITIONS where STATUS = 'UNUSABLE';
SQL> SELECT INDEX_NAME, TABLE_NAME from ALL_INDEXES where STATUS = 'UNUSABLE'; 

Regards

Stefan

[vince_laurent]

sapbqa:orabqa 4> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Apr 23 08:58:01 2008

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning and Data Mining options

SQL> SELECT INDEX_NAME, PARTITION_NAME from ALL_IND_PARTITIONS where STATUS = 'UNUSABLE';

no rows selected

SQL> SELECT INDEX_NAME, TABLE_NAME from ALL_INDEXES where STATUS = 'UNUSABLE';

no rows selected

SQL> connect sapr3/********

Connected.

SQL> SELECT INDEX_NAME, TABLE_NAME from ALL_INDEXES where STATUS = 'UNUSABLE';

no rows selected

SQL> SELECT INDEX_NAME, PARTITION_NAME from ALL_IND_PARTITIONS where STATUS = 'UNUSABLE';

no rows selected

SQL> connect sapr3shd/*******

Connected.

SQL> SELECT INDEX_NAME, PARTITION_NAME from ALL_IND_PARTITIONS where STATUS = 'UNUSABLE';

no rows selected

SQL> SELECT INDEX_NAME, TABLE_NAME from ALL_INDEXES where STATUS = 'UNUSABLE';

no rows selected

Curious no doubt...

Vince

[stefan_koehler]

Hello Vince,

in this case i can not understand the error of the BR*Tools.

Here is an example for an index that i have made unusable manually.

SQL> SELECT INDEX_NAME, TABLE_NAME from ALL_INDEXES where STATUS = 'UNUSABLE';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
I_ZTEST_COMP                   ZTEST

The only thing that i can think of is that the unusable indexes were recreated and now they are VALID.

Check when the indexes for the table SAPR3./BIC/B0000342000 were rebuild / created the last time. (this information is provided in the dba_objects view)

Regards

Stefan

[former_member185954]

Hello Vince,

Try this:

select distinct STATUS from dba_ind_partitions;

The above query will give you all possible distinct values for STATUS field and you would be then able to home into the STATUS that you are interested in rectifying using the queries others have provided.

Please note the change in the tablename which i have used dba_ind_partitions

Regards,

Siddhesh