DB Unload fails with 'ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired' error message
Article ID: 87971
Updated On:
Products
Issue/Introduction
Error Message :
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
DB Unload fails to complete and returns errors similar to those below - table names in [brackets] may differ:
20170714/213810.400 - U0000005 LOCK
20170714/213810.400 - U0037178 Error occured in SQL-Turncate of table [DIVDB].
20170714/213810.400 - U0037107 Error during reorganization of table ['RH']. Program exits with error.
20170714/213810.448 - U0037022 ABORTING due to error.
Investigation
This is a common message that is returned by Oracle in one of the following scenarios:
1) A table is already locked by some query. Example: a "select for update" has been executed and has not yet "committed/rollback" and then a "select query" is sent to the same table.
2) A Database user or another application is running a query that caused a lock on a table.
Since the introduction of the Performance Check with Automation Engine version 21.0.9 this problem can also occur at the exact hour (HH:00) and from version 21.0.10/24.x.x at minute 55 (HH:55).
This has been recognized as a Defect (DE129794).
Environment
Automation Engine any version
Cause
Lock on the Database side
Resolution
This has been fixed with the utilities on 21.0.12 and is expected to be fixed with 24.3.0. Please note as the fix is with the utilities, the utilities, initialdata, automation engine, and awi components should be upgraded as these core components should be on the same service pack and hotfix (where applicable).
Workaround:
The recommended actions are:
- Do a commit/rollback of the locked query and let it complete.
- If someone (or some app) put the lock on the table, kill the session (contact a DBA if necessary).
- Change SQLNET.EXPIRE_TIME in SQLNET.ORA file to a maximum of 60 seconds.
Once the lock / locking session has ended, run DB Unload again.
This can also be caused by multiple instances of the utilities running at the same time. For instance, if the archive is started while the unload is running, the unload will throw this error and end without finishing. Only run one utility at a time.
Additional Information
Defect ID: DE129794
Public Description: A problem has been solved where long running DB utilities aborted on Oracle while trying to access the DIVDB table.
As a workaround, please perform the following:
In the file <automic>/utility/bin/uc.msl used by the utilities, on the lines starting with 3556 add '54,' before the number 60 so:
00003556DI60,1460,1461,1555 becomes 00003556DI54,60,1460,1461,1555
00003556EI60,1460,1461,1555 becomes 00003556EI54,60,1460,1461,1555
00003556FI60,1460,1461,1555 becomes 00003556FI54,60,1460,1461,1555
After this modification, the error will continue to occur in case the reorg is running at :55 but this time it will be treated as deadlock and the statement that failed (in this case the truncate table DIVDB) should be re-run and the reorg jobs will finish correctly.
Feedback
