Disclaimer

Monday, 21 February 2022

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 ð“€—]

No comments:

Post a Comment

Understanding SQL Plan Baselines in Oracle Database

  Understanding SQL Plan Baselines in Oracle Database SQL Plan Baseline is the feature in Oracle started from Database 11g that helps to pre...