Loading Historical Data Into Flashback Archive Enabled Tables

blank

Oracle provides via OTN an import solution for FBA (Flashback Data Archive also known as Total Recall). The solution extends the SCN to TIMESTAMP mapping plus provides a wrapper to existing APIs to populate the history. However, issues like using a customized mapping period/precision or ORA-1466 when using the AS OF TIMESTAMP clause are not addressed. I show in this post how to load historical data into flashback archive enabled tables using the standard API. Unfortunately there are still some experimental actions necessary to get a fully functional result, at least with version 11.2.0.3.4.

Test Scenario

There are various reasons why you may want to load historical data into a FBA enable table. E.g. for testing purposes, to move FBA enabled tables from one database instance to another or to migrate conventionally historized tables. This example is based on a migration scenario. Table T1 has the following content and shall be migrated to FBA. You find the script to create and populate the table T1 here.

The column VID is the version identifier and the primary key. OID is the object identifier, which is unique at every point in time. CREATED_AT and OUTDATED_AT define the interval boundaries. Column C1 and C2 are the payload columns, which may change over time.

The following queries return data valid at 2012-12-23 19:36:08 and now.

It’s important to notice that OID 4 (see highlighted line above) is not part of the first query result, since OUTDATED_AT has an excluding semantic. I mention this fact because the column ENDSCN in the table SYS_FBA_HIST_<object_id> uses also an excluding semantic, which simplifies the migration process, at least in this area.

From UTC to SCN

Oracle uses its own time standard SCN for FBA. The SCN is initialized during database creation and is valid for a single Oracle instance, even if synchronization mechanisms among database instances exist (see also MOS note 1376995.1). A SCN may not represent a date-time value before 1988-01-01 00:00:00. The time spent between two SCNs is varying, it may be shorter when the database instance is executing a lot of transactions and it may be longer in more idle times or when the database instance is shut down. Oracle uses the table SYS.SMON_SCN_TIME to map SCN to TIMESTAMPs and vice versa and provides the functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN for that purpose.

So what is the first date-time value which may be converted into a SCN?

The first value is 2010-09-06 00:40:05. You may notice the two hour difference to MIN_TIME_DP. Oracle stores the date values in this table in UTC (DBTIMEZONE) and my database server’s time zone is CET (Central European Time) which is UTC+01:00 at the point of query (see SESSIONTIMEZONE). However, in September daylight saving time was active and back then CET was UTC+02:00. That explains the two hour difference.

Let’s test the boundary values.

As expected 2010-09-06 00:40:05 works and 2010-09-06 00:40:04 raises an ORA-8180 error.

I expect that you get complete different values in your database, since the SMON process deletes “old” values in SYS.SMON_SCN_TIME based on UNDO and FBA configuration. Since this table is solely used for timestamp to SCN conversion I assume it is save to extend it manually. In fact the PL/SQL package DBMS_FDA_MAPPINGS provided by Oracle is exactly doing that, but distributes the remaining SCNs between 1988-01-01 and the MIN(time_dp) in SYS.SMON_SCN_TIME uniformly. In my case there are only 18868 SCNs remaining to be assigned to timestamps before 2010-09-06 00:40:05. So if I know that I won’t need timestamps to be mapped to SCN let’s say before 2010-01-01 I may use the remaining values to improve the precision of timestamp to SCN mappings for this reduced period.

Im my case I do not need to extend the mapping in SYS.SMON_SCN_TIME, but here is an example how it can be done:

TIME_MP is the number of seconds since 1970-01-01. TIME_DP is the date value of TIME_MP. The calculation of these columns includes time zone conversion from CET to UTC which makes it a bit verbose. SCN_WRAP counts the number of times SCN_BASE has reached its 32-bit value maximum of 4294967295.

Alternatively you may simply extend the SYS.SMON_SCN_TIME based on SYS.V$LOG_HISTORY.

BTW: The TIMESTAMP_TO_SCN function uses an own kind of result cache. You may need to restart the database if you undo changes in SYS.SMON_SCN_TIME.

Next, we should check if the mapping from timestamp to SCN is precise enough, means a SCN should not be used by multiple timestamps. Here’s the query for T1:

T1 is ready to be migrated to a FBA enabled table if CNT_TS and CNT_TS_TO_SCN are equal. If the values are different you have basically two options. a) amend T1 (e.g. change timestamps or merge intervals) or b) amend SYS.SMON_SCN_TIME as explained above.

Migration

The following script creates a flashback archive and the FBA enabled table T2. At the end a small PL/SQL block is executed to create views for the 3 SYS_FBA_…_<object_id> tables.

