背景
公司之前部门拆分,但一些服务并没有拆分清楚。其中一个老服务,两个部门都在用,现在为了避免互相影响,决定克隆该服务。克隆就要克隆全套,当然也包括数据库,我们这个老服务,用的oracle,所以,就涉及到从旧的oracle中导出数据,然后再导入到另一套新的oracle实例中。
届时在线上肯定是要物理隔离的,就是oracle要建在各自的服务器上,服务器间网络隔离;但我现在只是在开发环境验证拆分后功能是否正常,所以偷了个懒,没有新建实例,只是新建了个用户,进行逻辑隔离。
下午简单弄了下,踩了几个小坑,记录下来备忘。
导出步骤
schema、用户
一个oracle实例下,可以有多个用户,用户间逻辑隔离。用户和schema是什么关系呢?可以看我在dbeaver中建schema时候的弹框,直接就说创建schema等同于创建用户:
创建用户的时候,默认就会创建一个同名的schema。像表这种东西,不会直接和用户挂钩,而是挂在某个schema下,简单而言,schema相当于是用户和表、索引这些object间的一个中间层。具体可以看看这篇文章:
https://www.modb.pro/db/508147
要导出的目标
我这边就是要把如下左侧这个HX_PLAT_NEW这个schema中的所有数据导出,然后再导入到一个新的schema中(做逻辑隔离)。其实本来想做物理隔离,重新搭个oracle实例,感觉有点繁琐,先偷个懒吧。
导出的话,我先是看了下dbeaver,发现好像没有这块功能,于是在网上找,发现有两种方式:
-
expdp/impdp命令,其优势是速度快,但是稍微复杂一点;
-
exp/imp命令,速度慢,适用于数据量较小的情况,使用上简单一些。
详细可以参考:https://mp.weixin.qq.com/s/voGgQseZQCp30J6Rx-vNcQ
我这边表比较多,使用的是expdp/impdp方式。
导出过程遇到的错误
首先是找运维拿到了开发机器的ssh密码,然后利用netstat -nltp根据端口找进程,根据进程找到数据库cwd目录。
找到后,在机器上执行expdp -h,也提示没有帮助文档,只能去网上找,改了改如下:
expdp 'sys/1234 as sysdba' schemas=hx_plat_new DIRECTORY=hx_plat_new_dump DUMPFILE=hx_plat_new.dmp
这里大概就是使用sys这个用户(密码:1234)作为sysdba身份登录,要导出的schema是hx_plat_new,导出后的文件名是hx_plat_new.dmp,文件放到哪里呢,由DIRECTORY这个参数指定(我自己在当前目录下建了个hx_plat_new_dump这个目录)
结果一执行,说ORACLE_HOME
环境变量不存在,设置:
vim /etc/profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
source /etc/profile
再一执行,说ORACLE_SID
这个环境变量找不到,虽然几年前搞过一阵oracle,现在我都不记得这玩意是啥,怎么设置了。
网上找到个sql查sid:
SELECT sys_context('USERENV', 'DB_NAME') AS ORACLE_SID FROM dual;
或者
SELECT sys_context('userenv','instance_name') FROM dual;
https://stackoverflow.com/questions/6288122/checking-oracle-sid-and-database-name
接着设置环境变量:
export ORACLE_SID=orcl
再执行,说目录不存在,查了下,原来directory后面不是乱写的,目录需要先创建,这里再引用。我这边是直接查了下现在有哪些可以用的目录:
SELECT directory_name, directory_path FROM dba_directories
所以,这边把目录改成了第四个(随便选一个),服务器托管网或者自己也可以自己新建,建完还要给用户授权:
# 创建文件夹
create directory 文件夹名称 as '路径,基于系统';
# 将文件夹读写权限给被授权用户
grant read,write on directory 文件夹名称 to 被授权用户;
最终的命令是:
expdp 'sys/1234QWER as sysdba' schemas=hx_plat_new DIRECTORY=DATA_PUMP_DIR DUMPFILE=hx_plat_new.dmp
然后在/u01/app/oracle/admin/orcl/dpdump/hx_plat_new.dmp
下就生成文件了。
导入步骤
建立新的schema
先是在dbeaver上建了个schema叫:hx_plat_split_test。
然后构造好了导入的命令:
i服务器托管网mpdp 'sys/oracle as sysdba' DIRECTORY=DATA_PUMP_DIR DUMPFILE=hx_plat_new.dmp schemas=HX_PLAT_SPLIT_TEST
结构一直报错说schema:HX_PLAT_SPLIT_TEST找不到:
后边比较了下这个在dbeaver中建的用户和其他用户,感觉有点不一样:
于是把dbeaver建出来的删了,在sqlplus中创建。
SQL> CREATE USER HX_PLAT_SPLIT_TEST IDENTIFIED BY "1qazxxxx";
User created.
建用户的过程中,当时也报错,后来发现是说,密码如果是数字开头,必须用双引号。
参考:https://stackoverflow.com/questions/36612365/oracle-sql-plus-error-ora-00922-missing-or-invalid-option-when-creating-user
导入
新建用户后执行,还是报相同错误,后来以为是权限问题导致,准备对比下这个用户是不是权限和其他人不一样。网上看了几篇讲权限的文章,发现挺复杂,于是先不深究,找了个sql先执行下授权:
GRANT IMP_FULL_DATABASE TO HX_PLAT_SPLIT_TEST;
结果还是没效果。后面网上查了下,发现有个文章很像我的问题:
https://blog.csdn.net/lxpaopao/article/details/114930924
通过以下命令导入数据到oracle数据库,报”ORA-39002: 操作无效”,”ORA-39165: 未找到方案” 错误
impdp tms/tms123@orcl schema=tms directory=dump_dir dumpfile=ZBTMS2021031701.DMP
原因是通过expdp导出的用户,与需要导入的用户名称不一致
意思就是,导出和导入的用户不一样会有这个问题,于是照着改了改。
impdp 'sys/oracle as sysdba' remap_schema=HX_PLAT_NEW:HX_PLAT_SPLIT_TEST remap_tablespace=HX_PLAT_NEW:HX_PLAT_SPLIT_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=hx_plat_new.dmp schemas=HX_PLAT_SPLIT_TEST
发现还是报错,后面去掉了最后的schemas=HX_PLAT_SPLIT_TEST
:
impdp 'sys/oracle as sysdba' remap_schema=HX_PLAT_NEW:HX_PLAT_SPLIT_TEST remap_tablespace=HX_PLAT_NEW:HX_PLAT_SPLIT_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=hx_plat_new.dmp
嗯,这下ok了。
总结
oracle这个还是过于复杂了,几年前也是小公司,dba出差了,项目需要,被迫搞了一阵,太痛苦了,几年下来,又全忘了。
参考资料
这里记录下后续发现的一些不错的参考资料:
官方,包含命令的各个选项的意思(11g版本的):
https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL824
https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300
https://mp.weixin.qq.com/s/voGgQseZQCp30J6Rx-vNcQ
https://mp.weixin.qq.com/s/m7GrQnJ-QalTf1lluUO4Gg
https://mp.weixin.qq.com/s/IvzXv4CKAQELGKcH3tbW4Q
如果没有服务器登录权限,可以采用第三方工具如TOAD:
https://mp.weixin.qq.com/s/hFmH-k6Bs65xlswU50bhfg
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net