ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_STAT

Document ID : KB000023393
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

nhiConvertDb fails with: "Error: Unable to execute 'GRANT EXECUTE ON DBMS_STATS TO $NH_USER (ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_STAT)."

 

Environment:

eHealth 6.2.2 onward on Solaris / Linux / Windows

 

Cause:

As the error message indicates, the SYS table of the database is locked. The easiest way to work around this issue is a "nhServer Stop" followed by a "nhStopDb immediate". This should kill all active connections to the database. Once it has been verified that the are no more active connections, the database can then be started with a "nhStartDb" and the nhiConvertDb can be run again.
CA Support have seen instances where the nhiConvertDb will still not succeed as there zombie processes or shared memory lurking in the background.

 

Resolution:

To verify whether this is the case stop the database with "nhStopDb immediate" and do the following:
 
1. Check for running eHealth & Oracle processes:
  •    ps -ef - grep ora
  •    ps -ef - grep nh
  •    ps -ef - grep dba
 
2. Check for Shared memory with ipcs command:
  • "ipcs -a - grep ehealth". If you see entries for eHealth then stop then with "ipcrm -m <id>"
  • The SYS table should no longer be locked by any user process but this can be checked with the following SQL queries after the database has been started again: 
  • select object_id, process, oracle_username, locked_mode FROM V$LOCKED_OBJECT;
  • select unique d.object_name from V$LOCKED_OBJECT l,DBA_OBJECTS d where l.object_id=d.object_id
 
3. You may want to wait until the other process has finished using the object or perhaps you can kill the session or recycle the Oracle Server.
  • nhStopDb immediate - if running
  • Reboot the machine
  • Do undo convert "nhConvertDb -undoconvert"
  • start convert or upgrade again.