百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

Oracle数据迁移实用入门

wptr33 2025-02-07 19:21 16 浏览

在一个大型企业中做数据工作，难免要跟各种不同种类的数据库打交道。Oracle，凭借其优异的性能，曾经是很多大型企业标配商业数据库，自然也是我们要重点应对的一种数据库。

Oracle的数据导入导出是一项基本的技能，但是对于懂数据库却不熟悉Oracle的同学可能会有一定的障碍。正好在最近的一个项目中碰到了这样一个任务，于是研究了一下Oracle的数据导入导出，在这里跟大家分享一下。

（本文以下内容假设大家熟悉Mysql PostgreSQL等常见的其他数据库，但是不了解Oracle）

Oracle的一些基本内容

表空间TableSpace

Oracle被设计用于管理大量的数据，当一些数据库的数据量太大，以至于一块磁盘存不下的时候该怎么办呢？

Oracle设计了表空间来应对这个问题，一个数据库可以包含多个表空间，一个表空间可以对应多个数据文件，而一张数据库表可以属于某一个表空间。这样一来，我们可以在不同的磁盘上面创建表空间，从而可以方便的用多块磁盘来存放数据了。这几个概念可以图示如下：

当然，表空间的意义远不止于解决多磁盘的问题，表空间其实是Oracle中的非常开创性的设计，它还可以应用于解决下面这些问题：

  • 控制用户所能占用的空间配额；
  • 控制数据库所占用的磁盘空间；
  • 提高数据读写性能；
  • 提高数据安全性。

对于一个大型数据库的数据导入导出工作，首先要做的一件事就是根据数据量大小来合理的规划表空间。

用户(User)与模式(Schema)

Oracle中的用户概念与其他数据库一致，都是用来连接数据库进行操作的。而Schema也与其他数据库中的schema概念一样，是一个数据表及其他对象的集合，用于进行统一管理。

但是Oracle中有一些特别的地方。在Oracle中，我们无法直接创建一个schema。当我们创建user的时候，会创建一个与此user同名的schema。这一点与其他数据库很不一样，需要注意。

虽然如此，通过授权还是可以实现一个用户访问另一个用户的schema。

数据导入导出工具

参考官方的文档可知，如果是从oracle到oracle进行数据导入导出，我们可以使用Data Pump Export/Import工具，也可以使用Export and Import Utilities工具进行数据导入导出。

Data Pump工具对应的命令行工具是expdp/impdp，其优势是速度快，但是使用上略显复杂（请参考后续实操部分）。

Export and Import Utilities工具对应的命令行工具是exp/imp，速度比Data Pump慢，官方更推荐使用Data Pump工具，但是这组工具使用上却更为简单。

如果是将Oracle数据同步到其他数据库或者基于hadoop的数据湖，则可以考虑使用数据平台常用的数据迁移工具sqoop，或者编写spark程序做数据导入导出。

字符集

Oracle支持多种字符集，这样一来，在数据导入导出的时候就需要关注字符集的转换，否则将可能出现乱码问题。

好在Oracle数据库足够聪明，内置了完善的字符集支持，可以自动完成大部分的字符集转换工作，尽量做到用户无感知。

在Oracle对Oracle的数据导入导出的过程中，将涉及到四处字符集：

  • 源数据库字符集；
  • Export过程中用户会话字符集（通过NLS_LANG环境变量设定）；
  • Import过程中用户会话字符集（通过NLS_LANG环境变量设定）；
  • 目标数据库字符集。

这四处字符集分别对应到数据导入导出的各个步骤，在执行某一个特定步骤时，expdp/impdp或exp/imp工具都可以自动的进行字符集转换。但是由于字符集情况比较复杂，事实上这类自动转换也不能完全处理所有情况。

比如，从GBK的数据库导入UTF8的数据库，导出的文件中的建表语句为..., some_column VARCHAR(100), ...时，导入过程可能发生错误数据长度过长(Value too long)的错误。此时需要手动修改建表语句，将上述字段改为..., some_column VARCHAR(100 CHAR), ...，以便以宽字节的方式来定义列长度。

实操练习

下面，为了打通整个数据导入导出流程，我们来完成一个数据导入导出的小练习。

我们将完成以下的任务：

  1. 构建一个oracle环境；
  2. 生成数据并测试数据导入导出；
  3. 比较expdp/impdp和exp/imp工具的性能；
  4. 用sqoop连接oracle数据库进行数据同步；
  5. 用spark连接oracle数据库进行数据同步。

构建oracle环境

oracle虽然是商业数据库，但是甲骨文公司为了降低其学习成本，发布了多个版本，其中的Express版本可以免费用于进行学习。虽然Express版本限制了数据库能使用的核数及数据文件的大小，但是用于完成我们的练习足够了。

