Hello, it’s happened that my college instead of disassociating flashback archive disabled it ( so all archive was lost ) and I had to restore it, here is what I have done to fix it:

1) restore controlfile:

SET DBID xxxxxxxx

run {
    allocate channel t1 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxxx.xxxx.local,NB_ORA_CLIENT=xxxxx.00.xxxx.local)';
    restore controlfile from autobackup;
}

2) find out tablespaces that must be restored:

 select distinct tablespace_name from dba_segments where owner in ('SYS', 'SYSTEM'); 	

SYSAUX
UNDOTBS1
USERS
SYSTEM
UNDOTBS2

3) some fixes

SQL> alter database flashback off;
SQL> alter database disable  BLOCK CHANGE TRACKING; 

4) Generate restore list ( list of tablespaces with flashback archives )

select listagg (name,',') WITHIN GROUP (order by name) from v$tablespace where name  like '%ARCH%' or name  in ('SYSAUX','UNDOTBS1','USERS','SYSTEM','UNDOTBS2','SLOW_FRA');

xxxx_ARCH,xxxx_ARCH_02,xxxx_ARCH_03,xxxx_ARCH_04_BIG,xxxx_ARCH_05,xxxx_ARCH_EMERGENCY,SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS

5)Generate skip list

select listagg (name,',') WITHIN GROUP (order by name) from v$tablespace where name not like 'PRE_DM_%' and name not in (
'xxxx_ARCH','xxxx_ARCH_02','xxxx_ARCH_03','xxxx_ARCH_04_BIG','xxxx_ARCH_05','xxxx_ARCH_EMERGENCY','SYSAUX','SYSTEM','UNDOTBS1','UNDOTBS2','USERS');

AUD_EVT_01,AUD_EVT_02,AUD_EVT_03,AUD_EVT_04,AUD_EVT_05,AUD_EVT_06,EGS_DB,EGS_SIA_DB,xxxx,xxxx_ADM,xxxx_CPP,xxxx_PDS,xxxx_SUPPORT,xxxx_TECH,xxxx_TEMP,SIA_DB,SIA_TEMP,SLOW_DATA,SLOW_FDA,TEMP

6) rman script look that way

run {
 	allocate channel t1 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxxx.xxx.local,NB_ORA_CLIENT=xxxxxx.xx.local)';
    allocate channel t2 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxxx.xx.local)';
    allocate channel t3 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxxx.xx.local)';
    allocate channel t4 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxx.xx.local)';

set until scn 1142308665621;
set newname for datafile 39 to '+DATA';
restore tablespace xxxx_ARCH,xxxx_ARCH_02,xxxx_ARCH_03,xxxx_ARCH_04_BIG,xxxx_ARCH_05,xxxx_ARCH_EMERGENCY,SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS,SLOW_FDA;
switch datafile all;
recover database skip forever tablespace  AUD_EVT_01,AUD_EVT_02,AUD_EVT_03,AUD_EVT_04,AUD_EVT_05,AUD_EVT_06,xxx_DB,xxx_xxxxx_DB,xxxx,xxxx_ADM,xxxx_CPP,xxxx_PDS,xxxx_SUPPORT,xxxx_TECH,xxxx_TEMP,xxx_DB,xxx_TEMP,SLOW_DATA;
}

7) alter database backup controlfile to trace as ‘/home/oracle/control.ctl’;
then I’ve removed all non restored datafiles and got this script:

ALTER TABLESPACE TEMP ADD TEMPFILE ;
ALTER TABLESPACE SIA_TEMP ADD TEMPFILE ;
ALTER TABLESPACE xxxx_TEMP ADD TEMPFILE ;

8) after that database is opened and query work like a charm:

create table xxxx.reg_ctx_flashback tablespace xxxx_fbda parallel (degree 4) unrecoverable as select * from xxxx.SYS_FBA_HIST_112683;

SQL> select /*+ parallel 4*/ count (*) from xxxx.SYS_FBA_HIST_112683;

  COUNT(*)
----------
  84774720

9) CTAS all flashback data and export it, then move to target host:

  SQL> create table xxxx.reg_ctx_flashback tablespace xxxx parallel (degree 4) unrecoverable as select * from xxxx.SYS_FBA_HIST_112683;

   expdp \'/ as sysdba\' directory=dump dumpfile=reg_cxt_fbda.dmp logfile=DROP_ME:export.log tables=xxxx.reg_ctx_flashback;

10) restore flashback

alter table xxxx.reg_cxt FLASHBACK ARCHIVE xxxx_ARCH_FL;

BEGIN
  DBMS_FLASHBACK_ARCHIVE.import_history (
    owner_name1       => 'xxxx',
    table_name1       => 'REG_CXT', 
    temp_history_name => 'REG_CTX_FLASHBACK', 
    options           => DBMS_FLASHBACK_ARCHIVE.NODELETE); END;
/