Oracle expdp/impdp

Oracle expdp/impdp

1 认识data pump：（服务端命令）

 1 Impdp 和expdp在linux跟unix平台下命令为小写形式

 Expdp跟exp产生的dump文件不兼容

Data pump导出导入数据的特性

1 支持并行处理导入，导出任务

2 支持暂停和重启动导入，导出任务

3 支持通过database link方式导出或导入远端数据库对象

4 支持在导入时通过REMAP_SCHEMA,REMAP_DATAFILE,REMAP_TABLESPACE几个参数实现导入过程中自动修改对象属主，数据文件和所在表空间

  5 导入导出时提供了非常细粒度的控制，可精确到对象类型，通过include或exclude两个参数，甚至可以详细指定是否包含或不包含某个对象

Data pump导入导出工具是一个服务器端的工具，通过调用服务器端的dump api来实现数据加载和卸载，导入导出（唯一的列外是通过expdp，impdp的network_link参数，

能够处理远端数据，不过这仍需要通过本地的database link对象）到本地磁盘，并且通过dumpfile参数指定dump文件时，并不是指定本地的详细文件路径，而只是指定文件名，expdp，impdp的参数directory用来指定dump的文件所在路径

  Directory，每个对象都有write和read权限

 即使dba在客户端执行data pump，文件最终也是生成在服务器

 

2 data dump导出导入数据的几种方式

1 直接路径方式（不需要sql层处理

2 外部表方式

3 复制数据文件方式：传输表空间

4 网络连接方式：impdp（db_link）

2 调用expdp跟impdp

 1 调用方式

  1 命令调用

    expdp scott/*@orcl directory=dump_file dumpfile=USEREXP.DMP nologfile=Y

2 参数文件的调用

  expdp scott/*@orcl parfile=pare.dat

 pare.dat内容

dumpfile=userexp.dmp

logfile=d_dmp

directory=dump_file

tables=(CMASK,CMASK_1,IDD_CHANNEL,IDD_CHANNEL_BACK,IDD_CHANNEL20130712)

 3 交互式方式

Expdp，impdp支持停止，重启动等状态操作，如果导入到一半（ctrl+c）中断了任务，此时任务并没有停止，而是转到后台执行，

用户可以再次执行expdp，impdp命令，附加参数attach重新连接到中断的任务

不指定attache参数，ora-31636

Expdp scott/scott attach=sys_exprot_schema_01 (应用Starting "SCOTT"."SYS_EXPORT_TABLE_01":)

2 操作模式

 1整库模式：对应full参数，只有拥有exp_full_database和imp_full_database或管理员角色的用户才能执行整库导入或导出

 2 schema模式：对应schems参数，权限同整库模式，用户a的一个index创建在用户b中，导出，导入时不会导出跟导入该index

 3 表模式：tables参数，只有指定的表或表的分区及依赖该表的对象（index，constraint等，前提是他们都在一个schema中，

 4 表空间模式：对应tablespaces参数，只有指定表空间包含的表及其相关对象可能被导出，如果是特权用户，则该表空间的所有都能被导出，如果是普通用户，则只有属于该用户的表会被导出，

   表空间模式导入时，指定表空间所包含的所有对象及其依赖对象均被导入

   5传输表空间模式：transport_tablespaces参数，生成的dump文件中并不包含逻辑数据，而只导出相关对象的元数据，逻辑数据任然在表空间的数据文件中，

导出时需要将元数据和数据文件同时复制到目标服务器端，导出的元数据非常小，主要在复制数据上

3 过滤对象或数据

1 过滤数据

 Query跟sample参数，query=schema.tablename:query_clause

 Query=tablea:”where id>5”,tableb:”where id <5”

 Sapmle=tablea:50 导出表tablea的50%的数据

2 过滤对象

A:Exclude----反规则：指定不被包含的对象类型或对象名称，指定的对象类型对应的所有对象都不会被导入或导出。（package，procedure，table等，支持通配符），所依赖的对象也不会被导出导入，

Exclude=object_type[:name_clause],

Exclude=index:”like ‘index_tablea%’”,constraint:”like’chik_con%’”,grant

不想导出以index_tablea开头的index，不想导出chik_con开头的约束，以及所有授权都不想导出

  B:include—正规则，与exclude的规则一样

4 expdp执行导出

 1 首先创建一个directory，然后赋予权限

 create directory dump_file_dir as '/u01/app/oracle/dump'

expdp scott/*@grs directory=expdp_file_dir dumpfile=all_expdp_20140324..dmp logfile=20140324.log  full=y  content=all estimate=blocks parallel=4;

SQL> grant read,write on directory dump_file_dir to scott;

1 [oracle@localhost dump]$expdp scott/**@grs directory=dump_file dumpfile=scott_20131022_test..dmp logfile=20131022.lod  tables=emp,dept

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_file dumpfile=scott_20131022_test..dmp logfile=20131022.lod tables=emp,dept

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows

. . exported "SCOTT"."EMP"                               7.835 KB      15 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/dump/scott_20131022_test..dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:07:47

 2 可以使用参数文件

  expdp scott/*@orcl parfile=pare.dat

pare.dat内容

dumpfile=userexp.dmp

logfile=d_dmp

directory=dump_file

tables=(CMASK,CMASK_1,IDD_CHANNEL,IDD_CHANNEL_BACK,IDD_CHANNEL20130712)

 2 跨数据库连方式导出

  先创建dblink

 3 并行方式导出提高效率

Parallel=4 并行度4， filesize=500m 单个文件最大500m，dumpfile=20131022_U%.dmp

 

-----20180322 rac expdp

select *from session_privs;

select * from 

select userenv('language') from dual;--SIMPLIFIED CHINESE_CHINA.AL32UTF8

 exp bol/*@192.168.19.58/bol owner=bol file=/home/oracle/bol_schemas_20180322.dump log=/home/oracle/bol_schemas_20180322.log

 EXP-00056: ORACLE error 12705 encountered

ORA-12705: Cannot access NLS data files or invalid environment specified

 

 select * from v$nls_parameters where parameter='NLS_CHARACTERSET'

 

 select * from nls_instance_parameters;

 

  export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

  

  EXP-00006: internal inconsistency error

EXP-00000: Export terminated unsuccessfully

#出错，exp不支持11g分区表的新特性（ANONYMOUSUSER_ALL是11g的新特性interval分区），解决办法，采用数据泵expd

 

 

 select * from dba_directories;

 --tablespaces=users

 --schemas=bol

 --full=y

 --tables=dept,emp

 

 expdp bol/*@192.168.19.58/bol  schemas=bol directory=DIR_NIE1 dumpfile=bol_schemas_20180322.dmp logfile=bol_schemas_20180322.log

 expdp bol/*@192.168.19.58/bol  schemas=sde directory=DIR_NIE1 dumpfile=sde_schemas_20180322.dmp logfile=sde_schemas_20180322.log

  

ORA-31694: master table "BOL"."SYS_EXPORT_SCHEMA_01" failed to load/unload

ORA-31617: unable to open dump file "/home/oracle/bol_schemas_20180322.dmp" for write

ORA-19505: failed to identify file "/home/oracle/bol_schemas_20180322.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

--去掉parallel=4

--create directory dump_file_dir as '/u01/app/oracle/dump'

 

expdp bol/*@192.168.19.58/bol  schemas=bol directory=DIR_NIE1 dumpfile=bol_schemas_20180322111.dmp logfile=bol_schemas_20180322111.log

 expdp bol/*@192.168.19.58/bol  schemas=sde directory=DIR_NIE1 dumpfile=sde_schemas_20180322111.dmp logfile=sde_schemas_20180322111.log 

 

 Data Mining and Real Application Testing options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31641: unable to create dump file "/home/oracle/bol_schemas_20180322.dmp"

ORA-27038: created file already exists

Additional information: 1

---rac环境，用实际ip去找文件

5 impdp执行导入

alter user scott identified by "987064"

 

  impdp scott/* directory=expdp_file_dir dumpfile=all_expdp_20140324.dmp LOGFILE=all_expdp_20140324.log full=y parallel=4;

1 导入对象到目标schemas

  在impdp中，content参数：all(默认),data_only只导入数据，metadata_only只导入对象定义，

Content=data_only

Data dump不会处理外部表对象的数据文件及其相关文件，

Table_exists_action=skip，append，truncate，replace

2 重新定义对象所属的schema和表空间

 Remap_schema=scott:grsv5

 Remap_tablespace=users:yyhhqq 该参数用来重新映射对象所存储的表空间，支持同时多个表空间进行转换，相互之间逗号分开，

[oracle@localhost dump]$   impdp scott/*@grs directory=dump_file dumpfile=USEREXP.DMP nologfile=Y remap_tablespace=YYHHQQ:USERS

 

Import: Release 10.2.0.1.0 - Production on Tuesday, 22 October, 2013 15:00:47

 

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/********@grs directory=dump_file dumpfile=USEREXP.DMP nologfile=Y remap_tablespace=YYHHQQ:USERS

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."IDD_CHANNEL"                       325.5 KB    3541 rows

. . imported "SCOTT"."IDD_CHANNEL20130712"               64.91 KB     797 rows

. . imported "SCOTT"."IDD_CHANNEL_BACK"                  62.07 KB     756 rows

. . imported "SCOTT"."CMASK"                             28.10 KB     756 rows

. . imported "SCOTT"."CMASK_1"                           11.26 KB     200 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 15:00:56

 

3 通过network_link导出远端数据库到本地数据库

Create  database link yyhhqq connect to scott identified by scott using ‘’

Impdp scott/*@orcl network_link=yyhhqq……..

4 优化导入效率

Impdp时的并行度parallel的数量不要大于dmp文件的数量

 

6 交互式管理

1用户可以再次执行expdp，impdp命令，附加参数attach重新连接到中断的任务

不指定attache参数，ora-31636

Expdp scott/scott attach=sys_exprot_schema_01 (应用Starting "SCOTT"."SYS_EXPORT_TABLE_01":)

2 进入交互式界面后，可以追加文件，（expdp）

 ADD_FILE=INFO_20131022_%U.dmp

Continue_client

Exit_client

Filesize=1g

Help

Kill_job中断正在执行的任务

Parallel=4

Stop_job

Start_job

status

7 data pump api

posted @ 2018-11-07 15:09  春困秋乏夏打盹  阅读(1515)  评论(0)  编辑  收藏  举报