Data migration of Oracle using EXPDP/IMPDP data Pump ____oracle

Source: Internet
Author: User
Tags create directory dba

To report the system environment in the usual way:

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

Visible from the above operation is the operating system platform is almost the same, the same platform database version of the environment

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

Below is an import export of data using Oracle's EXPDP/IMPDP data pump in the 10g and 11g versions, 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, making it unaffected by the client version, and then solves a certain version forward-backward compatibility problem, has a higher version incompatibility resolution than Exp/imp, and EXPDP/IMPDP and exp/ IMP exported data can not be mixed, the rest with exp/imp similarities and differences and characteristics please bash me.

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

Sql> select * from V$version;

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

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 in the system) and authorize the user who needs to use the directory:

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

Read/write authorization to User XXX for this directory dpdata1 (that is,/home/oracle/dumptest), preferably with users with DBA role privileges, such as system. Because this operation is a whole-library import export, using the user system that is automatically created by Oracle installation, the default User role is DBA database administrator, which is the highest privilege, so no more read and write authorization is required for the new directory, and the following is given to a user authorization statement:

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

Next, use system users to directly export the whole library to/home/oracle/dumptest/, the system password is Oracle, The export file is Testfullexpdp.dmp, and the export process log is testfullexpdp.log, where @orcl means the instance resolution name for the connection is ORCL, an instance of the server on the local ORCL, before you can use the command to try "tnsping [instance Name The method tests whether the instance resolves Orcl. About instance resolution name please see $oracle_home/network/admin/tnsname.ora file with the resolution name and instance name one by one correspondence: EXPDP SYSTEM/ORACLE@ORCL directory=dpdata1 Dumpfile=testfullexpdp.dmp Log=testfullexpdp.log full=y


Export Complete:

Next, send the export directory dpdata1/home/oracle/dumptest export file testfullexpdp.dmp to the administrative directory on the import target library server, assuming that the admin directory for the import library is also dpdata1, that is, the physical directory is/home /oracle/dumptest/, then import the data, first of all, to confirm that the directory exists and corresponds:

SELECT * from dba_directories where directory_name= ' DPDATA1 ';


Confirm, and then perform the import:

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

The following illustration indicates that the table already exists, and because this operation is an overlay import, you can use the "ignore=y" option to ignore

Import complete, there are quite a few error reports:


This is because the current execution is to overwrite the import operation, a large number of table space and data, as well as the index and so on already exist, so that the data repeat error, of course, there are also some other exception errors, according to the ORA-XXXXX error code combined with the command "Oerr Ora [error code]" to

This data import finished, as for other types of data import methods, such as import by table, import by Table space, import by query conditions, import by user, etc. you can refer to the following articles: Oracle EXPDP/IMPDP Export Import command and database backup (turn)

Using the version parameter to resolve differences in Oracle data pump versions

EXPDP/IMPDP Usage Detailed

Use of EXPDP/IMPDP Tools

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.