1 Reply Latest reply on Mar 21, 2011 9:54 AM by Mike Reider

    ORA-02441 error during Data migration upgrade

    Mike Reider

      We are in process of upgrading a 7.2 server to 8.0, during the Data MIGration Manager upgrade step, the DMM tool runs through 7.2 -> 7.3, but fails with following error on 7.3 -> 7.4 step

       

      CLOBOVERFLOW mig_job_run_event STEP 1 Thu Mar 17 11:24:25 EDT 2011 Failed ORA-02441: Cannot drop nonexistent primary key

       

      looking at blmigration.log, we are getting an error on this table,

       


        -----------------------------------------------------------------------
          v_at := v_at + 1;
          mig_log_task
          (
            v_script_name,
            v_task_name,
            'END',
            0,
            v_task_name||' succeeded'
          );

        RETURN;

      EXCEPTION
        WHEN OTHERS
        THEN
        mig_log_task
        (
          v_script_name,
          v_task_name,
          'STEP '||TO_CHAR(v_at),
          1,
          SQLERRM
        );
        ROLLBACK;
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
        mig_log_task
        (
          v_script_name,
          v_task_name,
          'END',
          1,
          v_task_name ||' FAILED!');
        RETURN;

      END mig_sequences;

      Thread-12 | Thu Mar 17 11:00:32 EDT 2011 | LOG_DEBUG | Finished executing action                             node(Sql) name(oracle_tskOMmainSP_mig_sequences.sql) with exit status(0) at(Thu                             Mar 17 11:00:32 EDT 2011)
      Thread-12 | Thu Mar 17 11:00:32 EDT 2011 | LOG_INFO | Time taken for action node                            (Sql) name(oracle_tskOMmainSP_mig_sequences.sql) : 28 milli-seconds

       

      We have ticket open with support and I'll post any resolution steps. Thanks.

       

      this is Oracle running on Solaris 9

        • 1. ORA-02441 error during Data migration upgrade
          Mike Reider

          We are suspecting that these errors are caused by incorrect TEMP and UNDO size on oracle during teh Data migration tool run. We also ran the tool based on old Externals scripts (we ran externals-76-116), theres a more recent, updated version of these available from BMC. Make sure to get teh latest version

           

          according to BMC support, when upgrading from BL 7.2 to 8.0 on Oracle, teh upgrade path is as follows (its different from Windows upgrades)

           

          1)  7.2 -> 7.6

          2)   7.6 -> 8.0

           

          request specific DB externals from BMC (the upgrade scripts on EPD site are old and not newest version.)

          once you get the correct 7.6 externals, upgrade the App Server, Reports, PXE,etc to 7.6, then run the DB Migration tool to upgrade teh DB to 7.6. It will display an upgrade path (7.2 -> 7.3 -> 7.4 -> 7.4.1...etc)

           

          before running the DB tool, have the DBA run proper sizing for UNDO and TEMP tablespace.

           

           

           

          some questions for DBA before start of data upgrade:

          1) What version and patch level or Oracle is currently running, i.e. 10.2.0.4?

          2) Is DB running with log mode or oracle flashback recovery technologies?  If so, it’s the general recommendation to turn these off during the duration of the migration.  The reason here is that we are expecting massive changes to the database to occur during migration which causes excessive tracking both for time and space.  If they cannot turn it off they should consider drastically increasing disk space to handle the growth.  The mode of recovery in a catastrophic failure in migration, i.e. it’s not recoverable in place, is a full re-load and because of this there isn’t much value in tracking all the changes using archiving.  Of course they will need to do some work after the migration is complete to get a good baseline for recovery going forward, but this is usually a better approach in the case of migration.

          3) For the portion of migration from 7.2 to 7.6, theres an estimate 30% growth in the database for the duration of the migration.  The DBA should be sure to allocate at least this much in both tablespaces, set auto extend on for the datafiles, and be sure that environment has enough datafile groupings to support the additional growth.  For instance, depending on how the blocks are set up in the database, the DB may only be able to grow a datafile to 32Gb, it wouldn’t matter if you have autoextend on in this case as the file would not grow larger than this, and so if the DB needs to support an additional 64Gb of space, it would need two datafiles each 32Gb in size.  For the 7.6 to 8.0, run the data sizing SQL scripts, it’s as part of the folder migration_sizing_8x_temp_undo in the external-files.zip (there is an accompanying readme on how to use the package in it).

          4) The client should think of the migration as a two part phase: 1) 7.2 to 7.6 and 2) 7.6 to 8.0.  We can send accompanying documentation to what is already available, but they need to get the 7.6 installers, external-files and documentation and be sure to follow the sizes for undo and temp space which are estimated with the folder migration_sizing_7x_temp_undo folder (see point 3 for data and index tablespace concerns).  They also need to get the 8.0 installers, external-files and documentation and be sure to follow the sizes for undo and temp space which are estimated with the folder migration_sizing_8x_temp_undo in the accompanying zip file.

          5) What are the memory settings on the Oracle database server?  Is the sga_target set, and if so what is it set too?  Or, are the memory settings maintained manually?

          6) What is the size of the total redo size, how many groupings, and size of files in groupings?