Two problems encountered during impdp import: impdp import encountered two problems

Two problems encountered during impdp import: impdp import encountered two problems

Two days ago, you need to import the data dump exported from a remote test database using expdp to a local test environment. Some problems are encountered, which is worth a summary.

Environment Introduction:

1. Data volume: 10 + tables, <10 million of data, dump file about 400 MB

2. Both the source and target databases are 11.2.0.4

3. Different character sets, the source library character set is GBK, the target library character set is UTF-8

Import command:

Test/test directory = exp_dump dumpfile = test. dmp logfile = test. log remap_schema = test1: test2 remap_tablespace = TBS_DAT_1: TBS_DAT_2, TBS_IDX_1: TBS_IDX_2

Two problems encountered during the import process:

(1) Chinese Character Set Conversion

KUP-11007: conversion error loading table "TEST". "T_PSR"
ORA-12899: the value of column REASON_CODE is too large (actual value: 21, maximum value: 20)
KUP-11009: data for row: REASON_CODE: 0X 'babdbfd5c6f7c8ddc1bfcfded6c6'

This involves the character set conversion problem, Chinese occupies 2 places in the GBK character set, but occupies 3 places in the UTF-8 character set, so in the case of saving less than 20 characters in the GBK, importing to the library of the UTF-8 may result in a ORA-12899 error that exceeds the field length definition due to extra space.

(2) primary foreign key Association

ORA-31693: Table data object "TEST". "T_ITE" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-02291: integrity constraint (TEST. FK_ITE_REF_PSR) violated-parent key not found

Because some tables are associated with primary and Foreign keys, only data is exported when data_only is selected for expdp export. During impdp import, foreign key records are inserted because no primary key record is inserted, an error occurs in the ORA-02291, in which case you can choose to disable the primary foreign key Association first, import and then restore the association.

Operation Sequence:

(A) Before importing, execute the following SQL statement to find the foreign key association to be disabled

Select 'alter table' | TABLE_NAME | 'Disable constraint' | constraint_name | ';'

From user_constraints WHERE CONSTRAINT_TYPE = 'R ';

(B) Execute (a) The result SQL

(C) After the import, execute the following SQL statement to find the foreign key association to be restored

Select 'alter table' | TABLE_NAME | 'Enable NOVALIDATE constraint' | constraint_name | ';'

From user_constraints WHERE CONSTRAINT_TYPE = 'R ';

The NOVALIDATE parameter does not verify the stored data, but the record to be inserted will follow the relationship between the primary and Foreign keys.

Summary:

1. use the expdp/impdp Data Pump import and export tool provided by 10 Gb or later versions. Compared with the previous exp/imp tool, whether it is on the selectivity of parameters, speed and compression ratio, we have made significant improvements to provide a more convenient and fast data import and export method.

2. Import and export may encounter the most problems. Character Set conversion is one of them. You must specify the degree of dependency between imported and exported data on the character set to ensure that the data is correctly imported and exported.

3. for data associated with a primary foreign key, if you select data_only to export data only, you can disable the constraints before import. In this way, the import process will not be affected by the primary foreign key Association, and the constraints can be restored after import, make sure the constraints are correct.