How to set up a test environment for RTC 3.0.1 with ORACLE? (error when upgrading to 4.0)

Leonardo Marzo (249●6●48●52) | asked Aug 01 '12, 12:33 p.m.

Hi people,

We have RTC 3.0.1 (with data warehouse configured) in production. We needed to replicate it in a sandbox environment in order to test the upgrade to 4.0. We are using Oracle 11g as database. We tried to perform the upgrade in the sandbox but it failed in the step repotools-jts -upgradeWarehouse with the following error message:

2012-07-31 11:38:07,275 ALTER TABLE RIODS.EXECRES_REQUEST_LOOKUP ADD CONSTRAINT EXECRGREQ_PK PRIMARY KEY(REQUEST_ID, EXECUTION_RESULT_ID, LINK_TYPE)

com.ibm.team.repository.common.TeamRepositoryException: ALTER TABLE RIODS.EXECRES_REQUEST_LOOKUP ADD CONSTRAINT EXECRGREQ_PK PRIMARY KEY(REQUEST_ID, EXECUTION_RESULT_ID, LINK_TYPE)

at com.ibm.team.datawarehouse.service.AbstractDataWarehouseDbProvider.internalExecuteSql(AbstractDataWarehouseDbProvider.java:247)

at

etc...

etc...

etc...

Caused by: java.sql.SQLException: ORA-00955: name is already used by an existing object.

We configured the data warehouse when we upgraded to 3.0.1. As you know, to configure the data warehouse you need to provide a RPTUSER with DBA privilegies, and the installation setup uses it to create some other users, tablespaces and other objects.

Because of that, I assume that the upgrade to 4.0 fails because we haven't set up the test environment properly

These are the steps that I followed to replicate the DB:

- I created a backup of production db (expdp)

- I followed the steps here to create a new db and all tablespaces and accounts needed

- I executed the following command to import the db backup in the test db:

