SA-69758

Summary

An in-place upgrade from Pega 6.3 (with a single schema) to Pega 8.1 (with a split schema) fails with ORA-00942.

Error Messages

Caused by: com.pega.pegarules.pub.database.DatabaseException: Database-General    ORA-00942: table or view does not exist
     [java]     942    42000    ORA-00942: table or view does not exist
     [java] 
     [java] DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
     [java] 
     [java]  | SQL Code: 942 | SQL State: 42000
     [java] 
     [java] From: (unknown) 
     [java] Caused by SQL Problems.
     [java] Problem #1, SQLState 42000, Error code 942: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
     [java] 
     [java]     at com.pega.pegarules.deploy.internal.archive.importer.ImporterDataModelImpl.getResultSetIteratorForClass(ImporterDataModelImpl.java:173)
     [java]     at com.pega.pegarules.deploy.internal.archive.importer.synchronization.UpgradeRulesSynchronizer.getIterator(UpgradeRulesSynchronizer.java:165)
     [java]     at com.pega.pegarules.deploy.internal.archive.importer.synchronization.AppBundleSynchronizer.synchOnClass(AppBundleSynchronizer.java:81)
     [java]     at com.pega.pegarules.deploy.internal.archive.importer.synchronization.AppBundleSynchronizer.performSynchronization(AppBundleSynchronizer.java:71)
     [java]     ... 15 more
     [java] Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
     [java] 
     [java]     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
     [java]     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
     [java]     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)

Steps to Reproduce

Using this PSC post for an in-place upgrade, https://community1.pega.com/community/product-support/question/upgrading-single-schema-split-schema-single-data-base-approach, take the following steps:

1. Migrate the single schema to a new schema.
2. Run PRPC_Setup.jar with the Rules.schema.name set to a new Rules schema to which Data has been migrated in Step 1 and the data.schema.name set to the old single schema.

Root Cause

A software use or operation error whereby the procedure that users followed for a single-schema to split-schema upgrade is incorrect.
The users consulted a Pega Support Community post. They should have consulted the Pega 8.1 Upgrade Guide for their application server and the Oracle database application, for example, https://community.pega.com/knowledgebase/documents/pega-platform-81-upgrade-guide-apache-tomcat-and-oracle.

For this reported case, users need to perform a Rules upgrade followed by a Data-only upgrade.

Resolution

For this reported case, complete the following steps of the in-place upgrade process.
Find these steps in the Pega 8.1 Upgrade Guide for your application server and the Oracle database, the Appendices.
Example: https://community.pega.com/knowledgebase/documents/pega-platform-81-upgrade-guide-apache-tomcat-and-oracle
Appendices
Migrate script properties
Editing the setupDatabase. properties file

Complete these prerequsites:

1. Start with a single-schema Pega system in a schema called Production.
2. Create an empty schema, called Rules, where the new rules tables will be created.

Complete the following steps of the upgrade process:

1. Set the following properties in migrateSystem.properties:
   
   Set the source connection properties to point to the Production schema
   Set the target connection properties to point to the Rules schema
   Set the pega.bulkmover.directory and pega.migrate.temp.directory to be different directories where migrate system artifacts will be created
   Set pega.move.admin.table=true so that admin tables needed for the upgrade will be moved
   Set pega.clone.generate.xml=true to create an xml representation of the source database
   Set pega.clone.create.ddl=true to generate ddl to create copies of tables in the source system in the target system
   Set pega.clone.apply.ddl=true to appy the ddl generated in the previous step
   Set pega.bulkmover.unload.db=true to export rows from the tables in the source system to the pega.bulkmover.directory
   Set pega.bulkmover.load.db=true to import rows from the pega.bulkmover.directory into tables in the target system
   Set pega.rules.objects.generate=false since this is only required when migrating an upgraded system
   Set pega.rules.objects.apply=false since this is only required when migrating an upgraded system
    
2. Run the migrate.bat or migrate.sh script.
3. Run an upgrade on the Rules schema.
   When you set the Rules and Data schemas in the installer or the setupDatabase.properties file, they should both be set to the Rules schema.
4. Apply the rules objects to the Rules schema using migrateSystem.
   Rules objects are indexes, views, stored procedures, functions and triggers on the rules schema.
   Some of those objects need to point to tables in the Production schema; therefore, they have to be recreated after the upgrade.
   
   Set the following properties in migrateSystem.properties:
   
   Set the target connection properties to point to the Production schema except set the pega.target.rules.schema=Rules
   Set  pega.move.admin.table, pega.clone.generate.xml, pega.clone.create.ddl, pega.clone.apply.ddl, pega.bulkmover.unload.db and pega.bulkmover.load.db to false
   Set pega.rules.objects.generate=true to generate the changes to the Rules schema
   Set pega.rules.objects.apply=true to apply the ddl created in the previous step.
    
5. Run the migrate.bat/sh script.
6. Specify the setupDatabase.properties. Be sure that rules.schema.name=Rules and data.schema.name=Production. 
7. Run the data schema upgrade.
   This process makes the required changes to the Production schema.
   At this point the Production schema should not be used. Any application servers targeting it should be taken down.
   To run the data schema upgrade, run upgrade.bat or upgrade.sh with the arguments "--dataOnly true" .