ORACLE数据库通过数据泵（expdp/impdp）迁移数据

• 业务场景

公司ORACLE数据库要上线云服务器，云端ORACLE数据库配置完成后，涉及本地数据库数据导出，并在云服务器导入操作，表空间数据容量800G左右

 

• 准备工作

1. 查看本地数据库的非系统用户信息，用于配置导出用户
2. 查看本地和云端数据库服务器磁盘容量，以确保数据导出和导入有足够空间存储（导出启用压缩可有效减少转储文件大小）
3. 查看本地和云端数据库服务器字符编码是否一致，如果不一致可能会导致表字段导入报长度错误（需要在导入完成后，调整新库报错表字段长度后再单独导入相关表数据）

 

• 具体操作

1. EXPDP导出
2. IMPDP导入
3. 查看导出/导入日志，如果有报错，逐一处理

 

【EXPDP导出】

首先，在本地数据库服务器 创建逻辑目录（通过SQLPLUS或ORACLE客户端执行SQL）

create directory dump_dir as '/data/dump';

注：dump_dir 即逻辑目录名称，/data/dump 即逻辑目录路径，创建前，请确保当前物理路径下有足够大的空间

select * from dba_directories;

注：查看已创建的逻辑目录

其次，根据已创建的逻辑目录路径，创建物理目录（通过服务器终端执行命令）

mkdir -p /data/dump

注：-p 递归创建目录

 再次，进入逻辑目录路径，创建用于expdp导出数据的parfile参数文件 vi expdp.par

USERID='/as sysdba'
COMPRESSION=ALL
DIRECTORY=dump_dir
DUMPFILE=dump_%U.dmp 
LOGFILE=dump_expdp.log
SCHEMAS=READER,OPERATOR
EXCLUDE=statistics
PARALLEL=4

注：
USERID 导出操作用户
COMPRESSION 减少转储文件大小（ALL, DATA_ONLY, [METADATA_ONLY] 和 NONE）
DIRECTORY 用于转储文件和日志文件的目录对象
DUMPFILE 指定目标转储文件名的列表
LOGFILE 指定日志文件名 
SCHEMAS 要导出的方案的列表（登录方案，这里列出业务表空间下的所有用户）
TABLESPACE 指定要导出的表空间（多个用逗号）
TABLES 指定要导出的表（多个用逗号）
QUERY 指定表查询条件（表名：条件）
EXCLUDE 排除特定对象类型（EXCLUDE=statistics排除统计信息）
PARALLEL 更改当前作业的活动 worker 的数量

最后，万事俱备，只欠东风，执行expdp命令，关联parfile参数为上面创建的参数文件，进行后台数据导出

nohup expdp parfile=expdp.par &

注：nohup 不中断地运行命令， & 后台运行命令，一般nohup和&搭配使用，以避免有的终端设置有登录超时，导致命令中断执行

命令执行后，可在当前逻辑目录路径看到已经生成的dump_01.dmp等转储文件，以及dump_expdp.log和nohup.out日志信息，可通过日志查看命令执行情况，也可以通过dba_datapump_jobs表查看数据泵任务执行情况

select * from dba_datapump_jobs;

注：job_name为任务名称，state为任务状态

如果任务已经执行，出于某种原因需要停止 则需要分别对服务器终端任务进程及数据库终端任务进行kill

服务器终端任务进程kill

ps –ef|grep expdp|grep –v grep
注：grep -v实现not操作，匹配不含grep的信息，排除grep自身进程

kill pid

数据库终端任务kill

select * from dba_datapump_jobs;
注：获取任务名称SYS_IMPORT_SCHEMA_01

impdp \"/ as sysdba\" attach=SYS_IMPORT_SCHEMA_01
注：登录数据库服务器，将job进行attch操作，然后kill

kill
注：进入impdp命令行，执行kill命令

 

【IMPDP导入】

需要将本地数据库服务器导出的转储文件上传至云端数据库服务器的逻辑目录路径，其余步骤与导出类似 ：）

首先，在云端数据库服务器 创建逻辑目录（通过SQLPLUS或ORACLE客户端执行SQL）

create directory dump_dir as '/data/dump';

注：dump_dir 即逻辑目录名称，/data/dump 即逻辑目录路径，创建前，请确保当前物理路径下有足够大的空间

select * from dba_directories;

注：查看已创建的逻辑目录

其次，根据已创建的逻辑目录路径，创建物理目录（通过服务器终端执行命令），并将已导出的转储文件放置于此目录

mkdir -p /data/dump

注：-p 递归创建目录

 再次，进入逻辑目录路径，创建用于impdp导入数据的parfile参数文件 impdp.par

USERID='/as sysdba'
DIRECTORY=dump_dir
DUMPFILE=dump_%U.dmp 
LOGFILE=dump_impdp.log
SCHEMAS=READER,OPERATOR
PARALLEL=4

注：导入时不需要配置压缩及排除特定对象
USERID 导出操作用户
DIRECTORY 用于转储文件和日志文件的目录对象
DUMPFILE 指定目标转储文件名的列表
LOGFILE 指定日志文件名 
SCHEMAS 要导出的方案的列表（登录方案）
PARALLEL 更改当前作业的活动 worker 的数量

最后，执行impdp命令，关联parfile参数为上面创建的参数文件，进行后台数据导出

nohup impdp parfile=impdp.par &

注：nohup 不中断地运行命令， & 后台运行命令，一般nohup和&搭配使用，以避免有的终端设置有登录超时，导致命令中断执行

命令执行后，可在当前逻辑目录路径看到已经生成的dump_expdp.log和nohup.out日志信息，可通过日志查看命令执行情况

 

【查看导出/导入日志，如果有报错，逐一处理】

本次遇到导入日志有字符编码不一致报错，导出库是GBK编码，导入库是UTF8，导致导入字段长度不够，具体报错信息如下

import done in AL32UTF8 character set and UTF8 NCHAR character set
export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions

ORA-02374: conversion error loading table "READER"."TEST"
ORA-12899: value too large for column ID (actual: 205, maximum: 200)

首先，需要将报错日志下载下来，考虑到日志文件可能较大，建议导入excel做下去重，然后整理涉及到上述问题的表字段信息

其次，在云端数据库服务器上通过SLQ拼接方式将涉及的表字段形成字段扩充SQL语句，批量执行

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MODIFY ('||COLUMN_NAME||' '||DATA_TYPE||'('||DATA_LENGTH*3||'));' 
from dba_tab_columns 
where TABLE_NAME='TEST' AND COLUMN_NAME IN ('ID') AND OWNER='READER';

注：DATA_LENGTH为原字段长度，后面*3意思是扩容3倍，可根据实际情况调整，执行结果如下
ALTER TABLE READER.TEST MODIFY (ID VARCHAR2(120));

再次，修改用于impdp导入数据的parfile参数文件 impdp.par，只导入参数标注的表，如果表存在则truncate后再导入新数据

USERID='/as sysdba'
DIRECTORY=dump_dir
DUMPFILE=dump_%U.dmp 
LOGFILE=dump_impdp.log
TABLES=READER.TEST
TABLE_EXISTS_ACTION=truncate
CONTENT=data_only
PARALLEL=4

注：导入时不需要配置压缩及排除特定对象
USERID 导出操作用户
DIRECTORY 用于转储文件和日志文件的目录对象
DUMPFILE 指定目标转储文件名的列表
LOGFILE 指定日志文件名 
TABLES 标识要导入的表的列表（多个表用逗号分隔）
TABLE_EXISTS_ACTION 导入对象已存在时执行的操作（ APPEND, REPLACE, [SKIP] 和 TRUNCATE）
CONTENT 指定要卸载的数据（[ALL], DATA_ONLY 和 METADATA_ONLY）
PARALLEL 更改当前作业的活动 worker 的数量
REMAP_SCHEMA 导入到不同用户
REMAP_TABLESPACE 导入到不同表空间
REMAP_TABLE 如果要导入到同一个schema下的不同表中，需要 （REMAP_TABLE=用户.旧表：新表）！！！！！

其中CONTENT选项：
data_only 只导数据；
metadata_only 只导元数据；

其中TABLE_EXISTS_ACTION选项：
append是为表增加数据；
replace是删除已存在表，重新建表并追加数据；
skip 是如果已存在表，则跳过并处理下一个对象；
truncate是截断表，然后为其增加新数据；

最后，执行impdp命令，关联parfile参数为上面创建的参数文件，进行后台数据导出

nohup impdp parfile=impdp.par &

注：nohup 不中断地运行命令， & 后台运行命令，一般nohup和&搭配使用，以避免有的终端设置有登录超时，导致命令中断执行

命令执行后，可在当前逻辑目录路径看到已经生成的dump_expdp.log和nohup.out日志信息，可通过日志查看命令执行情况，至此就完成了因两端数据库编码不一致造成相关表字段导入失败的二次导入操作