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 18.104.22.168.0 - Production on Thu Apr 7 11:18:59 2022 Version 22.214.171.124.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 126.96.36.199.0 - Production Version 188.8.131.52.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 184.108.40.206.0 - Production Version 220.127.116.11.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 but supposed to be fixed in the next database 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 18.104.22.168 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.
And there will be a follow up blog post about exporting and importing an FDA. I promise 🙂
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 – I know this isn’t associated with the topic in your entry but I wanted to ensure this question had the best chance to be answered.
We are, largely, on 19c; 3-node clusters and patch 2x a year (best we could negotiate). Our biggest hurdle is datapatch – it requires an outage. Can you link a doc that explains how we can avoid a business outage while we patch? Currently we take 3-4 hours of downtime to patch and want to better serve the business. How can we patch, with datapatch and keep the applications up?
I will have blog posts about all aspects in mid May 22 on the blog.
Hi Mike, we are in the process of upgrading a 12c database to 19c with FDA enabled. During the upgrade (using DBUA) the timezone upgrade fails (from v26 to v32) with the error ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table xxxx. The mentioned tables do have columns like timestamp(6) with time zone. I opened a ticket with Oracle Support but we are getting nowhere.
When i look at upgrading the timezone, i see messages like this: Applying a change to the database time zone file not only affects the way new data is handled, but potentially alters data stored in TIMESTAMP WITH TIME ZONE columns.
Using FDA, you are unable to do anything with the tables associated with it, and when looking at it, it makes sense because retrieving the historical data will be almost impossible because when altering tables it will be in different shapes (lets say renaming a column or worse, deleting one).
It seems like upgrading the timezone on databases with FDA enabled on tables with columns that use timestamp with timezone is impossible. Any idea on this?
And if I ignore this error and use an “old” timezone, do we get problems in the future?
I can ignore this ugprade timezone error, and database upgrade finishes.
can you please share the SR with me. I am really curious about this issue.
sure. It’s SR 3-29794483171 : During 19c upgrade timezone upgrade fails because of ORA-55622 errors
No updates on the SR as of now. I can’t believe we are the only company that uses Flashback archive on tables with timestamp columns with timezones and upgrade to 19c. I checked the changes in the updates on the timezone upgrade, and it seems we are lucky that none of those changes are relevant for the timezones we use.
I alerted the PM for FBDA as well weeks ago – and now I added your SR. I hope to get some progress soon. But the PM was on vacation the past weeks – and should be back the following week as far as I know. He raised it already with the development team.
after contact with Oracle Support through the SR, i got a document to see if that makes sense. So i tried some things out of it (upgrade timezone without data as a first) and all seemed to work out. It felt strange to me while it is the same package i’m using. So after 2 succesful timezone upgrades on the next 2 databases i tried to use the “normal” methode (cdb/pbd in upgrade mode, open dst upgrade window, run dbms_dst.upgrade_database with the default parameters) and it worked. After overcoming the first shock, it got me thinking. The big difference is that now I’m doing this from a PDB, while DBUA upgraded a 12c single instance to a 19c single instance. The upgrade of the timezone did fail here. For these databases i did uncheck Timezone upgrade in DBUA and after the upgrade i made a PDB from this SI. The CDB was a new created database in 19c, and already had the “new” version, 32, while PDB had 26. The only thing i can think of is that this must have been a single instance error, while it now is gone. Nothing else has changed on this DB (so no patching on the dbhome or something in the meantime). Just wanted to let you know the problem seems gone when upgrading the timezone in a PDB 🙂
thanks for this article. It seems that it is not possible to use this feature in CDB environment as official documentation for 21c also states: You can use this migration package only on a non-CDB environment. (https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/dbms_flashback_archive_migrate.html)
23c is the keyword here, Peter.
As I wrote:
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 but supposed to be fixed in the next database release. ”
this refers to the next LTS, which supposingly is 23c. You won’t play with 21c as it is an innovation release only.