如何构建一个oracle环境呢？当然最好使用docker了。恰好Oracle官方开源了对应的dockerfile，我们可以用它来快速构建一个镜像。

下面我们使用11g版本的oracle来完成此练习。

制作镜像

参考下面的命令可以完成镜像制作，并启动一个测试的oracle数据库。

mkdir test
cd test
git clone https://github.com/oracle/docker-images.git

# 下载 【Oracle Database 11g Release 2 Express Edition for Linux x86 and Windows】 https://www.oracle.com/in/database/technologies/oracle-database-software-downloads.html
mv oracle-xe-11.2.0-1.0.x86_64.rpm.zip docker-images/OracleDatabase/SingleInstance/dockerfiles/11.2.0.2/
cd docker-images/OracleDatabase/SingleInstance/dockerfiles/
bash buildDockerImage.sh -v 11.2.0.2 -x

cd -
mkdir data

docker run -d --name test-oracle \
    -p 21521:1521 -p 25500:5500 \
    --shm-size="2g" \
    -v `pwd`/data:/opt/oracle/oradata \
    oracle/database:11.2.0.2-xe
# 以上命令shm-size是必须要指定的，否则将报错内存不足

在启动容器时，系统将生成一个随机，通过查看容器运行日志可以找到此密码。

运行docker logs test-oracle可以看到我们的密码是system/xxx

连接数据库进行操作

在命令行中连接oracle需要使用sqlplus工具，这个工具在容器中已经安装好了。如果想通过其他的主机连接oracle实例，sqlplus也提供了一个纯客户端版本。关于sqlplus的更多信息可以参考官方文档。

下面的命令可以连接到oracle并执行命令：

# 激活默认生成的[HR用户](https://docs.oracle.com/cd/B13789_01/server.101/b10771/scripts003.htm)
sqlplus system/xxx@localhost
> ALTER USER hr IDENTIFIED BY hr;

# 生成一些测试数据
sqlplus hr/hr@localhost
> create table test(id int, val varchar(200));
> insert into test(id, val) values (1, '1');
> insert into test(id, val) values (2, '2');

生成数据并测试数据导入导出

下面我们将生成一些测试数据，并测试数据导入导出。

为了简单，我们就用刚刚生成的hr.test表。并创建一个hrdev用户用于数据导入。

创建hrdev用户并配置权限

使用下面的命令可以完成此操作。

sqlplus system/xxx@localhost
> CREATE USER hrdev IDENTIFIED BY hrdev;
> ALTER USER hrdev IDENTIFIED BY hrdev;
> GRANT READ,WRITE ON DIRECTORY dmpdir TO hrdev;
> GRANT CREATE TABLE TO hrdev;
> grant create session,resource to hrdev; -- 如果没有这一步，无法通过sqlplus连接
> grant imp_full_database to hrdev; -- 如果没有这一步，报错 ORA-31655: no data or metadata objects selected for job

创建目录并配置权限

通过expdp -help查看expdp的使用帮助可以看到，我们需要指定一个DIRECTORY才能进行数据导出。DIRECTORY在Oracle中是一个特殊的对象，是指映射到磁盘文件中的某个目录。用户还需要具有某个DIRECTORY对象的权限才能进行数据导入导出，因此还要完成相应的授权。

使用exp/imp工具则无需创建DIRECTORY对象，也无需相应的授权，故要简单不少。

通过以下命令可以创建一个目录并配置好权限。

mkdir /tmp/test
sqlplus system/xxx@localhost
> CREATE OR REPLACE DIRECTORY dmpdir AS '/tmp/test';
> GRANT READ,WRITE ON DIRECTORY dmpdir TO hr;
> ALTER USER hr IDENTIFIED BY hr;

使用expdp/impdp进行数据迁移并验证迁移结果

# 用expdp导出数据（仅test表）
expdp hr/hr TABLES="(test)" DIRECTORY=dmpdir DUMPFILE=schema.dmp LOGFILE=expschema.log
# 用impdp导入数据
impdp hrdev/hrdev REMAP_SCHEMA=hr:hrdev \
    INCLUDE=TABLE TABLE_EXISTS_ACTION=replace \
    DIRECTORY=dmpdir DUMPFILE=schema.dmp LOGFILE=impschema.log

通过读取数据表hrdev.test的数据可以查看数据导入是否成功。

sqlplus hrdev/hrdev@localhost
> SELECT owner, table_name, tablespace_name FROM all_tables where owner='HRDEV'; -- 这里必须是大写
> select count(*) from test;

使用exp/imp进行数据迁移并验证迁移结果

删除之前的hrdev.test表，然后我们来尝试使用exp/imp工具做数据迁移。