impdp system/********@SANDBOX

directory=backups

dumpfile=backup_rtc.dmp

logfile=log

schemas=RPTUSER, RICALM, RISCHK, RIASSET, RIDW, RIODS, CONFIG, TEAMCONCERT, JTS, TEAMCONCERT_RID, QM

The import finished without errors, however we're not being able to upgrade to 4.0.

Could you please tell me if the process that we're following to import the backup is correct? If it is correct, why the upgrade script may be failing?

I'll really appreciate any help you may give us.

Regards,

Accepted answer

permanent link

Rafik Jaouani (5.0k●1●6) | answered Aug 02 '12, 1:17 p.m.
JAZZ DEVELOPER

Leonardo, was your Oracle Server version upgraded before running the migration scripts?
I did some research and it seems this problem happens when you try to drop the PRIMARY KEY CONSTRAINT with a newer version of Oracle than the one it was created with. I don't think we had tests that cover that scenario.
As a workaround, can you first migrate the data warehouse before upgrading to a newer Oracle version?

If not, please contact IBM support and they will work on providing a patch for this issue.
The fix should be as you suggested. We will need to append a DROP INDEX to the statement.

Leonardo Marzo selected this answer as the correct answer

Comments

mike brosnan commented Aug 17 '12, 10:25 a.m.

We are using Oracle 11G. We upgraded the DB to 11G before upgrading from 2.0.0.2 to 3.0.1.

We are in the process of upgrading to 4.0. The Jazz and JTS DB's upgraded fine, but we have the same issue with our DW upgrade.

Is there going to be a patch available? We are testing the upgrade sequence in QA, and want to have a repeatable, predictable process we can apply to the production upgrade.

Rafik Jaouani commented Aug 17 '12, 10:42 a.m.

JAZZ DEVELOPER

Hi Mike, we delivered the fix for the upcoming 4.0.0.1 Fixpack. For a patch you will need to contact tech support.

4 other answers

Most liked answers|Newest answers|Oldest answers ↑

permanent link

Rafik Jaouani (5.0k●1●6) | answered Aug 01 '12, 4:27 p.m.
JAZZ DEVELOPER

I doubt that is the cause of the error. If I look at the upgrade scripts, I see the following:

ALTER TABLE RIODS.EXECRES_REQUEST_LOOKUP DROP CONSTRAINT EXECRGREQ_PK;
ALTER TABLE RIODS.EXECRES_REQUEST_LOOKUP ADD CONSTRAINT EXECRGREQ_PK PRIMARY KEY(REQUEST_ID, EXECUTION_RESULT_ID, LINK_TYPE);

The above will drop the existing contraints named EXECRGREQ_PK then will try to create a constraint with the same name.

The error you are getting is saying that the constraint already exists but it should have been deleted by the previous statement.

Please contact customer support and they will give you the sql scripts used to upgrade the data warehouse. They could get you to apply the upgrade manually. That way you can see the exact spot where the error occurs and disgnose the problem.

Comments

Leonardo Marzo commented Aug 03 '12, 2:36 p.m.

Rafik, I've just realized that both databases, production and sandbox are in the same version "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production".

So, the problem is not related with versions. Do you have any idea of what could be the cause of the issue?

Rafik Jaouani commented Aug 03 '12, 3:49 p.m.

JAZZ DEVELOPER

Did you do a dump of your production data to the sandbox to test the upgrade. That could have caused the problem.

Leonardo Marzo commented Aug 06 '12, 9:13 a.m.

Yes, that's what I did. But, why this affects the upgrade?

Rafik Jaouani commented Aug 08 '12, 9:15 a.m.

JAZZ DEVELOPER

Leonardo, I really do not know. This really feels like an Oracle bug to me as opposed to a feature. Please connect with IBM support, so they can provide you with a patch to workaround the problem.

permanent link

Leonardo Marzo (249●6●48●52) | answered Aug 01 '12, 4:44 p.m.
edited Aug 01 '12, 5:20 p.m.

Thanks for your answer Rafik. I tried to drop the constraint manually but I get the following error: "Cannot drop contraint - non existent contraint".

However, if I execute the following query:

select * from all_indexes where index_name = 'EXECRGREQ_PK'

I get this row:

1 RIODS EXECRGREQ_PK NORMAL RIODS EXECRES_REQUEST_LOOKUP TABLE UNIQUE DISABLED VNF_IDX 2 255 10 YES 0 0 0 0 0 0 VALID 0 0 6/30/2012 6:05:39 PM 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE NO

That's strange

permanent link

Leonardo Marzo (249●6●48●52) | answered Aug 02 '12, 10:33 a.m.

I think the DROP script should be like this:

ALTER TABLE RIODS.EXECRES_REQUEST_LOOKUP DROP CONSTRAINT EXECRGREQ_PK DROP INDEX ;

The index of the primary key is not being drop, because of that the add constraint shows the error "name is already used by an existing object. "

permanent link

Leonardo Marzo (249●6●48●52) | answered Aug 02 '12, 1:29 p.m.

You're right Rafik!. We have Oracle 10g in production and 11g in sandbox. I've already contacted IBM customer support, I'll ask them for a fix to setup our sandbox.

Thanks very much!

Comments

mike brosnan commented Aug 16 '12, 12:06 p.m.

We see the same issue on our oracle upgrade.

Did using the command manually fix the issue?

ALTER TABLE RIODS.EXECRES_REQUEST_LOOKUP DROP CONSTRAINT EXECRGREQ_PK DROP INDEX ;

Leonardo Marzo commented Aug 16 '12, 12:53 p.m.

Mike, that script doesn't fix the issue. This is the cause of the problem: since Oracle 10.2 the way impdp does is to create index first and then add the PK constraint, which will not drop the index when you just alter table drop constraint. If you create PK without creating index first, the drop constraint will drop the index too.

I found the following workaround. You should execute something like this for each script that fails:

alter table RIODS.EXECRES_REQUEST_LOOKUP modify constraint EXECRGREQ_PK disable; drop index RIODS.EXECRGREQ_PK; alter table RIODS.EXECRES_REQUEST_LOOKUP modify constraint EXECRGREQ_PK enable;

However, if you have the issue in your production environment, I recommend you to contact IBM support.

Rafik Jaouani commented Aug 17 '12, 10:45 a.m.

JAZZ DEVELOPER

Mike you can try the above for each of the following tables, then run the warehouse upgrade command. But I really would recommend talking to support if this is a production server.

Here is the list of tables that are affected:

RIODS.REQUIREMENT_HIERARCHY RIODS.EXECRES_REQUEST_LOOKUP RIODS.EXECSTEPRES_REQUEST_LOOKUP RIODS.EXECWORKITEM_REQUEST_LOOKUP RIODS.REQUEST_TESTCASE_LOOKUP
RIODS.REQUICOL_TESTPLAN_LOOKUP RIODS.REQUIREMENT_REQUEST_LOOKUP RIODS.REQUIREMENT_TESTCASE_LOOKUP

Rafik Jaouani commented Aug 17 '12, 10:47 a.m.

JAZZ DEVELOPER

let's try the table list again:

RIODS.REQUIREMENT_HIERARCHY,

RIODS.EXECRES_REQUEST_LOOKUP,

RIODS.EXECSTEPRES_REQUEST_LOOKUP,

RIODS.EXECWORKITEM_REQUEST_LOOKUP,

RIODS.REQUEST_TESTCASE_LOOKUP,

RIODS.REQUICOL_TESTPLAN_LOOKUP,

RIODS.REQUIREMENT_REQUEST_LOOKUP,

RIODS.REQUIREMENT_TESTCASE_LOOKUP

It's all about the answers!

Related questions

How to set up a test environment for RTC 3.0.1 with ORACLE? (error when upgrading to 4.0)

Accepted answer

4 other answers

Your answer