Oracle导入导出技术规范

1、Oracle导入导出技术规范编制人授权人：版本号生效日期：版本记录版本号日 期修 改 者说 明文 件 名Oracle 数据库导入导出技术规范第一部分：文档信息3第二部分：Oracle数据库导入导出的基本概念4第三部分：导入导出前应检查确认的信息5第四部分：参数说明9第五部分：EXPDP/IMPDP导入导出细则说明10第六部分：导入导出真实案例（案例采用生产数据库变更并随时更新）12第七部分：exp/imp导入导出细则说明12第八部分：导入完毕后对目标库的检查信息确认12第一部分：文档信息序号行动类型人员修改日期修改原因审核人员备注1创建本文档是针对Oracle10G及Oracle11g数据库导

2、入导出技术文档。需要补充如下几点内容：如果是U2L迁移 要保证数据一致性(1) 迁移前一定要把源库listener关闭(或监听端口修改掉,如果需要使用dblink迁移)(2)U2L迁移前一定把源库的用户LOCK住（如果采用dblink迁移，修改密码（注意记录原来的密码）(3)源库的JOB需要关闭如果迁移在固定时间内没有完成 需要回退 一定要把目标库的监听关闭，把目标库上的用户锁住第二部分：Oracle数据库导入导出的基本概念exp/imp EXPDP/IMPDP的基本概念EXPDP和IMPDP叫做数据泵（Oracle Data Pump），是ORACLE10G开始出现的导入导出工具，相对于数据

3、泵EXP和IMP叫做传统导入导出工具（the original Export and Import utilities），一般来说，ORACLE建议使用数据泵，因为他支持ORACLE10G之后的所有新特性，而传统导入导出工具不支持。这两种类型的导入导出工具都支持跨操作系统平台和跨ORACLE版本。exp/imp EXPDP/IMPDP的主要区别1）EXPDP/IMPDP数据泵是服务端的工具，它只能在服务端使用而不能在客户端使用,也不能在DG库上使用2）exp/imp可以在服务端和客户端使用,也可以在只读的DG库上使用使用传统导入导出工具的情况：1、需要导入由EXP生成的文件2、需要导出将会由I

4、MP导入的文件，例如从ORACLE10G导出数据，接着要导入到更低版本的数据库中。ORACLE数据泵技术可以非常高速的在两个库之间转移数据和元数据。这种技术只在ORACLE10.1和之后的版本可用。数据泵组件数据泵由三部分组成：1、命令行客户端（expdp和impdp）2、DBMS_DATAPUMP包 (也就是Data Pump API)3、DBMS_METADATA包 (也就是Metadata API)命令行客户端与传统的exp和imp非常相似，但它是通过使用DBMS_DATAPUMP包提供的过程执行导入导出命名。所有的数据泵执行过程都是在服务器端进行的，这意味着所有的非授权用户，需要让DB

5、A创建一个DIRECTORY来读写数据泵文件。对于授权用户，有一个默认的DIRECTORY可以用。DBMS_DATAPUMP 和DBMS_METADATA包可以独立于expdp、impdp而独立使用，就像ORACLE的其他包一样。导入导出传输模式1）exp导出分为表模式，用户模式，完全模式。分别对应导出表，导出整个用户下的对象，导出整个库下的所有对象2）EXPDP导出分为表模式、用户模式、数据库模式、可传输表空间模式源库和目标库1） 源库是指提供数据来源的数据库2） 目标库是指需要将数据导入的数据库第三部分：导入导出前应检查确认的信息1、字符集检查，检查源库、目标库的字符集字符集是否一致sel

6、ect userenv('language') from dual;USERENV('LANGUAGE')-AMERICAN_AMERICA.AL32UTF8echo $NLS_LANGAMERICAN_AMERICA.AL32UTF8需要保证源库上的字符集和目标库上的字符集一样,否则expdp/impdp (exp/imp) 容易报告错误，并且丟数据。另外，需要保证环境变量NLS_LANG和数据库字符集一样,如不一致，需在导入之前先设置字符集如：如果数据库字符集是AL32UTF8export nls_lang=AMERICAN_AMERICA.AL32UTF8

7、,而且需要重新登录后生效，可以使用env|grep NLS检查确认2、数据库版本检查，检查源库和目标库的数据库版本是否都是同一版本，不同版本需要考虑的情况不同。默认从低版本到高版本可以兼容，从高版本到低版本需要注意。SQL> select * from v$version;BANNER-Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for HPUX: Version 10.

