ORA-00942 Errors from Wnnn Slave Processes working on Behalf of FBDA: Solutions Provided

INTRO – Flashback Time Travel (FDA) internal queries fails with ORA-00942

While diagnosing an ORA-00001 unique constraint violation that was being silently ignored due to a WHEN OTHERS THEN NULL construct (which is not a recommended practice, see this blog post for more details), I enabled trace events for 10035 and error stack tracing for ORA-00001. However, after enabling the tracing, I began to see dozens of ORA-00942 errors in the alert log. These unexpected errors cluttered the logs and started to obscure the original issue I was trying to diagnose.

The database version was 19.22. I used commands provided below to diagnose the issue.

SYS@bltdb2>  alter system set events '10035 trace name context forever,level 1';

System altered.

SYS@bltdb2> alter system set events '942 trace name errorstack level 4';

System altered.

Here is a sample of output from the alert.log, captured within just a few seconds. It’s raining errors like cats and dogs!

…

PARSE ERROR: ospid=375397, error=942 for statement:
Additional information: hd=0x45bd9524f0 phd=0x45bd953918 flg=0x101476 cisid=0 sid=0 ciuid=0 uid=0 sqlid=7d0gb9abm82kv
…Current username=SYS
…Application: KTSJ Action: KTSJ Slave
PARSE ERROR: ospid=375397, error=942 for statement:
Additional information: hd=0x44bf321e60 phd=0x44bf323090 flg=0x101476 cisid=0 sid=0 ciuid=0 uid=0 sqlid=gn98a211a3gna
…Current username=SYS
…Application: KTSJ Action: KTSJ Slave
PARSE ERROR: ospid=375397, error=942 for statement:
Additional information: hd=0x463e7c3118 phd=0x463e7c4540 flg=0x101476 cisid=0 sid=0 ciuid=0 uid=0 sqlid=45dsaj93xa2ca
…Current username=SYS
…Application: KTSJ Action: KTSJ Slave
2024-10-22T09:47:06.819861+03:00
Errors in file /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w02f_377297.trc:
2024-10-22T09:47:06.981583+03:00
Errors in file /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w01s_88442.trc:
2024-10-22T09:47:07.025676+03:00
Errors in file /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w01o_93335.trc:
2024-10-22T09:47:07.079297+03:00
Errors in file /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w047_399848.trc:
2024-10-22T09:47:07.311135+03:00
Errors in file /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w052_375616.trc:
2024-10-22T09:47:08.591741+03:00
PARSE ERROR: ospid=377297, error=942 for statement:
Additional information: hd=0x463ee4a450 phd=0x463ee4b878 flg=0x101476 cisid=0 sid=0 ciuid=0 uid=0 sqlid=5m4z003f389n8
…Current username=SYS
…Application: KTSJ Action: KTSJ Slave
2024-10-22T09:47:08.867915+03:00
PARSE ERROR: ospid=88442, error=942 for statement:
Additional information: hd=0x45bde772b0 phd=0x45bde786d8 flg=0x101476 cisid=0 sid=0 ciuid=0 uid=0 sqlid=6a8hy2usvknmd
…Current username=SYS
…Application: KTSJ Action: KTSJ Slave
2024-10-22T09:47:08.877519+03:00
Errors in file /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w03u_96432.trc:
2024-10-22T09:47:08.945842+03:00
PARSE ERROR: ospid=377297, error=942 for statement:
Additional information: hd=0x467eb53a48 phd=0x467eb54e70 flg=0x101476 cisid=0 sid=0 ciuid=0 uid=0 sqlid=a31shtdfqjq7a
…Current username=SYS
…Application: KTSJ Action: KTSJ Slave
PARSE ERROR: ospid=377297, error=942 for statement:
Additional information: hd=0x44bf749050 phd=0x44bf74a7a8 flg=0x101476 cisid=0 sid=0 ciuid=0 uid=0 sqlid=0jcc6d7vc1j04

You may finish event tracing with commands provided below.

SYS@bltdb2>  alter system set events '10035 trace name context off';

System altered.

SYS@bltdb2> alter system set events '942 trace name errorstack off';

System altered.

What is KTSJ Process?

