Sunday, 12 March 2017

Weirdness - The database is not consistent with the BPM Product version

So we saw this issue last week: -

[3/9/17 7:06:35:804 UTC] 00000001 WsServerImpl  E   WSVR0009E: Error occurred during startup
com.ibm.ws.exception.RuntimeError: The database [jndi = jdbc/PerformanceDB] version [8.5.7.201612] is not consistent with the BPM Product version [8.5.7.0], please ensure the database is updated successfully before starting server.


during the build of a new IBM Business Process Manager (BPM) Advanced 8.5.7 Deployment Environment with an Oracle 12c database.

The problem appeared to be an inconsistency between database tables within Oracle and the expectations of the BPM product, specifically the LSW_SYSTEM_SCHEMA tables, of which there are two: -

PSUSER.LSW_SYSTEM_SCHEMA
PDWUSER.LSW_SYSTEM_SCHEMA

So, here's the thing …

These tables are defined when one first builds the BPM Deployment Environment using BPMConfig.sh: -

/opt/ibm/WebSphere/AppServer/bin/BPMConfig.sh -create -de /mnt/ResponseFiles/ Advanced-PS-ThreeClusters-Oracle.properties

specifically, here: -

/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PSCell1.De1/Oracle/orcl/psuser/createSchema_Advanced.sql: psuser.LSW_SYSTEM_SCHEMA WHERE PROPNAME = 'DatabaseSchemaVersion' ; 
/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PSCell1.De1/Oracle/orcl/psuser/createSchema_Advanced.sql: VALUES ('DatabaseSchemaVersion',

and: -

/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PSCell1.De1/Oracle/orcl/pdwuser/createSchema_Advanced.sql: pdwuser.LSW_SYSTEM_SCHEMA WHERE PROPNAME = 'DatabaseSchemaVersion' ; 
/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PSCell1.De1/Oracle/orcl/pdwuser/createSchema_Advanced.sql: VALUES ('DatabaseSchemaVersion',

thanks to fgrep : -

fgrep -R DatabaseSchemaVersion /opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/

Note that the exception related to the Performance Data Warehouse (PDW) database/schema, because we were starting the SupCluster, within which the PDW applications run.

When we saw this issue last week, the problem was that the tables in Oracle contained the value: -

8.5.7

( namely three columns / digits ), as evidenced: -

select * from PSUSER.LSW_SYSTEM_SCHEMA;

PROPNAME PROPVALUE
DatabaseSchemaVersion 8.5.7

select * from PDWUSER.LSW_SYSTEM_SCHEMA;

PROPNAME PROPVALUE
DatabaseSchemaVersion 8.5.7


whereas BPM appeared to be expected four columns / digits.

To prove / circumvent the issue, our friendly Oracle DBA deleted the current row: -

delete from PDWUSER.LSW_SYSTEM_SCHEMA where PROPNAME='DatabaseSchemaVersion';

and inserted a new row: -

insert into PDWUSER.LSW_SYSTEM_SCHEMA(PROPNAME,PROPVALUE) VALUES('DatabaseSchemaVersion','8.5.7.0');

and validated the change: -

select PROPNAME,PROPVALUE from PDWUSER.LSW_SYSTEM_SCHEMA where PROPNAME='DatabaseSchemaVersion';

PROPNAME PROPVALUE
DatabaseSchemaVersion 8.5.7.0


Once we did this, the SupCluster started without exception.

The strange thing ?

I can't recreate the same problem :-(

I'm using the same software: -

/opt/ibm/InstallationManager/eclipse/tools/imcl listInstalledPackages

com.ibm.cic.agent_1.8.6000.20161118_1611
com.ibm.bpm.ADV.v85_8.5.7000.20160301_1551
com.ibm.websphere.ND.v85_8.5.5011.20161206_1434
com.ibm.websphere.IHS.v85_8.5.5011.20161206_1434
com.ibm.websphere.PLG.v85_8.5.5011.20161206_1434

and followed the same installation process; specifically installing BPM Advanced 8.5.0.0 and patching to 8.5.7.0 at the same time ( plus also adding the Q1 2016 cumulative fix pack - CF2016.03

Specifically, this is what I see in the tables created by BPConfig.sh : -

/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PSCell1.De1/Oracle/orcl/psuser/createSchema_Advanced.sql

DECLARE
v_table_count NUMBER;
  BEGIN
   SELECT COUNT(*) INTO v_table_count FROM
    psuser.LSW_SYSTEM_SCHEMA WHERE PROPNAME = 'DatabaseSchemaVersion' ;
     IF (v_table_count = 0) THEN
      INSERT INTO
    psuser.LSW_SYSTEM_SCHEMA("PROPNAME",
 "PROPVALUE")
VALUES ('DatabaseSchemaVersion',
 '8.5.7') ;
END IF ;
END ;


/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PSCell1.De1/Oracle/orcl/pdwuser/createSchema_Advanced.sql

DECLARE
v_table_count NUMBER;
  BEGIN
   SELECT COUNT(*) INTO v_table_count FROM
    pdwuser.LSW_SYSTEM_SCHEMA WHERE PROPNAME = 'DatabaseSchemaVersion' ;
     IF (v_table_count = 0) THEN
      INSERT INTO
    pdwuser.LSW_SYSTEM_SCHEMA("PROPNAME",
 "PROPVALUE")
VALUES ('DatabaseSchemaVersion',
 '8.5.7') ;
END IF ;
END ;


So, as far as I can establish, we shouldn't have needed to apply the modification in Oracle :-(

Whilst I'm not 100% sure what went wrong, I do, at least, have a mitigation if it occurs again.

For the client in question, we're going for a clean installation of BPM Advanced 8.5.7.0 plus the Q4 2016 cumulative fix pack - CF2016.12.

For the record, I had seen a similar problem before: -


That time around, I'd NOT run the appropriate createSchema_Advanced script.

Also, the IBM Knowledge Centre reports: -


• Make sure that there is one row in the LSW_SYSTEM_SCHEMA table of the Process Server or Performance Data Warehouse database with the name DatabaseSchemaVersion. Make sure that the value is the same as the value of the IBM BPM version of your current profile.
• If the row does not exist, insert a new row with the correct value. If the row does not exist, insert a new row with the correct value. The value must be a 3-digit version number, for example, 8.5.6.
• If the row exists but the value is not the same as your current profile, you might not have finished migration. Make sure to run the database upgrade step to upgrade your database.
• To disable version validation, set a WebSphere variable in the cell scope. Log in to the administrative console and go to Environment > WebSphere variables. Under cell scope, create a variable named SKIPSTDDBVERSIONCHECK and set its value to true. Restart the servers. The version validation is skipped during server start.

which is pretty spot-on.

No comments: