Automic Workload Automation

Expand all | Collapse all

Upgrade error

  • 1.  Upgrade error

    Posted 09-18-2020 02:36 PM
    I am upgrading from 11.2 to 12.3.  

    I'd like to know if the upgrade is restartable?

    The issue:
    the first abort was because there was a file called "Set_privileges_access_to_messages.sql" that was in the oracle/12.0 folder that was not in the oracle/12.1 folder.  So I copied the file to 12.1, 12.2 and 12.3 folders and restarted.  I think that the upgrade creates a check file that has the date and time on it, so I thought that it may be a restart point.  it ran and got through 12.1 and 12.2 but blew up in 12.3 with this error.
    ___________________________________________________________________________________________________

    20200918/141300.426 - U00038042 ../db/oracle/12.3/steps/step_007_noilm.sql

    20200918/141300.426 - U00038042 ../db/oracle/12.3/steps/step_007.sql

    20200918/141300.427 - U00038042

     

    declare i integer;

    begin

        select count(index_name) into i from user_indexes where table_name = 'OT' and upper(index_name) = upper('NK_OT_SEARCH_NC');

        if i > 0 then

            EXECUTE IMMEDIATE 'DROP INDEX NK_OT_SEARCH_NC';

        end if;

        EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX';

    END;

     

     

    20200918/141300.455 - U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: 1b849e0  DB-HDBC: 1c23118

    20200918/141300.455 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'

    20200918/141300.455 - U00003592 UCUDB - Status: '' Native error: '2429' Msg: 'ORA-02429: cannot drop index used for enforcement of unique/primary key

    ORA-06512: at line 7'

    20200918/141300.455 - U00003594 UCUDB Ret: '3590' opcode: 'EXEC' SQL Stmnt: '
    _____________________________________________________________________________________________


    Has anyone seen this before and know what I should do?  I will have the DBA restore again, but is there anything I should do before rerunning?




  • 2.  RE: Upgrade error

    Broadcom Employee
    Posted 09-23-2020 10:28 AM

    Tl;dr: I'd recommend a restore, just to be safe.

    The first error is known and will be fixed: https://knowledge.broadcom.com/external/article?articleId=197060

    The second error, from what I can tell, wouldn't be related to hitting that first error. It's doing (and you could manually mimic):

    select count(index_name) into i from user_indexes where table_name = 'OT' and upper(index_name) = upper('NK_OT_SEARCH_NC'); 
    -- this is returning a non-zero result, so it is running:
     EXECUTE IMMEDIATE 'DROP INDEX NK_OT_SEARCH_NC';
    -- this is what is failing.

    I haven't seen that offhand in the upgrade, but it would be a db-level constraint being present. I'd really want to see if that happened on a fresh upgrade attempt. 




  • 3.  RE: Upgrade error

    Posted 09-23-2020 06:39 PM
    Hi @Darren Sniezak,

    I did a refresh and it did it again in the same spot.  Its gets all the way through from 11.2 to 12.2.

    Where the index and the primary key created together?  If they were created separately wouldn't that throw this error?
    ______________________________
    20200923/174021.482 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: ea3480 DB-HDBC: f03078
    20200923/174021.483 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
    20200923/174021.483 - U00003592 UCUDB - Status: '' Native error: '2429' Msg: 'ORA-02429: cannot drop index used for enforcement of unique/primary key
    ORA-06512: at line 7'
    20200923/174021.483 - U00003594 UCUDB Ret: '3590' opcode: 'EXEC' SQL Stmnt: '

    declare i integer;
    begin
    select count(index_name) into i from user_indexes where table_name = 'OT' and upper(index_name) = upper('NK_OT_SEARCH_NC');
    if i > 0 then
    EXECUTE IMMEDIATE 'DROP INDEX NK_OT_SEARCH_NC';
    end if;
    EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX';
    END;

    '
    20200923/174021.483 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-02429: cannot drop index used for enforcement of unique/primary key
    ORA-06512: at line 7'
    20200923/174021.483 - U00038089 Error: Problem during execution of SQL command:
    '

    declare i integer;
    begin
    select count(index_name) into i from user_indexes where table_name = 'OT' and upper(index_name) = upper('NK_OT_SEARCH_NC');
    if i > 0 then
    EXECUTE IMMEDIATE 'DROP INDEX NK_OT_SEARCH_NC';
    end if;
    EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX';
    END;

    '
    20200923/174021.483 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-02429: cannot drop index used for enforcement of unique/primary key
    ORA-06512: at line 7'
    20200923/174021.483 - U00038068 ABORTING due to error.

    ________________________


  • 4.  RE: Upgrade error

    Broadcom Employee
    Posted 09-28-2020 10:11 AM

    It is created after the table's creation, but that index shouldn't have anything directly with PK:

    CREATE TABLE OT(
    OT_OH_Idnr NUMBER(38,0) NOT NULL,
    OT_Type NUMBER(38,0) NOT NULL,
    OT_Lnr NUMBER(38,0) NOT NULL,
    OT_Content CLOB NULL,
    CONSTRAINT PK_OT PRIMARY KEY (
    OT_OH_Idnr, OT_Type, OT_Lnr
    ) using index TABLESPACE UC4_INDEX,
    CONSTRAINT FK_OT_OH FOREIGN KEY (
    OT_OH_Idnr
    ) REFERENCES OH (
    OH_Idnr
    )
    ) LOB(OT_Content) STORE AS(ENABLE STORAGE IN ROW CACHE) TABLESPACE UC4_DATA;

    I'd probably, at that point, look at dropping the constraints and running the:

    EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX';

    Outside of the upgrade




  • 5.  RE: Upgrade error

    Posted 30 days ago
    I'm refreshing the DB now.  Would I drop the constraint prior to starting the upgrade and allow the step_007.sql to create the table?  Maybe I'm lost, (not a dba)...

    I did drop the constraint "ALTER TABLE OT DROP CONSTRAINT PK_OT" and restarted the upgrade. It appears to have picked up on step007.sql and completed with a code "0", but it's not starting up.  I get the standard memory dump error "Error while checking data source".

    The DBA here said to drop it before starting the upgrade and that step007.sql would create it, but I'd like a confirmation as this is driving me crazy and how to proceed.

     



  • 6.  RE: Upgrade error

    Broadcom Employee
    Posted 29 days ago
    I would drop it before - but even without the constraint in place, that wouldn't have anything to do with error checking data source; something else has to be causing that.

    After the upgrade, did the DB recreate the constraint? I would assume so.


  • 7.  RE: Upgrade error

    Posted 29 days ago
    I think it did, but we are refreshing again due to the data source error.  So my steps are going to be droping the constraint and running the upgrade for 11.2 to 12.3 again.  Hopefully it will upgrade without error and recreate the constraint and I should be able to start with no data source error.  I will respond again after I do these steps.


  • 8.  RE: Upgrade error

    Posted 29 days ago
    So the refresh has not happend yet, so I checked to see if the constraint was created by the restart of the upgrade and it was not.

    SQL> SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE owner='UC4' and table_name = 'OT';

    SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE owner='UC4' and table_name = 'OT'

                                                                    *

    ERROR at line 1:

    ORA-00942: table or view does not exist

    so I assume I should run the execute:
    EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX';



    So, do I run 




  • 9.  RE: Upgrade error

    Posted 29 days ago
    It didn't like that.

    SQL> EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX'

    BEGIN IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX'; END;

     

                    *

    ERROR at line 1:

    ORA-06550: line 1, column 17:

    PLS-00103: Encountered the symbol "CREATE INDEX NK_OT_SEARCH_NC ON OT

    (OT_OH_IDNR, OT_TYPE, OT_LNR,

    substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1," when expecting

    one of the following:

    := . ( @ % ;

    The symbol ":=" was substituted for "CREATE INDEX NK_OT_SEARCH_NC ON OT

    (OT_OH_IDNR, OT_TYPE, OT_LNR,

    substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1," to continue.

     
    SQL>




  • 10.  RE: Upgrade error

    Broadcom Employee
    Posted 28 days ago

    What would:

    CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX

    Return (without the begin/end)




  • 11.  RE: Upgrade error

    Posted 27 days ago
    Hey Darren,

    Yes, that is what I did.  I think in sqlplus it added that BEGIN/END to show me what command I was processing.  I entered...

    EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX'

    at the "sql>" prompt


    I worked with a DBA and this is what we came up with to replace the sql script in step_007.sql.
    ____________________________________________________

    declare i integer;

    begin

        select count(index_name) into i from user_indexes where table_name = 'OT' and upper(index_name) = upper('NK_OT_SEARCH_NC');

        if i > 0 then

            EXECUTE IMMEDIATE 'ALTER TABLE "UC4"."OT" DROP CONSTRAINT PK_OT';

            EXECUTE IMMEDIATE 'DROP INDEX NK_OT_SEARCH_NC';

            EXECUTE IMMEDIATE 'CREATE INDEX NK_OT_SEARCH_NC ON OT (OT_OH_IDNR, OT_TYPE, OT_LNR, substr(UPPER("SYS"."DBMS_LOB"."SUBSTR"("OT_CONTENT",2000,1)),1,2000)) TABLESPACE UC4_INDEX';

            EXECUTE IMMEDIATE 'ALTER TABLE "UC4"."OT" ADD CONSTRAINT "PK_OT" PRIMARY KEY ("OT_OH_IDNR", "OT_TYPE", "OT_LNR") USING INDEX "UC4"."NK_OT_SEARCH_NC"  ENABLE';

        end if;

    end;

    ______________________________________________________

    I hope it works. 

    Does anything jump out at you?