3 Replies Latest reply on Sep 6, 2013 3:15 PM by Ben Speckhard-Oracle

    Best way to recover from incorrect migration


      Let me start out by saying that we know we did things incorrectly, and that "doing it over again the right way" isn't the advice we're looking for in this discussion.


      Here's the situation:

      • Existing database instance (A) is Oracle 10g on AIX.
      • New database instance (B) is Oracle 11g on Solaris.
      • We exported our schema from A and imported it into B.


      Unfortunately, about 12 tables in the schema were versioned using Workspace Manager in the 10g instance, but no data from WMSYS was exported.

      So we have a situation where we have MyTable_LT tables containing rows, but the MyTable views are reporting 0 rows. User_WM_Versioned_Tables reports 0 rows.


      It would be perfectly OK with us if we could "unversion" the tables in the new instance, and start again.

      Is there a way to determine which rows in the _LT tables are belonging to the LIVE workspace?

      Alternatively, is there a way to export/import the metadata from WMSYS in instance A into instance B?


      Thanks in advance for your advice.

        • 1. Re: Best way to recover from incorrect migration
          Ben Speckhard-Oracle



          Since you do not seem to about the history or any child workspaces, then one option is to drop the current tables on the new database.  Then use dbms_wm.Export from the original source db and export the tables using the LIVE workspace and the versioned_db parameter set to false.  Import the tables using dbms_wm.Import, and then version enable them.


          If this suggestion isn't feasible for some reason, you can find the version in the LIVE workspace on the original source db by querying the all_version_hview view.  Keep in mind that this view might be showing different results compared to the time when the export was done.  I would need to know alot more about your usage as to whether that would be the case, and if it would even make a difference in this instance.  After you determine the versions, they can then be used to match up with version column in the _HIST view or the _LT table.  You need to be sure to only keep the most recent change for a particular primary key, if multiple ones exist in the LIVE workspace.  At this point, you can version enable the table again.


          There is no way to only import the WMSYS data for a single schema.




          1 person found this helpful
          • 2. Re: Best way to recover from incorrect migration

            Ben -


            Thank you - that is very helpful. Your first solution looked like it would be best.


            Yesterday I looked at obtaining the LIVE version data from the source DB instance, but the drift in data content since the time the original export was done meant that the data refresh would have been fraught with risk, just as you identified.


            I convinced the powers-that-be that a do-over of the full export/import was the best way to go, however, this time we first un-versioned the tables in the source DB.


            I intend to study the documentation for how best to perform this process if we *did* want to retain all the workspaces and versioned data, but can I beg your indulgence and ask you what you would recommend?


            Assuming a clean install of Oracle 11g (complete with WMSYS) as the destination, would it be appropriate to:

            • drop the WMSYS schema in the destination;
            • export the MY_SCHEMA and WMSYS from the source;
            • import into the destination
            • upgrade the WMSYS in the destination from 10g to 11g


            Is this the basic process, or am I missing something important?


            - Colin

            • 3. Re: Best way to recover from incorrect migration
              Ben Speckhard-Oracle

              Hi Colin,


              On 11g, we only support full database import/export using the same version of Workspace Manager on both the source and target databases.  Since your upgrade is from 10g to 11g, this by default is not supported.  In the past, we have recommended to uninstall the current version of Workspace Manager from the target, install the same version that is on the source.  This will allow the import to succeed, after which you can upgrade Workspace Manager back to its original version.


              The restriction with having to have the same version on both the source and target has been relaxed as of  We support imports into using a dumpfile created with at least version