Migration with impdp. Problems in Oracle 19c creating procedures.

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

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

Migration with impdp. Problems in 19c.

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

Let’s see why it’s taking time

When reviewing the waits of the import sessions we see that they stay in Library Cache Lock, being able to take almost 200 seconds for a single procedure and when there are many the times are extended more than the account. Seeing that the only sessions in the database are those of the import we have tried to perform the import with PARALLEL=1 to see the behavior. In this case, the passage 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 failure that occurs since 12.2 in the blocking 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 move on to try option 2.

Resuming migration

We make the parameter change and restart the two instances. Once done we test 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 leave the parameter as it was previously:

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

The change requires a restart.

We hope that you find the entrance useful and that it saves you some scare in the future.

If you do not want to miss our publications, subscribe to our newsletter. With one email per month you will be informed of all our content.

If you prefer that we perform the migration for you, do not hesitate to contact without obligation on our contact page. Thank you.

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

Comments are closed.