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
- Configure migrateSystem.properties
- 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***
Pegasystems Inc.
IN
Hi Harshana,
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.
Thanks,
Abhinav
EVONSYS
LK
Hi Abhinav,
Thank you for the reply.
Error message came when I ran the upgrade.sh. If those tables are required in upgrade script to run, It must be migrated to rule schema first. To run upgrade script, I created tables in rule schema.
I did below configuration in migrateSystem.properties file for operations.
pega.move.admin.table=true
pega.clone.generate.xml=true
pega.clone.create.ddl=true
pega.clone.apply.ddl=true
pega.bulkmover.unload.db=true
pega.bulkmover.load.db=true
pega.rules.objects.generate=false
pega.rule.objects.apply=false
This configuration creates tables and move records at the end of migration. I reviewed the migration log file. Those tables were not created.
My questions are, why those tables didn't migrate to rule schema? Is there a file in script folder which has list of tables are migrating to rule schema? If it is a known issue, what is the SR number?
Thanks,
Harshana
Pegasystems Inc.
IN
Hi Harshana,
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.
Thanks,
Abhinav
EVONSYS
LK
Hi Abhinav,
Below configuration I have done in migrateSystem.properties.
# Source properties configuration
pega.source.jdbc.driver.jar=full path/ojdbc8.jar
pega.source.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.source.database.type=oracledate
pega.source.jdbc.url=database URL
pega.source.jdbc.username=Deployment user name
pega.source.jdbc.password=password
pega.source.rules.schema=single schema name which is going to be data schema after upgrade
pega.source.data.schema=
# Target properties configuration
pega.target.jdbc.driver.jar=full path/ojdbc8.jar
pega.target.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.target.database.type=oracledate
pega.target.jdbc.url=database URL
pega.target.jdbc.username=Deployment user name
pega.target.jdbc.password=password
pega.target.rules.schema=blank schema name which is going to be rules schema
# Operation properties configuration
pega.move.admin.table=true
pega.clone.generate.xml=true
pega.clone.create.ddl=true
pega.clone.apply.ddl=true
pega.bulkmover.unload.db=true
pega.bulkmover.load.db=true
pega.rules.objects.generate=false
pega.rule.objects.apply=false
Hi Abhinav,
Below configuration I have done in migrateSystem.properties.
# Source properties configuration
pega.source.jdbc.driver.jar=full path/ojdbc8.jar
pega.source.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.source.database.type=oracledate
pega.source.jdbc.url=database URL
pega.source.jdbc.username=Deployment user name
pega.source.jdbc.password=password
pega.source.rules.schema=single schema name which is going to be data schema after upgrade
pega.source.data.schema=
# Target properties configuration
pega.target.jdbc.driver.jar=full path/ojdbc8.jar
pega.target.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.target.database.type=oracledate
pega.target.jdbc.url=database URL
pega.target.jdbc.username=Deployment user name
pega.target.jdbc.password=password
pega.target.rules.schema=blank schema name which is going to be rules schema
# Operation properties configuration
pega.move.admin.table=true
pega.clone.generate.xml=true
pega.clone.create.ddl=true
pega.clone.apply.ddl=true
pega.bulkmover.unload.db=true
pega.bulkmover.load.db=true
pega.rules.objects.generate=false
pega.rule.objects.apply=false
Hope I haven't done any mistake here. Same configuration is available in upgrade guide.
I couldn't find any script which migrates these tables to rules schema.
Regards,
Harshana
EVONSYS
LK
And setupDatabase.properties configuration is:
# Connection Information
pega.jdbc.driver.jar=full path/ojdbc8.jar
pega.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.database.type=oracledate
pega.jdbc.url=URL of the database
pega.jdbc.username=Deployment user name
pega.jdbc.password=password
rules.schema.name=rules migrated schema
data.schema.name=Single schema name which is going to be data schema
user.temp.dir= Temp directory location
bypass.pega.schema=true
bypass.udf.generation=true
rebuild.indexes=true
update.existing.applications=true
update.applications.schema=true
run.ruleset.cleanup <deault value is false. We are manually running clean up script later>
automatic.resume=true
We are upgrading Dev. To make the process fast, we don't want to run any script manually except cleanup.
Pegasystems Inc.
IN
Hi Harshana,
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.
Thanks,
Abhinav
EVONSYS
LK
Hi Abhinav,
This is wrong because source cannot be blank schema in migration. If I am correct, target would be rule schema and source would be data schema.
Regards,
Harshana
EVONSYS
LK
Hi Abhinav,
We are performing the out-of-place upgrade with the single migration. Please refer pg33-39.
https://community.pega.com/knowledgebase/documents/pega-platform-82-upgrade-guide-ibm-websphere-and-oracle
Regards,
Harshana
Pegasystems Inc.
US
Upgrade process is the following:
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.
Pegasystems Inc.
US
If you open a Support Request with Global Client Support - please mention this PSC post and then let us know the SR ID here so that we may keep track for you!
Thanks!
EVONSYS
LK
@MarissaRogers, Sure I will share the SR ID.
Pegasystems Inc.
US
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.
bypass.pega.schema=true
bypass.udf.generation=true