oracle迁移数据库用dump命令(exp、imp命令)

场景

公司需要迁移数据库。有几个表的数据量比较大，有2000万条数据。

方案比较

navicat进行迁移

navicat的迁移数据库的方式最简单，但是原理是sql导出和插入，实测速度大概3000条/秒 。 3小时只导入了60万条数据。 远远无法满足要求。

pl/sql进行迁移

用pl/sql的导出dmp导出速度可以达到 10万/秒。但是导入很慢，只有 100条/秒。
用pl/sql的导出dpe导出速度可以达到 10万/秒。
还是无法满足要求。

sqlplus(oracle自带命令行工具)进行迁移

比较高效，但是对比界面操作需要点oracle基础。

综合考虑，决定用sqlplus进行迁移。

sqlplus迁移过程

要有oracle权限的。

# 切换到oracle用户
su - oracle; 
# 以dba登录，
sqlplus / as sysdba;
# 查看当前用户(应该是sys用户)
show user;

单表导出导入

单表导出比较顺利，依次执行命令即可。

# 源库 切换到oracle用户 导出命令
# directory=dpdata1 用来设置文件夹，但是实测不成功，先不管
exp userid=zhangsan/1234@crm tables=t_user file=t_user.dmp;

# 目标库 切换到oracle用户 导入命令
imp lisi/1234@crmnew file=t_user.dmp  full=y  ignore=y;

然后循环所有表即可：

-- 查看表名 和 大概条数
SELECT table_name,num_rows FROM user_tables order by num_rows desc;

库导出导入

用单表的方式，循环可以完成，但是比较麻烦。 可以导出整个库吗？
当然可以，expdp命令就可以。
话虽简单，但是过程蛮曲折的。

执行命令：

# 导出库
expdp zhangsan/1234@crm schemas=crmdata dumpfile=mydump.dmp ; 

报错：only a DBA can import a file exported by another DBA

是因为没有授权。

sqlplus / as sysdba;
grant imp_full_database to zhangsan;

授权之后不报这个错了，继续报错。

报错信息

UDE-03113: operation generated ORACLE error 3113
ORA-03113: end-of-file on communication channel
Process ID: 1001
Session ID: 785 Serial number: 195

再次执行报错：

UDE-12514: operation generated ORACLE error 12514
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

太麻烦了，不搞了。

其他报错

导入命令：

impdp lisi/1234@crmnew SCHEMAS=crmdata dumpfile=mydump.dmp ;

后来又试了次成功了：

# 注意导出库是expdb，而且directory参数一定要加
expdp jxskdata/jxskdata@bwfp schemas=jxskdata dumpfile=jxskdata.dmp DIRECTORY=dpdata1;

其他

这篇文章介绍的不错：
expdp / impdp 用法详解

其他报错

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null

可能原因1

目录不存在。

办法：
指定目录，例如 添加directory=data1

注：这是因为这里的directory不是os(操作系统)的目录。

如何查看directory名称和实际路径?
select * from dba_directories;

字段	意义
directory_name	directory=对应的内容
directory_path	os中实际路径

如果目录不存在，创建一个：

create or replace directory data1 as '/home/oracle/data1'; 
grant read,write on directory data1 to sys; # 授权sys用户对data1目录的读写权限
grant imp_full_database to sys; # 如果需要导入，授权导入全库权限

然后再试试导入命令。