oracle 数据泵

页内索引：

1.data pump export 参数

2.data pump import 参数

3.数据泵目录创建及查询相关信息

4.数据导入导出用例

5.expdp 自动化shell脚本

 

1.DATA PUMP EXPORT 参数：

[oracle@c200 ~]$ expdp help=y

--常用参数：

CLUSTER:
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.

COMPRESSION:
减少转储文件大小。
有效的关键字值为: ALL, DATA_ONLY, [METADATA_ONLY] 和 NONE。
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

CONTENT:
指定要卸载的数据。
有效的关键字值为: [ALL], DATA_ONLY 和 METADATA_ONLY。
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DIRECTORY:
用于转储文件和日志文件的目录对象。
Directory object to be used for dump and log files.

DUMPFILE:
指定目标转储文件名的列表 [expdat.dmp]。
例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

FULL:
导出整个数据库 [N]。
Export entire database [N].

HELP:
显示帮助消息 [N]。
Display Help messages [N].

INCLUDE:
包括特定对象类型。
例如, INCLUDE=TABLE_DATA。
Include specific object types.
For example, INCLUDE=TABLE_DATA.

EXCLUDE:
排除特定对象类型。
例如, EXCLUDE=SCHEMA:"='HR'"。
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

LOGFILE:
指定日志文件名 [export.log]。
Specify log file name [export.log].

NETWORK_LINK:
源系统的远程数据库链接的名称。
Name of remote database link to the source system.

NOLOGFILE:
不写入日志文件 [N]。
Do not write log file [N].

PARALLEL:
更改当前作业的活动 worker 的数量。
Change the number of active workers for current job.

PARFILE:
指定参数文件名。
Specify parameter file name.

QUERY:
用于导出表的子集的谓词子句。
例如, QUERY=employees:"WHERE department_id > 10"。
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA:
指定数据转换函数。
例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

SCHEMAS:
要导出的方案的列表 [登录方案]。
List of schemas to export [login schema].

TABLES:
标识要导出的表的列表。
例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES:
标识要导出的表空间的列表。
Identifies a list of tablespaces to export.

VERSION:
要导出的对象版本。
有效的关键字值为: [COMPATIBLE], LATEST 或任何有效的数据库版本。
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

--OTHER

ATTACH:
连接到现有作业。
例如, ATTACH=job_name。
Attach to an existing job.
For example, ATTACH=job_name.

DATA_OPTIONS:
数据层选项标记。
有效的关键字值为: XML_CLOBS。
Data layer option flags.
Valid keyword values are: XML_CLOBS.

ENCRYPTION:
加密某个转储文件的一部分或全部。
有效的关键字值为: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY 和 NONE
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM:
指定加密的方式。
有效的关键字值为: [AES128], AES192 和 AES256。
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE:
生成加密密钥的方法。
有效的关键字值为: DUAL, PASSWORD 和 [TRANSPARENT]。
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD:
用于在转储文件中创建加密数据的口令密钥
Password key for creating encrypted data within a dump file.

ESTIMATE:
计算作业估计值。
有效的关键字值为: [BLOCKS] 和 STATISTICS。
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY:
计算作业估计值而不执行导出。
Calculate job estimates without performing the export.

FILESIZE:
以字节为单位指定每个转储文件的大小。
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN:
用于重置会话快照的 SCN。
SCN used to reset session snapshot.

FLASHBACK_TIME:
用于查找最接近的相应 SCN 值的时间。
Time used to find the closest corresponding SCN value.

JOB_NAME:
要创建的导出作业的名称。
Name of export job to create.

REUSE_DUMPFILES:
覆盖目标转储文件 (如果文件存在) [N]。
Overwrite destination dump file if it exists [N].

SAMPLE:
要导出的数据的百分比。
Percentage of data to be exported. 

SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.

SOURCE_EDITION:
用于提取元数据的版本。
Edition to be used for extracting metadata.

