Upgrade error

[smick]

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?

[Darren Sniezak]

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. 

[smick]

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.

________________________

[Darren Sniezak]

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

[smick]

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.

 

[Darren Sniezak]

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.

[smick]

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.

[smick]

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 

[smick]

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>

[Darren Sniezak]

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)

[smick]

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?

[Darren Sniezak]

Just to follow up on this; what appears to have happened is at some point the primary key was dropped and recreated (https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm):

If an index is present containing the same columns and order as the PRIMARY KEY CONSTRAINT, the new PK will be based on this index (NK_OT_SEARCH_NC in this case). This is done by Oracle to decrease space usage (one index less) and increase performance (one index less to maintain).

The recommended series of SQL to get a table encountering this back to 'default' would be:

ALTER TABLE OT DROP CONSTRAINT PK_OT;
DROP INDEX NK_OT_SEARCH_NC;
ALTER TABLE OT ADD CONSTRAINT PK_OT PRIMARY KEY (OT_OH_Idnr, OT_Type, OT_Lnr) using index TABLESPACE UC4_INDEX ENABLE VALIDATE;
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;