Flashback Query (since Oracle9i) and Flashback Data Archive (since Oracle 11g) are tightly coupled. The SYS_FBA_…<object_id> tables are created delayed by the FBDA background process. DML and querying table T2 is possible anyway with the help of UNDO and Flashback Query. The highlighted lines show the PL/SQL block to enforce the creation of the SYS_FBA…<object_id> tables. This step is necessary to create the views T2_DDL_COLMAP, T2_HIST and T2_TCRV. These views simplify the access to the underlying tables in my SQL scripts.

The next script copies data from table T1 to T2. Basically that’s what the PL/SQL package DBMS_FDA_IMPORT provided by Oracle does.

The following queries return data valid at 2012-12-23 19:36:08 and now (as for T1 above).

As you see the results are identical with the ones of T1. The script here compares the result for every single point in time in T1 with T2. I’ve run it without detecting differences.

Migration Issue 1 – ORA-1466 When Using AS OF TIMESTAMP Clause

In the examples above I avoided the use of the AS OF TIMESTAMP clause and used the AS OF SCN clause instead. The reason becomes apparent when executing the following query:

Ok, we have not changed the table definition, but was the table definition for T2 valid on 2012-12-23 19:36:08?

Since I’ve created the table on 2013-01-03 00:17:42 Oracle assumes that no columns exist before this point in time. However, the AS OF SCN clause is not that picky.

To fix the problem we need to update T2_DDL_COLMAP but unfortunately DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA does not allow us to change the content of the DDL_COLMAP directly (it is possible indirectly by altering the HIST table, but this is not helpful in this case).

I’ve written a PL/SQL package TVD_FBA_HELPER which updates SYS.TAB$ behind the scenes to overcome this restriction. Please consult Oracle Support how to proceed if you plan to use it in productive environments. The package is provided as is and of course you are using it at your own risk.

Here is the script to fix validity of the DDL_COLMAP:

Now the AS OF TIMESTAMP clause works as well:

Migration Issue 2 – Different Number of Intervals

If you query the full history of T2 you get 10 rows, but T1 contains 8 rows only.

The highlighted rows for OID 1 and 2 could be merged. The content is not really wrong it’s just different to T1. The reason is, that DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA does not allow us to modify T2_TCRV (SYS_FBA_TCRV…<object_id> table). This table contains validity information for the current rows in T2.

That’s why I had to inserted the actual rows also in T2_HIST.

But with the help of the PL/SQL package TVD_FBA_HELPER the data may be fixed as follows:

Now the query returns 8 rows:

The timestamps are slightly different to the ones in T1, but that’s expected behavior since the precision of TIMESTAMP_TO_SCN conversion is limited to around 3 seconds.

Conclusion

Loading historical data into FBA enable tables requires a strategy to populate historical mappings in SYS.SMON_SCN_TIME. Afterwards you may load the associated SYS_FBA_HIST_<object_id> table with the help of the Oracle supplied PL/SQL package DBMS_FLASHBACK_ARCHIVE and its procedures DISASSOCIATE_FBA and REASSOCIATE_FBA. I recommend this approach for 11gR2 Database environments.

The solutions to fix migration issue 1 (ORA-1466 when using AS OF TIMESTAMP clause) and migration issue 2 (different number of intervals) are considered experimental. I suggest to contact Oracle Support to discuss how to proceed if you need a solution in this area.

Updated on 2014-04-10, changed calculation of SCN_WRP and SCN_BAS in Extend SMON_SCN_TIME (1) and Extend SMON_SCN_TIME (2), changed link to new version of TVD_FBA_HELPER.

