Posted: 24 Dec 2019 11:32 EST Last activity: 27 Dec 2019 14:05 EST
Pega 8.2 Upgrade.sh execution fails due to ORA-00942: table or view does not exist
We are upgrading Pega 6.3 platform to Pega 8.2. We followed below steps.
Pega 6.3 platform has single schema. Split current schema as rule schema and data schema(Current schema).
Create DB users with privileges
Run migrate.sh. Step was success.
configure setupDatabase.properties with bypass schema generation and UDF generation as true.
Run upgrade.sh without any parameter
Execution was failed due to following error message.
sql=SELECT pzInsKey AS "pzInsKey", pxObjClass AS "pxObjClass" FROM <Rule schema name>.pi_data_connect WHERE pxObjClass = 'Data-Admin-Connect-DSM-EventCatalog', OriginalSql = SELECT pzInsKey AS "pzInsKey", pxObjClass AS "pxObjClass" FROM <Rule schema name>.pi_data_connect WHERE pxObjClass = 'Data-Admin-Connect-DSM-EventCatalog', Error Msg = ORA-00942: table or view does not exist
For the time being I copied pi_data_connect table in data schema to rule schema and continue upgrade script execution. Finally we created pi_data_connect, pr_data, pr_data_file, pr_index, pr_operators, pr_sys_locks, pr_sysstatusnodes, pr_sys_updatecache, pr_data_sample_uidetails, pr_data_samplesrcustomer, pr_data_samplesrproduct, pr_data_samplesrpurchase, pr_data_sample_shoppingcart tables with data in rule schema to continue upgrade.
Please let me know, anyone face this issue before? Is this proper way to continue upgrade?
***Edited by Moderator Marissa to update SR Details***
It seems issue reported due to migration from single schema to split schema and bypass DDL generation.Did you verify pi_data_connect table exists in rule schema or not?
You need to run DDLs manually to create all the tables or move tables to correct schema.As mentioned above you have already done that step so I think your upgrade would be successful.Please let me know if you have faced any other issue after that.
Since you are upgrading from 6.x to 8.x so there is a high probability of such issues.I think this happened due to configuration mistake. Did you observe any script which migrates these tables to rules schema.Also Did you provide rules schema name in that script.
I'm not aware of any SR related to such issue. These issues may come due to config mistakes.
pega.source.rules.schema=single schema name which is going to be data schema after upgrade
It means your existing schema would be data schema after upgrade.Since those tables are present in existing schema ,which becomes data schema therefore your are getting that error.Can you interchange rules & data schema & then verify.I mean make your existing schema as rules schema.
1. Migrate rules tables and pr_data_admin table to new rules schema
source rules and data schemas are the existing single schema. Target rules and data are the new rules schema. If you don't set data schema it will just use rules.
2. Upgrade rules schema, rules and data schema are new rules schema. There are ddl changes needed for this upgrade so if you want to bypass schema changes you need to run the generateddl script to apply the changes manually. As part of this step dummy data tables will be created in the new rules schema
3. Run migrate script again to create connections between rules and data schemas and rules schema objects. This is the first time that the target data schema needs to be set to what will actually be your data schema in the upgraded environment. Source is new rules schema, target is new rules schema and existing single schema which will be the data schema in upgraded environment.
4. Upgrade data schema. Again there will be data schema changes needed so if you want to bypass the upgrade doing them automatically you need to use the generateddl script to generate the script to be applied manually.
The problem is not with the migrate but with the upgrade of the rules schema.
In setupDatabase.properties you should have both rules and data schemas pointing to the new rules schema that you have migrated the rules tables too. In order to actually run this upgrade you need to generate and apply the changes needed to upgrade the rules tables and it will also generate dummy tables in the data schema. You can either do this manually by running the generateddl script and applying the schema changes or you can have the upgrade script do it for you. Per your setupDatabase.properties file you have set bypass.pega.schema to true which indicates that you ran the generateddl and applied the output manually but that is not the case as the dummy data tables do not exist in your new rules schema. If you don't want to have to run this process manually change the bypass.pega.schema to false and the upgrade script will do it for you. These dummy data tables will get cleaned up in a post upgrade cleanup task but they are needed for this step in the upgrade process.