KTSJ is the process work on behalf of  Space Management Coordinator process (SMCO).

What is SMCO Process?

The SMCO process coordinates the execution of various space management related tasks.

This background process coordinates the execution of various space management tasks, including proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement these tasks.

What are Wnnn slave Processes?

Wnnn processes perform various background space management tasks, including proactive space allocation and space reclamation.

When performing work on behalf of Space Management, Wnnn processes are slave processes dynamically spawned by SMCO to perform space management tasks in the background. These tasks include preallocating space into locally managed tablespace and SecureFiles segments based on space usage growth analysis, and reclaiming space from dropped segments. After being started, the slave acts as an autonomous agent. After it finishes task execution, it automatically picks up another task from the queue. The process terminates itself after being idle for a long time.

What is the relation of Wnnn Processes with Flashback Data Archive?

Actually Wnnn processes not only work on behalf of SMCO, but also on behalf of FBDA process. There is an old discussion about it in forums.oracle.com.

According to the Flashback Data Archive Whitepaper, Archiving of individual transaction is either done by FBDA process, slave processes coordinated by FBDA, or inline by foreground processes doing the DMLs.

It is also documented in FDA – Flashback Data Archive Usage and Best Practices (a.k.a. Total Recall)(Doc ID 2370465.1), “Monitor the workload for FDBA tracked tables and check for DELETE statements against the SYS_FDBA_TCRV_ tables.  These deletes are normally executed by the FBDA and the w00n worker processes.” 

Now, Let’s open one of trace files to gather more information about the underlying issue.

[root@blt02 ~]# vi /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w052_375616.trc

Trace file /u01/app/oracle/diag/rdbms/bltdb/bltdb2/trace/bltdb2_w052_375616.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.22.0.0.0
Build label: RDBMS_19.22.0.0.0DBRU_LINUX.X64_231229
ORACLE_HOME: /u01/app/oracle/product/19.22/dbhome_1
System name: Linux
Node name: exa02.localdomain
Release: 4.14.35-2047.528.2.2.el7uek.x86_64
Version: #2 SMP Thu Oct 12 14:50:29 PDT 2023
Machine: x86_64
Storage: Exadata
CLID: P
Instance name: bltdb2
Redo thread mounted by this instance: 2
Oracle process number: 520
Unix process pid: 375616, image: oracle@exa02.localdomain(W052)

*** 2024-10-22T08:52:24.508738+03:00
*** SESSION ID:(2535.8584) 2024-10-22T08:52:24.508760+03:00
*** CLIENT ID:() 2024-10-22T08:52:24.508765+03:00
*** SERVICE NAME:(SYS$BACKGROUND) 2024-10-22T08:52:24.508770+03:00
*** MODULE NAME:(KTSJ) 2024-10-22T08:52:24.508775+03:00
*** ACTION NAME:(KTSJ Slave) 2024-10-22T08:52:24.508781+03:00
*** CLIENT DRIVER:(SERVER) 2024-10-22T08:52:24.508785+03:00

error while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while delete

*** 2024-10-22T08:55:06.787405+03:00
error while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while delete

*** 2024-10-22T08:55:07.839243+03:00
error while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while delete

*** 2024-10-22T09:02:16.009323+03:00
error while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while delete
…

*** 2024-10-22T09:47:07.310892+03:00
error while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while deleteerror while delete
*** 2024-10-22T09:47:07.310958+03:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=4, mask=0x0)
—– Error Stack Dump —–
at 0x7ffefc5f0770 placed dbkda.c@298
at 0x7ffefc6016e0 placed kpoodr.c@237
—– Current SQL Statement for this session (sql_id=9yhhyjsd47xhk) —–
DELETE /*+ NO_PARALLEL */ FROM “KARTAL”.SYS_FBA_TCRV_84654 WHERE (RID, STARTSCN) IN ( SELECT /*+ NO_PARALLEL(h) */ rid, startscn FROM “KARTAL”.SYS_FBA_HIST_84654 h WHERE ENDSCN BETWEEN :1 AND :2 )

—

