Upgrading CA Strong Authentication from 8.1 9.0

[Legacy User]

While performing upgrade from 8.1 to 9.0 i am running the oracle dbscript for upgrade

The db-config-for-common-8.x-to-9.0.sql run fine 

Instead, while running the db-config-for-strongauth-8.x-to-9.0.sql i got an error:

Error starting at line : 550 in command -
BEGIN DECLARE num_rows NUMBER := 0;
BEGIN

SELECT count(*)
INTO num_rows
FROM user_constraints
WHERE constraint_name = 'PK_ARWFSEQUENCE';
IF (num_rows = 1) THEN -- IF CONSTRAINT EXISTS, DROP IT
EXECUTE IMMEDIATE 'ALTER TABLE ARWFSEQUENCE DROP CONSTRAINT
PK_ARWFSEQUENCE';
END IF;
EXECUTE IMMEDIATE 'ALTER TABLE ARWFSEQUENCE ADD CONSTRAINT PK_ARWFSEQUENCE
PRIMARY KEY (SEQUENCENAME, INSTANCENAME)';
END;
END;
Error report -
ORA-00955: name is already used by an existing object
ORA-06512: at line 11
00955. 00000 - "name is already used by an existing object"
*Cause:
*Action: 

 

 

This is the snippet of the scripts that cause the error:

BEGIN DECLARE num_rows NUMBER := 0;
BEGIN

SELECT count(*)
INTO num_rows
FROM user_constraints
WHERE constraint_name = 'PK_ARWFSEQUENCE';
IF (num_rows = 1) THEN -- IF CONSTRAINT EXISTS, DROP IT
EXECUTE IMMEDIATE 'ALTER TABLE ARWFSEQUENCE DROP CONSTRAINT PK_ARWFSEQUENCE';
END IF;
EXECUTE IMMEDIATE 'ALTER TABLE ARWFSEQUENCE ADD CONSTRAINT PK_ARWFSEQUENCE PRIMARY KEY (SEQUENCENAME, INSTANCENAME)';
END;
END;

 

 

Is this something already seen before? help on how to fix

 

Best Regards

Claudio

[paiot01]

Hi Claudio,

I am working in an upgrade from 8.0 to 9.0.
I got numerous errors like yours and also some different.
Examples:

 

===========================================================================================
Error report -
ORA-01430: column being added already exists in table
ORA-06512: at line 8
01430. 00000 - "column being added already exists in table"
*Cause:
*Action:
Error starting at line : 553 in command -
BEGIN DECLARE num_rows NUMBER := 0;
BEGIN

SELECT count(*)
INTO num_rows
FROM user_constraints
WHERE constraint_name = 'PK_ARWFSEQUENCE';
IF (num_rows = 1) THEN -- IF CONSTRAINT EXISTS, DROP IT
EXECUTE IMMEDIATE 'ALTER TABLE ARWFSEQUENCE DROP CONSTRAINT PK_ARWFSEQUENCE';
END IF;
EXECUTE IMMEDIATE 'ALTER TABLE ARWFSEQUENCE ADD CONSTRAINT PK_ARWFSEQUENCE PRIMARY KEY (SEQUENCENAME, INSTANCENAME)';
END;
END;
=========================================================================================== 
Error report -
ORA-00955: name is already used by an existing object
ORA-06512: at line 5
00955. 00000 - "name is already used by an existing object"
*Cause:
*Action:

Error starting at line : 369 in command -
BEGIN DECLARE v_table_exists NUMBER := 0;
BEGIN
SELECT COUNT(1) INTO v_table_exists FROM user_tables where table_name='ARWFVERIFIEDCHALLENGES_AR';
IF (v_table_exists = 0) THEN
EXECUTE IMMEDIATE 'CREATE TABLE ARWFVERIFIEDCHALLENGES_AR (
DMDV INTEGER NOT NULL,
CHALLENGEID VARCHAR2(128) NOT NULL,
ORGNAME VARCHAR2(256),
USERREFID INTEGER,
VALIDITYSTARTDATE DATE,
VALIDITYENDDATE DATE
-- CONSTRAINT PK_ARWFVERIFIEDCHALLENGES PRIMARY KEY (CHALLENGEID)
)';
END IF;
END;
END;
===========================================================================================
Error report -
ORA-02260: table can have only one primary key
ORA-06512: at line 11
02260. 00000 - "table can have only one primary key"
*Cause: Self-evident.
*Action: Remove the extra primary key.
Error starting at line : 569 in command -
BEGIN DECLARE num_rows NUMBER := 0;
BEGIN

SELECT count(*)
INTO num_rows
FROM user_constraints
WHERE constraint_name = 'UN_ARWFPROTOCONFIG_INSTIP_PORT';
IF (num_rows = 1) THEN -- IF CONSTRAINT EXISTS, DROP IT
EXECUTE IMMEDIATE 'ALTER TABLE ARWFPROTOCOLCONFIGURATION DROP CONSTRAINT UN_ARWFPROTOCONFIG_INSTIP_PORT';
END IF;
EXECUTE IMMEDIATE 'ALTER TABLE ARWFPROTOCOLCONFIGURATION ADD CONSTRAINT UN_ARWFPROTOCONFIG_INSTIP_PORT UNIQUE (INSTANCEID, HOSTNAME, PORTNO)';
END;
END;
========================================================================================== 
Error report -
ORA-02261: such unique or primary key already exists in the table
ORA-06512: at line 11
02261. 00000 - "such unique or primary key already exists in the table"
*Cause: Self-evident.
*Action: Remove the extra key.
20 rows updated.
committed.
session SET altered.
Error starting at line : 13 in command -
===========================================================================================

I opened a support case and they told me, as soon as the "arwfutil vdb" command works fine, bringing all tables and that tests over the new upgraded environment works fine, this error messages that tells that "something already exists in table" can be ignored.
If you have problems testing your new upgraded environment or if you are not comfortable with it, I recommend you to open a case in the support so they can help you to validated it.

 

Regards,

Otavio.