WebSphere Portal how to: Recover a WPSDB on derby with SQL status XJ040

Some days ago I've received a new ticket from one of my customers with a WebSphere portal unable to start due some issue on WPSDB.

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 com.ibm.ws.bootstrap.ExtClassLoader@5bfa5bfa, 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 com.ibm.ws.bootstrap.ExtClassLoader@5bfa5bfa, 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

WASHOME\derby\lib

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

ij>

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.