oracle导出某个schema数据并导入到另一个schema

背景

公司之前部门拆分，但一些服务并没有拆分清楚。其中一个老服务，两个部门都在用，现在为了避免互相影响，决定克隆该服务。克隆就要克隆全套，当然也包括数据库，我们这个老服务，用的oracle，所以，就涉及到从旧的oracle中导出数据，然后再导入到另一套新的oracle实例中。

届时在线上肯定是要物理隔离的，就是oracle要建在各自的服务器上，服务器间网络隔离；但我现在只是在开发环境验证拆分后功能是否正常，所以偷了个懒，没有新建实例，只是新建了个用户，进行逻辑隔离。

下午简单弄了下，踩了几个小坑，记录下来备忘。

导出步骤

schema、用户

一个oracle实例下，可以有多个用户，用户间逻辑隔离。用户和schema是什么关系呢？可以看我在dbeaver中建schema时候的弹框，直接就说创建schema等同于创建用户：

image-20230905211447858

创建用户的时候，默认就会创建一个同名的schema。像表这种东西，不会直接和用户挂钩，而是挂在某个schema下，简单而言，schema相当于是用户和表、索引这些object间的一个中间层。具体可以看看这篇文章：

https://www.modb.pro/db/508147

image-20230905212400179

要导出的目标

我这边就是要把如下左侧这个HX_PLAT_NEW这个schema中的所有数据导出，然后再导入到一个新的schema中（做逻辑隔离）。其实本来想做物理隔离，重新搭个oracle实例，感觉有点繁琐，先偷个懒吧。

image-20230905212549132

导出的话，我先是看了下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

image-20230905215151103

接着设置环境变量：

export ORACLE_SID=orcl

再执行，说目录不存在，查了下，原来directory后面不是乱写的，目录需要先创建，这里再引用。我这边是直接查了下现在有哪些可以用的目录：

SELECT directory_name, directory_path FROM dba_directories

image-20230905215718371

所以，这边把目录改成了第四个（随便选一个），或者自己也可以自己新建，建完还要给用户授权：

# 创建文件夹
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。

然后构造好了导入的命令：

impdp \'sys/oracle as sysdba\'  DIRECTORY=DATA_PUMP_DIR DUMPFILE=hx_plat_new.dmp  schemas=HX_PLAT_SPLIT_TEST

结构一直报错说schema：HX_PLAT_SPLIT_TEST找不到：

image-20230905221427653

后边比较了下这个在dbeaver中建的用户和其他用户，感觉有点不一样：

image-20230905221611798

于是把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