Backup and recovery of Oracle Databases (EXPDP and IMPDP)

Reference: http://blog.csdn.net/oraclemch/article/details/5551634

Oracle 11g Beginner's Guide

Http://www.cnblogs.com/netsql/articles/1745978.html

Online information are very fragmented, and most of them can not complete the requirements of the function, so did some sorting and improvement

Preparatory work
1 Configure the Environment variable Squadron bin directory. By default, when you install an Oracle database, the appropriate environment variables are automatically configured.
such as D:/oracle/product/10.2.0/db_1/bin
2 in the Bin folder of the Oracle installation path, determine the presence of the Expdp.exe and Impdp.exe files.
3 Create an external directory.
Data pump requires the creation of a directory for the file and log files that will be created and read to point to
The external directory used. When creating directory objects in Oracle, you can use the Create directory
Statement.
Instance
1, check, advanced environment variable-pathpath there is no bin directory
2, check whether the Expdp.exe, Impdp.exe file exists.
3. Create a Directory

       C:/> sqlplus/nolog       sql> conn Sys/sys as SYSDBA       sql> Create directory mypump as ' d:/app/temp ';       Sql> Grant Read, write on directory mypump to Scot

Implementing Data Export

Instance
1, Table mode Export
EXPDP scott/scott_2009 directory=mypumpdumpfile=expdptab.dmp tables=dept,emp
(select * from dba_tablespaces; altertablespace testspace online;)

2,schema Mode Export

(ORA-39083 This error is due to the user's permission, but also to execute Grant Exp_full_database to Scott prior to EXPDP at the time of the export)
EXPDP System/system directory=mypumpdumpfile=expdp.dmp Schemas=scott nologfile=y
3, table spatial data export
EXPDP System/system directory=mypumpdumpfile=expdpspace.dmp Tablespaces=episcmcc_dts

4, full-Library mode export
Expdpsystem/system directory=mypump dumpfile=expdp.dmp full=y

Implementing data Import

1, Table mode Import
impdpscott/scott_2009 directory=mypump dumpfile=expdptab.dmp tables=dept,emp

2,schema Mode Import
Impdpsystem/system directory=mypump dumpfile=expdp.dmp Schemas=scott

3, table space data import
IMPDP system/tiger directory=mypump dumpfile=expdspaces.dmp Remap_tablespace=episcmcc_dts:episcmcc_dts table_ Exists_action=replace

4, full-Library mode import
Impdpsystem/system directory=mypump dumpfile=expdp.dmp full=y table_exists_action=replace

Where: In the table space import and full-Library import when you want to create a table space and the corresponding table space user specific steps are as follows:

Before importing to a database, create the appropriate tablespace and user in the new database
Where the table space in the source database is Epicmcc_dts, the user in the table space is EPICMCC

/* Create temporary tablespace */create temporary tablespace episcmcc_temptempfile ' C:\app\z002w00r-e01\oradata\orcl\EPISCMCC_TEMP.dbf ' Size 50mautoextend onnext 50m maxsize 20480mextent management local/* CREATE table space */create tablespace episcmcc_ Dtsloggingdatafile ' C:\app\z002w00r-e01\oradata\orcl\EPISCMCC_DTS.dbf ' size 50mautoextend onnext 50m maxsize 20480mextent Management local/* Create user-specified table space */create user EPISCMCC identified by Tigerdefault tablespace EPISCMCC_ Dtstemporary tablespace episcmcc_temp/* to user authorization */grant CONNECT,RESOURCE,DBA to EPISCMCC

Backup and recovery of Oracle Databases (EXPDP and IMPDP)