Example of Oracle expdp/impdp from high version to low version

The original version of a testing database of the company is Oracle 11gR2. Some of the 10g syntax is somewhat different. The boss said to re-build the test database and use the 10G version.

Although it can be shut down, the data volume is not big, but during the day, R & D colleagues need to use the test library, so they can only work overtime at night. We plan to use the data pump expdp/impdp. Since the version parameter needs to be added to the import from a higher version to a lower version, it has not been tested before. Use this opportunity to test.

For more examples of data pump, refer to my Blog:

Oracle expdp/impdp example

The procedure is as follows:

1. Install the 10g library and create an instance

2. view the total number of original user objects

SQL> select count (*) from dba_objects where owner in ('hangou', 'up', 'qishun ');

After the import, you can compare it.

3. Create a directory and use expdp to export data

SQL> create directory backup as '/u01/backup ';

# Expdp/'/as sysdba/' directory = backup full = y dumpfile = wgfullexp. dmp logfile = fullexp. log version = 10.2.0.1;

-- Note that the parameter is exported from the 11gR2 database. I want to import a 10 Gb library and specify the version information through version.

4. Use Toad to export scripts such as table space users in the original test database.

5. Use the exported script to create the tablespace in the new test database.

6. Create dir and use impdp to import data

SQL> create directory backup as '/u01/backup ';

# Impdp/'/as sysdba/' directory = backup full = y dumpfile = wgfullexp. dmp logfile = fullimp. log;

Impdp failed. The final error is as follows:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE: "ORDDATA". "ORDDCM_PREFS_LOOKUP" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE: "ORDDATA". "ORDDCM_PREFS_LOOKUP" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE: "ORDDATA". "ORDDCM_PREFS_LOOKUP" creation failed

ORA-39125: Worker unexpected fatal error in KUPW $ WORKER. PUT_DDLS while calling DBMS_METADATA.CONVERT [TABLE_STATISTICS]

ORA-06502: PL/SQL: numeric or value error

LPX-00225: end-element tag "HIST_GRAM_LIST_ITEM" does not match start-element tag "EPVALUE"

ORA-06512: at "SYS. DBMS_SYS_ERROR", line 105

ORA-06512: at "SYS. KUPW $ WORKER", line 6241

----- PL/SQL Call Stack -----

Object line object

Handle number name

0x4de6dba8 14916 package body SYS. KUPW $ WORKER

0x4de6dba8 6300 package body SYS. KUPW $ WORKER

0x4de6dba8 12279 package body SYS. KUPW $ WORKER

0x4de6dba8 3279 package body SYS. KUPW $ WORKER

0x4de6dba8 6889 package body SYS. KUPW $ WORKER

0x4de6dba8 1262 package body SYS. KUPW $ WORKER

0x4de62824 2 anonymous block

Job "SYS". "SYS_IMPORT_FULL_01" stopped due to fatal error at 20:58:03 # o b0d5j2u18982013

Because there are many users here, my colleagues gave me three users, but I went on to check them out, obviously more than three users. So I don't want to perform user-based data import. I just want to perform full = y full-database data import.

Impdp is automatically created if the user does not exist. So when I impdp, I didn't create a user. However, if it is a production environment, it should be step by step. The tablespace is created first. Users are guided by users.

Imdp failed, but the leading table, index, and other objects have been imported. Usage:

SQL> select count (*) from dba_objects where owner in ('hangou', 'up', 'qishun ');

View the objects of the three users in the new test database, 20 fewer than the original database.

Export the detailed data of the two database objects to the txt file and compare it with UC. The 20 fewer objects are triggers. Use Toad to export the SQL statements of these 20 triggers from the original database and execute them on the new database. Finally, I checked. The objects are consistent.

Because impdp reports an error in the last statistical item, you have checked the statistical information and imported it. Let the development colleagues test it. There is no problem.