STATUS:
监视作业状态的频率, 其中
默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TRANSPORTABLE:
指定是否可以使用可传输方法。
有效的关键字值为: ALWAYS 和 [NEVER]。
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK:
验证所有表的存储段 [N]。
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES:
要从中卸载元数据的表空间的列表
List of tablespaces from which metadata will be unloaded.

 

 

2.DATA PUMP IMPORT 参数：

[oracle@c200 ~]$ impdp help=y

--常用参数

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.

CONTENT:
指定要加载的数据。
有效的关键字为: [ALL], DATA_ONLY 和 METADATA_ONLY。
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DIRECTORY:
用于转储文件, 日志文件和 SQL 文件的目录对象。
Directory object to be used for dump, log and SQL files.

DUMPFILE:
要从中导入的转储文件的列表 [expdat.dmp]。
例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

EXCLUDE:
排除特定对象类型。
例如, EXCLUDE=SCHEMA:"='HR'"。
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FULL:
导入源中的所有对象 [Y]。
Import everything from source [Y].

HELP:
显示帮助消息 [N]。
Display help messages [N].

INCLUDE:
Include specific object types.
For example, INCLUDE=TABLE_DATA.
包括特定对象类型。
例如, INCLUDE=TABLE_DATA。

LOGFILE:
不写入日志文件 [N]。
Log file name [import.log].

PARALLEL:
更改当前作业的活动 worker 的数量。
Change the number of active workers for current job.

PARFILE:
指定参数文件。
Specify parameter file.

QUERY:
用于导入表的子集的谓词子句。
例如, QUERY=employees:"WHERE department_id > 10"。
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA:
指定数据转换函数。
例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE:
在所有 DDL 语句中重新定义数据文件引用。
Redefine data file references in all DDL statements.

REMAP_SCHEMA:
将一个方案中的对象加载到另一个方案。
Objects from one schema are loaded into another schema.

REMAP_TABLE:
将表名重新映射到另一个表。
例如, REMAP_TABLE=EMP.EMPNO:REMAPPKG.EMPNO。
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE:
将表空间对象重新映射到另一个表空间。
Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES:
如果表空间已存在, 则将其初始化 [N]。
Tablespace will be initialized if it already exists [N].

SCHEMAS:
要导入的方案的列表。
List of schemas to import.

SQLFILE:
将所有的 SQL DDL 写入指定的文件。
Write all the SQL DDL to a specified file.

TABLE_EXISTS_ACTION：
导入对象已存在时执行的操作。
有效的关键字为: APPEND, REPLACE, [SKIP] 和 TRUNCATE。
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES：
标识要导入的表的列表。
例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES：
标识要导入的表空间的列表。
Identifies a list of tablespaces to import.

VERSION：
要导入的对象的版本。
有效的关键字为: [COMPATIBLE], LATEST 或任何有效的数据库版本。
仅对 NETWORK_LINK 和 SQLFILE 有效。
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

--OTHER
ATTACH:
连接到现有作业。
例如, ATTACH=job_name。
Attach to an existing job.
For example, ATTACH=job_name.

DATA_OPTIONS:
数据层选项标记。
有效的关键字为: SKIP_CONSTRAINT_ERRORS。
Data layer option flags.
Valid keywords are: SKIP_CONSTRAINT_ERRORS.

ENCRYPTION_PASSWORD:
用于访问转储文件中的加密数据的口令密钥。
对于网络导入作业无效。
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.

ESTIMATE:
计算作业估计值。
有效的关键字为: [BLOCKS] 和 STATISTICS。
Calculate job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.

FLASHBACK_SCN:
用于重置会话快照的 SCN。
SCN used to reset session snapshot.

FLASHBACK_TIME:
用于查找最接近的相应 SCN 值的时间。
Time used to find the closest corresponding SCN value.

JOB_NAME:
要创建的导入作业的名称。
Name of import job to create.

NETWORK_LINK:
源系统的远程数据库链接的名称。
Name of remote database link to the source system.

