How to set up a test environment for RTC 3.0.1 with ORACLE? (error when upgrading to 4.0)
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)
atetc...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
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.
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.
Comments
4 other answers
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
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
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