This was a dev environment so WPSDB was resident on local derby instance
inside SystemOut.log this was the relevant error during Portal start
Failed to start database 'C:/IBM/WebSphere/wp_profile/PortalServer/derby/wpsdb' with class loader [email protected], see the next exception for details.DSRA0010E: Stato SQL = XJ040, Codice errore = 40.000
Failed to start database 'C:/IBM/WebSphere/wp_profile/PortalServer/derby/wpsdb' with class loader [email protected], see the next exception for details.
Recovery failed unexpected problem log record is Not first but transaction is not in transaction table : 40980828.
ERROR XSLAO: Recovery failed unexpected problem log record is Not first but transaction is not in transaction table : 40980828.
Inside websphere setup there are some derby libraries available under
so to verify the WPSDB status we have copied the WPSDB folder in a temporary directory than we have started the troubleshooting:
java -cp "derby.jar;derbytools.jar" org.apache.derby.tools.ij
this prompt was the response
here we tried to connect to our copy
ij> connect 'jdbc:derby:C:/temp/wpsdb';
the connections worked so we checked the table status with the following command
SELECT schemaname || '.' || tablename as TableName,SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid and t.tabletype = 'T' ;
tables status looked good so this point us to derby tranlog corruption.
To have a new log with correct file structure we have created a new empty derby db with following command
ij> connect 'jdbc:derby:MyDbTest;create=true';after that we have copied the log/ from new db to log/ of wpsdb (all steps with releated backup) and after have copied back the new wpsdb folder the Portal was able to start again !
I like to thank Andrea Grassi for his blogpost about derby corrupted database recovery guide.