Object Migrator Views Databases and Links

NEW! Deploy Metrics v1.3

ZDS

Kintana Standards

If you're using Object Migrator you need to understand the role Object Migrator Views play in enabling object type migrations and be very aware of what you need to do to maintain them in good shape to avoid unnecessary migration failures.

Object Migrator has to provide a list of parameter values to the concurrent program (for example 'Migrate Users' CLMRMUS1.rdf ) that it launches to migrate a specific object type.

The list of values is retrieved from the source database at the time of the migration using custom views (queries).

There is one custom view for each object type. A view is a set of unions that select from each database defined in the CLM_DATABASES value set using the database links defined in the CLM_DB_LINKS value set.

When retrieving a list of values using a view Object Migrator connects to the selected source database and returns the values for that.

For example suppose CLM_DATABASES has the following enabled values:

Value   Meaning
    2   DEV
    3   QA
    4   PROD
  900   Object Archive

and CLM_DB_LINKS has the enabled values:

Value   Meaning
    2  DEV_LINK
    3  QA_LINK
    4  PROD_LINK
  900  Object Archive

The view for the User Migrator object type would be:

SELECT 2 db_value, user_name object_name FROM fnd_user@DEV_LINK
UNION
SELECT 3 db_value, user_name object_name FROM fnd_user@QA_LINK
UNION
SELECT 4 db_value, user_name object_name FROM fnd_user@PROD_LINK
UNION
(select clause to query the Object Archive tables against the local database)

Now note! Because of the way views are built using unions if ANY of the source or destination databases referenced in a view are unavailable at the time Object Migrator issues the query the parameter form will return an error and migrations for that object type will NOT be executed.

This can happen if the password for a remote database has changed so the database link is out of date, or if the database is offline for backup, or down for some reason.

The source and destination databases being used may be running and their db-link passwords current, but an error on a database not being used for a particular migration can still block migrations for the object type supported by the view.

You need to bear this in mind when trouble-shooting Object Migrator failures. You need to check the status of the databases and test the database links for not just the source and destination databases but ALL databases referenced in the view for the object type being migrated.

Bearing in mind what we just said there are things that you need to do pro-actively to minimize Object Migrator failures:

When database passwords change, drop the database link and and re-create it with the new password!

Update the CLM_DATABASES and CLM_DB_LINKS value sets to keep then in sync with database and database link changes! (For example when an Object Migrator source or destination becomes available or unavailable.)

You are NOT done yet!

You MUST run the Maintain Object*Migrator Views program (CLMRDVW1.rdf) after making changes to either the CLM_DB_LINKS or CLM_DATABASES value sets! This program validates and rebuilds the Object*Migrator views based on the current values in the CLM_DB_LINKS and CLM_DATABASES value sets, as well as checking the current status of your remote databases.

DO look at the report output by the program!

The output lists any errors encountered while trying to connect to the remote databases enabled for use with Object*Migrator and any errors need to be addressed right away. You also get a useful listing of each object type's view, which is useful when you are troubleshooting migration errors and need to know what databases are referenced in a view.

Schedule a cron job to run the Maintain Object*Migrator Views program! If you make password changes daily then schedule it daily

Use the Submit Requests form, Standard Menu Path: Other:Requests:Run
When prompted for report parameters select Report Only mode.

I experienced this ORA-02055 failure on a report migration:

It turned out that the passwords on the AUDT, CNV2 and VSTG servers had changed and the IMUD server was out of service. None of these were the source or target databases. To get past this quickly we temporarily deleted the entries for these from CLM_DATABASES and CLM_DB_LINKS and ran 'Maintain Object Migrator Views' to rebuild the views without them.

Object Migrator was then able to launch a concurrent program to do the update. The update actually failed with a 'Set the OPTIMIZER_GOAL to RULE' message but at least the update ran and we were past OM transport problems and into the realm of logical fixes.

When the tumult and the shouting died away we put back the CLM_DATABASES and CLM_DB_LINKS entries, dropped and re-created the db-links, and re-ran 'Maintain Object Migrator Views'.

And wrote this document...

Kintana™ and 'HP PPM (Project and Portfolio Management)'™ are trademarks of Hewlett Packard Corporation.