Unable to bring up pega application post data refresh
We have split schema rules_a and worker(data) for our application and in pega 7.2.2, we dropped all the objects from both rules and data schema and imported the dump from the working env, we verified, post import all the object count between source and target matched.
From WAS console, we tested the connectivity of the datasource, it works fine, checked the namespace binding, all good.
But when we tried to start the application server, in the start up log we see below error in which it couldnt find the table.
As per other article in pega community, we checked the prengineclasses table and we see records exists and highest code set version is 07-10-27. Not sure why pega unable to find the table and giving an message table dint exist, although the datasource connectivity from WAS console works fine.
[6/16/18 7:46:00:533 EDT] 0000006f PRBootstrap Z com.pega.pegarules.internal.bootstrap.PRBootstrap Engine classes schema: RSM_RULES_B_D7
[6/16/18 7:46:00:534 EDT] 0000006f PRBootstrap Z com.pega.pegarules.internal.bootstrap.PRBootstrap Assembled classes schema: RSM_RULES_B_D7
[6/16/18 7:46:00:534 EDT] 0000006f PRBootstrap Z com.pega.pegarules.internal.bootstrap.PRBootstrap prbootstrap.properties merged with prbootstrap entries in Data-Admin-System-Settings
[6/16/18 7:46:00:548 EDT] 0000006f PRBootstrap Z com.pega.pegarules.internal.bootstrap.PRBootstrap PRBootstrap.calculateEngineCodeVersionFromDB() - unable to calculate codeset version from database: ORA-00942: table or view does not exist
[6/16/18 7:46:00:549 EDT] 0000006f PRMiniLoader Z com.pega.pegarules.internal.bootstrap.PRMiniLoader Will load phase 2 bootstrap from Pega-EngineCode:
[6/16/18 7:46:00:551 EDT] 0000006f PRMiniLoader Z com.pega.pegarules.internal.bootstrap.PRMiniLoader PRMiniLoader - unable to load classes from the database: ORA-00942: table or view does not exist
What are the names of your rules and data schemas? Within your jdbc/PegaRULES datasource in WebSphere what database user is being used to run the system?
From the log snippet you have posted looking in RSM_RULES_B_D7. If this is your actual rules schema name then the database user that is defined in the datasource does not have the permissions to actually access the tables in this schema (and maybe the data schema).
RSM_RULES_B_D7 is the rules schema and RSM_WORKER_D7 is the Data schema, and the DB User fid for both the schema are of same name RSM_RULES_B_D7 & RSM_WORKER_D7 respectively.
When we try to access some of the table objects from sql developer using the DB USer, we are able to access them, we are not sure, why you say they does not have the permission to actually access the table when we connect from WAS console, although the test connectivity works perfectly fine.
The database is throwing an ORA-00942 error when we try to query the pr_engineclasses table. Assuming the table actually exists, either the query is not schema qualifying the call correctly or the database user who is running the query does not have permissions to actually see that table.
Within your app server there is a single datasource, jdbc/PegaRULES which is actually making the database connection and running the system. What database user is that? Typically it could be the data schema user as during the install/upgrade it would have been granted all of the privileges it needs on the rules schema. You could also use a different db user who is not the schema user who has been granted privileges on everything in the data and rules schemas.
For whatever database user that is defined in the jdbc/PegaRULES datasource outside of pega connect to the database with that user and run this query:
SELECT count(*), pzcodesetversion, pzcodeset FROM RSM_RULES_B_D7.PR_ENGINECLASSES GROUP BY pzcodesetversion, pzcodeset
I ran the query given by you "SELECT count(*), pzcodesetversion, pzcodeset FROM RSM_RULES_B_D7.PR_ENGINECLASSES GROUP BY pzcodesetversion, pzcodeset"
26671 06-01-20 pega-enginecode
111738 07-10-17 pega-enginecode
44338 06-03-10 pega-enginecode
143931 07-10-27 pega-enginecode
When you ran this query what database user did you use? On your application server you should have a datasource for jdbc/PegaRULES. In that datasource you define what database user should be used to run this system. That database user needs to have privileges to insert/update/delete/select/execute the tables, stored procedures, etc. in the data and rules schema. As part of the install the data schema user should have been granted the correct privileges to access its own schema and what is needed in the rules schema. You can use the data schema user as your base database user in jdbc/PegaRULES. Or you can define a different database user to use. The specific privileges needed are in the deployment guide. Besides the CRUD operations on data and rules schema the user also needs SELECT_CATALOG_ROLE on Oracle.