当前位置: 代码迷 >> 综合 >> 使用expdpimpdp 迁移public dblink
  详细解决方案

使用expdpimpdp 迁移public dblink

热度:47   发布时间:2023-12-20 12:20:41.0

--oracle中有时在库间迁移时,按schema导出时不会导出public对象,可以通过数据泵expdp和impdp的方式进行迁移

--测试如下:

环境:11gr2  redhat7 单节点

步骤:

1、创建public dblink
create public database link test 
 connect to r_test   IDENTIFIED BY  test123
 using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mydb11g)
    )
  )';

测试dblink连通性

select * from dual@test;

--DUMMY
--X

2、创建导出目录

CREATE DIRECTORY test AS '/oracle/dump_dir';


3、按dblink名称导出dblink,注意命令行的字符转义,如果不想用转义可以通过parfile方式导

expdp  r_test/test123  DIRECTORY=test  dumpfile=expdp_public_dblink.dmp LOGFILE=expdp_public_dblink.log  full=y include=DB_LINK:\"IN \(\'TEST\'\)\"  


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "R_TEST"."SYS_EXPORT_FULL_01":  r_test/******** DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=expdp_public_dblink.log full=y include=DB_LINK:"IN ('TEST')" 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "R_TEST"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for R_TEST.SYS_EXPORT_FULL_01 is:
  /oracle/dump_dir/expdp_public_dblink.dmp
Job "R_TEST"."SYS_EXPORT_FULL_01" successfully completed at Mon Jul 15 17:09:49 2019 elapsed 0 00:00:08

4、删除dblink

drop public database link test;


5、导入

impdp  r_test/test123  DIRECTORY=test  dumpfile=expdp_public_dblink.dmp LOGFILE=impdp_public_dblink.log  

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "R_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "R_TEST"."SYS_IMPORT_FULL_01":  r_test/******** DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=impdp_public_dblink.log 
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "R_TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 15 17:12:20 2019 elapsed 0 00:00:01


6、检查导入结果,检查dblink连通性
select * from dba_db_links;

select * from dual@test;

--DUMMY
--X

--测试成功,可以通过expdp和impdp实现public dblink的迁移,这在不知道创建dblink的用户密码时比较有用。同理还有public synonym也可以用这种方式

  相关解决方案