In this case, the failure of the SQL statement with sql_id (9yhhyjsd47xhk) due to the ORA-00942: table or view does not exist error suggests that the Wnnn worker processes are attempting to delete older records from a table in the Flashback Data Archive, specifically targeting the non-existent “KARTAL”.SYS_FBA_TCRV_84654 table.

Let’s clarify the issue. According to the “What Event Triggers The Creation Of The Sys_fba* Tables For Total Recall? (Doc ID 1302393.1)“

The SYS_FBA_* tables are created lazily and queries on SYS_FBA_* tables are not supported. The history tables are created lazily when there is history to be generated. There should be no need to query the history tables directly because flashback “as of” and “versions” queries are transparently used to access the history tables as needed. If there is no history generated (DML/DDL issued on the table after enabling flashback archive), the history tables are not created. Tracking the creation of the history tables is only meaningful after changes have been made to the base table. By default FBAR (Flashback Archive) wakes up at 5 minutes interval in 11.2.0.1 and performs it’s activities like creating history tables (SYS_FBA_*) etc. In 11.2.0.2.0 > it wakes up every 6 seconds.

Regarding the creation of history tables:

For Non-RAC env:
After enabling flashback archive for a table, FBAR would create history tables when it wakes up next for scanning. In this case, the delay could be max up to 5 minutes.

For RAC env:
After enabling flashback archive for a table, FBAR doesn’t create history tables until a DML is performed.
Post DML operation, at first FBAR scan these tables would be created.

SYS_FBA_DDL_COLMAP_ Maps out the current state of all columns for an existing FBDA-enabled table.

SYS_FBA_HIST_ Contains the actual “delta” values for all recent (i.e. non-purged) transactions for an existing FBDA-enabled table.

SYS_FBA_TCRV_ Maps the latest set of changes applied against an existing FBDA-enabled table to their corresponding UNDO transaction information.

Conclusion

Given that no DML operations have occurred on the related object (object with ID 84654), it makes sense that the Flashback Data Archive (FBDA) background worker processes are encountering issues due to the absence of these internal SYS_FBA_* tables. FBDA is likely still trying to manage background tasks for this table based on the specified retention period, even though no modifications are triggering the creation of the associated history tables.

These failures are significantly impacting the overall time metrics in the V$SYS_TIME_MODEL view, specifically increasing the failed parse elapsed time. Each unsuccessful attempt by the FBDA worker processes to access the non-existent SYS_FBA_* tables results in repeated parse failures, which not only disrupts normal operations but also skews database performance metrics, making it harder to accurately monitor and analyze system performance. (High % of DB time for failed parse elapsed time – High ratio of parse count(failures) to parse count(hard) in AWR)

Possible Solutions

Solution 1 : Question whether these tables truly need to be tracked with Flashback Data Archive, given that no changes have occurred for a considerable period (in this case, 5 months no change for 26 tables).

You can use query provided below to detect these tables.

SQL> SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE ARCHIVE_TABLE_NAME NOT IN 
(SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE 'SYS_FBA_HIST%');

Here are some examples of what should be considered for Flashback Data Archiving:

• Data that is highly sensitive and the nature of its change is important and will be reviewed at a later date.
• Personal individual information (PII) data where changes to that information must be tracked to comply with laws and regulations.
• Personal health information (PHI) data mandated by HIPPA/HIPPA-2 laws and regulations.
• Certain financial information data that may pertain to financial reporting to meet governance laws for public companies.

Here are some examples of what should NOT be considered for Flashback Data Archiving:

• Data that contains history such as batch job logs, audit logs, and any other data that already contains temporal changes.
• Data that contains orders for goods and services as this data already contains (or should contain) sufficient history.
• Data that remains static in nature and undergoes very little change.

The above are just example guidelines but your business must consider its own requirements for what data should be tracked by FBDA.

It is not advisable to track nearly every table in the application schema as this simply requires more resources – storage space, processing and maintenance – in order to manage all of the tables being tracked.

Solution 2 : To prompt the creation of the associated SYS_FBA_* tables, make a minor, inconsequential modification on the underlying table and revert it immediately (after a 6-second delay probably).

By the way, did I mention that, like Tom Kyte, I hate “WHEN OTHERS”?

Hope it helps.