Posted: 3 Aug 2017 8:53 EDT Last activity: 9 Aug 2017 12:23 EDT
Regarding Data Schema Upgrade
We have already upgrade our Dev environment successfully(using appendix C approach of upgrade guide) and now we are planning to upgrade our SIT environment, so for this we are doing below high level steps:
1. Creating two blank schema one for Data and one for Rule
2. Upgrading rule schema using data pump method we are importing already upgraded complete rule schema from the Dev environment to SIT environment.
3. For Data schema we are creating all the object(tables, view, function etc) from the meta data of Dev(which is already upgarded) and afterward populating the tables again using data pump method from old schema of SIT environment to this new data schema.
So now my question as we are creating the object by getting the DDL from already upgraded dev environment, so do we need to run the upgrade.sh in the SIT environment again, as per me we only need to run this if this scripts is doing any DML operation. So is this upgrade.sh is doing any DML operation?
***Updated by moderator: Lochan to add Categories***
To become doubly sure I am asking question again, as the article you have pointed me to says that they doing Data schema upgrade again in next environment.
As you suggested we don't have to upgrade the data schema again as we are building all the objects using the meta data of the already upgraded lower environment just pushing the data into the created objects(using meta data of lower environment) will work. If my understanding is correct then why we need to truncate any table as we are not running ugrade.sh --dataonly in this higher environment
Just to summarize you are planning on exporting upgrades rules schema from lower environment and importing into higher environment using Oracle datapump. This will be both structure and data. For the data schema you are planning on exporting just the table structures, stored procedures, etc. but not any of the data from upgraded DEV and then populated the these tables by importing existing SIT data schema. Is that correct?
I think this approach could lead to issues. There will be table structure changes between old data schema and new schema that could lead to issues trying to import old data.
Better option would be to just move upgraded rules schema to SIT, truncate a couple of tables, and run dataOnly upgrade. You didn't mention what version you are upgrading from or to. If there are schema name changes then you will probably want to run the migrate script to recreate the rules schema objects and connections between data and rules schemas.
Typically you will want to truncate pr_data_products and pr_data_restore as the pc_data_uniqueid table in the data schema will be out of sync with the contents of these tables from any hot fixes that were installed in lower environment.