Not Deploying is also Important!

Hello there,

I mentioned about writing a nasty ORA-600 error and here it is; One day I woke up and one my databases was started to raise some ORA-600 errors:

ORA-00600: internal error code, arguments: [ktfacht1-0], [], [], [], [], [], [], [], [], [], [], []

there were also some “snapshot too old” errors too but don’t confuse with manual or insufficient undo etc. Application on this database is using Flashback Data Archive. some of the tables are in FBA (short for FlashBack [data] Archive). some thimes developers or app users are trying to get even before the flashback retention time and they got snapshoot too old error so we are used to it. My first response was “naah probably nothing” well, it wasn’t 🙂

Suddenly some developers contact me about getting snapshot error on flashback archive tables and they were getting error for any time even just for 5 minutes ago (this short time range even doesn’t need flashback archive, standard undo retention is enough for that) What was happening? For some table that are in flashback data archive we started to get snapshot too old error, then number of tables are started to increase! first 1 then 4 then 6…

When I check the trace files for that ora-600 errors I got statements like this:

(I deleted actual column list, owner and table_name)

you might familiar with flashback archive but if you are not check this post: https://mustafakalayci.me/2019/03/02/goodbye-log-triggers-welcome-flashback-data-archive/

Preliminary Information

Flashback archive has some tables in SYS schema (mostly stores context data) and when you add a table into flashback archive, Oracle creates 3 tables in actual table’s schema.

SYS_FBA_DDL_COLMAP_nnnnn : column mapping table, when you add/remove/modify a column a new row will be inserted here
SYS_FBA_HIST_nnnnn : actual table historical data.
SYS_FBA_TCRV_nnnn : historical transaction data.

“nnnnn” is OBJECT_ID in dba_objects for that table.

when you add a table into flashback archive, Oracle does not create those SYS_FBA tables immediately! They created after some transactions on the table and whenever the flashback data required to written into those tables. (that is I think a problem by the way, they should be created immediately).

End of Preliminary Information

When I checked some important tables data in SYS_FBA_HIST_nnnnn table, I did see that new data is coming but when I run a flashback query I got ORA-00600. when you search for “ktfacht1-0” you get some docs about “virtual columns” which are not related to our case, there were no any virtual columns (or functioned based indexes etc).

Of course create an SR with severity 1 and as you can guess this was happening on only PROD environment! Murphy laws. After around 2 weeks (yes it was a little bit long), uploading mass amount of log/trace files to Oracle support and talking to many different support technicians, they offered a zoom call, during the call we check the system again and trace files as well, finally they told me that only one table has a problem and the problem is empty SYS_FBA_DDL_COLMAP table!

When a table is added to FBA and SYS_FBA_DDL_COLMAP is created, current columns of table are written into ddl_colmap. Somehow it wasn’t exist on that table. Because of that Oracle cannot write other flashback data (like old table data and transaction information) cannot be written into sys_fba tables and as a result of that, Oracle blocks some rollback segments which also contains other tables data (other tables that cannot run flashback query on them).

Support told me to remove this table from flashback archive will solve the problem and since this tables newly added into FBA and there were no historical data, I talked to dev team and removed table from FBA and viola! everything get back to normal. that is it. Oracle support couldn’t tell why this could be happen (and they cannot because actually we did it 🙂 ).

After talking to support and solving the problem, I started to dig why that happened. During my checks, I realized that there some SYS_FBA tables but they are not related to any actual tables! empty SYS_FBA_HIST_nnnnn tables were there, then I started to check my audits about DDL operations on the problematic table and I found it! it was added to FBA on Thursday (day is important) then weekend and on Wednesday BAM, we got ORA-00600s.

after getting into really deep I finally solved the mystery: as said before, SYS_FBA tables are not created immediately, they are created after some transactions. This table is a parameter definition table and not get much transactions. When we add this table into FBA on THURSDAY, there were no any SYS_FBA tables. here is step by step how Murphy laws are activated:

1. Table is added into Flashback archive on THURSDAY (for all databases, dev, test, acp and prod)!
2. on that weekend there were “Application PRODUCTION Release”. Dev team is using a third party tool to get differences between two databases (ACP and PROD), generate scripts and run them in PROD database. Release is completed.
3. ACP database is copied from PROD few years ago so some of the table has same object id including this problematic table.
4. on ACP database some DML statements has run on that table and SYS_FBA tables are created on ACP db.
5. since there were no limitation on third party tool (to get differences between dbs) this tool generates create script for all SYS_FBA tables that is not exists in PROD. This tool does not copy data , just structure and SYS_FBA table for that table created on PROD without data (that is way sys_fba_ddl_colmap was empty).
6. after weekend on Wednesday, a new row is inserted into table and Oracle found SYS_FBA tables are created (even if they are not actual sys_fba tables) so tried to use them but data was missing and that eventually caused ORA-00600.

and also that is why there some SYS_FBA tables that are not related to actual tables (they were not in dba_flashback_Archive_Tables or their object_id (nnnnn at the end) was not exists in dba_objects). this tool was copying them into upper databases (dev => test => acp => prod).

After finding the root cause, we defined some filters on the application not to get sys_fba (and some other Oracle related) tables and removed all un-related SYS_FBA tables in databases. by the way SYS_FBA tables cannot be dropped by default using DROP TABLE command but as you guess, these ones dropped because they are actual sys_fba tables, they were just user defined tables.

Thanks for reading if you come to the bottom 🙂 Deployment is one of the most important parts of application development, so choose your methods wisely.

Wish you good, healthy days.