11 Replies Latest reply: Jul 22, 2013 11:44 AM by 930888 RSS

    Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g

    906825
      Hi All,
      We have received following error during product upgrade from 9.1.0.2 to 11g.
      Error occurred during fourth step ( Task 4: Use Upgrade Assistant to Upgrade the Oracle Identity Manager Schema)

      On the screen while running upgrade assistance (ua.bat)
      Following message came on screen.
      Schema Version registry >> succeeded
      Oracle Identity Manager Schema Upgrade >> Failed

      I could see only following error in the log file.

      ***
      \middleware_home\Oracle_IDM1/server/db/oim/oracle/Upgrade/Oracle_upg_91_92_x2ell_92.sql
      [2012-06-28T09:34:51.147-06:00] [FrameworkMR] [NOTIFICATION] [] [upgrade.FrameworkMR] [tid: 14] [ecid: 0000JWnikOB6qIvyKWYBSQ1Fv7Zd000005,0] finalize() instance 1 of 2
      [2012-06-28T09:35:00.863-06:00] [OIM] [INCIDENT_ERROR] [] [upgrade.OIM] [tid: 13] [ecid: 0000JWnijRj6qIvyKWYBSQ1Fv7Zd000004,0] UPGCMP-00924: SQL error [{0}]
      [2012-06-28T09:35:00.863-06:00] [OIM] [INCIDENT_ERROR] [] [upgrade.OIM] [tid: 13] [ecid: 0000JWnijRj6qIvyKWYBSQ1Fv7Zd000004,0] [[
      java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
      ORA-06512: at line 31

           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:889)
           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)
           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:540)
           at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
           at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1074)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1466)
           at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:2123)
           at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:2067)
           at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:306)
           at oracle.ias.mrua.plugin.oim.SchemaUpgrader.loadScript(SchemaUpgrader.java:303)
           at oracle.ias.mrua.plugin.oim.SchemaUpgrader.doUpgrade(SchemaUpgrader.java:139)
           at oracle.ias.mrua.plugin.oim.OIMSchemaUpgradePlugin.doUpgrade(OIMSchemaUpgradePlugin.java:302)
           at oracle.ias.mrua.MRUA.executePlugins(MRUA.java:3672)
           at oracle.ias.mrua.MRUA.internal_driver(MRUA.java:2448)
           at oracle.ias.mrua.MRUA.driver(MRUA.java:1710)
           at oracle.ias.upgrade.gui.UAUpgradeThread.run(UAUpgradeThread.java:390)
      **

      Any Idea ?
      Ritu
        • 1. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
          Nishith Nayan
          Error because, It is trying to create a database object (such as a table, view, cluster, index, or synonym) that already exists.

          May you have some customization in 9102 which lead this issue.
          • 2. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
            Le�ncio-Oracle
            Please check this script(Oracle_upg_91_92_x2ell_92.sql) that you are running and compare what's doing and what you have into those tables. It looks like a PK or UniqueKEY violation.

            I hope this helps,
            Thiago Leoncio.
            • 3. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
              Gyanprakash Pandey
              Before running upgrade assistant to upgrade schema, please run 'OSI data upgrade utility' on your 9i database and then run the upgrade assistant. You can download it from metalink. Also, make sure you don't have any custom tables added in the database which are having same name as the one in 11g. If there is any custom table then change name of table and rerun upgrade assistant.

              regards,
              GP
              • 4. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                906825
                Hi All,
                Thanks for all your suggestion.

                I am trying to drill down what obejct it is but couldn't find it.
                Upgrade also generate one .out file. i checked the last entry of .out file in Oracle_upg_91_92_x2ell_92.sql.
                There is no create statment after this.

                lat entry in .out file
                ***
                ELSE
                DBMS_OUTPUT.PUT_LINE('Copying data from UPP_ALLOW_LIST to UPP_ALLOW_LIST_CLOB failed');

                Select Count(1) into Cnt from all_tab_columns where TABLE_NAME='UPP' and COLUMN_NAME='UPP_ALLOW_LIST_CLOB' AND OWNER=UPPER('oimuser');
                IF Cnt=1 Then
                Begin
                     EXECUTE IMMEDIATE 'ALTER TABLE UPP DROP COLUMN UPP_ALLOW_LIST_CLOB';
                End;
                END IF;     

                END IF;
                Commit;

                END;
                ****

                values after above entry in file Oracle_upg_91_92_x2ell_92.sql after above queries.


                **
                Oracle_upg_91_92_x2ell_92.sql and i dont see any problem in below statements

                ***DECLARE

                new_col_count NUMBER;
                old_col_count NUMBER;
                Cnt          Int;

                BEGIN

                Select Count(1) into Cnt from all_tab_columns where TABLE_NAME='UPP' and COLUMN_NAME='UPP_DENY_LIST_CLOB' AND OWNER=UPPER('oimuser');
                IF Cnt=0 Then
                Begin

                     EXECUTE IMMEDIATE 'ALTER TABLE UPP ADD UPP_DENY_LIST_CLOB CLOB';

                     EXECUTE IMMEDIATE 'UPDATE UPP SET UPP_DENY_LIST_CLOB = UPP_DENY_LIST';
                     
                End;
                End If;     

                EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM UPP WHERE UPP_DENY_LIST_CLOB IS NOT NULL' INTO new_col_count;

                ***


                Any ideas please.

                Ritu
                • 5. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                  Le�ncio-Oracle
                  hi Ritu,

                  Error: ORA-00955 name is already used by an existing object. Usually happens when there is an attempt to create an existing database object. These objects includes such as table, column, view, cluster, index, or synonym.

                  So, looking to this SQL that you provided. The only possibility here is:

                  "...ALTER TABLE UPP ADD UPP_DENY_LIST_CLOB CLOB..."

                  Oracle database has a SYSTEM table called DBA_OBJECTS and all existing objects are there. Run this command==> Eg as system database user: select * from dba_objects where upper(object_name) like upper('%UPP_DENY_LIST%')

                  If this SQL returns a value this should be your problem. It's trying to create that column that is already there into UPP table. If this is correct, try to check if you follow all steps correctly and if there is any step missing during the process.


                  I hope this helps,
                  Thiago Leoncio.
                  • 6. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                    906825
                    Thiago,
                    If this is the case query 'select UPP_DENY_LIST_CLOB from upp' should return some result.
                    But this query is following error.

                    ORA-00904: "UPP_DENY_LIST_CLOB": invalid identifier
                    00904. 00000 - "%s: invalid identifier"
                    *Cause:   
                    *Action:
                    Error at Line: 6 Column: 7

                    I think this proves that column is there in the table ?
                    Please suggest ?

                    Ritu
                    • 7. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                      Le�ncio-Oracle
                      no, pls type into your OIM Schema: describe UPP

                      My OIM11G shows this result below:

                      Name Null Type
                      ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      UPP_KEY NOT NULL NUMBER(19)
                      USR_KEY NOT NULL NUMBER(19)
                      UPP_ALLOW_LIST CLOB()
                      UPP_DENY_LIST                           CLOB()
                      UPP_ORG_NOT_ALLOW_LIST CLOB()
                      UPP_DATA_LEVEL NUMBER(1)
                      UPP_CREATE DATE
                      UPP_CREATEBY NUMBER(19)
                      UPP_UPDATE DATE
                      UPP_UPDATEBY NUMBER(19)
                      UPP_ROWVER RAW(8 BYTE)
                      UPP_NOTE CLOB()


                      If there is a column named 'UPP_DENY_LIST' as CLOB type, as my example above. This column was already inserted. If so, I have one question: Are you sure that you run this SQL only one time. Or it was more than one time? I guess you already ran this SQL successfully before.


                      btw:
                      ORA-00904: "UPP_DENY_LIST_CLOB": invalid identifier
                      00904. 00000 - "%s: invalid identifier"

                      This erros means that you typed something wrong in your SQL statement, in this case, the column name.

                      I hope this helps,
                      Thiago Leoncio.
                      • 8. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                        906825
                        Hi Thiago,
                        These are the columns in table UPP at my end.
                        UPP_EY
                        USR_KEY
                        UPP_DATA_LEVEL
                        UPP_CREATE
                        UPP_CREATEBY
                        UPP_UPDATE
                        UPP_UPDATEBY
                        UPP_ROWVER
                        UPP_NOTE
                        UPP_ALLOW_LIST
                        UPP_DENY_LIST
                        UPP_ORG_NOT_ALLOW_LIST
                        UPP_ALLOW_LIST_CLOB

                        Again i dont see column 'UPP_DENY_LIST_CLOB' in the above table hence following code should not give any error.
                        IF new_col_count = old_col_count THEN

                        Select Count(1) into Cnt from all_tab_columns where TABLE_NAME='UPP' and COLUMN_NAME='UPP_ALLOW_LIST' AND OWNER=UPPER('oimuser');
                        IF Cnt=1 Then
                        Begin
                        EXECUTE IMMEDIATE 'ALTER TABLE UPP DROP COLUMN UPP_ALLOW_LIST';
                        EXECUTE IMMEDIATE 'ALTER TABLE UPP RENAME COLUMN UPP_ALLOW_LIST_CLOB TO UPP_ALLOW_LIST';

                        End;
                        END IF;
                        ELSE
                        DBMS_OUTPUT.PUT_LINE('Copying data from UPP_ALLOW_LIST to UPP_ALLOW_LIST_CLOB failed');
                        Select Count(1) into Cnt from all_tab_columns where TABLE_NAME='UPP' and COLUMN_NAME='UPP_ALLOW_LIST_CLOB' AND OWNER=UPPER('oimuser');
                        IF Cnt=1 Then
                        Begin
                             EXECUTE IMMEDIATE 'ALTER TABLE UPP DROP COLUMN UPP_ALLOW_LIST_CLOB';
                        End;
                        END IF;     
                        END IF;
                        Commit;
                        Please suggest.
                        Ritu
                        • 9. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                          Le�ncio-Oracle
                          hey Ritu,

                          We can not go deep and discuss reasons why this is like this(because we don't have this information), but what we could see into this script is:
                          EXECUTE IMMEDIATE 'ALTER TABLE UPP ADD UPP_DENY_LIST_CLOB CLOB'; ==> to ADD this column
                          ...
                          then
                          EXECUTE IMMEDIATE 'UPDATE UPP SET UPP_DENY_LIST_CLOB = UPP_DENY_LIST'; ==>tells that has the same values as UPP_DENY_LIST.

                          It looks like when you ran, those information were already there.

                          As you can see into this other script you provided:
                          This other example they renamed the other column, if the count is 1 from all_tab_columns. But to make sure that you will not have any issue, they drop UPP_ALLOW_LIST column first.

                          EXECUTE IMMEDIATE 'ALTER TABLE UPP DROP COLUMN UPP_ALLOW_LIST';
                          ....
                          EXECUTE IMMEDIATE 'ALTER TABLE UPP RENAME COLUMN UPP_ALLOW_LIST_CLOB TO UPP_ALLOW_LIST';

                          So, my guess the problem you are facing is related of these statements. III suggest you to ask Oracle thru Service Request , so they can help you to fix this quickly.

                          I hope this helps,
                          Thiago Leoncio.
                          • 10. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                            387641
                            Ritu ,

                            This is such a long time you got this issue.

                            I am sure you might have got it resolved.


                            I am getting exactly same error do you by any chance remember what you might have done ?

                            Would appreciate if you could respond with the solution.


                            Dilip

                            Edited by: dilipnimse on Mar 22, 2013 3:31 PM
                            • 11. Re: Error during OIM schema upgrade during product ugprade from 9.1.0.2 to 11g
                              930888

                              Hi All,

                               

                              We are also trying to upgrade OIM 9.1.0.2 which is present in our environment to OIM11g. Request your help.

                               

                              Now how do we achieve the upgrade? Should my source of OIM9i software  and target of OIM 11g software  be installed on the same server?

                               

                              I did install OIM11g and OIM9i on the same server  and tried to do schema upgrade based on the link:

                               

                              Upgrading Oracle Identity Manager Environment

                               

                              But during examine phase I get error the below error

                               

                              UPGAST 00091- ERROR DURING EXAMINE OF COMPONENETS

                              UPGAST 01004- UPGRADE IS UNABLE TO DYNAMICALLY LOAD COMPONENET SPECIFIC SOFTWARE MODULES.

                               

                              When i check in the log in internet it says :

                               

                              Cause: Repository upgrade framework was unable to dynamically load the Java classes of its component-specific software modules. mrua.xml file might have been corrupted.


                              Action: This is an internal error. Verify that the Java classpath has been correctly set, the mrua.xml file is correct, and that the .jar files for all component-specific software modules are present in the jlib directory. Contact Oracle Support Services for assistance


                              What is the javaclasspath to be set?how to check if mrua.xml and jar files are fine


                              Pls help.Any help is much appreciated.