We have ODM development environment in HP-UX 11.31 (Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production). We need to migrate this to Oracle Enterprise Linux 6 (2.6.32) (Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 64bit ).
What approach should we take to migrate this? We just tried using datapump (full) to export and import. But it seems that isn't working because many objects of ODMSYS are failing to import.
In the meantime I was trying little bit different approach
1) Created database same as source, using source scripts
2) Created ODM repository (using SQLDeveloper -Data Miner)
3) Created users
4) Exported models from source using
EXECUTE dbms_data_mining.export_model('all_odm_models.dmp', 'dump');
5) Imported models into target
EXECUTE dbms_data_mining.import_model('all_odm_models01.dmp', 'dump');
6) Imported all other user object in user schemas
7) compared number of objects and they all match
8) Compared the models using SELECT * FROM all_mining_models
9) Connected using SQL Developer to new database but can't see the projects and workflows
Is this method correct ? should I do something else to get projects, workflows ?
or RMAN is the only option?
Yes. The workflows/projects reside on the client that built them using ODM in SQL Developer.
Only the models and other objects that are created when the workflow is run, will be stored in the database.
Well, you took the manual approach that is for sure.
The approach you took will only partially works so I will explain what is missing.
First just a correction on Brendan's statement regarding the workflows.
The projects/workflows are actually all stored on the database in the ODMRSYS repository.
Recap of Process and Issues
Since you created a new ODMRSYS repository, rather than use RMAN to copy the db, then the repository is empty.
By copying over all the objects in the user accounts, you were able to retain the underlying tables,models that were originally generated by the workflow.
You solved your lack of projects/workflows by manually exporting them, one at a time, from the old db' ODMRSYS repository to your file system, and then importing them into the new db's ODMRSYS repository.
However, when you import a workflow you have an option to retain the original model names and any table or view names generated by the Create Table node.
If you do not choose this option the model names and table/views are renamed if there are conflicts with preexisting objects in the user's account.
So I have to assume that you choose the option to preserve the names.
However, when a workflow is imported, all of the hidden table/view references that it contains to such objects as model test result tables, statistical output tables etc., are blanked out. So the workflow is in a incomplete but ready state. All the metadata is there, but it will need to be run to regenerate all the underlying objects again. So the fact that you copied these objects over will not really do you any good. In fact, you now have some garbage tables/views that will never be dropped unless you drop the entire repository, or run special scripts to find out what ODMr generated objects are not referenced in workflows.
I can provide a script to find the garbage tables/views and remove them.
So, in summary, the process you followed does not preserve the results, but does preserve the workflow metadata.
The effort of copying the objects over from the old db was not necessary. Except for the source tables you need to perform mining on.
Hope this helps explain things.