Update Indexes Clause is missing in split operation of FDA Internal Tables
We have been utilizing Flashback Data Archive for numerous tables in our main database. During this time, we have encountered several issues, such as FDA not being purged, split operations not occurring, duplicate data, SMON_SCN_TIME table storing not enough data etc. We have worked closely with Oracle support to address these problems and have gained considerable expertise in this area.
I have detected two different problems lately (June 2024) and opened a Service Request for Them.
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 by FBAR. 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. For RAC Environments, FBAR doesn’t create history tables until a DML is performed. Post DML operation, at first FBAR scan, these tables would be created. These tables are created with indexes on them by FBAR internally. ( Oracle is constantly changing these indexes and index orders with RUs, but that is another story )
Previously, we were on Release Update 19.20 with the August MRP applied. Recently, we updated to version 19.22 and applied the March MRP for 19.22.
After updating to release 19.22, we noticed that the internal index (in the format of SYS_FBA_HIST_IDX1_object_id) on SYS_FBA_HIST_object_id is created with DEGREE DEFAULT INSTANCE DEFAULT option, whereas it should be created with NOPARALLEL. According to 2981801.1 FDA Indexes are created With PARALLEL DEFAULT After Applying Patch 35039202, this is a known bug fixed in 35957640. Bugfix 35957640 included in the 19.22 RU. I’ve verified through opatch lsinventory that both fixes (35957640 and 35039202) are present in my inventory.
Despite the so-called bug fixes, the initial indexes are still created with the DEFAULT DEGREE.
Oracle Support Team replied to my service request with the following statement, which I don’t find convincing.
” Development team confirmed that it is expected behavior that the index on HIST table is created with “PARALLEL” clause only. It seems that the referenced document ( Doc ID 2981801.1 ) is an incorrect one.”
They removed Doc ID 2981801.1 document from customer access. They claim that, from now on, these indexes will be created with DEFAULT Degree.
Last but not least, these indexes become unusable when a split operation occurs on the high partition (HIGH_PART) of the related table (SYS_FBA_HIST_). If the size of the HIGH_PART partition is small (although what constitutes ‘small’ exactly is uncertain), there are no issues. However, if the partition size is not small, when the split operation occurs, the local indexes on these tables gets unusable.
I have debugged FBDA process with the command below.
SQL> ALTER SYSTEM SET "_FBDA_DEBUG_MODE"=4481 scope=both sid='*';It has been observed that split operations occur consistently at the same time period every day and this is peak hour and that is not good also. I have requested more information about it.
SQL> SELECT COUNT (*) AS AMOUNT,
TO_CHAR (CREATED, 'HH24:MI') AS SPLITTIME
FROM dba_objects
WHERE object_type = 'TABLE_PARTITION'
AND object_name LIKE 'SYS_FBA_HIST_%'
AND SUBOBJECT_NAME <> 'HIGH_PART'
AND CREATED > SYSDATE - 10
GROUP BY TO_CHAR (CREATED, 'HH24:MI')
ORDER BY 1 DESC;
AMOUNT SPLITTIME
----- ---------
95 13:43
48 13:44To investigate what’s happening behind the scenes, I enabled 10046 event tracing on all instances for the FBDA process at 13:40.
SYS@XXXX> oradebug setorapname FBDA
SYS@XXXX> oredebug tracefile_name
/u01/app/oracle/diag/rdbms/XXXX/XXXX1/trace/XXXX1_fbda_61972.trc
SYS|XXXX> oradebug event 10046 trace name context forever, level 12;The situation takes a serious turn here. The section provided below is extracted from the trace file of FBDA.
[oracle@bltdb1 trace]$ vi /u01/app/oracle/diag/rdbms/XXXX/XXXX1/trace/XXXX1_fbda_61972.trc
...
PARSING IN CURSOR #140274087025200 len=142 dep=1 uid=0 oct=1 lid=0 tim=14605835509601 hv=4096064267 ad='45512069e0' sqlid='1t5g44bu29ysb'
alter table "TEST_SCHEMA".SYS_FBA_HIST_2054816 split partition high_part at (812444320056) into (partition part_812444320056, partition high_part)
END OF STMT
...
As you can see above, there is no UPDATE INDEXES clause in the split operation command. It should be written as shown below, or an index rebuild operation should be performed. However, neither of these actions is being taken.
SQL> alter table "TEST_SCHEMA".SYS_FBA_HIST_2054816 split partition high_part at (812444320056) into (partition part_812444320056, partition high_part) UPDATE INDEXES;You may also observe the same behavior with the test case I provided below.
CREATE TABLE SYSTEM.TEST_TBL_SYS_FBA_HIST_83244
(
RID VARCHAR2 (4000 BYTE),
STARTSCN NUMBER,
ENDSCN NUMBER,
XID RAW (8),
OPERATION VARCHAR2 (1 BYTE),
COL01 NUMBER (15),
COL02 NUMBER (15),
COL03 VARCHAR2 (15 BYTE),
COL04 NUMBER (15),
COL05 VARCHAR2 (15 BYTE),
COL06 NUMBER (15),
COL07 NUMBER (15),
COL08 NUMBER (15),
COL09 NUMBER (15),
COL10 NUMBER (15),
COL11 NUMBER (15),
COL12 DATE,
COL13 DATE,
COL14 NUMBER (13, 3),
COL15 VARCHAR2 (1 BYTE)
)
COMPRESS FOR OLTP
TABLESPACE USERS
PARTITION BY RANGE (ENDSCN)
(
PARTITION HIGH_PART VALUES LESS THAN (MAXVALUE)
COMPRESS FOR OLTP
TABLESPACE USERS
);
-- We imported sample data from our environment.
INSERT INTO SYSTEM.TEST_TBL_SYS_FBA_HIST_83244 (RID,
STARTSCN,
ENDSCN,
XID,
OPERATION,
COL01,
COL02,
COL03,
COL04,
COL05,
COL06,
COL07,
COL08,
COL09,
COL10,
COL11,
COL12,
COL13,
COL14,
COL15)
SELECT RID,
STARTSCN,
ENDSCN,
XID,
OPERATION,
'123456789',
'12345',
'01234567890',
'12345',
'1234567890',
'12345678',
'',
'12345678',
'123456',
'12345678',
'1',
TRUNC (SYSDATE),
TRUNC (SYSDATE),
1,
'1'
FROM XXX.SYS_FBA_HIST_83244 WHERE ROWNUM<300000;
COMMIT;
-- Index is created with default degree as Oracle FBAR does, it should be created with noparallel option.
CREATE INDEX SYSTEM.TEST_SYS_FBA_HIST_IDX1_83244 ON SYSTEM.TEST_TBL_SYS_FBA_HIST_83244
(ENDSCN, STARTSCN, RID)
TABLESPACE USERS
LOCAL (
PARTITION HIGH_PART
TABLESPACE USERS
)
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT ); ---> First Problem is here
-- Note down the median value of startscn.
SELECT (MIN(STARTSCN)+MAX(STARTSCN))/2 AS SPLIT_SCN_NUMBER FROM SYSTEM.TEST_TBL_SYS_FBA_HIST_83244;
SPLIT_SCN_NUMBER
----------------
812059386480
--Index is in usable state
select index_name, partition_name, status from dba_ind_partitions where index_name='TEST_SYS_FBA_HIST_IDX1_83244';
INDEX_NAME PARTITION_NAME STATUS
---------------------------- -------------- --------
TEST_SYS_FBA_HIST_IDX1_83244 HIGH_PART USABLE
--- High partition is splitted into two partitions.
alter table SYSTEM.TEST_TBL_SYS_FBA_HIST_83244 split partition high_part at (812059386480) into (partition part_812059386480, partition high_part);
-- Index is in unusable state now.
select index_name, partition_name, status from dba_ind_partitions where index_name='TEST_SYS_FBA_HIST_IDX1_83244';
INDEX_NAME PARTITION_NAME STATUS
---------------------------- -------------- --------
TEST_SYS_FBA_HIST_IDX1_83244 HIGH_PART UNUSABLE ------> Second Problem is here
TEST_SYS_FBA_HIST_IDX1_83244 PART_812059386480 UNUSABLE ------> Second Problem is hereI have provided the test case with my SR and will provide more information as soon as possible.
Update on 06.03.2025: Patch 36791812: FDA DDLS ARE OFFLINE LEADING TO UNUSABLE FDA INDEXES has been provided as a one-off patch to fix the issue.
Hope it helps.
Osman’s DBlog 
Leave your comment