NOLOGFILE:
不写入日志文件 [N]。
Do not write log file [N].

PARTITION_OPTIONS:
指定应如何转换分区。
有效的关键字为: DEPARTITION, MERGE 和 [NONE]。
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.

SKIP_UNUSABLE_INDEXES:
跳过设置为“索引不可用”状态的索引。
Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION:
用于提取元数据的版本。
Edition to be used for extracting metadata.

STATUS:
监视作业状态的频率, 其中
默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STREAMS_CONFIGURATION：
启用流元数据的加载
Enable the loading of Streams metadata

TARGET_EDITION：
用于加载元数据的版本。
Edition to be used for loading metadata.

TRANSFORM：
要应用于适用对象的元数据转换。
有效的关键字为: OID, PCTSPACE, SEGMENT_ATTRIBUTES 和 STORAGE。
Metadata transform to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

TRANSPORTABLE：
用于选择可传输数据移动的选项。
有效的关键字为: ALWAYS 和 [NEVER]。
仅在 NETWORK_LINK 模式导入操作中有效。
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES：
按可传输模式导入的数据文件的列表。
List of data files to be imported by transportable mode.

TRANSPORT_FULL_CHECK：
验证所有表的存储段 [N]。
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES：
要从中加载元数据的表空间的列表。
仅在 NETWORK_LINK 模式导入操作中有效。
List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.

 

3.数据泵目录创建及查询相关信息

--1.创建 DIRECTORY
create directory DATA_PUMP_DIR as '/home/oracle/DATA_PUMP_DIR';

grant read,write on directory DATA_PUMP_DIR to cp_meta;

--2.通过数据字典 DBA_DIRECTORIES 查询已创建目录
select * from dba_directories t where t.directory_name = 'DATA_PUMP_DIR';

--3.查询数据泵正在执行的作业
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
  FROM dba_datapump_jobs
 WHERE job_name NOT LIKE 'BIN$%'
 ORDER BY 1, 2;

--4.清楚作业
DECLARE 
   h1 NUMBER; 
BEGIN 
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SCOTT'); 
   DBMS_DATAPUMP.STOP_JOB (h1); 
END;

4. 数据导入导出用例

    4.1 导表

    4.1.1 导出多表：

expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=emp.dmp schemas=scott logfile=emp.exp.log include=table:\"in \(\'emp\'\)\"  compression=all parallel=4

impdp hr/hr directory=data_pump_dir dumpfile=emp.dmp remap_schema=scott:hr remap_tablespace=tbs_scott:tbs_hr logfile=emp.imp.log table_exists_action=truncate 

   

expdp scott/scott directory=data_pump_dir dumpfile=scott.dmp tables=emp,dept logfile=scott.exp.log compression=all parallel=4

 

   4.1.2 使用参数文件：

expdp scott/scott directory=data_pump_dir dumpfile=scott.dmp schemas=scott parfile=pfile.par logfile=scott.exp.log compression=all parallel=4

prafile:

include=table:"in('emp','dept')"

   4.1.3 使用模糊匹配

expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=scott.dmp schemas=scott include=table:\"like \'emp_%\'\" logfile=scott.exp.log compression=all parallel=4

   4.1.4 使用查询条件

expdp scott/scott directory=data_pump_dir dumpfile=scott.dmp schemas=scott tables=emp,dept QUERY=taa:\"where tablespace_name='SYSTEM'\",tee:\"where index_type='LOB'\" logfile=scott.exp.log compression=all parallel=4

 

expdp scott/tiger directory=data_pump_dir dumpfile=table_20190823.dmp tables=dm_reinspector_work_kpi,da_clm_veh_kpi,da_clm_veh_kpi_group query=dm_reinspector_work_kpi:\"where to_char\(ver_approve_date_l,\'yyyymmdd\'\) between 20190801 and 20190820\",da_clm_veh_kpi:\"where to_char\(ver_approve_date_l,\'yyyymmdd\'\) between 20190801 and 20190820\",da_clm_veh_kpi_group:\"where to_char\(ver_approve_date_l,\'yyyymmdd\'\) between 20190801 and 20190820\" logfile=table_20190823.exp.log

