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) 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,

1 vote

5 answers

7,376 views

1 vote

Accepted answer

Permanent link

Rafik Jaouani

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

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

1 vote

Comments

mike brosnan

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

JAZZ DEVELOPER Aug 17 '12, 10:42 a.m.

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

Permanent link

Rafik Jaouani

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

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.

1 vote

Comments

Leonardo Marzo

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

JAZZ DEVELOPER Aug 03 '12, 3:49 p.m.

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

Leonardo Marzo

Aug 06 '12, 9:13 a.m.

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

Rafik Jaouani

JAZZ DEVELOPER Aug 08 '12, 9:15 a.m.

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) 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

0 votes

Permanent link

Leonardo Marzo

(249●6●48●52) 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. "

0 votes

Permanent link

Leonardo Marzo

(249●6●48●52) 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!

0 votes

Comments

mike brosnan

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

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

JAZZ DEVELOPER Aug 17 '12, 10:45 a.m.

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

JAZZ DEVELOPER Aug 17 '12, 10:47 a.m.

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

Your answer

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers

Answers and Comments

Question details

rational-team-concert

× 42,898

clm

× 7,495

installation

× 2,357

× 267

× 42

× 35

× 23

Question asked: Aug 01 '12, 12:33 p.m.

Question was seen: 7,376 times

Last updated: Aug 17 '12, 10:47 a.m.

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

5 answers

7,376 views

1 vote

Accepted answer

Comments

4 other answers

Comments

Comments

Your answer

Follow this question

Question details

Related questions