ORA-08104: this index object is being online built or rebuilt

Got this error after session was killed (or if we have done a ctrl + C) while doing an index rebuild and then trying to rebuild the index again, its seems the cause is – A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not

SQL> ALTER INDEX ORAUSER.TEST_INDX
REBUILD
TABLESPACE EPS_DATA
STORAGE (
         INITIAL     64M
         NEXT        64M
        )
ONLINE;
ALTER INDEX ORAUSER.TEST_INDX
*
ERROR at line 1:
ORA-08104: this index object 18902057 is being online built or rebuilt

SQL> select obj# ,name from obj$ where OBJ#=18902057;

      OBJ# NAME
---------- ------------------------------
  18902057 TEST_INDX

SQL> select obj#,flags from ind$ where obj#=18902057;

      OBJ#      FLAGS
---------- ----------
  18902057        517


SQL> declare
   lv_ret BOOLEAN;
    begin
    lv_ret :=dbms_repair.online_index_clean(18902057);
    end;
    /

PL/SQL procedure successfully completed.

SQL> select obj#,flags from ind$ where obj#=18902057;

      OBJ#      FLAGS
---------- ----------
  18902057          5

SQL> select obj# ,name from obj$ where OBJ#=18902057;

      OBJ# NAME
---------- ------------------------------
  18902057 TEST_INDX

SQL> ALTER INDEX ORAUSER.TEST_INDX
REBUILD
TABLESPACE EPS_DATA
STORAGE (
         INITIAL     64M
         NEXT        64M
        )
ONLINE;

Index altered.

ORA-20000: ** FAILED TO GET COLUMN STATS FOR THE TABLE

SQL> BEGIN
STAT_MAINTENANCE.GATHER_SCHEMA_STATS_JOB('TESTUSER');
END;
/

BEGIN
*
ERROR at line 1:
ORA-20000: ** FAILED TO SET STAT_TIME FOR THE TABLE **: TESTUSER.TEST_TABLE
ERROR: ORA-20000: ** FAILED TO GET COLUMN STATS FOR THE TABLE **:
TESTUSER.TEST_TABLE Make sure Table has Standard Stats. ERROR: ORA-20000:
Unable to get values for column AVAILABLE_DATE
ORA-06512: at "SYS.DBMS_STATS", line 12211
ORA-06512: at "SYS.DBMS_STATS", line 12224
ORA-06512: at "STAT_MAINTENANCE", line 1072
ORA-06512: at "STAT_MAINTENANCE", line 1086
ORA-06512: at "STAT_MAINTENANCE", line 1171
ORA-06512: at "STAT_MAINTENANCE", line 1186
ORA-06512: at "STAT_MAINTENANCE", line 1645
ORA-06512: at "STAT_MAINTENANCE", line 1779
ORA-06512: at line 2



SQL> select * from user_tab_stat_prefs where table_name='TEST_TABLE';

TABLE_NAME   PREFERENCE_NAME      PREFERENCE_VALUE
------------ -------------------- ----------------------------
TEST_TABLE   ESTIMATE_PERCENT     DBMS_STATS.AUTO_SAMPLE_SIZE
TEST_TABLE   METHOD_OPT           FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1,"C4" SIZE 1
  
SQL> select DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE')
------------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1,"C4" SIZE 1


Describe the table and check if any of the columns were removed.

SQL> DESC TESTUSER.TEST_TABLE

 Name     Null?    Type
 -------- -------- ----------------------------
 P1       NOT NULL NUMBER(38)
 C1                NUMBER(38)
 T1                VARCHAR2(3 CHAR)
 T2                TIMESTAMP(6)
 C2                VARCHAR2(32 CHAR)
 C3                FLOAT(126)
 C5                VARCHAR2(255 CHAR)
 C7       NOT NULL NUMBER(38)

  --- from PREFERENCE_VALUE, remove column dropped and use it below
SQL> EXEC DBMS_STATS.DELETE_TABLE_PREFS ('TESTUSER', 'TEST_TABLE','METHOD_OPT');


SQL> BEGIN
dbms_stats.Set_table_prefs('TESTUSER', 'TEST_TABLE', 'METHOD_OPT' , 'FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS C1 SIZE 1,C2 SIZE 1,C3 SIZE 1');  
END;
/


SQL> select * from user_tab_stat_prefs where table_name='TEST_TABLE';

TABLE_NAME   PREFERENCE_NAME      PREFERENCE_VALUE
------------ -------------------- --------------------------------------
TEST_TABLE   ESTIMATE_PERCENT     DBMS_STATS.AUTO_SAMPLE_SIZE
TEST_TABLE   METHOD_OPT           FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1

SQL> select DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE')
--------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS C1 SIZE 1,C2 SIZE 1,C3 SIZE 1



SQL> BEGIN
STAT_MAINTENANCE.GATHER_SCHEMA_STATS_JOB('TESTUSER');
END;

/

ORA-15135: The ASM cluster is not in rolling patch state

Issue: In one of our cluster nodes when we were doing a Oracle Bundle patching in rolling fashion(involves GI patching as well), We started hitting the below error. We realized that the CRS Stack was down on the other nodes prior to patching due to which nodes cannot communicate about its patching/rolling state to other nodes (because CRS was down on other nodes) and it ends up failing. 

Solution: To overcome this we had to stop the CRS on first node and then start the patching on the next node.

2017-02-23 23:29:47.453 [ORAAGENT(39105)]CRS-5017: The resource action "ora.asm start" encountered the following error: 
2017-02-23 23:29:47.453+ORA-15135: The ASM cluster is not in rolling patch state. The patch level [3351897854] of instance +ASM2 is not the same as the current cluster patch level [2552957799].
. For details refer to "(:CLSN00107:)" in "/opt/oracle/diag/crs/linux02/crs/trace/ohasd_oraagent_orprod.trc".

When doing a opatch lsinventory on GI home it result in the below, we can verify that the patching levels are different. 

Patch level status of Cluster nodes :
Patching Level Nodes
-------------- -----
2552957799 linux01
3351897854 linux02

In case the CRS stack is kept up on all nodes, the information about the patching state is made aware to the other node and it passes through successfully. Below is the result of opatch lsinventory when the CRS stack was kept up.

Patch level status of Cluster nodes :
Patching Level Nodes
-------------- -----
2552957799 linux02,linux01