19.11 High Archive generation for FBDA process (Doc ID 2794340.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 19.11.0.0.0 and later
Information in this document applies to any platform.

Symptoms

1. On 19.11, setting the below context level for FBDA will cause high cpu for FBDA and additional redo.

You must have created a flashback data archive to start the issue.

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('TYPICAL');
OR
EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');

Setting to 'NONE' immediately solves the issue.

2. After upgrading to 19.11, the FBDA process continuously runs millions of queries and creates redo.

This occurs even after dropping all of the flashback data archives CT had for over a year before upgrade.

Removing all flashback data archives and restarting the instance fixes the problem which is not acceptable by certain customers as they wish to have flashback setup.

3. check the v$logmnr_contents.

SQL> select seg_owner, table_name, operation, count(*) from v$logmnr_contents group by seg_owner, table_name, operation order by 4 desc;

SEG_OWNER TABLE_NAME OPERATION COUNT(*)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- ----------
COMMIT 213639 <<<<<<<<<<< <<<<<<<<<<<<<< <<<<<<<<<<< <<<<<<<<<<<<<<
START 213637
SYS SYS_FBA_BARRIERSCN UPDATE 213141 <<<<<<<<<<< <<<<<<<<<<<<<< <<<<<<<<<<< <<<<<<<<<<<<<<
SYS COL_USAGE$ UPDATE 787 <<<<<<<<<<< <<<<<<<<<<<<<< <<<<<<<<<<< <<<<<<<<<<<<<<
INTERNAL 95 <<<<<<<<<<< <<<<<<<<<<<<<< <<<<<<<<<<< <<<<<<<<<<<<<<
SYS WRH$_ACTIVE_SESSION_HISTORY INSERT 53
SYS MON_MODS_ALL$ UNSUPPORTED 37
SYS UNDO$ UPDATE 30
SYS SEG$ UNSUPPORTED 23
SYS HIST_HEAD$ DELETE 18
SYS SEQ$ UPDATE 15
SYS MON_MODS_ALL$ INSERT 12
SYS HISTGRM$ UNSUPPORTED 11
SYS OBJ$ INSERT 8
SYS INDPART$ INSERT 5
SYS WRI$_ALERT_THRESHOLD_LOG INSERT 4
SYS MON_MODS_ALL$ UPDATE 4
SYS WRI$_ALERT_THRESHOLD_LOG DELETE 4
SYS WRI$_ALERT_HISTORY INSERT 3
UNKNOWN OBJ# 19822 DELETE 3
AUDSYS AUD$UNIFIED UPDATE 3
SYS OBJ$ UPDATE 3
AUDSYS AUD$UNIFIED INSERT 3
UNKNOWN OBJ# 19822 INSERT 3
SYS SMON_SCN_TIME UNSUPPORTED 2
SYS WRM$_SNAPSHOT_DETAILS INSERT 2
SYS WRM$_WR_CONTROL UPDATE 2
SYS TABPART$ UPDATE 2
SYS TABPART$ INSERT 2
SYS DEPENDENCY$ UNSUPPORTED 2
SYS OPATCH_XINV_TAB INSERT 1
SYS WRM$_DATABASE_INSTANCE INSERT 1
SYS WRP$_REPORTS_TIME_BANDS DDL 1
SYS OPTSTAT_HIST_CONTROL$ UNSUPPORTED 1
SYS MON_MODS_ALL$ DELETE 1
SYS WRI$_ALERT_OUTSTANDING DELETE 1
SYS WRM$_WR_SETTINGS UNSUPPORTED 1
SYS SYS_FBA_TSFA UNSUPPORTED 1
SYS TABPART$ DELETE 1
SYS TAB$ UNSUPPORTED 1
SYS LTXID_TRANS DELETE 1
SYS WRP$_REPORTS_TIME_BANDS INSERT 1
SYS WRP$_REPORTS_DETAILS INSERT 1
SYS LOBFRAG$ INSERT 1
SYS OBJ$ DELETE 1
SYS LTXID_TRANS INSERT 1
SYS WRP$_REPORTS INSERT 1
SYS WRM$_DATABASE_INSTANCE UNSUPPORTED 1