# 用exp导出数据
exp hrdev/hrdev file=test.dmp compress=y feedback=1000000 tables=test1
# 用imp导入数据
imp hrdev/hrdev file=test.dmp tables=test

使用上述类似的命令可以验证数据是否导入成功。

sqlplus hrdev/hrdev@localhost
> SELECT owner, table_name, tablespace_name FROM all_tables where owner='HRDEV'; -- 这里必须是大写
> select count(*) from test1;

到这里我们就完成了expdp/impdp和exp/imp工具的基本导入导出使用。

比较expdp/impdp和exp/imp工具的性能

expdp/impdp具有更好的性能，但是使用却颇为麻烦，其性能究竟比exp/imp工具好上多少呢？我们可以做一个小测试。

生成测试数据

下面的命令可以生成一个较大的测试数据表。

sqlplus system/06d94313bc6a23ca@localhost
> alter tablespace system add datafile '/tmp/oracle/tables.dbf' size 10m autoextend on maxsize unlimited; -- 创建一个表空间用于存储大表
> alter user hrdev DEFAULT TABLESPACE devspace quota unlimited on devspace;

sqlplus hrdev/hrdev@localhost <<

测试性能

运行下面的命令可以完成一个简单的性能测试。

time exp hrdev/hrdev file=test.dmp compress=y feedback=1000000 tables=test1  # 耗时1m30s
time imp hrdev/hrdev file=test.dmp tables=test1 # 耗时15m
time expdp hrdev/hrdev TABLES="(test1)" DIRECTORY=dmpdir DUMPFILE=test1.dmp # 耗时17s
time impdp hrdev/hrdev INCLUDE=table DIRECTORY=dmpdir DUMPFILE=test1.dmp # 耗时34s

在我的测试环境中进行测试，将相应的任务耗时标记在了上述脚本中。可以看到expdp/impdp相比exp/imp工具确实可以带来约几倍到几十倍的性能提升。所以在数据量很大exp/imp工具太慢时，还是可以考虑使用expdp/impdp工具的。

另外expdp/impdp还支持PARALLEL参数，以便进行并行导入导出，由于Express版本不支持PARALLEL，所以在我们的测试环境中并不能完成此测试。理论上expdp/impdp应该会比上述结果更快。

用sqoop连接oracle数据库进行数据同步

使用sqoop将数据导入到hive可以通过一下命令来实现：

sqoop import --table TEST --connect jdbc:oracle:thin:@xxx.xxx.xxx.xxx:21521:XE --username hrdev --password hrdev \
    --hive-import --hive-overwrite --hive-database test_oracle --hive-table test \
    --warehouse-dir /user/hive/warehouse \
    -m 1  # 这里不能用`--split-by id`，否则会报错`No columns to generate for ClassWriter`

在使用上述命令之前，需要注意：

  1. 将连接oracle的jar包下载到sqoop的库目录中（我使用的hdp数据平台，此目录为/usr/hdp/current/sqoop-server/lib/ojdbc6.jar)；
  2. 如果此命令卡住无反应，可能是sqoop运行过程中在等待用户输入密码，参考这里，添加beeline-hs2-connection.xml文件可以解决。

使用sqoop除了可以进行数据迁移，还可以进行方便的执行一些sql命令，比如创建表、查询数据量大小都可以实现。它就是sqoop eval了，通过查询它的帮助文档可以了解更多。

由于sqlplus命令行工具最多只能输入2499个字符，所以一些创建表的语句会无法执行。此时，使用sqoop eval就可以执行这些语句。

用spark连接oracle数据库进行数据同步

spark是大数据开发中常用的工具，其生态相对成熟，可以很容易的实现类似sqoop的并行数据迁移。

使用spark进行oracle数据读取，只需要下面这几行代码：

spark: SparkSession = SparkSession.builder.enableHiveSupport().appName("data-migration").getOrCreate()
df = spark.read \
    .format("jdbc") \
    .option("url", 'jdbc:oracle:thin:@xxx.xxx.xxx.xxx:21521:XE') \
    .option("driver", "oracle.jdbc.driver.OracleDriver") \
    .option("fetchsize", "10000") \
    .option("dbtable", "hrdev.test") \
    .option("user", "hrdev") \
    .option("password", "hrdev") \
    .load()
df.write.saveAsTable(...)

用spark进行数据迁移时需要注意，当我们指定了partition相关参数时，数据迁移并不一定可以得到加速。

在我们的测试过程中发现，当导入一个超过100G的数据表且指定了分区参数时，任务执行过程中出现了大量的executor超过内存限制被Yarn杀掉的情况。而无分区进行数据迁移时，executor对内存几乎没有任何要求。

从这里的现象可以大致分析出，spark在无分区时使用了流式的数据处理机制，无需占用过多内存，但是一旦引入分区则对内存就提出了更多的要求。（测试spark版本为2.3.2）。

