This discussion is archived
3 Replies Latest reply: Oct 25, 2012 7:16 AM by wkobargs RSS

SQL Server to Oracle 10g Migration

969730 Newbie
Currently Being Moderated
Hello,

Our company use MS SQL 2008 and Oracle 10g, we need move all database to Oracle, How to migrate MS SQL to Oracle ? We use Oracle SQL Developer but it give some errors no move table structer.

Thanks.
  • 1. Re: SQL Server to Oracle 10g Migration
    wkobargs Journeyer
    Currently Being Moderated
    Hello,

    please give us more details to be able to help you.

    - Which version of SQL Developer are you using?
    - Are you doing an online or offline migration?
    - In which phase exactly are you getting the error?
    - Can you show us the errors you are getting?
    - Does the error happen on all tables, or only on some? In the latter case, what might be special in those tables? Show us an example, please.

    Best regards
    Wolfgang
  • 2. Re: SQL Server to Oracle 10g Migration
    969730 Newbie
    Currently Being Moderated
    Wolfgang thanks for reply ,

    - Which version of SQL Developer are you using?
    -- Oracle SQL Developer Version 3.2.10.09 Build MAIN-09.57

    - Are you doing an online or offline migration?
    -- Online Migration, We can only migrate Table and view structure migrate. I don't want to migrate data

    - In which phase exactly are you getting the error?
    --Inserting Row

    Database Migration
    Error:ORA-06510:PL/SQL:unhandled user-defined exception ORA-06512:at "SYSTEM.SS2K5ALLPLATFORM",Line 3497ORA-07098:trigger"SYSTEM.MIRLOG..

    - Can you show us the errors you are getting?
    -- I shapshot error but I can't attach

    Database Migration
    Error:ORA-06510:PL/SQL:unhandled user-defined exception ORA-06512:at "SYSTEM.SS2K5ALLPLATFORM",Line 3497ORA-07098:trigger"SYSTEM.MIRLOG..

    - Does the error happen on all tables, or only on some? In the latter case, what might be special in those tables? Show us an example, please.
    Migration log file is empty,
  • 3. Re: SQL Server to Oracle 10g Migration
    wkobargs Journeyer
    Currently Being Moderated
    Hello,

    it took some time to decipher this: ORA-07098:trigger"SYSTEM.MIRLOG..

    I am sure that it means:

    ORA-04098: trigger "SYSTEM.MIGRLOG_TRG" is invalid and failed re-validation

    The definition of the trigger is:

    create or replace
    TRIGGER "MIGRLOG_TRG" BEFORE INSERT OR UPDATE ON MIGRLOG
    FOR EACH ROW
    BEGIN
    if inserting and :new.id is null then
    :new.id := MD_META.get_next_id;
    end if;
    END;

    And if that trigger does not work, it explains that there is nothing in the migration log.

    I suspect there is some corruption in your migration repository to which this trigger belongs. I recommend to delete the repository and recreate it. Please follow the advice in the online help how to create the migration repository which says:


    +Creating a Database User for the Migration Repository+

    +SQL Developer requires a migration repository to migrate a third-party database to an Oracle database. To use an Oracle database for the migration repository, you must have access to that database using a database user account. Oracle recommends that you use a specific user account for migrations, For example, you may want to create a user named MIGRATIONS, create a database connection to that user, and use that connection for the migration repository; and if you wish, you can later delete the MIGRATIONS user to remove all traces of the migration from the database.+

    +When you create a user for migrations, specify the tablespace information as in the following example, instead of using the defaults for tablespaces:+
    +CREATE USER migrations IDENTIFIED BY <password>+
    +DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp,+

    +Do not use a standard account (for example, SYSTEM) for migration.+
    +...+

    "SYSTEM.MIGRLOG_TRG" indicates that you used the SYSTEM user for the repository.

    Please try what I suggested and let me know whether it helped.

    Regards
    Wolfgang

Legend

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