6 Replies Latest reply: Aug 26, 2014 2:06 AM by aschilling RSS

    After server migration old differences reappear

    aschilling

      Hi everyone,

       

      at our customers site there's currently a migration going on from an Oracle 10g RAC to a 11g RAC.

      Its all in testing mode right now so we can redo things as often as we want, but at some point of course we need a bullet-proof migration.

      We need to completely migrate the database, including the history and all workspaces.

      Afaik this should be possible using the full database export/import.

      However, we are facing several issues. The first ones are fixable, though they are strange. They are not really the scope of this question, but maybe someone has an idea though:

      We're having some triggers on version enabled tables that convert a value inserted in one column into another representation for another column of the table.

      For some odd reason after the migration has been done the table is in LWEV mode with a failed DROP on the respective procedure for one of those triggers (DROP failed due to too long name).

      We have no clue where that DROP is issued from at all. RecoverMigratingTable helps and we then can re-add the trigger.

      What we noticed however is that in the 11g version the trigger is not represented by an procedure anymore. Is that the reason why the upgrade procedure to 11g issues a drop?

       

      Now for the actual question: we have for one row some old workspace difference popping up.

      The difference already has been merged in the database state from which the new database is migrated. In the source (production) database issueing a SELECT on the _DIFF view for the respective table gives 0 rows. Issueing the same SELECT on the migrated database given 3 rows for one entry. Comparing things with the _HIST view entries, DiffBase and WS1 rows correctly reflect the current state of that entry, however the row for WS2 is some very old state from December 2011.

      If the user now edits that row in one of the workspaces WS1 or WS2 we suddenly receive 6 rows for that entry because the new difference is also determined.

       

      Is it known how something like that can happen? We definitely need to understand the reasons because else we can't be sure that problems like that migth also occur at other places.

       

      Thanks in advance for any input!

       

      Kind regards,

       

        Andreas Schilling

        • 1. Re: After server migration old differences reappear
          Ben Speckhard-Oracle

          Hi Andreas,

           

          What are the exact versions of the source and target databases?

           

           

          Your first issue is known for upgrades to 11.2.x.  There reason is that we are attempting to drop a procedure with a name > 30 characters, as you mentioned.  This will occur for any user defined triggers that have more than 24 characters in its original name.  The workaround is to do what you did using ignore_last_error with RecoverMigratingTable.  Using this method, it should be sufficient to skip the failing drop procedure statement.  You shouldn't have to recreate the trigger.  An alternative is to rename any triggers so that every trigger name is <=24 characters prior to the upgrade.

           

           

          If these workarounds are not sufficient and you need a backport of this fix prior to the upgrade, I would suggest filing a SR so that one can be created.  This has already been fixed in 12.1.0.1.  Also, the trigger procedure is still being created, except that it is using a system generated name, instead of using the procedure name.

           

          Be aware that to use full database import/export on 11.2.x, the source and target version of Workspace Manager need to be the same.

           

           

          As for your 2nd issue, I haven't seen this exact issue before.  If you query the top level view(the original table name) for the particular primary key value from the WS2 workspace, is the row from Dec-2011 returned or some other row?  The fact that 6 rows are being returned for a single primary key value leads me to believe that there might be an underlying metadata issue either in _LT or WMSYS.  This might also happen due to a failed operation during an import.  Were there any that you noticed in the import log?

           

           

          Regards,

          Ben

          • 2. Re: After server migration old differences reappear
            aschilling

            Hi Ben!

             

            Thanks alot for the input so far, that already helps.

            Here's some more information:

            Source DB is 10.2.0.4.0 with OWM 10.2.0.4.3

            Target DB is 11.2.0.3.0 with OWM 11.2.0.3.0 (prior to migration OWM was downgraded to 10.2.0.4.3, then the import took place and then it was upgraded back to 11.2.0.3.0)

             

            Concerning the _DIFF-problem: There's pretty sure some problem with the metadata... when I issue a SELECT with the PK of the row in the respective parent workspace where the _DIFF problem occurs (WS2 in our example), I get two rows... the very old one and the current one I actually want to receive.

             

            In the import log are some warnings and two errors. I'm not the DBA, so I can't tell for sure what of those is problematic. Some information I can give is:

            • Import was issued with FULL=Y TABLE_EXISTS_ACTION=REPLACE REUSE_DATAFILES=Y
            • The two errors were: (quick-translated from german)
              ORA-39083: Objekttyp PROCOBJ could not be created, Error:
              ORA-29357: Objekt AUTO_TASK_CONSUMER_GROUP is already present
              Erroneous SQL is:
              BEGIN dbms_resource_manager.create_consumer_group('AUTO_TASK_CONSUMER_GROUP','System maintenance task consumer group','ROUND-ROBIN');COMMIT; END;

              ORA-39083: Objekttyp PROCACT_SYSTEM could not be created, Error:
              ORA-00001: Unique Constraint (SYS.WRI$_ALERT_THRESHOLD_PK) violated
              Erroneous SQL is:
              BEGIN dbms_server_alert.set_threshold(2018,dbms_server_alert.operator_GE,'100',NULL,NULL,1,2,'oracomo1',dbms_server_alert.object_type_SYSTEM,'');COMMIT; END;
            • The only thing I wondered is that I can't see any log entries for the import of any SYS or WMSYS table rows. Or are system schemas ommited from logging?
            • The warning are all compile-warnings for the Triggers and Views of our schema (e.g. the _CONF-views). After migration they are fine though, so I assume a recompilation is all that's needed? At least some people reported that triggers and views needed recompilation after migration

             

            Hope that information is of use.

             

            Thanks alot already!

             

            Regards,

             

              Andreas

            • 3. Re: After server migration old differences reappear
              Ben Speckhard-Oracle

              Hi,

               

              That is the best way to do a import/export between differing db versions.  Both of those errors that you listed are not Workspace Manager related and so would not have any impact.  The compilation warnings on views and triggers are common.  They get recompiled at the end of the import, after which they should all be valid.  The WMSYS schema is a schema that is never exported.  As a result, we use procedural actions to move the data.  That is why the tables aren't listed in the import log.  An error would be logged however if one occurred during a dml on a WMSYS tables.

               

              As for the _DIFF view problem, if you are seeing duplicate rows in that view, then the issue is with the metadata, not the _DIFF view itself.  The question is whether this was the result of the import, the upgrade, or some other operation.  Would be interesting to know if the rows were also being seen as duplicated after the import, but prior to the upgrade to 11.2.0.3.  That would help to narrow down the cause.  This might require a SR to gather the appropriate information.  Another piece of information that would be needed is the value for each metadata column for each row of the affected primary key from the base _LT table.  That along with the WMSYS metadata would definitively show if there was a metadata issue.  That would potentially indicate why duplicate rows are being seen, but not necessarily why the metadata is in its current state if that does turn out to be the problem.

               

              Regards,

              Ben

              • 4. Re: After server migration old differences reappear
                aschilling

                Hi Ben,

                 

                the trigger problem was easy to fix by renaming the triggers prior to migration, thanks alot!

                Concerning the _DIFF problem: after import and before the OWM upgrade the data for the row is fine, so no duplicates and no reappearing DIFF entries.

                What would be the best way continue here?

                Is there anything we could have performed in a wrong way during the upgrade so that a review of our process would be necessary or is it kind of bulletproof and we'd need to file a SR?

                 

                Kind regards,

                 

                  Andreas

                • 5. Re: After server migration old differences reappear
                  Ben Speckhard-Oracle

                  Hi Andreas,

                   

                  To answer your last question, I would need to know exactly how you are doing the upgrade.  It should be sufficient to run the owmupgrd.plb file from the newer patchset directory while no other sessions are manipulating versioned tables or workspaces.  Make sure the upgrade log contains no errors.

                   

                  If the upgrade itself is introducing the duplicate row, then I would suggest to file a SR.  Do the duplicate entries appear immediately after the upgrade or does some other process need to modify the table for the problem to manifest itself?

                   

                  Also, assuming the same set of rows have duplicate values each time the upgrade is run, it should be fairly easy to track down the cause.  If you do file a service request, include a query of the_LT table for the metadata columns(version, nextver, delstatus, ltlock and optionally createtime, retiretime for history enabled tables) for any of the primary key values that exhibit this issue.  If the values are changed during the upgrade, provide both the before and after snapshots.  Might need additional information, but that would provide a good starting point.

                   

                  Regards,

                  Ben

                  • 6. Re: After server migration old differences reappear
                    aschilling

                    Hi everyone,

                     

                    to give some feedback on this issue so this thread is not open:

                    Some tables contained broken metadata. There were rows that referenced a non-(anymore-)existing version and also rows for that version.

                    We needed an SR to get that solved. I won't go into detail because applying what we were asked to do might be plain wrong in other cases, but it boils down to getting rid of those rows (and thus that version). They were dangling anyway, so nobody could read them in the state they were and there was sufficient history information available before and after those row versions.

                    The dates of those rows matched some merge/rollback table issue we had around that time. Probably some process back then messed a few things up.

                     

                    regards,

                     

                      Andreas