Data migration to Oracle using the EXPDP/IMPDP data pump

The system environment is reported by convention:

Export environment:

Operating system Release version :CentOS 6.5

OS kernel kernel version: 2.6.32-431.el6.x86.64

Oracle Version information:

Sql> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production

Import Environment:

Operating system Release version :CentOS 6.4

OS kernel kernel version: 2.6.32-358.el6.x86_64

Oracle Version information:

Sql> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production

This operation is seen by the operating system platform is almost the same, the same platform database version of the environment

-------------------------------------------Dividing line--------------------------------------------

The following is a copy of the data import and export using Oracle's EXPDP/IMPDP data pump launched in 10g and 11g, the following Oracle-related commands are executed in the user Oracle environment:

(PS:EXPDP/IMPDP is a server-side tool that runs only on the database server, so it is not affected by the client version, but next it resolves a certain version of the forward-backward compatibility issue, the version incompatibility resolution is higher than Exp/imp, and EXPDP/IMPDP and exp/ IMP exported data can not be mixed, the rest and exp/imp similarities and differences and characteristics please bash me)

First look at the Oracle version confirming that the library was imported:

Sql> select * from V$version;

Then look at the Administrator directory and confirm that the operating system exists for the directory to store the exported data. Because Oracle does not care if the directory is present when the directory is created, an error occurs if it does not exist (created using Mkdir-p/xxx/xxx, using chown-r oracle:oinstall/xxx/xxx to change directory dependencies for Oracle to avoid permissions issues):

SQL>select * from Dba_directories;

For special scenarios such as user rights control and data security considerations, you can create a new logical directory (note that the directory is created in the same location as the system) and authorize users who need to use the directory:

Sql>create directory dpdata1 as '/home/oracle/dumptest ';

Give user xxx A read/write authorization to the directory dpdata1 (i.e./home/oracle/dumptest), preferably authorized by a user with DBA role privileges, such as system. Since this operation is a full-library import and export, using the user system automatically created at Oracle installation, the default user role is the DBA database administrator, as the highest privilege, so no need to read and write authorization to the new directory, the following gives a user authorization statement:

Sql>grant read,write on directory dpdata1 to xxx;

Next use the system user to directly export the whole library file to /home/oracle/dumptest/ , the system password is Oracle, Export file is testfullexpdp.dmp, the export process log is testfullexpdp.log, here @orcl meaning that the connection instance resolution name is ORCL, for the local server instance ORCL, before using the command can first try to use the "tnsping [instance Name ] "To test whether the instance resolves ORCL is in effect. For instance resolution name see the $oracle_home/network/admin/tnsname.ora file with the parse name and instance name one by one correspondence:

EXPDP system/[email protected] directory=dpdata1 dumpfile=testfullexpdp.dmp log=testfullexpdp.log full=y

Export Complete:

The export file under directory dpdata1 , /home/oracle/dumptest , is then exported testfullexpdp.dmp Sent to the management directory on the import target library server, assuming that the management directory of the import library is also dpdata1, that is, the physical directory is /home/oracle/dumptest/ , then import the data, first of all to confirm the directory exists and the corresponding situation:

SELECT * from dba_directories where directory_name= ' DPDATA1 ';

after confirming, proceed to import:

IMPDP system/oracle directory=dpdata1 dumpfile=testfullexpdp.dmp log=testfullimpdp.log FULL=y IGNORE=y;

If the prompt table already exists, because this operation is to overwrite the import, so you can use the "ignore=y" option to ignore

The import is complete and there are quite a few error reports:

This is because the current implementation of the overwrite import operation, a large number of table space and data, as well as indexes and so on already exist, so prompt data duplication error, of course, there are also some other exception errors, you can according to the ORA-XXXXX error code combined with the command "Oerr Ora [error code]" to step through the problem.

At this point, the data import, as for other types of data import methods, such as import by table, by Table space Import, by query criteria import, by user import, etc. can refer to the following articles:

Oracle EXPDP/IMPDP Export Import command and database backup (RPM)

Using the version parameter to resolve the Oracle data pump release differences

EXPDP/IMPDP Usage Explanation

Use of EXPDP/IMPDP tools

Data migration to Oracle using the EXPDP/IMPDP data pump