您好,登录后才能下订单哦!
单位有一套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表空间。
2. 目标库存在users表空间,但用户在users表空间配额为0,其默认表空间为bank_tbs。
3. 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.
再次导入,即可以正常完成了。
对 于测试数据迁移,其实还有一点,就是是不是所有数据,都需要迁移?因为往往测试库中有一些,仅临时使用的表对象等信息,如果执行前,筛选一下真正需要的数 据,再开始执行导出导入,可能只需要迁移小部分数据,对于垃圾数据就可以直接忽略,这就是人们常说优化的极致,即不做任何事。
如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。