Oracle Data Pump Restore (IMPDP command) "Go"

The Oracle Database Restore IMPDP command is relative to the EXPDP command, and the direction is reversed. That is, the restore operation for the database backup.
I. Knowledge of the IMPDP order

　　

C:\>impdp -help Import: Release 11.1.0.7.0 - Production on星期六, 28 9月, 2013 15:37:03 Copyright (c) 2003, 2007, Oracle.  Allrights reserved. 数据泵导入实用程序提供了一种用于在 Oracle 数据库之间传输 数据对象的机制。该实用程序可以使用以下命令进行调用:      示例: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp 您可以控制导入的运行方式。具体方法是: 在 ‘impdp‘命令后输入各种参数。要指定各参数, 请使用关键字:      格式: impdp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)      示例: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp USERID 必须是命令行中的第一个参数。 关键字               说明 (默认) ------------------------------------------------------------------------------ ATTACH                连接到现有作业, 例如 ATTACH [=作业名]。 CONTENT               <SPAN style="COLOR: #ff0000">指定要加载的数据, 其中有效关键字为:(ALL),DATA_ONLY和METADATA_ONLY。 </SPAN>DATA_OPTIONS          数据层标记,其中唯一有效的值为:SKIP_CONSTRAINT_ERRORS-约束条件错误不严重。 DIRECTORY            <SPAN style="COLOR: #ff0000"> 供转储文件,日志文件和sql文件使用的目录对象。</SPAN> DUMPFILE              <SPAN style="COLOR: #ff0000">要从(expdat.dmp)中导入的转储文件的列表,例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。 </SPAN>ENCRYPTION_PASSWORD   用于访问加密列数据的口令关键字。此参数对网络导入作业无效。 ESTIMATE              计算作业估计值, 其中有效关键字为:(BLOCKS)和STATISTICS。 EXCLUDE               <SPAN style="COLOR: #ff0000">排除特定的对象类型, 例如 EXCLUDE=TABLE:EMP。</SPAN> FLASHBACK_SCN         用于将会话快照设置回以前状态的 SCN。 FLASHBACK_TIME        用于获取最接近指定时间的 SCN 的时间。 FULL<SPAN style="COLOR: #ff0000">从源导入全部对象(Y)。 </SPAN>HELP                  显示帮助消息(N)。 INCLUDE               <SPAN style="COLOR: #ff0000">包括特定的对象类型, 例如 INCLUDE=TABLE_DATA。 </SPAN>JOB_NAME              要创建的导入作业的名称。 LOGFILE               日志文件名(import.log)。 NETWORK_LINK          <SPAN style="COLOR: #ff0000">链接到源系统的远程数据库的名称。</SPAN> NOLOGFILE             不写入日志文件。 PARALLEL              <SPAN style="COLOR: #ff0000">更改当前作业的活动worker的数目。</SPAN> PARFILE               <SPAN style="COLOR: #ff0000">指定参数文件。 </SPAN>PARTITION_OPTIONS     指定应如何转换分区,其中有效关键字为:DEPARTITION,MERGE和(NONE) QUERY                 <SPAN style="COLOR: #ff0000">用于导入表的子集的谓词子句。 </SPAN>REMAP_DATA            指定数据转换函数,例如REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO REMAP_DATAFILE        在所有DDL语句中重新定义数据文件引用。 REMAP_SCHEMA          <SPAN style="COLOR: #ff0000">将一个方案中的对象加载到另一个方案。 </SPAN>REMAP_TABLE          <SPAN style="COLOR: #ff0000"> 表名重新映射到另一个表,例如 REMAP_TABLE=EMP.EMPNO:REMAPPKG.EMPNO。 </SPAN>REMAP_TABLESPACE      <SPAN style="COLOR: #ff0000">将表空间对象重新映射到另一个表空间。</SPAN> REUSE_DATAFILES       如果表空间已存在, 则将其初始化 (N)。 SCHEMAS               <SPAN style="COLOR: #ff0000">要导入的方案的列表。 </SPAN>SKIP_UNUSABLE_INDEXES 跳过设置为无用索引状态的索引。 SQLFILE               将所有的 SQL DDL 写入指定的文件。 STATUS                在默认值(0)将显示可用时的新状态的情况下,要监视的频率(以秒计)作业状态。 STREAMS_CONFIGURATION 启用流元数据的加载 TABLE_EXISTS_ACTION  <SPAN style="COLOR: #ff0000"> 导入对象已存在时执行的操作。有效关键字:(SKIP),APPEND,REPLACE和TRUNCATE。 </SPAN>TABLES                <SPAN style="COLOR: #ff0000">标识要导入的表的列表。 </SPAN>TABLESPACES           标识要导入的表空间的列表。 TRANSFORM             要应用于适用对象的元数据转换。有效转换关键字为:SEGMENT_ATTRIBUTES,STORAGE,OID和PCTSPACE。 TRANSPORTABLE         用于选择可传输数据移动的选项。有效关键字为: ALWAYS 和 (NEVER)。仅在 NETWORK_LINK 模式导入操作中有效。 TRANSPORT_DATAFILES   <SPAN style="COLOR: #ff0000">按可传输模式导入的数据文件的列表。</SPAN> TRANSPORT_FULL_CHECK  验证所有表的存储段 (N)。 TRANSPORT_TABLESPACES 要从中加载元数据的表空间的列表。仅在 NETWORK_LINK 模式导入操作中有效。 VERSION               要导出的对象的版本, 其中有效关键字为:(COMPATIBLE), LATEST 或任何有效的数据库版本。仅对 NETWORK_LINK 和 SQLFILE 有效。 下列命令在交互模式下有效。 注: 允许使用缩写 命令               说明 (默认) ------------------------------------------------------------------------------ CONTINUE_CLIENT       返回到记录模式。如果处于空闲状态, 将重新启动作业。 EXIT_CLIENT           退出客户机会话并使作业处于运行状态。 HELP                  总结交互命令。 KILL_JOB              分离和删除作业。 PARALLEL              更改当前作业的活动 worker 的数目。PARALLEL=<worker 的数目>。 START_JOB             启动/恢复当前作业。START_JOB=SKIP_CURRENT 在开始作业之前将跳过作业停止时执行的任意操作。 STATUS                在默认值 (0) 将显示可用时的新状态的情况下,要监视的频率 (以秒计) 作业状态。STATUS[=interval] STOP_JOB              顺序关闭执行的作业并退出客户机。STOP_JOB=IMMEDIATE 将立即关闭数据泵作业。 <BR><SPAN style="COLOR: #ff0000">备注：红色标记的选项是比较常用的，需知晓其用法。</SPAN>

