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

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]

Issue: I was doing an import using datapump and encountered the below error.

Database version: 12.1.0.2.0

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]

ORA-30926: unable to get a stable set of rows in the source tables

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPW$WORKER", line 11252

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

0xe6850a88     27109  package body SYS.KUPW$WORKER

0xe6850a88     11279  package body SYS.KUPW$WORKER

0xe6850a88     24279  package body SYS.KUPW$WORKER

0xe6850a88     20683  package body SYS.KUPW$WORKER

0xe6850a88      4545  package body SYS.KUPW$WORKER

0xe6850a88     12056  package body SYS.KUPW$WORKER

0xe6850a88      2081  package body SYS.KUPW$WORKER

0x3584ffed0         2  anonymous block

Solution:

We have a work around to this issue. Reimport the data with impdp and use an additional parameter EXCLUDE=STATISTICS.

I was able to import the data successfully and post the import I have gathered the stats using the DBMS_STATS package.

How to use OEM to run a script against many databases

We had a requirement to create a particular user in all our 500+ databases. Instead of creating the user individually we thought of leveraging the power of OEM to do so and we succeeded in doing so by just a few clicks.

The steps followed are as followed:
1. Develop a script you want to get it executed.(sample provided below)
2. Go to Enterprise --> Job --> Library
3. In the "create library job" section select "SQL Script" and click "Go"
4. In the "General" section provide the "Name" of Job
5. Provide Target Type as "Database Instance"
6. Click "Parameters" and specify the SQL Script over there (sample provided below)
7. Click "Credentials" and specify the "Database" and "Host" credentials 
8. Click "Schedule" and specify how often you want to run the job, for me it was "One Time (Immediately)"
9. Finally "Save the job" to library

10. Once the job is saved to the Library, Select the job and click the "Submit" button
11. On the new page click "Add" to add the targets(in our case we have selected database instances) and then "Submit" the Job.



Sample SQL:
WHENEVER SQLERROR EXIT FAILURE;
DECLARE
cursor c1 is select DATABASE_ROLE from v$database;
db_role VARCHAR2(40);
BEGIN
open c1;
fetch c1 INTO db_role;
IF db_role='PRIMARY' THEN
execute immediate 'create user test identified by TesT_1_23';
execute immediate 'grant connect, resource to test';
dbms_output.put_line('created TEST user');
ELSE
dbms_output.put_line('STANDBY DATABSE');
END IF;
END;
/