Oleksandr Denysenko's Blog

21.02.2013

Funny FBDA BUG: ORA-00600 [ktfa_error_handler_map], [942]

Filed under: 11.2,BUG,FBDA,oracle — odenysenko @ 16:49
Tags: , , , ,

I have faced quite interesting situation with Flashback Data Archive(FBDA)  that wasn’t covered yet in blogosphere.

So I have just created new Flashback Archive and altered some tables specifying it(Flashback Archive).

Just after some time of any DMLs on altered tables next error appears in alert.log

Starting background process FBDA
Thu Feb 21 11:07:07 2013
FBDA started with pid=203, OS id=3626
Errors in file /u01/app/oracle/diag/rdbms/siam_opt/siamopt/trace/orcl_fbda_3626.trc  (incident=57628):
ORA-00600: internal error code, arguments: [ktfa_error_handler_map], [942], []
ORA-00942: table or view does not exist

Please, notice presence of additional funny error  – ORA-00942. Why it may be here ? we’ll see later…One more funny fact – user doesn’t receive any error – errors logged only into alert.log. It may be because FBDA process works asynchronously.

So lets look inside mentioned trace file – I have additionally formatted it for readability:

Trace file /u01/app/oracle/diag/rdbms/siam_opt/orcl/trace/orcl_fbda_3626.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

Unix process pid: 3626, image: oracle@server (FBDA)

Flashback Archive: Error ORA-957 in SQL
create table SCHEMA.SYS_FBA_HIST_128014
(RID VARCHAR2(4000),
STARTSCN NUMBER,
ENDSCN NUMBER,
XID RAW(8),
OPERATION VARCHAR2(1),
START_TIME DATE,
ROW_VERSION NUMBER(4),
PERSON VARCHAR2(14),
OPERATION VARCHAR2(14),
PASSWORD VARCHAR2(12),
PARAM1 VARCHAR2(254),
WINDOW_NAME VARCHAR2(50),
SEANS NUMBER(4))
tablespace FDA COMPRESS FOR ALL OPERATIONS
partition by range (ENDSCN) (partition high_part values less than (MAXVALUE))

Flashback Archive: Error ORA-957 in SQL
create global temporary table SYS_MFBA_NHIST_128014
(RID VARCHAR2(4000),
STARTSCN NUMBER,
ENDSCN NUMBER,
XID RAW(8),
OPERATION VARCHAR2(1),
START_TIME DATE,
ROW_VERSION NUMBER(4),
PERSON VARCHAR2(14),
OPERATION VARCHAR2(14),
PASSWORD VARCHAR2(12),
PARAM1 VARCHAR2(254),
WINDOW_NAME VARCHAR2(50),
SEANS NUMBER(4)
)ON COMMIT PRESERVE ROWS

Flashback Archive: Error ORA-942 in SQL
create index SYS_MFBA_NHIST_128014_idx on SYS_MFBA_NHIST_128014(RID)

Flashback Archive: Error ORA-942 in SQL
insert /*+ append */ into SYS_MFBA_NHIST_128014
select /*+ leading(r) use_nl(v)  NO_PARALLEL(r) NO_PARALLEL(v)  */
v.ROWID “RID”, v.VERSIONS_STARTSCN “STARTSCN”,  v.VERSIONS_ENDSCN “ENDSCN”,
v.VERSIONS_XID “XID” ,v.VERSIONS_OPERATION “OPERATION”,  v.START_TIME “START_TIME”,
v.ROW_VERSION “ROW_VERSION”,  v.PERSON “PERSON”,  v.OPERATION “OPERATION”,
v.PASSWORD “PASSWORD”,  v.PARAM1 “PARAM1″,  v.WINDOW_NAME “WINDOW_NAME”,
v.SEANS “SEANS”  from (select * from SYS_MFBA_NROW  order by RID) r,
SCHEMA.LAUNCHLOG versions between SCN :1 and MAXVALUE v where v.ROWID = r.rid
Flashback Archive: Error ORA-942 in SQL
update SYS_MFBA_NHIST_128014 set ENDSCN = STARTSCN where OPERATION = ‘D’

DDE: Problem Key ‘ORA 600 [ktfa_error_handler_map]‘ was flood controlled (0x2) (incident: 57628)
ORA-00600: internal error code, arguments: [ktfa_error_handler_map], [942], []
ORA-00942: table or view does not exist

So we see that FBDA process was creating internal Flashback Archive tables for storing old data and it wasn’t able to do it, but because of another arror ORA-957 duplicate column name, because of user table has column OPERATION and Flashback Archive internal table requires the same column for internal usage.

What I really don’t understand are:

  1. why oracle chose that quite common name(OPERATION) ? without any additional suffix like “#” or “$”…
  2. why presence of such columns are not verified at the time of ALTER TABLE … FLASHBACK ARCHIVE ?

Actually, oracle allows to specify FLASHBACK ARCHIVE option at the table level, but is not able to create internal Flashback Archive tables when DMLs operations are committed.

There are some similar issues described on MOS(check Reference section), but this particular case is not described(and may be not identified ;-)) yet at the time of writing.

Conclusion:

  • sometimes obvious checks are not implemented, allowing declarative actions that actually can not be implemented later
  • asynchronous processing requires more checks than synchronous to provide correct status at declaration time
  • one error may mask another: ORA-00942 logged, but actual error was ORA-00957

References:

  • 1330817.1  Errors After Enabling Fbda ORA-1950 ORA-942 ORA-30036
  • 14208172.8 FBDA may spin or signal error if tracked table has special column name
About these ads

1 Comment »

  1. Nice shot!
    I have another story regarding Total Recall making this feature totally (huh?) unusable. Will try to post it later, stay tuned :)

    Comment by andriydmytrenko — 22.02.2013 @ 10:42 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers

%d bloggers like this: