Sunday, July 25, 2021

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired during impdp import in 19c

Problem:

During importing a big dump file on a 19c database using data pump impdp, I got the following error repeatedly:

Failing sql is:
ALTER INDEX "WM"."IDX_FK_PO2LU_NATIONALITY" LOGGING
10-JUN-21 15:10:54.278: ORA-39083: Object type INDEX:"WM"."VEN2PERSONORG2_FK" failed to create with error:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Analysis:

When you use DISABLE_ARCHIVE_LOGGING:Y during the import, it's expected to face ORA-00054, this is because the import process tries to re-enable the logging mode for each object while the object is still being locked by the data pump process. This looks like a bug, but Oracle Support keep saying: it's a normal behavior!

I tried setting DDL_LOCK_TIMEOUT parameter instance wide to a big value before executing the import to give those ALTER statement the sufficient time to wait for the lock to be released without any luck!

Workaround:

There are three workarounds I can think of:

1- Execute all the failing ALTER statements manually after the import; just grep all the failing ALTER statements from the import log and execute them from SQLPLUS:
e.g.
# grep "ALTER" import.log
terminate each statement with semicolon ";" at the end, and then execute them from SQLLPUS

2- If you don't care about enabling the LOGGING mode on the underlying objects; i.e. you are importing the data into a non-production database that doesn't need to be restored to a point in time when it fails, then just simply ignore the errors to leave those objects in NOLOGGING mode.

3- If you can sustain turning the target DB into NOARCHIVELOG mode during the import time window; then better restart the DB and alter the DB in NOARCHIVELOG mode, then start the import without using the parameter transform=DISABLE_ARCHIVE_LOGGING:Y , once the import is done, restart the DB and set it back to ARCHIVELOG mode. [A Stone Age workaround I know 𓀗]

1 comment: