Migration with impdp. Problems in 19c creating procedures.

Hello everyone, today we wanted to comment with you about a very curious thing that happened to us with a migration through expdp / impdp in 19c.

Migration consists of a logical migration of data between version 11 and version 19. We had done the export with parallel 8 to speed up migration times and when importing with the same parameters we found that it took much longer than expected. To see the times used by each of the steps we have used the LOGTIME = ALL parameter, which includes the information we need in the log file. When we check the import again we see the following:

Migration with impdp. Problems in 19c.

Only in the step of creating the stored procedures it has taken 2 and a half hours !! ?? 😨😨

Let’s see why it’s taking

When reviewing the wait for the import sessions, we see that they remain in Library Cache Lock, which can take almost 200 seconds for a single procedure and when there are many, the times are longer than necessary. Seeing that the only sessions in the database are those of the import, we have tried to carry out the import with PARALLEL = 1 to see the behavior. In this case, the step of the procedures has been solved in a very short time but the import of the tables causes us to leave the migration window, so we have a problem.

Researching in metalink we have found this note:

‘Library Cache Lock’ (Cycle) Seen During DataPump Import in 12.2 RAC Environment (Doc ID 2407491.1)

It seems that it is a bug that occurs since 12.2 in the locking mechanism in the import processes. When we see the possible solutions we see:

1 / Run metadata import with parallel = 1 (default).

– or –

2 / Disable S-Optimization using:

ALTER SYSTEM SET "_lm_share_lock_opt" = FALSE SCOPE = SPFILE SID = '*';

and restart all RAC instances.

Option 1 we have already seen that it is not viable, so we go on to test option 2.

Resuming migration

We make the change of the parameter and restart the two instances. Once done, we try the import again with parallel 8 and the result is:

12-AUG-21 14: 53: 54.121: Processing object type DATABASE_EXPORT / SCHEMA / PROCEDURE / PROCEDURE
12-AUG-21 14: 53: 56.577: Processing object type DATABASE_EXPORT / SCHEMA / PROCEDURE / GRANT / OWNER_GRANT / OBJECT_GRANT

The import of the procedures has taken less than 3sg !!

With these times it is already possible to carry out the migration without problems, so we save the parameter for future migrations. Once the migration is finished, we return the parameter as it was previously:

alter system reset "_lm_share_lock_opt" scope = spfile sid = '*';

The change requires a reboot.

We hope that the entry will be useful for you and that it will save you some scare in the future.

If you don’t want to miss our publications, subscribe to our newsletter . With one email a month you will be informed of all our content.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn

If you prefer that we carry out the migration for you, do not hesitate to contact without obligation in our contact page. Thanks.

More info about export / import: https://oracle-base.com/articles/10g/oracle-data-pump-10g