总结

本文总结了Oracle的数据导入导出相关工作，分享了一些实操经验。

参考

官方数据导入导出文档：

  • https://docs.oracle.com/cd/E17781_01/server.112/e18804/impexp.htm#BABCJCBD
  • https://oracle-base.com/articles/10g/oracle-data-pump-10g
  • https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL929

字符集转换：
https://my.oschina.net/u/2291124/blog/392174

表空间：
https://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html

导数据权限：
https://oraclehandson.wordpress.com/2011/09/26/ora-31655-no-data-or-metadata-objects-selected-for-job/


基于在多家企业多个项目的经验总结，我们沉淀出一套企业数据开发工作台，可以帮助团队高效交付数据需求，帮助企业快速构建数据能力，工作台地址：
https://data-workbench.com/

我们开源了其中的核心模块--ETL开发语言，开源项目地址：
https://github.com/easysql/easy_sql

相关推荐

Linux文件系统操作常用命令（linux文件内容操作命令）

在Linux系统中，有一些常用的文件系统操作命令，以下是这些命令的介绍和作用：#切换目录，其中./代表当前目录，../代表上一级目录cd#查看当前目录里的文件和文件夹ls#...

别小看tail 命令，它难倒了技术总监

我把自己以往的文章汇总成为了Github，欢迎各位大佬star...

lnav:基于 Linux 的高级控制台日志文件查看器

lnav是一款开源的控制台日志文件查看器，专为Linux和Unix-like系统设计。它通过自动检测日志文件的格式，提取时间戳、日志级别等关键信息，并将多个日志文件的内容按时间顺序合并显示，...

声明式与命令式代码（声明模式和命令模式）

编程范式中的术语和差异信不信由你，你可能已经以开发人员的身份使用了多种编程范例。因为没有什么比用编程理论招待朋友更有趣的了，所以这篇文章可以帮助您认识代码中的流行范例。命令式编程命令式编程是我们从As...

linux中的常用命令（linux常用命令和作用）

linux中的常用命令linux中的命令统称shell命令shell是一个命令行解释器，将用户命令解析为操作系统所能理解的指令，实现用户与操作系统的交互shell终端：我们平时输入命令，执行程序的那个...

提高工作效率的--Linux常用命令，能够决解95%以上的问题

点击上方关注，第一时间接受干货转发，点赞，收藏，不如一次关注评论区第一条注意查看回复：Linux命令获取linux常用命令大全pdf+Linux命令行大全pdf...

如何限制他人操作自己的电脑?（如何控制别人的电脑不让发现）

这段时间，小猪罗志祥正处于风口浪尖，具体是为啥？还不知道的小伙伴赶紧去补一下最近的娱乐圈八卦~简单来说，就是我们的小罗同事，以自己超强的体力，以及超强的时间管理能力，重新定义了「多人运动」的含义，重新...

最通俗易懂的命令模式讲解（命令模式百科）

我们先不讲什么是命令模式，先通过一个场景来引出命令模式，看看命令模式能解决什么样的问题。现在有一个渣男张三，他有还几个女朋友，你现在是不是还是单身狗，你就说你气不气？然后他需要每天分别叫几个女朋友起床...

互联网大厂后端必看!Spring Boot 中Runtime执行与停止命令?

你是否曾在使用SpringBoot开发项目时，遇到需要执行系统命令的场景？比如调用脚本进行文件处理，又或是启动外部程序？很多后端开发人员会使用Processexec=Runtime.get...

Linux 常用命令（linux常用的20个命令面试）

日志排查类操作命令...

Java字节码指令:if_icmpgt(0xA3)（java字节码使用的汇编语言）

if_icmpgt是Java字节码中的一条条件跳转指令，其全称是"IfIntegerCompareGreaterThan"。它用于比较两个整数值的大小。如果栈顶的第一个...

外贸干货|如何增加领英的曝光量和询盘

#跨境电商#...

golang执行linux命令（golang调用shell脚本）

需求需要通过openssl生成rsa秘钥，然后保存该秘钥。代码实例packagemainimport("io/ioutil""bytes"&...

LINUX磁盘挂载（linux磁盘挂载到windows）

1、使用root用户查看磁盘挂载情况：fdisk-l2、使用df查看当前磁盘挂载情况，根据和fdisk-l的结果进行对比，查看还有那些磁盘未使用3、挂载：mount磁盘挂载路径...

Linux命令学习——nl命令（linux ln命令的使用）

nl命令主要功能为每一个文件添加行号，每一个输入的文件添加行号后发送到标准输出。当没有文件或文件为-时，读取标准输入...

一周热门
最近发表
标签列表
Copyright Your WebSite.Some Rights Reserved.