I downloaded the OMWB from
Found the Informix JDBC and plug-in and put them in the following:
I got our DBA to create the 2 logins based on the documentation..
CREATE USER omwb_user IDENTIFIED BY omwb_user;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM TO
omwb_user WITH ADMIN OPTION;
GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE,
ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,
CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY
SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY
ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE TO
CREATE USER REPUSER IDENTIFIED BY REPUSER;
GRANT CONNECT,RESOURCE,CREATE VIEW,CREATE PUBLIC SYNONYM TO REPUSER;
Upon starting the OMWB I have tried multiple things..
I have got ORA-00942 errors trying to user the omwb_user and REPUSER as the Repository login and ORA-01031 errors once I finally got a Source Model and Oracle model built using the Oracle Lite repository but the ORA-01031 got thrown when it was trying to create user accounts I'm thinking in the destination Oracle 11g for example the informix user account which is tied to the tables and indexes and once it failed trying to create the user, the creation of the tables, etc. failed. The Oracle Model got created but only because I used the Oracle Lite as the repository, but when I tried to do the migration it failed because of the userid's that it tried to migrate over from Informix.
I'm also not sure of the difference between the omwb_user login and the REPUSER login as to when to use which one during the process. I have tried different scenerios using each one and neither seems to have any success. I also had my dba drop both of them and recreate them since I found that on a thread and in one piece of documentation the REPUSER only needed CONNECT,RESOURCE but in a second I found it needed CONNECT,RESOURCE,CREATE VIEW,CREATE PUBLIC SYNONYM but that didn't help either.
So.. I'm now wondering if the OMWB 10.1.0.4.0 is the correct tool when trying to migrate from Informix 7 to Oracle 11g.
If not, could you point me in the correct direction of what I should be using or maybe what I am doing wrong.
What is your exact version of Informix ? OMWB only works with 7.3 (and with 9.x, but with a different plugin)
Regarding your question about the users omwb_user and repuser, only one is required (omwb_user appears as an example in the release note and repuser appears as an example in the user's guide). You only need one user to handle the repository.
Once the Source and Oracle models created (even in the default olite repository), the migration wizard will ask you for a a username. This username will be the one which will create all the migrated objects in the Oracle Destination Database. Please choose an existing user with sufficient privileges (system for example).
(which one did you choose previously ?)
I hope it will help
Thank you Jean-Patrick for trying to help me.
We have Informix 7.3
Thanks for the clarification about the 2, so I will use the omwb_user one going forward.
When using Olite for the data model, if I used REPUSER when I tried to connect to my destination database I got the ORA-00942 error so then I tried the omwb_user login to connect and it connected but then I got the ORA-01031 errors when it tried to creates the Users from the Source Model.. See error log...
-- This file is a log of the SQL create statements that were
-- unsuccessful. The logging messages associated with these
-- statements can be seen in the Log Window of the Oracle
-- Migration Workbench.
REM Start Tablespaces
REM End Tablespaces
REM Start Users
REM Message : Failed to create User: informix; ORA-01031: insufficient privileges
REM User : omwb_user
CREATE USER informix IDENTIFIED BY oracle;
GRANT ALL PRIVILEGES TO informix;
*****Note: There are 3 more users it couldn't create with the same error as above.
REM Start Packages for omwb_emulation
REM End Packages for omwb_emulation
REM End Users
REM Start Tables
REM Message : Failed to create table : informix.VACACCRUAL;
REM User : omwb_user
CREATE TABLE informix.VACACCRUAL(EMPL_ID CHAR (10) NOT NULL,NAME CHAR (50) ,SERVICE_DATE CHAR (25) ,DEPT CHAR (10) ,END_BAL FLOAT (63) ,YTD_ACCRUAL FLOAT (63) ,YTD_TAKEN FLOAT (63) ,YTD_VAC_BAL FLOAT (63) ,CO_HOL_ELIG FLOAT (63) ,CO_HOL_TAKEN FLOAT (63) ,CO_HOL_REMAIN FLOAT (63) ,FL_HOL_ELIG FLOAT (63) ,FL_HOL_TAKEN FLOAT (63) ,FL_HOL_REMAIN FLOAT (63) ) TABLESPACE IES_FMT01;
**** Since informix is associated with practically all our tables, all the create tables failed...
Thanks for helping,
Once the Informix and Oracle models created (I guess it worked), the next step is to "Migrate to Oracle" (menu Actions - Migrate to Oracle). The Wizard will prompt you for the Destination Database detail. At this point, give the host, port, and sid of the datbase you want to migrate to, AND GIVE SYSTEM AS USERNAME. System will have all the required credential to create the migrated objects...
Let me know the result, but please note that I am in EMEA zone, so it may tke some time for me to respond...
Using the System login to perform the migration worked !!!
Thank you so much for the help.
Out of 741 tables I only have 58 errors and that is due to the Informix datetime conversion which I'm testing to see if changing the mapping to Timestamp in Oracle fixes that issue.
But, it's working now, so I can move forward.
Thank you so much for all your help !!!
If you need to use Oracle Enterprise Edition database, then use Oracle 10g. With Oracle 11g R1 or R2, you will run into issues with repository creation etc. I have had similar problems before.
Once you complete migration to Oracle 10g database, you can easily copy the migrated objects to Oracle 11g R1 or R2 as desired. Also keep in mind that the OMWB has not been enhanced for quite some time and is only to be used for Informix migrations (upto 9.4) as Oracle SQL Developer does not have the capaibility yet.