Doing a TEST upgrade QC11->QC12.2 (Oracle), 1st time worked, now getting 28 ”extra” tables?

  • Questions
  • Doing a TEST upgrade QC11->QC12.2 (Oracle), 1st time worked, now getting 28 ''extra'' tables?
Question ID: 106322
0
0

A few weeks ago, we stood up a TEST environment to test the upgrade from 11 to 12.2 (including a SEPARATE instance of Oracle).
We successfully upgraded the qcsiteadmin_db and were able to migrate and upgrade a major project we were interested in.

The other day, we were asked to migrate/upgrade the project again (to get latest data), so I asked my DBA to get the previous night’s Schema backup and to restore it on top of the existing schema-user and data.
We re-copied the project’s repository as well.

Then, we edited the DBID.xml to point to the NEW DB server and repository locations as usual, and RESTOREd the project.

When we ran VERIFY, it lists 28 "extra" tables — they look like they could be QC-related, and on examinations/comparison with the Schema in 11 and with a new/blank project-schema in 12.2, they ARE new tables from 12.2 !

How did they get into our migrated project Schema?

Can we just delete them and try VERIFY again?

Marked as spam
Posted by (Questions: 185, Answers: 13)
Asked on August 10, 2015 9:33 pm
2 views
Answers (1)
0
Private answer

When migrating project DB and Repository to a NEW server, it is best practice to DELETE all previous copies prior to copying/restoring so as to avoid remnant data or files.

Some project upgrades take a few rounds of restoring the DB and repository/RESTORE to QC, then running VERIFY (and possibly REPAIR and UPGRADE). If VERIFY requires some MANUAL DB fixes, you can do them on the copy, but if REPAIR fails, you need to address the reason for failure on a NEW copy of the DB (restored from backup), then RESTORE and run VERIFY/REPAIR again. Also, once REPAIR starts running, it is possible for repair to alter/upgrade part of your Repository, so you would need a good copy of the repository again, too.

In ORACLE, the best practice when re-doing a Schema-User migration for upgrade would be to DELETE the Schema-User and included ''objects'' prior to importing the backed up schema with the same name.
This deletion is called a ''cascade'' delete option and you should use it to ensure ALL child objects (e.g. tables etc) are gone for this schema-user as well as the schema-user.

Your DBA only deleted the Schema-User and did NOT use the ''cascade'' option to also delete all objects (tables) associated with that User. So, since the UPGRADED schema has more tables and some different structure than 11 did, when restoring the backup it overwrote many tables, and added columns to them, but VERIFY later found ''extra'' table and columns or column changes.

These were ''leftovers'' or an artifact from copying an OLD schema over the top of a NEW schema with the same name - this could only happen in ORACLE because it treats the ''objects'' (tables and columns) the way it does.

BEST PRACTICE:

1) When re-migrating a project during an upgrade (or test upgrade), make sure to DELETE the entire Project DB/Schema (and in Oracle the schema-user) first.

2) It is also a good idea to DELETE and re-copy the project-repository for any re-migration as well.

Marked as spam
Posted by (Questions: 3, Answers: 466)
Answered on August 10, 2015 9:50 pm