impdp hr/hr directory=data_pump_dir dumpfile=dm_reinspector_work_kpi_20190823.dmp remap_schema=PAIC_KPI:ADS_KPI_PAIC remap_tablespace=TBS_PAIC_KPI_DATA:TBS_ADS_KPI_PAIC_DATA,TBS_PAIC_KPI_INDEX:TBS_ADS_KPI_PAIC_INDEX logfile=dm_reinspector_work_kpi_20190823.imp.log

 

4.1.5 只导表结构

         content ：all  导出对象定义及其所有数据.

                         metadata_only  只导出对象定义

                         data_only          只导出对象数据

expdp scott/tiger directory=data_pump_dir dumpfile=scott.dmp content=metadata_only compression=all logfile=scott.exp.log

 

5.expdp 自动化shell脚本

#!/bin/bash


export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl

WORKDIR="/u01/oradata/dbbackup"

ARGS=$#

#sleep 30

if [ ${ARGS} -ne 2 ];then
    echo "Usage: $(basename $0) username tablename "
    exit 1
fi


USER_NAME=$1
TABLE_NAME=$2
FILE_NAME=${TABLE_NAME}_`date +"%Y-%m-%d"`.dmp
echo ${USER_NAME}
echo ${FILE_NAME}

cd ${WORKDIR}

if [ -f "${FILE_NAME}" ];then
        rm -f "${FILE_NAME}"
fi


expdp system/system directory=DATA_PUMP_DIR tables=${USER_NAME}.${TABLE_NAME} dumpfile=${FILE_NAME} logfile=${FILE_NAME}.exp.log compression=all parallel=2

 

6.数据泵常见错误

   6.1 示例：

   该环境使用的RAC，而登录使用的scan ip ，需要加上 CLUSTER=N

[oracle@drpdb01 log]$ expdp scott/tiger directory=data_pump_dir dumpfile=TABLE2_20190826.dmp tables=DM_CLM_ESTIMATE_ITEM_KPI,DM_CLM_VEH_ADT_EST_KPI,DM_CLM_VEH_ADT_REI_KPI,DM_CLM_VEH_EXT_KPI,DM_CLM_VEH_KPI,DM_CLM_VEH_PRE_ADT_KPI,DM_LOS_PART_KPI,DM_REINSPECTOR_WORK_KPI,D_CLM_VEH_KPI,D_INSURANCE_KPI,D_ORG_KPI,D_SEG_NUMBER_OC logfile=TABLE2_20190826.exp.log compression=all parallel=2

Export: Release 11.2.0.4.0 - Production on Mon Aug 26 14:14:13 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "PAIC_KPI"."SYS_EXPORT_TABLE_02":  PAIC_KPI/******** directory=data_pump_dir dumpfile=TABLE2_20190826.dmp tables=DM_CLM_ESTIMATE_ITEM_KPI,DM_CLM_VEH_ADT_EST_KPI,DM_CLM_VEH_ADT_REI_KPI,DM_CLM_VEH_EXT_KPI,DM_CLM_VEH_KPI,DM_CLM_VEH_PRE_ADT_KPI,DM_LOS_PART_KPI,DM_REINSPECTOR_WORK_KPI,D_CLM_VEH_KPI,D_INSURANCE_KPI,D_ORG_KPI,D_SEG_NUMBER_OC logfile=TABLE2_20190826.exp.log compression=all parallel=2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.22 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "PAIC_KPI"."DM_LOS_PART_KPI" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/oracle/app/oracle/product/11.2.0/db_1/rdbms/log/TABLE2_20190826.dmp" for write
ORA-19505: failed to identify file "/oracle/app/oracle/product/11.2.0/db_1/rdbms/log/TABLE2_20190826.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3