Migrating Flashback Data Archive (FDA) tables

This morning I received an email question from a customer asking whether there is support for the Migrating Flashback Data Archive (FDA) tables in Oracle 19c. And since this is not the first time I received this question, I thought it may be good to have a blog post about it.

Photo by Anita Jankovic on Unsplash

What is Flashback Data Archive (FDA)?

Well, google it. Actually I was looking for a link in our documentation but to my surprise I found the usual suspects such as Tim Hall’s excellent oracle-base page – but not much in our own doc set. I then realized: The feature has been relabeled to Flashback Time Travel. Smart move. Now you need to use the term < “flashback time travel” oracle > in your favorite search engine.

But anyhow, if I remember correctly, the feature got introduced back in the Oracle 11g days and was called Total Recall. So in fact, this is now the third name change of the feature. Allow me to call it still FDA. It’s purpose is and was to allow you the transactional versioning of records in the database for an extended period of time. The idea of the feature is that you’ll be able to see the changes to records in FDA protected tables over weeks, months or years, depending how you’ve defined it.

This part of the 19c documentation tells you more about it, how to create and maintain a Flashback Data Archive. Ups … Flashback Time Travel.

How do you migrate your FDA?

What the documentation doesn’t tell you, but what customers using the FDA quickly found out is the fact that you couldn’t migrate the FDA from one database to another. When you do an upgrade, all is fine. But as soon as you tackle a database with expdp/impdp or Transportable Tablespaces to migrate it to another platform, your may realize that your FDA didn’t get migrated.

This can be a real problem. In the above case, the customer just realized this by accident. But I have received comments also from customer who were totally stuck since they’ve had to move away from a Big Endianness platform and wanted to carry their FDA with them certainly.

With Oracle Database 21c there is a relief. It introduces a migration routine. See the New Features Guide for Migration Flashback Data Archive-enabled Tables. I’m quoting this part here since it contains some very important information:

A new PL/SQL package called DBMS_FLASHBACK_ARCHIVE_MIGRATE enables the migration of Flashback Data Archive-enabled tables from a database on any release (in which the package exists) to any database on any release (that supports Flashback Data Archive).

Using the DBMS_FLASHBACK_ARCHIVE_MIGRATE PL/SQL package users can export and import the Flashback Archive base tables, along with their history, to another database via the Oracle Transportable Tablespaces capability. Compression is preserved when History Tables enabled with the Advanced Compression Optimization for Flashback Data Archive History Tables capability are migrated.

That is interesting.

So let’s have a closer look at the package DBMS_FLASHBACK_ARCHIVE_MIGRATE in the PL/SQL Guide.

You must create the package at first

When I wanted to play with the package at first, I didn’t read the documentation carefully from start and simply missed the package. But when you check, you’ll see:

The DBMS_FLASHBACK_ARCHIVE_MIGRATE package must be compiled on both the source and target databases as SYS. The source file is located at ?/rdbms/admin/crefbamig.sql, using which the package can be created or compiled.

I added the bold marking above.

Let me create it in my 21c database at first:

SQL> start ?/rdbms/admin/crefbamig.sql

Session altered.

Package created.

No errors.

Package body created.

No errors.

Session altered.

This was fast.

Can you create the package in a 19c database?

But can I create the package in my 19c database as well? The prerequisites section in the documentation says:

• Database version >= 11.2

And further, it tells me If database version is 11.2, following conditions should be met:

• shared_pool_size >= 500M
  streams_pool_size >= 40M

or

• sga_target >= 2G

Well, this should apply to almost all of the databases where you’d like to test and use it I guess.

So let me start with my first attempt – failing of course:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 7 11:18:59 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> start /u01/app/oracle/product/21/rdbms/admin/crefbamig.sql

Session altered.

SP2-0310: unable to open file "/u01/app/oracle/product/19/rdbms/admin/prvtfdams.plb"
SP2-0310: unable to open file "/u01/app/oracle/product/19/rdbms/admin/prvtfdamb.plb"

Session altered.

Hm. I should have looked at crefbamig.sql at first.

Rem    NOTES
Rem      Need to be run as SYS
Rem      If the package is needed in the CDB/PDB environment, run it under
Rem      the required CDB/PDB as SYS

What does this mean? Do I need to run it in each PDB separately? I’d guess so. Or I kick it off with catcon.pl right away. But I have a separate paragraph on Multitenant and FDA below.

From the script you will find the bug name for this enhancement:

• BUG 30663290 – FLASHBACK_ARCHIVE_MIGRATE PACKAGE SCRIPTS ARE NOT AVAILABLE IN RDBMS/ADMIN DIR

The bug is non-public so you may not find much about it on MOS. But I think this doesn’t matter since it describes the evolution of the package which then has been documented in the official Oracle documentation.

These are the scripts being called by crefbamig.sql:

@@?/rdbms/admin/sqlsessstart.sql
@@?/rdbms/admin/prvtfdams.plb
@@?/rdbms/admin/prvtfdamb.plb
@?/rdbms/admin/sqlsessend.sql

So I quickly copy:

$ cp /u01/app/oracle/product/21/rdbms/admin/prvtfdams.plb $OH19/rdbms/admin

$ cp /u01/app/oracle/product/21/rdbms/admin/prvtfdamb.plb $OH19/rdbms/admin

to my 19c home and try it again.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> start /u01/app/oracle/product/21/rdbms/admin/crefbamig.sql

Session altered.

Package created.

No errors.

Package body created.

No errors.

Session altered.

Looks good at first sight.

Package routines

Now what is in this package and what can it do?

SQL> desc DBMS_FLASHBACK_ARCHIVE_MIGRATE
PROCEDURE EXPORT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA_OWNER			VARCHAR2		IN
 FDA_TABLES			VARCHAR2_TABLE		IN
 TTS				VARCHAR2		IN
 DATA_PUMP_DIR			VARCHAR2		IN
 IGNORE_ERRORS			BOOLEAN 		IN     DEFAULT
PROCEDURE EXPORT_ANALYZE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA_OWNER			VARCHAR2		IN
 FDA_TABLES			VARCHAR2_TABLE		IN
 TTS				VARCHAR2		IN
PROCEDURE IMPORT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA_OWNER			VARCHAR2		IN
 TTS				VARCHAR2		IN
 DATA_PUMP_DIR			VARCHAR2		IN
 TTS_DATA_FILES 		VARCHAR2_TABLE		IN

But still I struggle a bit with the documentation. Especially in this chapter the example I was desperately looking for does not match the routines I see being created in my database. Well, a second blog post follow up may be needed to check how this really works.

But at least I know now that the routines are used to trigger a TTS export/import of the metadata, and that my FDA must be isolated in one or more tablespaces.

Multitenant and FDA

One question I’ve had was right away:
Does this work with Multitenant?

The note in crefbamig.sql puzzled me a bit: If the package is needed in the CDB/PDB environment, run it under the required CDB/PDB as SYS

Does this mean I need to run it in CDB$ROOT and PDBs with an FDA? Or should I run it with catcon.pl which I usually use to execute scripts in Multitenant environments?

Of course I wanted to try this as well:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/oracle/logs -b crefbamig -d /u01/app/oracle/product/19/rdbms/admin crefbamig.sql

And this worked nicely:

SQL> select con_id, status, object_type from cdb_objects where object_name='DBMS_FLASHBACK_ARCHIVE_MIGRATE' order by con_id;

    CON_ID STATUS  OBJECT_TYPE
---------- ------- -----------------------
	 1 VALID   PACKAGE
	 1 VALID   PACKAGE BODY
	 2 VALID   PACKAGE
	 2 VALID   PACKAGE BODY
	 3 VALID   PACKAGE BODY
	 3 VALID   PACKAGE
	 5 VALID   PACKAGE BODY
	 5 VALID   PACKAGE

But … well, isn’t there always a “but” when you think too positively? Of course, I’m German. And the stereotype says, Germans are negative and see glasses mostly half-empty per se.

So here you go:

• BUG 31618290 – FDA MIGRATION EXPORT FAILS WITH ORA-31603 IN CDB ENV

FDA as feature was not available with Multitenant in Oracle 12.1. And since the above bug isn’t public, I can tell you that it isn’t fixed yet and [correction of previous version of this blog post – July 4, 2023] it may not be fixed in Oracle 23c, at least not in the initial release. That means, in case you have an FDA within your CDB$ROOT or a PDB, you may not be able to leverage the new procedure to export and transport the FDA to another database.

Final question: Upgrades?

If you did ask yourself: Does this affect me during upgrades as well? No, it doesn’t. When you upgrade, all stays valid and healthy. No need to export and transport your FDA. So this topic applies only to databases where you want to export the FDA. Most likely you may see this topic for Endianness migrations, for instance moving an 11.2.0.4 database from AIX to Linux with an FDA in the source database. In such cases, extra treatments are required.

In my customer’s case, since the source was on Intel Solaris, the best way would have been a plain upgrade. But expdp/impdp got used. And it just became clear afterwards that the FDA is missing now in the 19c database.

To me, this is the real pitfall you should be aware of in case you are using FDA.

Final question: Is FDA a feature you’ll have to license?

This is taken from the Oracle Database 19c License Guide – Chapter 1.3.

I planned to write up a follow-up blog post about export/import for FDA. But due to the current workload this won’t be coming soon. Meanwhile, please check with Oracle Support for possibilities to export/import your FDA for migrations.

Further Information and Links

• Oracle 19c – Dev Guide – Flashback Time Travel
• Oracle 21c – New Features – Migration Flashback Data Archive-enabled Tables
• Oracle 21c – Package DBMS_FLASHBACK_ARCHIVE_MIGRATE

–Mike