7 Comments

  1. blank Mike Tefft says:

    Can you please provide details on your opening statement: Oracle Support provides on request an import solution for FBA. You mention DBMS_FDA_MAPPINGS from Oracle Support but that only addresses a small piece of the problem.

    I had several SRs open over the past year and a half trying to get Oracle to answer whether this could be done, with a final conclusion that the answer was ‘no’, short of a full-instance restore.

    There is an OTN ‘sample code’ offering, that includes DBMS_FDA_MAPPINGS as well as a DBMS_FDA_IMPORT package. Is that what Oracle Support is providing?

    Thanks,
    Mike

    • blank Philipp says:

      Hi Mike,

      We’ve got a file named fbda_import.tar.gz directly from an Oracle representative for testing purposes at a customer site. Beside the different file name, the content is identical to fda_import.tar.gz which is available on OTN. My statement that Oracle Support provides this code is basically wrong. Therefore I’ve changed the post accordingly. Thanks for the hint.

      The PL/SQL package DBMS_FDA_IMPORT uses standard API calls, such as querying the view USER_FLASHBACK_ARCHIVE_TABLES, calling dbms_flashback_archive.disassociate_fba and dbms_flashback_archive.reassociate_fba and executing a dynamically generated INSERT statement (INSERT INTO SYS_FBA_HIST_ SELECT * FROM TEMP_HISTORY). That’s fine to be used in every environment, even in production. But the other PL/SQL package DBMS_FDA_MAPPINGS inserts rows into SMON_SCN_TIME, and since manipulated SYS tables directly via SQL is typically something which should be reserved to Oracle code, you should get an approval by Oracle Support to avoid arguments later on. I strongly suggest to consider alternative strategies to populate SYS.SMON_SCN_TIME in a generic way (e.g. maintaining a 3 seconds interval as long as possible which suits a wide range of scenarios well). Present your solution approach in a SR and ask Oracle Support how to proceed.

      Regards
      Philipp

  2. blank Mike Tefft says:

    Thank you very much for writing this article, by the way.

    I understand your cautions on SCNs. For the moment I am trying to copy history from one schema/table to another, in the same database. Since I am working with data in an existing flashback archive, I presume the SCN history goes back that far.

    How were you able to discover what DBMS_FDA_IMPORT does? (the code is wrapped) Did you trace it?

    How did you know how to populate the SYS_xxx_HIST table? Is it documented anywhere? Oracle provides examples for deleting but not for inserting (and I can see why…)

    I seem to be blocked by the facts that the HIST table does not have information about the current rows, and the TCRV table is offically not available for manipulation. I see that you have added rows to the history (in listing ‘Migration’, second part of the UNION ALL) but it is not clear why that would work.

     

     

    • blank Philipp says:

      >How were you able to discover what DBMS_FDA_IMPORT does?
      >(the code is wrapped) Did you trace it?

      I’ve unwrapped the code. Niels Teusink posted a handy unwrap phyton script on http://blog.teusink.net/2010/04/unwrapping-oracle-plsql-with-unwrappy.html.

      >How did you know how to populate the SYS_xxx_HIST table?

      There are some documentations, presentations and articles available. In fact the fda_import.tar.gz contains also a file named sample_import_session with some insert statements. And of course some testing was necessary too.

      >the HIST table does not have information about the current rows,

      Correct.

      >and the TCRV table is offically not available for manipulation

      Correct too.

      >I see that you have added rows to the history (in listing ‘Migration’,
      >second part of the UNION ALL) but it is not clear why that would work.

      Let’s look at record VID 4 in the conventionally historized table T1. This record has been created at 2012-12-27 11:40:41 and is currently valid. Table T2 is FBA-enabled. When I insert this record into T2 it will be valid from the time of insert, but not before. Therefore I have to create a history record from 2012-12-27 11:40.41 until the time of insert into T2. That’s what I’m doing in line 29-42 of the Migration listing. Inserting a historical record into the HIST table (having the same values as the current row).

      A query such as

      produces an execution plan like

      You see the result is a UNION-ALL of the HIST table and the join between the T2 and the TCRV table. For an “AS OF SCN” query a result row comes either from the HIST or from the T2 table, otherwise it’s a bug. In this case the result is taken from the HIST table. It’s just irrelevant that all columns have the same column values as the current record.

      Based on the script provided for T1 and the listings you should be able to reproduce this example in your environment.

      HTH
      Philipp

  3. blank Mike Tefft says:

    Ah, now I see. You had FBDA enabled on your table when you did your initial population.

    In my attempt, I did things in this order:
    – Create the target table without FBDA
    – Populate the target table with ‘current’ data
    – Enable FBDA for the target table
    – Populate the HIST table

    We are looking for the ability to clone a schema, such as from production to test, with the FBDA history in place. In one of our sample schemas, there is 1 FBDA-enabled table with over 100 million rows, and 5 others with over 10 million. We have already found that it is not practical to have FBDA enabled when we do these large initial loads (the FBDA background process backs up for days), and we really only need it to track changes after the initial population.

    So, following this technique, we would have to:
    – Have FBDA enabled during our initial ‘import’ into the target environment. (And I doubt that we would be able to use DataPump for this stage, either – I’ll have to try that.) This is already not workable.
    – Add the entire contents of the current table to the HIST table. This would practically double our storage requirements.

    Conclusion: We need to get at that TCRV table.

  4. blank PraSad Tammana says:

    We need to move tables that have FDA enabled form tablespace A to tablespace B.

    I have disaccociate and reassociated the table. But when we qury the fda data using as of timestamp, they are returning wrong results. Oracle claims that since rowed is changed with table move, it does not support, as FDA data uses row id to track.

    I have updated row id in FDA tables but even still I got wrong results.

    is there a way t fix the issues?

    • Beside handling ROWID correctly, the simplest way is to copy the content of the HIST, TCRV and DDL_COLMAP tables. For TCRV and DDL_COLMAP this is not supported by Oracle. You need TVD_FBA_HELPER (or something similar) to enable/disable DML on these tables. I suggest to discuss this further with Oracle support, because there is currently no supported way to do what you want.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.