8、2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - Production3、 存储容量检查，主要检查目标库存储大小是否满足AIX:df -gHP:bdf Linux:df h4、表空间检查 表空间大小情况检查作为记录select df.tablespace_name tbs, totalspace total_mb, decode(maxbytes, 0, freespace, freespace + df.auto_totalspace - df.totalspace) free_mb, decode(maxbytes, 0, round(1 -

9、freespace / totalspace) * 100, 2), round(1 - (freespace + df.auto_totalspace - df.totalspace) / auto_totalspace) * 100, 2) used_rate from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace, round(sum(decode(AUTOEXTENSIBLE, 'YES', maxbytes, bytes) / 1024 / 1024) auto_totalspa

10、ce, max(maxbytes) maxbytes from dba_data_files group by tablespace_name) df, (select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name order by 4;检查表空间对应的数据文件大小 select tablespace_name, file_name,

11、bytes / 1024 / 1024 from dba_data_files where tablespace_name in ('FAXDBSERVER');4、 用户检查检查用户所对应表空间select username, default_tablespace from dba_users order by 1;检查用户状态及默认表空间select username, account_status, default_tablespace from dba_users where username in ('FAXDBSERVER') order by 2;

12、 检查所属owner select distinct owner, segment_type, tablespace_name from dba_extents where owner in ('FAXDBSERVER');5、 数据检查，检查表的行数select table_name, num_rows from all_tables where owner = 'FAXDBSERVER' group by table_name, num_rowshaving num_rows > 500 order by table_name;TABLE_NAME N

13、UM_ROWS- -T_DNINFO 26878T_FAXRECINFO 98120T_FAXSENDINFO 219703T_FAXSENDINFO_BK 3563检查对象的数量Select owner, object_type, count(*) from dba_objects where owner = 'FAXDBSERVER' group by owner, object_type order by owner, object_type;OWNER OBJECT_TYPE COUNT(*)- - -FAXDBSERVER INDEX 5FAXDBSERVER SEQ

14、UENCE 3FAXDBSERVER TABLE 6FAXDBSERVER TRIGGER 26、 数据库大小检查select sum(SumMB) / 1024, sum(usedMB) / 1024, sum(freeMB) / 1024, (sum(usedMB) / 1024 + sum(freeMB) / 1024) from (select a.tablespace_name, a.bytes / 1024 / 1024 SumMB, (a.bytes - b.bytes) / 1024 / 1024 usedMB, b.bytes / 1024 / 1024 freeMB, ro

15、und(a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used" from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name =

16、 b.tablespace_name);SUM(SUMMB)/1024 SUM(USEDMB)/1024 SUM(FREEMB)/1024 (SUM(USEDMB)/1024+SUM(FREEMB)/1024)- - - - 118.770508 76.6248779 42.1456299 118.7705087、 数据类型检查,特殊字段需要注意，尤其是longselect distinct(DATA_TYPE),OWNER from dba_tab_columns where owner in ('KHZZFW', 'KHZZFWAPP', 'KHZZ

17、FWMOD', 'KHZZFWDML') and table_name not like 'BIN$%'8、 数据库scheam下的表大小检查select owner, segment_name, bytes / 1024 / 1024 from dba_segments where owner = 'CIS'第四部分：参数说明以下参数适用于数据泵导入导出参数注释说明DUMPFILE=exp%U.dmp#required#DUMPFILE=directory_object:file_name , .#Default: expdat.dmp

18、导出文件名（%U并行度相关 例：PARALLEL=2 则产生2个dmp文件）DIRECTORY=DATA_PUMP_DIR#required#DIRECTORY=directory_object#Default: DATA_PUMP_DIRDIRECTORY目录，存放日志文件和dump文件 需手动指定和创建，要考虑空间大小。LOGFILE=exp.log#required#LOGFILE=directory_object:file_name#Default: export.log日志文件，用于查询导出打入日志JOB_NAME=exp_job#required#JOB_NAME=jobname_

19、string#Default: system-generated name of the form SYS_EXPORT_<mode>_NN以下为根据实际情况选择的参数配置PARALLEL=2 #optional#PARALLEL=integer#Default: 1并行度设置参数 默认值是1 CONTENT=all#optional#CONTENT=ALL | DATA_ONLY | METADATA_ONLY#Default: ALLALL默认包含表结构、数据 DATA_ONLY 仅数据 METADATA_ONLY仅结构SCHEMAS=HR,SH#optional#SCHEMA

20、S=schema_name , .#Default: current user's schema可选多个SCHEMAS一起导出,默认是当前用户下的对象。#TABLES=employees,jobs#optional#TABLES=schema_name.table_name:partition_name , .#Default: none#EXCLUDE=VIEW,PACKAGE#optional#EXCLUDE=object_type:name_clause , .#Default: none#INCLUDE=table:"LIKE 'EMP%'"

21、#optional#INCLUDE = object_type:name_clause , .#Default: none#QUERY=hr.employees:"WHERE department_id > 10 AND salary > 10000"#optional#QUERY = schema.table_name: query_clause#Default: noneTABLE_EXISTS_ACTION=REPLACE#optional#TABLE_EXISTS_ACTION=SKIP | APPEND | TRUNCATE | REPLACE#Def

22、ault: SKIPSQLFILE=expfull.sql#optional#SQLFILE=directory_object:file_name#Default: noneREMAP_SCHEMA=HR:NEW#optional#REMAP_SCHEMA=source_schema:target_schema#Default: none#Multiple REMAP_SCHEMA lines can be specifiedREMAP_SCHEMA=SH:NEW#optional#REMAP_SCHEMA=source_schema:target_schema#Default: none#M

23、ultiple REMAP_SCHEMA lines can be specifiedREMAP_TABLESPACE=USERS:DMP#optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default: none#Multiple REMAP_TABLESPACE parameters can be specifiedREMAP_TABLESPACE=USERS2:DMP#optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default: no

24、ne#Multiple REMAP_TABLESPACE parameters can be specified第五部分：EXPDP/IMPDP导入导出细则说明查询是否存在directoryselect * from dba_directories;创建directorycreate directory expdp_dir as '/cxdhxs/cxdhxs_u06/dump/;授权directorygrant read,write on directory expdp_dir to <指定的用户>在数据迁移中一般导出多个用户(owner,app,mod,read,dml

25、和extr用户)可以在目标库上先创建好所有这些用户，并授予相应权限导入导出脚本导出：expdp '/ as sysdba' parfile=expparoraclemydb11g $ cat exppar DUMPFILE=exp%U.dmp #required#DUMPFILE=directory_object:file_name , .#Default: expdat.dmpDIRECTORY=DATA_PUMP_DIR #required#DIRECTORY=directory_object#Default: DATA_PUMP_DIRLOGFILE=exp.log #r

26、equired#LOGFILE=directory_object:file_name#Default: export.logJOB_NAME=exp_job #required#JOB_NAME=jobname_string#Default: system-generated name of the form SYS_EXPORT_<mode>_NNPARALLEL=2 #optional#PARALLEL=integer#Default: 1CONTENT=all #optional#CONTENT=ALL | DATA_ONLY | METADATA_ONLY#Default:

27、 ALLSCHEMAS=HR,SH #optional#SCHEMAS=schema_name , .#Default: current user's schema#TABLES=employees,jobs #optional#TABLES=schema_name.table_name:partition_name , .#Default: none#EXCLUDE=VIEW,PACKAGE #optional#EXCLUDE=object_type:name_clause , .#Default: none#INCLUDE=table:"LIKE 'EMP%

28、9;"#optional#INCLUDE = object_type:name_clause , .#Default: none#QUERY=hr.employees:"WHERE department_id > 10 AND salary > 10000" #optional#QUERY = schema.table_name: query_clause#Default: none导入：impdp '/ as sysdba' parfile=impparDUMPFILE=exp%U.dmp #required#DUMPFILE=dir

29、ectory_object:file_name , .#Default: expdat.dmpDIRECTORY=DATA_PUMP_DIR #required#DIRECTORY=directory_object#Default: DATA_PUMP_DIRLOGFILE=exp.log #required#LOGFILE=directory_object:file_name#Default: export.logJOB_NAME=exp_job #required#JOB_NAME=jobname_string#Default: system-generated name of the f

30、orm SYS_EXPORT_<mode>_NNPARALLEL=2 #optional#PARALLEL=integer#Default: 1CONTENT=all #optional#CONTENT=ALL | DATA_ONLY | METADATA_ONLY#Default: ALLSCHEMAS=HR,SH #optional#SCHEMAS=schema_name , .#Default: current user's schema#TABLES=employees,jobs #optional#TABLES=schema_name.table_name:par

31、tition_name , .#Default: none#EXCLUDE=INDEX,CONSTRAINT #optional#EXCLUDE=object_type:name_clause , .#Default: none#INCLUDE=index,constraint #table:"LIKE 'EMP%'"#optional#INCLUDE = object_type:name_clause , .#Default: none#QUERY=hr.employees:"WHERE department_id > 10 AND sal

32、ary > 12000" #optional#QUERY = schema.table_name: query_clause#Default: noneTABLE_EXISTS_ACTION=REPLACE#optional#TABLE_EXISTS_ACTION=SKIP | APPEND | TRUNCATE | REPLACE#Default: SKIP#SQLFILE=expfull.sql #optional#SQLFILE=directory_object:file_name#Default: noneREMAP_SCHEMA=HR:NEW #optional#RE

33、MAP_SCHEMA=source_schema:target_schema#Default: none#Multiple REMAP_SCHEMA lines can be specifiedREMAP_SCHEMA=SH:NEW #optional#REMAP_SCHEMA=source_schema:target_schema#Default: none#Multiple REMAP_SCHEMA lines can be specifiedREMAP_TABLESPACE=USERS:DMP #optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default: none#Multiple REMAP_TABLESPACE parameters can be specifiedREMAP_TABLESPACE=USERS2:DMP #optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default