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.
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
- 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 220.127.116.11.0 - Production on Thu Apr 7 11:18:59 2022 Version 18.104.22.168.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 22.214.171.124.0 - Production Version 126.96.36.199.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 188.8.131.52.0 - Production Version 184.108.40.206.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.
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 220.127.116.11 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