通过导出/导入oracle dmp文件做数据库迁移

一、exp/imp(空表不会被导出)

注意：先建表空间、用户名、授权。导出的时候可以整库或按用户名导出。导入的时候按用户名一个个用户导入。

 

直接在CMD中运行：
exp test/test123@appdb
Enter array fetch buffer size: 4096 > 回车
Export file: expdat.dmp > d:\a.dmp  生成导出的文件名
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 1
Export table data (yes/no): yes > 回车
Compress extents (yes/no): yes > 回车
Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > cmamenu  要导出的表名
. . exporting table                        CMAMENU       4336 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >要导出的表名n
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > 回车
Export terminated successfully without warnings.

说明：

在oracle用exp导出数据时，如果是空表则不导，原因是没有为空表分配segment

用如下语句取出空表，生成的SQL  再执行一下就可以了，就可以分配并可导出了

select 'alter table '||table_name||' allocate extent(size 64k);'     from tabs t     where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);

导入：

su - oarcle

imp file=/opt/../1.dmp full=y;经过验征，需要删除旧的表、序列、视图等等

导入：

C:\Users\Administrator>imp test/test123@appdb FROMUSER=KMSEARCH TOUSER=KMSEARCH

 

二、expdp和impdp工具（可以导出空表，中途遇到很多问题，后面有解决办法）

1)创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等 管理员创建。

create directory db_bak as 'opt\dump';

2)查看管理理员目录同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）
select * from dba_directories;

3)给system用户赋予在指定目录的操作权限，最好以system等管理员赋予。

 
grant read,write on directory db_bak to system;

4)导出数据

按用户导

expdp system/manager@orcl schemas=kmproduct dumpfile=expdp.dmp

DIRECTORY=db_bak

并行进程

parallel 

expdp system/manager@orcl directory=db_bak dumpfile=system3.dmp parallel=40

job_name=system3

按表名导

 

expdp system/manager@orcl TABLES=emp,dept dumpfile=expdp.dmp

DIRECTORY=db_bak;

按查询条件导

expdp system/manager@orcl directory=db_bak dumpfile=expdp.dmp Tables=emp

query='WHERE deptno=20';

按表空间导

expdp system/manager DIRECTORY=db_bak DUMPFILE=tablespace.dmp

TABLESPACES=temp,example;

导整个数据库

expdp system/mtmadmin DIRECTORY=db_bak DUMPFILE=full.dmp FULL=y

五、还原数据

 
1)导到指定用户下

impdp system/manager DIRECTORY=db_bak DUMPFILE=expdp.dmp

SCHEMAS=system;
2)改变表的owner 

impdp system/manager DIRECTORY=db_bak DUMPFILE=expdp.dmp

TABLES=system.dept REMAP_SCHEMA=system:system;

3)导入表空间

impdp system/manager DIRECTORY=db_bak DUMPFILE=tablespace.dmp

TABLESPACES=example;
4)导入整个数据库
impdb system/mtmadmin DIRECTORY=db_bak DUMPFILE=full.dmp FULL=y;

5)追加数据

impdp system/manager DIRECTORY=db_bak DUMPFILE=expdp.dmp

SCHEMAS=system TABLE_EXISTS_ACTION=append;

问题：

1）导入时

ORA-39002: 操作无效

ORA-39070: 无法打开日志文件。

ORA-29283: 文件操作无效

ORA-06512: 在 "SYS.UTL_FILE", line 536

ORA-29283: 文件操作无效

解决方法：
先运行directory=data_pump_dir

然后，再导出，如：

C:\Users\Administrator>expdp 'system/manager@orcl' schemas=kmproductdirectory=data_pump_dirdumpfile=kmproduct.dmp (注意：derectory改成了dada_pump_dir)

 

2)导出时：

ORA-39006:internal error
 
ORA-39213:Metadata processing is not available

解决办法：

查看ORA-39213 具体内容：运行　oerr ora 39213，会出现：

39213, 00000, "Metadata processing isnot available"

// *Cause: The Data Pump could not use the Metadata API.  Typically,

//         this is caused by the XSL stylesheets not being set up properly.

// *Action: Connect AS SYSDBA and executedbms_metadata_util.load_stylesheets

//         to reload the stylesheets.

 

SQL> conn / as sysdba;

Connected.

SQL> execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

再次impdp 成功：

 table_exists_action=replace;(可选)