Researching a Bug

A few months ago we hit an Oracle bug related to streams replication crash after creating an index (bug 21320182). There is a patch so we installed the patch in test and it seemed to solve the problem, but we never patched the production.
Today we hit this issue in production after creating an index we needed. It’s important to say that we wanted to patch the prod a while ago, but we didn’t get approval for maintenance window.

You can guess that there was pressure to fix this issue (without installing the patch obviously), so I went to check the note mentioned above. The first thing I looked for is a workaround, as these notes sometimes have one, but in this case it said “no workaround”. This means that I might not be able to workaround this and we will have to install the patch after getting an “emergency” maintenance window.
Instead of giving up, I read the note describing the bug and tried to find the exact issue (sometimes these notes don’t provide enough information, but here it did provide some). The note said that the problem is with a concatenated index and tables having virtual columns. From the example it seemed that the indexed columns were created after the virtual columns, so I guessed that this is related to the bug.
I went and check the index and sure enough, it included 3 columns. I then went and check the table. I queried DBA_TAB_COLS to see the table’s columns and ordered them by INTERNAL_COLUMN_ID. I found 5 virtual columns in my table (it was easy to identify them, because their name started with SYS_ and also DBA_TAB_COLS has a column called VIRTUAL_COLUMN which had the value YES for these columns). I also noticed that one of the columns we tried to index had a higher INTERNAL_COLUMN_ID than these virtual columns.
At that point I though about creating the index without that column, but that wasn’t an option. So if the problem is related to the virtual columns, maybe we can remove them. I saw that the DATA_DEFAULT column for these columns was SYS_OP_COMBINED_HASH(…). These are extended statistics columns, and that makes sense since we are using 12.1.0.2, where extended statistics are created automatically (Oracle disabled this in 12.2).
The next step was to remove the extended statistics and make sure that the virtual columns disappear. We can do that by querying DBA_STAT_EXTENSIONS, get the EXTENSION and make sure that DROPPABLE=’YES’. In my case DROPPABLE was yes and I had 5 columns (which matches the columns I saw in DBA_TAB_COLS) so I deleted them using:
exec dbms_stats.drop_extended_stats(ownname => ‘OWNER’, tabname => ‘TABLE’, extension => ‘EXT’);
Where EXT is the EXTENSION from DBA_STAT_EXTENSIONS (e.g. ‘(f1,f2)’).
After I removed the extended statistics I checked DBA_TAB_COLS and didn’t see those virtual columns anymore. At this point we created the index again and monitored the behavior of the system. Before these steps, once the index was created, the errors started immediately. After these steps, everything seems to be fine, and everything is up and running for a few days now.
Conclusion: never be lazy, even if there is no workaround in MOS notes, there might be a workaround for your specific scenario. Read the note about the bug or the bug description (if available), try to figure out what might cause this issue and maybe you’ll be able to find a solution.