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.
Environment
Automation Engine any version
Cause
Lock on the Database side
Resolution
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
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 (DE610689) and will be fixed in future versions of the Automation Engine (expected to be fixed on version 21.0.12 and 24.2.0).
As a workaround, please perform the following:
Please add to the uc.msl file used by the utilities (bin directory) on the lines with 3556 before the 60 the number 54
00003556DI54,60,1460,1461,1555
00003556EI54,60,1460,1461,1555
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 (on this case the truncate table DIVDB) should be re-run and the reorg jobs will finish correctly.
Feedback
