This discussion is archived
7 Replies Latest reply: May 29, 2012 6:43 AM by J.A. RSS

Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g

878425 Newbie
Currently Being Moderated
I downloaded the OMWB from
http://www.oracle.com/technology/software/tech/migration/workbench/index.html

Found the Informix JDBC and plug-in and put them in the following:
C:\OMWB\omwb\plugins\Informix7.jar
C:\OMWB\omwb\drivers\ifxjdbc.jar

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
omwb_user;

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.
Thanks,
Suzanne
  • 1. Re: Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g
    jplogut Newbie
    Currently Being Moderated
    Hello,
    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
    Jean-Patrick
  • 2. Re: Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g
    878425 Newbie
    Currently Being Moderated
    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
    REM Start Tablespaces
    REM

    REM
    REM End Tablespaces
    REM

    REM
    REM Start Users
    REM

    REM
    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;

    REM
    *****Note: There are 3 more users it couldn't create with the same error as above.
    REM
    REM Start Packages for omwb_emulation
    REM

    REM
    REM End Packages for omwb_emulation
    REM

    REM
    REM End Users
    REM

    REM
    REM Start Tables
    REM

    REM
    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,
    Suzanne
  • 3. Re: Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g
    jplogut Newbie
    Currently Being Moderated
    Suzanne,
    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...

    Cheers,
    Jean-Patrick
  • 4. Re: Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g
    878425 Newbie
    Currently Being Moderated
    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 !!!
    Suzanne
  • 5. Re: Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g
    J.A. Explorer
    Currently Being Moderated
    hi !

    I want to know if I can migrate from Informx 9x to Oracle 11gR2 ?? is that posible, coz the documentation of Workbech says that only work for Oracle 11g


    Thanks in advance
  • 6. Re: Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g
    84959 Explorer
    Currently Being Moderated
    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.

    Regards

    Prakash
  • 7. Re: Does OMWB 10.1.0.4.0 work to Migrate Informix 7 to Oracle 11g
    J.A. Explorer
    Currently Being Moderated
    Thank you for the advice and your time

    I will keep in mind this

    Kind regards

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points