数据泵使用dblink的方式迁移数据

文档课题：数据泵使用dblink的方式迁移数据.
说明：本文内容摘自“OGG配置—oracle 11.2.0.4到oracle 19.16 ddl复制”
源    端：192.168.133.103 数据库oracle 11.2.0.4 64位，实例名：orcl
目 标 端：192.168.133.109 数据库oracle 19.16.0.0 64位，实例名：simdb
说    明：该方法的优点在于不用执行单独在expdp，而且是直接导入到目标端，解决源端因 expdp导出的dmp文件导致空间不足的问题.
1、创建db_link
--配置tns 服务名TEST_LINK.
[oracle@leo-19c-ogg admin]$ vi tnsnames.ora
添加如下：
ORCL_LINK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.103)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
说明：192.168.133.103为源端IP.
--创建db link.以下oracle_4U为源端system密码.
sys@SIMDB 2022-12-07 23:17:06> create public database link ORCL_LINK connect to system identified by oracle_4U using 'ORCL_LINK';

Database link created.
--初始化完成后可删除此dblink.
DROP PUBLIC DATABASE LINK ORCL_LINK;
--目标端测试db link.
sys@SIMDB 2022-12-07 23:19:22> select * from dual@ORCL_LINK;

D
-
X
sys@SIMDB 2022-12-07 23:21:25> select instance_name from v$instance@ORCL_LINK;

INSTANCE_NAME
----------------
orcl
2、查源端scn
sys@ORCL 2022-12-07 23:22:49> SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN" FROM v$transaction
  2  UNION ALL
  3  SELECT TO_CHAR(current_scn) FROM v$database;

Please select the minimum SCN
----------------------------------------

2716340
3、创建表空间、目录
--数据泵导入数据，目标端创建表空间.
sys@SIMDB 2022-12-07 23:21:47> create tablespace PROD_SCFOP_TBS datafile '/u01/app/oracle/oradata/SIMDB/prod_scfop_tbs01.dbf' size 2g;

Tablespace created.
--目标端创建dump数据目录.
[oracle@leo-19c-ogg ~]$ mkdir dump
sys@SIMDB 2022-12-07 23:30:05> create directory expdp_dir as '/home/oracle/dump/';

Directory created.

sys@SIMDB 2022-12-07 23:31:09> grant read,write on directory expdp_dir to public;

Grant succeeded.
4、导入数据
--目标端使用dblink初始化数据.
[oracle@leo-19c-ogg ~]$ nohup impdp system/oracle_4U@SIMDB directory=EXPDP_DIR version=11.2.0.4 NETWORK_LINK=ORCL_LINK flashback_scn=2716340 exclude=statistics parallel=4 schemas=SCOTT logfile=impdp_scott.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y &
[oracle@leo-19c-ogg ~]$ ll
total 4
drwxr-xr-x 2 oracle oinstall  29 Dec  7 23:35 dump
-rw-------  1 oracle oinstall 629 Dec  7 23:35 nohup.out
drwxr-xr-x. 2 oracle oinstall  71 Dec  2 23:20 scripts
秒客网

数据泵使用dblink的方式迁移数据

相关文章