Second, the Operation example

  1, full-Library mode import [fully]
  IMPDP orcldev/oracle directory=backup_path dumpfile=orcldev_ Schema.dmp full=y table_exists_action=replace --If the table already exists, replace the operation.
  In general, when you restore a database operation, you first delete the user, and then you perform a IMPDP restore operation.
    eg:
   (1) sql>drop USER Orcldev CASCADE;
   (2) IMPDP orcldev/oracle directory=backup_path dumpfile=orcldev_2013.dmp full=y 
  2, Schema schema import [schema]
 --Restore Orcldev This scenario (user)
  IMPDP orcldev/oracle directory=backup_path dumpfile= Orcldev_schema.dmp Schemas=orcldev table_exists_action=replace

3. Table mode Import [table]
--Restore a specific table under a user
(1) Windows version:
IMPDP orcldev/oracle directory=backup_path dumpfile=orcldev_table.dmp tables= ' tab_test ' table_exists_action=replace
(2) UNIX version: ' single quote ' is required for escape operation
IMPDP orcldev/oracle directory=backup_path dumpfile=orcldev_table.dmp tables=\ ' ius_tran\ ' table_exists_action= Replace

4. Table space mode import [tablespace]
IMPDP orcldev/oracle Directory=backup_path dumpfile=orcldev_tablespace.dmp Tablespace=user,orcldev

