Tag Archives: expdp

When expdp uses the version parameter, ora-39373 is reported

One night, my colleague encountered some problems during data migration using the data pump, and briefly recorded them.

  1. Overview

Colleagues need to migrate the data in a set of 19C database to the database with compatible = 12.1.0, and add the option of version = 12.1.0 in the command of using data pump export. There are several ora-39373 error messages in the exported log. The specific log is as follows:

ORA-39373： cannot export SYSTEM_ GRANT: ….. to version 12.1.0 due to long identifiers

ORA-39373： cannot export SYSTEM_ GRANT: ….. to version 12.1.0 due to long identifiers

ORA-39373： cannot export INDEX_ STATISTICS to version 12.1.0 due to long identifiers

  2. It can be seen from the error message that the exported Version (version = 12.1.0) is specified, and some identifiers are too long, so they cannot be exported. System_ Grant keyword seems to be the content of system permission; INDEX_ Statistics should be the statistics of the index.

  3. Colleagues worry about whether this error report will lead to inconsistent migrated data. In fact, these errors can be processed manually after data migration, and will not cause inconsistent business data. After data migration, compare the system permissions of business users in the two libraries to see what system permissions the business users at the target end lack. You can manually grant these system permissions. The statistics of the index need to be collected manually after the data migration is completed.

  4. In 12.2 Oracle database, the maximum length of identifiers is increased to 128 bytes for most identifiers, up from 30 bytes in previous releases. The solution in MOS is to rename data objects with more than 30 characters and then re export them.

 

 

 

 

Streams AQ: enqueue blocked on low memory wait event causes slow expdp export

Background:
it takes 2.5 hours for customers to export 2G data, which is exported in expdp mode (12c)

Problem solving:

According to the AWR report,
the exception is as follows

Refer to
expdp and impdp slow performance in 11gr2 and 12cr1 and waits on streams AQ: enqueue blocked on low memory (document ID 1596645.1)

View streams_ pool_ size

1 SQL> show parameter streams
2 
3 NAME TYPE VALUE
4 ------------------------------------ --------------------------------- ------------------------------
5 streams_pool_size big integer 0

This is automatic shared memory management, view the actual size

1 select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where x.inst_id=userenv('Instance')
4 and y.inst_id=userenv('Instance')
5 and x.indx=y.indx
6 and x.ksppinm like '%streams_pool%'
7 /

the actual size is 32m

Adjust to 150m
alter system set streams_ POOL_ SIZE=150M SCOPE=BOTH;<
it is found that the export is still slow,
after restarting the database, the export is fast
(scope = both, you still need to restart here)

more content, please pay attention to WeChat official account: DBA every Journal