WebSphere Application Server, the Service Integration Bus, CWSIS1501E and ORA-28000: the account is locked

I was trying to work out why my two Service Integration (SI) buses were failing to start up. My back-end database is Oracle 11g R2, with which I'm slowly becoming familiar.

I could see: -

[4/25/13 19:28:15:752 BST] 00000013 SibMessage    I   [MONITOR.BAMCELL.Bus:BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus] CWSID0016I: Messaging engine BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus is in state Starting.
[4/25/13 19:28:15:753 BST] 00000012 SibMessage    I   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSID0016I: Messaging engine BAMSR01.Messaging.000-CEI.BAMCELL.BUS is in state Starting.

but the Messaging Engines associated with each bus never completed starting, eventually failing to do so with: -

[4/25/13 19:43:17:590 BST] 00000013 SibMessage    E   [MONITOR.BAMCELL.Bus:BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus] CWSIS0002E: The messaging engine encountered an exception while starting. Exception: com.ibm.ws.sib.msgstore.PersistenceException: CWSIS1501E: The data source has produced an unexpected exception: java.sql.SQLException: ORA-28000: the account is locked
 DSRA0010E: SQL State = 99999, Error Code = 28,000
[4/25/13 19:43:17:662 BST] 00000013 SibMessage    E   [MONITOR.BAMCELL.Bus:BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus] CWSID0035E: Messaging engine BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus cannot be started; detected error reported during com.ibm.ws.sib.msgstore.impl.MessageStoreImpl start()
[4/25/13 19:43:17:664 BST] 00000013 SibMessage    E   [MONITOR.BAMCELL.Bus:BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus] CWSID0027E: Messaging engine BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus cannot be restarted because a serious error has been reported.
[4/25/13 19:43:17:665 BST] 00000013 SibMessage    I   [MONITOR.BAMCELL.Bus:BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus] CWSID0016I: Messaging engine BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus is in state Stopped.
[4/25/13 19:43:17:667 BST] 00000013 SibMessage    I   [MONITOR.BAMCELL.Bus:BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus] CWSID0016I: Messaging engine BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus is in state Joined.
[4/25/13 19:43:17:668 BST] 00000013 SibMessage    E   [MONITOR.BAMCELL.Bus:BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus] CWSID0039E: HAManager-initiated activation has failed, messaging engine BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus will be disabled. Reason Refer to earlier messages

and: -

[4/25/13 19:43:17:724 BST] 00000012 SibMessage    E   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSIS0002E: The messaging engine encountered an exception while starting. Exception: com.ibm.ws.sib.msgstore.PersistenceException: CWSIS1501E: The data source has produced an unexpected exception: java.sql.SQLException: ORA-28000: the account is locked
 DSRA0010E: SQL State = 99999, Error Code = 28,000
[4/25/13 19:43:17:777 BST] 00000012 SibMessage    E   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSID0035E: Messaging engine BAMSR01.Messaging.000-CEI.BAMCELL.BUS cannot be started; detected error reported during com.ibm.ws.sib.msgstore.impl.MessageStoreImpl start()
[4/25/13 19:43:17:778 BST] 00000012 SibMessage    E   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSID0027E: Messaging engine BAMSR01.Messaging.000-CEI.BAMCELL.BUS cannot be restarted because a serious error has been reported.
[4/25/13 19:43:17:781 BST] 00000012 SibMessage    I   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSID0016I: Messaging engine BAMSR01.Messaging.000-CEI.BAMCELL.BUS is in state Stopped.
[4/25/13 19:43:17:782 BST] 00000012 SibMessage    I   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSID0016I: Messaging engine BAMSR01.Messaging.000-CEI.BAMCELL.BUS is in state Joined.
[4/25/13 19:43:17:783 BST] 00000012 SibMessage    E   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSID0039E: HAManager-initiated activation has failed, messaging engine BAMSR01.Messaging.000-CEI.BAMCELL.BUS will be disabled. Reason Refer to earlier messages

When I looked at SQL script that I'd used to create the two Oracle schemas ( schemae ? ) and the corresponding database tables etc, I immediately saw the problem: -

CREATE USER MONCM00 IDENTIFIED BY passw0rd DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS ACCOUNT LOCK;

CREATE USER MONCE00 IDENTIFIED BY passw0rd DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS ACCOUNT LOCK;

which was easy to resolve: -

$ sqlplus / as sysdba;

SQL> alter user MONCM00 account unlock;

SQL> grant connect, resource to MONCM00;
SQL> alter user MONCE00 account unlock;
SQL> grant connect, resource to MONCE00;
SQL> quit

To be clear, I have two SI Buses: -

CEI.BAMCELL.BUS Common event infrastructure bus  

MONITOR.BAMCELL.Bus Bus for Business Monitor  

each of which has a corresponding Messaging Engine: -

BAMSR01.Messaging.000-CEI.BAMCELL.BUS

BAMSR01.Messaging.000-MONITOR.BAMCELL.Bus  

Each Messaging Engine has a corresponding Message Store ( a set of Oracle database tables "owned" by a schema / user ).

Here's the final picture: -

Bus Description Messaging Engine Schema

CEI.BAMCELL.BUS Common event infrastructure bus   BAMSR01.Messaging.000-CEI.BAMCELL.BUS MONCM00
MONITOR.BAMCELL.Bus Bus for Business Monitor   BAMSR01.Messaging.000-CEI.BAMCELL.BUS  MONCE00

I generated the SQL for the schemas as follows: -

/opt/IBM/WebSphere/AppServer/bin/sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema MONCM00 -statementend ";" >> ~/createMESchemas.sql
/opt/IBM/WebSphere/AppServer/bin/sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema MONCE00 -statementend ";" >> ~/createMESchemas.sql

That was the command that generated the aforementioned CREATE USER commands, thus locking the users.

On a related note, I also saw: -

[4/25/13 20:41:52:045 BST] 00000013 SibMessage    E   [CEI.BAMCELL.BUS:BAMSR01.Messaging.000-CEI.BAMCELL.BUS] CWSIS0002E: The messaging engine encountered an exception while starting. Exception: com.ibm.ws.sib.msgstore.PersistenceException: CWSIS1501E: The data source has produced an unexpected exception: java.sql.SQLException: ORA-01045: user MONCE00 lacks CREATE SESSION privilege; logon denied
 DSRA0010E: SQL State = 72000, Error Code = 1,045

which I resolved with: -

$ sqlplus / as SYSDBA
SQL>  grant create session to MONCE00;  
SQL>  grant create session to MONCM00;
SQL>  quit