5. Transfer table space mode import [transportable tablespace]
(1) Oracle_online
You cannot export transportable tablespaces and then import them to a database at a lower release level. The target database must is at the same or higher release level as the source database.
The transport_tablespaces is valid only if the Network_link parameter is also specified.
This means that the version of the target library is equal to or higher than the version of the source database, and the transport_tablespaces parameter option is valid if the Network_link parameter needs to be specified.
Query database version number SQL statement: SELECT * from V$version;

EG:IMPDP orcldev/oracle directory=dackup_path network_link=db_link_test01 transport_tablespaces=test0001 TRANSPORT_ Full_check=n transport_datafiles= ' app/oradata/test0001.dbf '

(2) Create a database Dblink method:
Grammar:
CREATE [public] DATABASE LINK link_name
CONNECT to Username identified by Password
USING ' connectstring ';

Note:
     1) Creating Dblink requires a CREATE DATABASE link or create public database The system permissions of link and the account used to log in to the remote database must have the CREATE session permission.
    2) ConnectString refers to the listener name that is configured in the Tnsnames.ora file.
     3) when Global_name=true, the Dblink name must be the same as the global database name global_name the remote database; otherwise, it can be named arbitrarily.

(3) To view the Global_name parameter method:
    sql> Show Parameters global_name;
        name                                      type        VALUE
      ----------------------------------------------------------------------------
       global_names                          boolean     FALSE

6. Remap_schema Parameters
It is well known that the Fromuser and Touser parameters of the Imp tool enable the migration of one user's data to another user.
(1) IMPDP data pump using REMAP_SCHEMA parameters to achieve data migration between different users;
Grammar:
Remap_schema=source_schema:target_schema
EG:IMPDP orcldev/oracle Directory=backup_path dumpfile=oracldev.dmp remap_schema=orcldev:orcltwo
A parameter option similar to Remap_schema, such as Remap_tablespace, imports all objects from the source table space into the target tablespace.

7. Remap_table Parameters
Map source table data to a different destination table
EG:IMPDP orcldev/oracle Directory=backup_path dumpfile=oracldev.dmp REMAP_TABLE=TAB_TEST:TEST_TB
The data is imported into the TEST_TB table, but information such as the index of the table is not created and needs to be initialized manually.
8. Remap_datafile Parameters

Syntax: Remap_datafile=source_datafile:target_datafile
Oracle_online:
Remapping datafiles is useful when you move databases between platforms, that has different file naming conventions. The Source_datafile and target_datafile names should be exactly as you want them to appear in the SQL statements where the Y is referenced. Oracle recommends, enclose datafile names in quotation marks to eliminate ambiguity on platforms for which a colon is a valid file specification character.

9. Parallel parameters
Using the parallel parameter can improve the efficiency of the data pump restore, provided that there must be multiple EXPDP files, such as Expdp01.dmp,expdp02.dmp,expdp03dmp, and so on, otherwise there will be problems. When you run the IMPDP command, a worker process is started to import metadata, and then multiple worker processes are started to import data and other objects, so you will see only one worker importing metadata in the first place, And IMPDP also need to dump files are multiple, you can also use%u to import.
EG:IMPDP orcldev/oracle directory=backup_path dumpfile=orcldev_schema_%u.dmp schemas=orcldev parallel=4

Note:

The worker processes of EXPDP and IMDP are started on multiple instance after 11GR2, so directory must be on the shared disk, if no shared disk is set or cluster=no is specified to prevent an error.

　　
10. Content Parameters
The content parameter option has all,data_only and metadata_only, which by default is all. You can choose to import only metadata or import data only.
EG:IMPDP orcldev/oracle directory=backup_path dumpfile=orcldev_schema.dmp schemas=orcldev CONTENT=DATA_ONLY

　　
11. The include, exclude, parfile, query, and version parameter options are the same as the parameter options for the EXPDP command.

third, data pump backup (EXPDP command)
(1) http://www.cnblogs.com/oracle-dba/p/3344230.html

(2) http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm#i1007829

　　
Iv. references
Oracle website
(1) http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm#g1025464

Oracle Data Pump Restore (IMPDP command) "Go"