数据迁移中碰见的一些问题

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 数据迁移中碰见的一些问题

单位有一套Oracle 9i的古老测试数据库,因为机房搬迁,所以需要迁移数据,新库是Oracle 11g了,一个比较简单的需求,但过程中碰见了一些问题,看似比较琐碎,值得总结一下。

由于源库是9i,因此只能用imp/exp,不能用数据泵。

问题1:导入目标库用户的默认表空间

源库由于不规范的使用,对象默认存储的是数据库默认表空间USERS,既然是迁移,新库就要尽量规范一些。但问题来了,impdp/expdp可以使用remap_tablespace映射新旧表空间,exp/imp应该如何做?

网上有一种说法是,首先收回用户user的unlimited tablespace权限,然后设置user默认表空间为bank_tbs,再将user对system和users表空间配额设置为0,意图是让imp导入的时候,发现users表空间无权限,则自动找用户的默认表空间bank_tbs。

revoke unlimited tablespace from user;
alter user user quota unlimited on bank_tbs;
alter user user quota 0 on system;
alter user user quota 0 on users;

但从我实测看,并不是这样,可以使用imp命令的show选项,看dmp文件内容,create table子句是会跟着tablespace users,即指定了表使用的表空间名称,由于user用户在users表空间配额为0,因此会报quota相关的错误,并不会找用户默认的bank_tbs表空间。

我们再捋一下,

  1. dump文件中有指定了tablespace users表空间。
  1. 目标库存在users表空间,但用户在users表空间配额为0,其默认表空间为bank_tbs。

  2. imp执行导入,报错users表空间quota错误。

用户默认表空间的作用,是若create table语句未指定tablespace子句,则会默认存储此表空间,既然如此,既然如此,又由于这是一套测试库,因此首先改一下users表空间名称,

alter tablespace users rename to users_k;

然后执行imp导入,就可以正常存入user用户默认的bank_tbs中。顺着思路想,可以改一下数据库的默认表空间users,只要保证不存在users表空间,dmp中create table语句就不能根据tablesapce子句,插入对应的表空间,而是找用户默认的表空间。

除此之外,可以初始化就导入users表空间,然后拼接SQL语句,将对象可以move至其他表空间,当然这就需要两倍的空间。另外还可以收工改一下dmp文件中tablespace子句对应的表空间,但只适应于小容量文件。

这里有一些知识点值得关注,

  1. unlimited tablespace权限,是为用户授予resource角色是自动添加的,但从安全性的角度来考虑,在创建用户并且授予resource角色之后应该回收unlimited tablespace这个系统权限,原因就是有了这个权限,用户可以在任意表空间中创建对象,就有可能恶意占领系统表空间,影响数据库的正常运行。

  2. Oracle 9i以前,数据库默认用户的表空间是SYSTEM,这是极为不合理的,因为SYSTEM存储的是数据库重要的底层数据字典信息,如果无限制地存储用户数据,极有可能影响数据库的运行。从9i开始,默认表空间则变为了USERS,建库的时候会默认创建。

使用如下语句,可以查询当前系统默认表空间,

select property_value from database_properties where property_name = ‘DEFAULT_PERMANENT_TABLESPACE’;

使用如下语句,可以改下当前数据库默认的用户表空间以及临时表空间,

alter database default [temporary] tablespace tablespace_name;

问题2:数据库字符集

为了保证数据导出导入,不会出现乱码,字符集要尽量保持一致,可以使用如下语句检索当前数据库使用的字符集,

select userenv(‘language’) from dual;

例如返回结果是AMERICAN_AMERICA.ZHS16GBK。

若要检索当前操作系统字符集,可以使用,

echo $NLS_LANG

例如返回结果是AMERICAN_AMERICA.AL32UTF8。

若要更新操作系统字符集,可以使用,

export  NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

问题3:导入过程中的一些报错

报错1:

Export file created by EXPORT:V09.02.00 via conventional path IMP-00013: only a DBA can import a file exported by another DBA IMP-00000: Import terminated unsuccessfully

错误信息提示,只用DBA用户可以导入另一个DBA导出的文件。意思就是这个dmp文件,导出用户是有DBA角色的,因此导入使用的用户,必须要有DBA角色。

解决方法1:使用非DBA角色的用户,重新exp导出,再用非DBA用户imp导入。

解决方法2:使用DBA用户执行imp导入操作。

相比而言,生产系统一般会选择方案1,毕竟一般业务数据的属主,不会是一个DBA角色的用户,如果用方案2,则要求目标端用户需要DBA角色,未来要是再有导出导入需求,还是需要DBA角色,无休无止了。

报错2:

Export file created by EXPORT:V09.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments IMP-00000: Import terminated unsuccessfully

此时执行imp可以指定full=y,或者使用fromuser和touser参数,例如,

imp user/user file=… log=… fromuser=user touser=user

明确导出和导入的用户名称。

问题4:创建视图报错

导入日志中显示,创建视图的时候报错了,

ORA-01031: insufficient privileges

原因就是为用户授予resource和connect常规角色,并不会自动授予创建视图的权限,具体可以参考(http://blog.csdn.net/bisal/article/details/31735185),此时可以授予,

SQL grant createany view to user;
Grant succeeded.

Grant succeeded.

再次导入,即可以正常完成了。

对于测试数据迁移,其实还有一点,就是是不是所有数据,都需要迁移?因为往往测试库中有一些,仅临时使用的表对象等信息,如果执行前,筛选一下真正需要的数据,再开始执行导出导入,可能只需要迁移小部分数据,对于垃圾数据就可以直接忽略,这就是人们常说优化的极致,即不做任何事。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

数据迁移中碰见的一些问题 数据迁移中碰见的一些问题
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 数据迁移中碰见的一些问题


 上一篇
修改表字段长度的操作,对业务是否有影响? 修改表字段长度的操作,对业务是否有影响?
前两天测试同学问了一个问题,表中某一个字段,需要改一下长度,对业务是否会有影响? 可能隐约之中,我们觉得没影响,但又好像有影响,究竟有何影响,我们从实验来看最科学。 首先建测试表,NAME字段是VARCHAR2(10),10个字节的字符串类
下一篇 
MySQL的rpm和源码两种安装操作 MySQL的rpm和源码两种安装操作
相比于传统行业,互联网浪潮中MySQL一直是数据库的主力军,无论是曾经的SUN,还是现在的Oracle,虽然可能商业策略不同,但发展方向还是平稳向前的,因此说自己不会MySQL,还真有些不好意思了。 我只能算是MySQL中的小小白,研究生毕