Dear Tom,

Oracle 19.22 / ibm aix 7.2

We have a set of 7 tables on which flashback data archive was enabled for almost more than 7 years and was running without any issues. The databases were upgraded from 11g to 19c a couple of years back and post the upgrade as well everything was running fine until very recently. A couple of days back, adding to the coincidence of this error, a failover and failback of the database had happened. Post this, the application teams started getting ORA-55616 errors. We did raise a Oracle SR but so far no positive solutions have come.

While debugging the issue we found that the 1st time an insert is run in a session, the insert runs through fine without any issues. However, the 2nd time an insert is run, ORA-55616 error occurs. We were able to consistently reproduce this error.

Example - This is what we did to test but the table had a lot more columns with different data types.

SQL> insert into t1 values(1,2,3);

1 row inserted.

SQL> rollback;

rollback complete.

SQL> insert into t1 values(1,2,3);

ERROR at line 1:
ORA-55616: Transaction table needs Flashback Archiver processing

SQL> conn / as sysdba
connected.
SQL> alter system set container=pdb1;

Session altered.

SQL> insert into t1 values(1,2,3);

1 row inserted.

SQL> rollback;

rollback complete.

SQL> insert into t1 values(1,2,3);

ERROR at line 1:
ORA-55616: Transaction table needs Flashback Archiver processing


Due to this reason, we decided that we will disable flashback archive on those set of tables and try the operations. The operations worked fine without any issues.

Post this it was decided to check if we can re-enable the flashback archive and see if it works. When we tried to re-enable it, we ran into the ORA-55624 error. For none of the 7 tables, we were able to enable the flashback archive.

SQL> alter table t1 flashback archive;
alter table t1 flashback archive
*
Error at line 1:
ORA-55624: The table t1 cannot be enabled for Flashback Archive at this point

Neither the oracle documentation nor Oracle support has extensive documentation pertaining to these errors. Would you be able to help us out understand why we are running into this error and if there is anything to understand the workflow of the internals which is resulting in this error.

Thanks,
Amarnath

alter system set "_fbda_debug_mode" = 1
alter system set events '55616 trace name errorstack, level 3'


spool /tmp/fbda.txt
set linesize 150 pagesize 400
show pdbs
show con_name
select * from sys.sys_fba_barrierscn; 
alter session set container=...
show con_name
 
column owner_name format a20
column table_name format a20
column flashback_archive_name format a22
column archive_table_name format a20
select owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
from   dba_flashback_archive_tables
where owner_name <> 'SYS'
order by owner_name, table_name;
 
select segment_name, partition_name, bytes/1025/1025/1025 size_gb 
from dba_segments 
where segment_name like 'SYS_FBA_HIST%' order by size_gb desc;
 
col owner format a20
col table_name format a25
col tablespace_name format a20
select owner, table_name, tablespace_name
from dba_tables
where table_name like 'SYS_FBA%'
and owner <> 'SYS';
 
select owner_name,
      flashback_archive_name,
      flashback_archive#,
      retention_in_days,
      to_char(create_time, 'dd-mon-yyyy hh24:mi:ss') as create_time,
      to_char(last_purge_time, 'dd-mon-yyyy hh24:mi:ss') as last_purge_time,
      status
from   dba_flashback_archive
order by owner_name, flashback_archive_name;
 
select * from sys.sys_fba_barrierscn; 

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from dba_flashback_archive;

column scn format 9999999999999999999999
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select time_dp, scn, scn_bas, scn_wrp from smon_scn_time
 where scn = (select min(scn) from smon_scn_time);
 
column scn format 9999999999999999999999
select * from sys_fba_fa order by fa#;

column DROPSCN format 9999999999999999999999
select * from sys_fba_trackedtables order by  FA#, OBJ#, DROPSCN asc;

spool off