Oracle数据库的几种数据迁移方法探析

2018-11-07 11:37:30 电脑知识与技术2018年21期

饶淑珍

摘要:针对大数据背景下愈加庞大烦琐的数据库,文章探讨了几种数据导入导出的迁移方法,用于数据的迁移维护和日常应用,尤其是批量数据在不同版本、不同格式和跨数据库之间的迁移。

关键词:oracle;数据库;Export/Import;Date Pump;PLSQL;SQLloader

中图分类号:TP393 文献标识码:A 文章编号:1009-3044(2018)21-0013-02

近年来,大数据的概念被越来越多的提及,数据库作为其承载体也被愈加重视,在日常的开发应用了,Oracle几乎占据了绝大多数的市场,无论是Oracle软件本身的数据维护、备份,还是转换版本乃至更换数据库软件,都需要掌握多种数据导入导出的迁移方法。

Oracle本身提供了相应的工具解决导入导出功能,比如传统工具Exp/Imp、数据泵Expdp/Impdp等,但在跨软件环境之间还存在或多或少的兼容性问题,本文以常用的Oracle 11g和PLSQL Developer软件环境为例,从实际常用的编码设置、DMP文件迁移、CSV(文本文件)迁移等入手,探讨了几种便捷实用的数据迁移方法。

1 统一数据库字符集格式

服务端数据库字符集一旦创建,所存储的字符就受到了限制,在本地开发环境配置时,应保持服务端、客户端、PLSQL Developer三者的一致性,对于导入的DMP数据文件,也要首先校验其字符集格式,否则极易引起乱码等情况,给数据带来不必要的隐患。排查步骤详细如下:

1)查询Oracle 11g服务端字符集格式

select * from sys.nls_database_parameters;

2)配置Oracle 11g 客户端字符集格式

select * from nls_instance_parameters;

3)若与服务端字符集不相符

修改注册表HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1的NLS_LANG键值为服务器端字符集,比如:SIMPLIFIED CHINESE_CHINA.ZHS16GBK。

2 Export/Import工具迁移数据

Export/Import工具历史悠久,自Oracle软件推广应用以来就被作为备份迁移的配套工具,虽然存在速度慢、效率低的诟病,但其便捷部署、灵活选择、跨平台、跨版本的特性,使得其非常吻合中小数据的备份迁移工作。

1)Export进行数据的导出

Export命令结构为:EXP USER/PASSWORD@SID FILE=EXPDAT.DMP [ PARAMETER 参数 ],因篇幅所限,本处仅列出几种常用参数和优化参数。

1TABLES:指定导出的数据表,该参数允许同时指定多个表名,相互之间以逗号分开即可。

2QUERY:指定数据表导出的条件,其条件只能限制在导出表中使用,如同时指定了多个表,其条件必须同时满足所有表,一般可以和WHERE等查询语句嵌套使用。

3BUFFER:指定数据缓冲区的大小,根据服务器配置和数据表大小适当调整缓存区大小可缩短导出时间。

4LOG:指定操作目录日志的输出,如不指定则默认输出屏幕显示不进行保存,考虑对备份文件的详细说明,强烈建议养成日志操作的习惯。

5例如:D:\oracle\dmp>EXP LPSZY/RSZZZ@JSSPRE_192.168.1.9 FILE=LPSZY_0612.DMP FILESIZE=500M TABLES=(table1,table2) QUERY=\"WHERE ID='01'\" LOG=LPSZY_0612.LOG

2)Import进行数据的导入

Import相当于Export的反向操作,Import在处理DMP文件时,首先依赖EXP命令导出的数据,如果EXP未导入相应的对象,那么IMP业务进行导入;其次根据IMP命令设置的参数控制导入的类型和内容。常用参数和示例如下:

1FROMUSER:用来指定DMP对象文件原有的属主。

2TOUSER:用来指定DMP文件的新属主,即要导入的目标用户。

3例如:IMP TEMP1/RSZZZ FILE=LPSZY_0612.DMP FROMUSER=LPSZY TOUSER=TEMP1 FULL=Y

3 Date Pump导入和导出数据

随着数据量爆炸式的增长,原生的EXP/IMP在大数据量处理时愈加力不从心,在Oracle发展到10g版本后,Date Pump横冲出世,中文译名一般称之为“数据泵”,加强了数据的并行处理能力,并支持暂停和远端操作,大幅提高了数据迁移的效率,其对应命令分别为EXPDP和IMPDP。

1)Date Pump执行导出

虽然Date Pump数据操作与原生的Export/Import工具在功能和效率区别很大,但两者的命令操作极为相似,但Date Pump是服务端工具,最终的DUMP文件是保存在目标服务器的,在执行命令之前必须为Dump文件指定Directory对象,即创建相关Directory对象并授予读写权限,相关步骤示例如下:

