exp/imp EXPDP/IMPDP的主要区别
1）EXPDP/IMPDP数据泵是服务端的工具，它只能在服务端使用而不能在客户端使用,也不能在DG库上使用
2）exp/imp可以在服务端和客户端使用,也可以在只读的DG库上使用
使用传统导入导出工具的情况：
1、 需要导入由EXP生成的文件
2、 需要导出将会由IMP导入的文件，例如从ORACLE10G导出数据，接着要导入到更低版本的数据库中。
ORACLE数据泵技术可以非常高速的在两个库之间转移数据和元数据。这种技术只在ORACLE10.1和之后的版本可用。
数据泵组件
数据泵由三部分组成：
1、 命令行客户端（expdp和impdp）
2、 DBMS_DATAPUMP包 (也就是Data Pump API)
3、 DBMS_METADATA包 (也就是Metadata API)
命令行客户端与传统的exp和imp非常相似，但它是通过使用DBMS_DATAPUMP包提供的过程执行导入导出命名。所有的数据泵执行过程都是在服务器端进行的，这意味着所有的非授权用户，需要让DBA创建一个DIRECTORY来读写数据泵文件。对于授权用户，有一个默认的DIRECTORY可以用。DBMS_DATAPUMP 和DBMS_METADATA包可以独立于expdp、impdp而独立使用，就像ORACLE的其他包一样。
导入导出传输模式
1）exp导出分为表模式，用户模式，完全模式。分别对应导出表，导出整个用户下的对象，导出整个库下的所有对象
2）EXPDP导出分为表模式、用户模式、数据库模式、可传输表空间模式
源库和目标库
1） 源库是指提供数据来源的数据库
2） 目标库是指需要将数据导入的数据库
第三部分：导入导出前应检查确认的信息
1、字符集检查，检查源库、目标库的字符集字符集是否一致
select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.AL32UTF8 |
echo $NLS_LANG AMERICAN_AMERICA.AL32UTF8 |
需要保证源库上的字符集和目标库上的字符集一样,否则expdp/impdp (exp/imp) 容易报告错误，并且丟数据。
另外，需要保证环境变量NLS_LANG和数据库字符集一样,如不一致，需在导入之前先设置字符集
如：如果数据库字符集是AL32UTF8 export nls_lang=AMERICAN_AMERICA.AL32UTF8,而且需要重新登录后生效，可以使用env|grep NLS检查确认 |
2、数据库版本检查，检查源库和目标库的数据库版本是否都是同一版本，不同版本需要考虑的情况不同。默认从低版本到高版本可以兼容，从高版本到低版本需要注意。
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for HPUX: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production |
3、 存储容量检查，主要检查目标库存储大小是否满足
AIX:df -g HP:bdf Linux:df –h |
4、表空间检查
表空间大小情况检查作为记录
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 - 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_totalspace, 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, 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; |
检查所属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_rows having num_rows > 500 order by table_name; TABLE_NAME NUM_ROWS ------------------------------ ---------- T_DNINFO 26878 T_FAXRECINFO 98120 T_FAXSENDINFO 219703 T_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 5 FAXDBSERVER SEQUENCE 3 FAXDBSERVER TABLE 6 FAXDBSERVER TRIGGER 2 |
6、 数据库大小检查
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, round(((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 = 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.770508 |
7、 数据类型检查,特殊字段需要注意，尤其是long
select distinct(DATA_TYPE),OWNER from dba_tab_columns where owner in ('KHZZFW', 'KHZZFWAPP', 'KHZZFWMOD', '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 导出文件名（%U并行度相关 例：PARALLEL=2 则产生2个dmp文件） |
DIRECTORY=DATA_PUMP_DIR | #required#DIRECTORY=directory_object#Default: DATA_PUMP_DIR DIRECTORY目录，存放日志文件和dump文件 需手动指定和创建，要考虑空间大小。 |
LOGFILE=exp.log | #required#LOGFILE=[directory_object:]file_name#Default: export.log 日志文件，用于查询导出打入日志 |
JOB_NAME=exp_job | #required#JOB_NAME=jobname_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#SCHEMAS=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%'" | #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 |
TABLE_EXISTS_ACTION=REPLACE | #optional#TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}#Default: SKIP |
SQLFILE=expfull.sql | #optional#SQLFILE=[directory_object:]file_name#Default: none |
REMAP_SCHEMA=HR:NEW | #optional#REMAP_SCHEMA=source_schema:target_schema#Default: none#Multiple REMAP_SCHEMA lines can be specified |
REMAP_SCHEMA=SH:NEW | #optional#REMAP_SCHEMA=source_schema:target_schema#Default: none#Multiple REMAP_SCHEMA lines can be specified |
REMAP_TABLESPACE=USERS:DMP | #optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default: none#Multiple REMAP_TABLESPACE parameters can be specified |
REMAP_TABLESPACE=USERS2:DMP | #optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default: none#Multiple REMAP_TABLESPACE parameters can be specified |
第五部分：EXPDP/IMPDP导入导出细则说明
查询是否存在directory
select * from dba_directories;
创建directory
create directory expdp_dir as '/cxdhxs/cxdhxs_u06/dump/;
授权directory
grant read,write on directory expdp_dir to <指定的用户>;
在数据迁移中一般导出多个用户(owner,app,mod,read,dml和extr用户)
可以在目标库上先创建好所有这些用户，并授予相应权限
导入导出脚本
导出：expdp \'/ as sysdba\' parfile=exppar [oracle@mydb11g ~]$ cat exppar DUMPFILE=exp%U.dmp #required#DUMPFILE=[directory_object:]file_name [, ...]#Default: expdat.dmp DIRECTORY=DATA_PUMP_DIR #required#DIRECTORY=directory_object#Default: DATA_PUMP_DIR LOGFILE=exp.log #required#LOGFILE=[directory_object:]file_name#Default: export.log JOB_NAME=exp_job #required#JOB_NAME=jobname_string#Default: system-generated name of the form SYS_EXPORT_<mode>_NN PARALLEL=2 #optional#PARALLEL=integer#Default: 1 CONTENT=all #optional#CONTENT={ALL | DATA_ONLY | METADATA_ONLY}#Default: ALL SCHEMAS=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%'"#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=imppar DUMPFILE=exp%U.dmp #required#DUMPFILE=[directory_object:]file_name [, ...]#Default: expdat.dmp DIRECTORY=DATA_PUMP_DIR #required#DIRECTORY=directory_object#Default: DATA_PUMP_DIR LOGFILE=exp.log #required#LOGFILE=[directory_object:]file_name#Default: export.log JOB_NAME=exp_job #required#JOB_NAME=jobname_string#Default: system-generated name of the form SYS_EXPORT_<mode>_NN PARALLEL=2 #optional#PARALLEL=integer#Default: 1 CONTENT=all #optional#CONTENT={ALL | DATA_ONLY | METADATA_ONLY}#Default: ALL SCHEMAS=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=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 salary > 12000" #optional#QUERY = [schema.][table_name:] query_clause#Default: none TABLE_EXISTS_ACTION=REPLACE#optional#TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}#Default: SKIP #SQLFILE=expfull.sql #optional#SQLFILE=[directory_object:]file_name#Default: none REMAP_SCHEMA=HR:NEW #optional#REMAP_SCHEMA=source_schema:target_schema#Default: none#Multiple REMAP_SCHEMA lines can be specified REMAP_SCHEMA=SH:NEW #optional#REMAP_SCHEMA=source_schema:target_schema#Default: none#Multiple REMAP_SCHEMA lines can be specified REMAP_TABLESPACE=USERS:DMP #optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default: none#Multiple REMAP_TABLESPACE parameters can be specified REMAP_TABLESPACE=USERS2:DMP #optional#REMAP_TABLESPACE=source_tablespace:target_tablespace#Default: none#Multiple REMAP_TABLESPACE parameters can be specified |
第六部分：导入导出真实案例（案例采用生产数据库变更并随时更新）
Case 1：分区表导出数据到非分区表（不带索引、仅数据、并行导入导出）
Case 2：存储不足的情况下对1.3T数据的scheam进行分批迁移
Case 3：导出多张table带where条件
第七部分：exp/imp导入导出细则说明
第八部分：导入完毕后对目标库的检查信息确认
1、 对象数据校验核对：（源库和目标库的对比）
--对象数量
select owner,object_type,count (*) from dba_objects
where owner ='DWD'
group by owner,object_type
order by owner,object_type;
2、 主键索引核对：（源库和目标库的对比）
-----主键核对
select count(1),a.status from dba_constraints a where a.owner='owner名'
and a.constraint_type='P' GROUP BY A.status;
3、 大表校验：（源库和目标库的检查）
--num_rows行数验证
select table_name,num_rows from all_tables where owner='KMPRO' group by table_name,num_rows
having num_rows>500 order by table_name;
--大小验证
select owner, segment_name, bytes / 1024 / 1024
from dba_segments
where segment_type = 'TABLE'
and owner = 'CIS';
4、 账号权限、同义词验证：源库
针对导入导出scheam建议统一用owner创建GRANT_RIGHT_ALL统一授权脚本，然后用owner执行
-------------------------------------------------------------------------------执行统一授权脚本
GRANT_RIGHT_ALL('INSURANCEDB','INSURANCEDBAPP','INSURANCEDBMOD','INSURANCEDBDML','INSURANCEDBREAD','');
如果是针对单表的导入导出，没有导入导出，在导入导出完毕之后手动授权
CREATE OR REPLACE PROCEDURE GRANT_RIGHT_ALL
(
OWNER VARCHAR2
,OWNERAPP VARCHAR2
,OWNERMOD VARCHAR2
,OWNERDML VARCHAR2
,OWNERDEV VARCHAR2
,OWNEREXTR VARCHAR2
) AS
---------Create by CPIC 2012-09-26
V_OBJECT VARCHAR2(128);
-----------第一：对表,视图做授权
CURSOR CUR_BRANCH01 IS
SELECT OBJECT_NAME
FROM USER_OBJECTS T
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW');
--AND CREATED >= SYSDATE - 0.5
-----------第二：对存储，函数，包等做授权
CURSOR CUR_BRANCH02 IS
SELECT OBJECT_NAME
FROM USER_OBJECTS T
WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY');
--AND CREATED >= SYSDATE - 0.5
-----------第三：对序列做授权
CURSOR CUR_BRANCH03 IS
SELECT OBJECT_NAME
FROM USER_OBJECTS T
WHERE OBJECT_TYPE = 'SEQUENCE';
--AND CREATED >= SYSDATE - 0.5
BEGIN
V_OBJECT := '';
OPEN CUR_BRANCH01;
LOOP
FETCH CUR_BRANCH01
INTO V_OBJECT;
EXIT WHEN CUR_BRANCH01%NOTFOUND;
---------------- 授权，建同义词 开始
IF LENGTH(TRIM(OWNERAPP)) <> 0
THEN
EXECUTE IMMEDIATE 'grant select , insert, update, delete on ' || V_OBJECT || ' to ' || OWNERAPP;
EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;
END IF;
IF LENGTH(TRIM(OWNERMOD)) <> 0
THEN
EXECUTE IMMEDIATE 'grant select , insert, update, delete on ' || V_OBJECT || ' to ' || OWNERMOD;
EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERMOD || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;
END IF;
IF LENGTH(TRIM(OWNERDML)) <> 0
THEN
EXECUTE IMMEDIATE 'grant select , insert, update, delete on ' || V_OBJECT || ' to ' || OWNERDML;
EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERDML || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;
END IF;
IF LENGTH(TRIM(OWNERDEV)) <> 0
THEN
EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERDEV;
EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERDEV || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;
END IF;
IF LENGTH(TRIM(OWNEREXTR)) <> 0
THEN
EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNEREXTR;
END IF;
COMMIT;
END LOOP;
CLOSE CUR_BRANCH01;
OPEN CUR_BRANCH02;
LOOP
FETCH CUR_BRANCH02
INTO V_OBJECT;
EXIT WHEN CUR_BRANCH02%NOTFOUND;
---------------- 授权，建同义词 开始
IF LENGTH(TRIM(OWNERAPP)) <> 0
THEN
EXECUTE IMMEDIATE 'grant execute on ' || V_OBJECT || ' to ' || OWNERAPP;
EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;
END IF;
COMMIT;
END LOOP;
CLOSE CUR_BRANCH02;
OPEN CUR_BRANCH03;
LOOP
FETCH CUR_BRANCH03
INTO V_OBJECT;
EXIT WHEN CUR_BRANCH03%NOTFOUND;
---------------- 授权开始
IF LENGTH(TRIM(OWNERAPP)) <> 0
THEN
EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERAPP;
EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;
END IF;
IF LENGTH(TRIM(OWNERDML)) <> 0
THEN
EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERDML;
EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERDML || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;
END IF;
COMMIT;
END LOOP;
CLOSE CUR_BRANCH03;
END;
/
