Category: Flashback

This post discusses troubleshooting ORA-00942 errors from Wnnn Processes working on Behalf of FBDA (Flashback Data Archive) process. By enabling error tracing, numerous ORA-00942 entries appeared in the alert log. The focus is on the relationship between Wnnn slave processes and Flashback Data Archive management, highlighting that untriggered archive history table creation leads to parsing failures. The absence of SYS_FBA_* tables due to lack of DML operations led to failed parsing attempts by FBDA, adversely affecting performance metrics. Two solutions are suggested: reconsidering Flashback Data Archive necessity for these tables or triggering the creation of SYS_FBA_* tables through minor modifications.

After updating to release 19.22, issues arose with Flashback Data Archive (FDA). Despite bug fixes(35957640), internal indexes were created with the DEFAULT PARALLEL degree. Additionally, split operations caused related indexes to become unusable. A debug of the FBDA process revealed that the split operations lack an “UPDATE INDEXES” clause and no index rebuild operation is done internally. The test case provided highlights the issues. These concerns have been raised in a Service Request for resolution.

The excessive memory usage for SQL queries was discovered in a critical database, traced back to internal queries by the FBDA process. Hard parses, which are resource-intensive, were identified as the cause. The issue was detected through SQL plan and memory consumption analysis. A solution involving the use of bind variables and a workaround for the problem were proposed. A bug related to queries without bind variables was identified and it was revealed that a similar issue did not exist in another database using the flashback feature efficiently. The proposed workaround involved removing the quota clause from flashback data archives.