Date Pump的数据导出极为灵活,可按照用户、进程、表名、表空間乃至全库导出,例如按照表名进行导出:expdp LPSZY/RSZZZ@JSSPRE TABLES=table1 dumpfile=table1.dmp DIRECTORY=dump_dir;

虽然说Date Pump是工作在服务端的导出工具,但其提供了一个network_link命令参数,允许通过一个指定的本地用户导出远端数据库服务器的数据,通过这个参数我们可以变相导出远端数据库到本地,但需要在创建Directory对象和授予读写权限之前增加连接源数据库的数据库链接。若导出文件较大,应尽量避免数据库链的导出方式,因为跨数据库链必然占用网络带宽,不仅仅会降低速度,还会影响其他依赖网络的应用,建议数据源端导出后使用压缩软件压缩后直接传输。

2)Date Pump执行导入

IMPDP命令与EXPDP相对应,一般常用按用户导入,全库导入、按表空间导入和追加数据。

1按用户导入:impdp LPSZY/RSZZZDIRECTORY=dump_dir dumpfile=table1.dmp SCHEMAS=lpszy;

2全库导入:impdp LPSZY/RSZZZDIRECTORY=dump_dir dumpfile=full.dmp FULL=y;

3按表空间导入:impdp LPSZY/RSZZZDIRECTORY=dump_dir dumpfile=table1.dmp TABLESPACES=example;

4追加数据导入:impdp LPSZY/RSZZZDIRECTORY=dump_dir dumpfile=table1.dmp SCHEMAS=lpszy TABLE_EXISTS_ACTION

4 第三方工具PLSQL Developer导入和导出数据

PLSQL Developer是一个第三方公司开发的集成开发环境,主要用于数据库的开发、测试、调试和优化操作,功能全而且提供了开始化界面,尤其是Windows端加入了64位操作系统支持,已基本成为Oracle操作的必备工具。

PLSQL Developer的文件导入导出可通过可视化窗口界面直接操作,一般常用导出格式为dmp文件、sql文件和pde文件,如下图所示:

dmp文件和Exp/Imp工具导出文件一致,可以跨平台使用,效率也不错,应用最为广泛;

sql文件可直接用文本编辑器查看编辑,通用性较好,但效率低于dmp文件,比较适合小数据量的迁移导出,需要注意的是sql文件不支持大字段导出,pde文件是PLSQL Developer的专用文件格式,只能使用PLSQL Developer软件进行导入导出。

5 CSV(文本文件)的导入

在日常的数据迁移中,往往会遇到其他非标准DMP格式文件,例如软件生成的TXT文件或者电子表格的CSV文件,此时就需要利用第三方软件PLSQL Developer或者SQLloader数据加载工具进行导入。

1)PLSQL Developer导入CSV(文本文件)

PLSQL提供了图形化向导界面的CSV(文本文件)导入,相应导入步骤如下:

1开始菜单—工具—文本导入器—打开数据文件(或粘贴文件)

2切换至到Oracle数据—创建表—对应导入文件与数据表字段

3配置每次提交数量—选择是否覆盖—微调个别字段参数—确定数据导入

2)SQLloader导入CSV(文本文件)

SQLloader是Oracle发布时包含的命令行工具,命令名称为sqlldr,主要用于外部文件的导入,尤其是大量数据的导入,其效率极高。SQLLoader在运行时,需要一个控制文件和一个数据文件,在命令下执行sqlldr就可看到其详细参数说明,应着重关注一下几个参数:

6 结束语

在當今的信息化时代,包括各种概念提及的大数据、物联网,归根到底的载体都是数据,说一句数据为王并不过分,作为一个数据库操作管理人员,必须掌握一种或者多种数据备份迁移的技能,唯有如此才能减少损失,应对各种情况可能带来的数据隐患。文中虽列举了集中数据迁移的方法,但技术不是唯一的,例如TOad、ODBC数据源和联机备份等也能完成数据的 迁移,应当尽量掌握了解各种方法的优缺点和便捷程度,在合适的场景选择最稳妥快速的解决方案。

参考文献:

[1] 徐小亚 谢延华. 基于Oracle数据库的备份和恢复分析[M]. 信息安全与技术, 2014(03).

[2] 李慧. 基于RMAN的Oracle备份及其与IT服务管理的整合[M]. 信息通信,2013(03).

[3] 刘娟. Oracle超大型数据库数据迁移方法论[M]. 电脑知识与技术,2016(30).

[4] 张怀亮 徐京渝. 医院信息系统Oracle数据库更换字符集的技术实践[M]. 医疗卫生装备,2017(01).

[5] 方约翰. 基于oracle数据库的信息系统的备份方案设计[M]. 信息技术与信息化,2017(Z1).

【通联编辑:王力】