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 22.214.171.124. 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?
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 126.96.36.199.0 with OWM 188.8.131.52.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 184.108.40.206.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!
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 220.127.116.11. 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.
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?
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.
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.