文档课题：oracle数据泵+dblink 不落盘导单表的测试.
源 端：oracle 19.13 数据库实例(tmis) IP(192.168.133.101) 单实例
目标端：oracle 19.13 数据库实例(orclcdb) IP(192.168.133.101) 多租户
1、数据确认
1.1、源端数据
leo@TMIS> show user;
USER is "LEO"
leo@TMIS> select * from fruits;
F_ID S_ID F_NAME F_PRICE
---------- ---------- ------------------------------ ----------
a1 101 apple 5.2
b1 101 blackberry 10.2
bs1 102 orange 11.2
bs2 105 melon 8.2
leo@TMIS> select * from books;
BK_ID BK_TITLE COPYRIGHT
---------- -------------------------------------------------- -------------------
11078 Learning MYSQL 2010-01-01 00:00:00
11033 Study Html 2011-01-01 00:00:00
leo@TMIS> select TABLE_NAME,tablespace_name from user_tables where table_name in ('BOOKS','FRUITS');
TABLE_NAME TABLESPACE_NAME
-------------------- --------------------
BOOKS USERS
FRUITS USERS
1.2、目标端数据
leo@ORCLPDB> select * from fruits;
F_ID S_ID F_NAME F_PRICE
---------- ---------- ------------------------------ ----------
a1 101 apple 5.2
b1 101 blackberry 10.2
leo@ORCLPDB> select * from books;
BK_ID BK_TITLE COPYRIGHT
---------- -------------------------------------------------- -------------------
11078 Learning MYSQL 2010-01-01 00:00:00
11033 Study Html 2011-01-01 00:00:00
11035 How to use php 2003-01-01 00:00:00
11072 Teach yourself javascript 2005-01-01 00:00:00
leo@ORCLPDB> select TABLE_NAME,tablespace_name from user_tables where table_name in ('BOOKS','FRUITS');
TABLE_NAME TABLESPACE_NAME
-------------------- --------------------
BOOKS USERS
FRUITS USERS
2、导入前准备
2.1、源端建连接用户
sys@TMIS> create user ogg identified by ogg;
User created.
sys@TMIS> grant connect,resource,unlimited tablespace,dba to ogg;
Grant succeeded.
--测试ogg用户的连通情况.
[oracle@leo-oracle-19c admin]$ sqlplus ogg/ogg@tmis
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 29 14:29:09 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
ogg@TMIS> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
[oracle@leo-oracle-19c admin]$ tnsping tmis
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-JUL-2024 14:29:16
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-19c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tmis)))
OK (10 msec)
2.2、目标端建导入用户
sys@ORCLCDB> show con_name
CON_NAME
------------------------------
ORCLPDB
sys@ORCLCDB> drop user ogg;
User dropped.
sys@ORCLCDB> create user ogg identified by ogg;
User created.
sys@ORCLCDB> grant connect,resource,unlimited tablespace,dba,IMPORT FULL DATABASE TO ogg;
Grant succeeded.
[oracle@leo-oracle-19c ~]$ sqlplus ogg/ogg@192.168.133.101:1521/orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 29 14:52:59 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
ogg@ORCLPDB> show con_name
CON_NAME
------------------------------
ORCLPDB
2.3、目标端建dblink
2.3.1、tnsnames.ora文件
确保tnsnames.ora有如下内容：
TMIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-19c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tmis)
)
)
sys@ORCLCDB> sho con_name
CON_NAME
------------------------------
ORCLPDB
2.3.1、建dblink
sys@ORCLCDB> create public database link TMIS_LINK connect to ogg identified by ogg using 'TMIS';
Database link created.
--测试dblink.
sys@ORCLCDB> select * from leo.fruits@TMIS_LINK;
F_ID S_ID F_NAME F_PRICE
---------- ---------- ------------------------------ ----------
a1 101 apple 5.2
b1 101 blackberry 10.2
bs1 102 orange 11.2
bs2 105 melon 8.2
sys@ORCLCDB> select * from leo.books@TMIS_LINK;
BK_ID BK_TITLE COPYRIGHT
---------- -------------------------------------------------- -------------------
11078 Learning MYSQL 2010-01-01 00:00:00
11033 Study Html 2011-01-01 00:00:00
2.4、目标端建directory
sys@ORCLCDB> create directory impdp_dir as '/home/oracle/dump';
Directory created.
sys@ORCLCDB> grant read,write on directory impdp_dir to public;
Grant succeeded.
2.5、编辑导入脚本
[oracle@leo-oracle-19c ~]$ cat impdp.sh
#!/bin/bash
export ORACLE_SID=orclcdb
impdp ogg/ogg@192.168.133.101:1521/orclpdb \
network_link=TMIS_LINK \
remap_schema=LEO:LEO \
tables=leo.fruits,leo.books \
REMAP_TABLESPACE=USERS:USERS \
table_exists_action=replace \
directory=impdp_dir \
parallel=8 \
job_name=impdp#LEO.job \
logfile=impdp#LEO.out
[oracle@leo-oracle-19c ~]$ sh impdp.sh
Import: Release 19.0.0.0.0 - Production on Mon Jul 29 15:09:07 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "OGG"."IMPDP#LEO": ogg/********@192.168.133.101:1521/orclpdb network_link=TMIS_LINK remap_schema=LEO:LEO tables=leo.fruits,leo.books REMAP_TABLESPACE=USERS:USERS table_exists_action=replace directory=impdp_dir parallel=8 job_name=impdp#LEO.job logfile=impdp#LEO.out
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "LEO"."BOOKS" 2 rows
. . imported "LEO"."FRUITS" 4 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "OGG"."IMPDP#LEO" successfully completed at Mon Jul 29 15:09:16 2024 elapsed 0 00:00:08
3、数据验证
--目标端进行数据验证.
leo@ORCLPDB> select * from books;
BK_ID BK_TITLE COPYRIGHT
---------- -------------------------------------------------- -------------------
11078 Learning MYSQL 2010-01-01 00:00:00
11033 Study Html 2011-01-01 00:00:00
leo@ORCLPDB> select * from fruits;
F_ID S_ID F_NAME F_PRICE
---------- ---------- ------------------------------ ----------
a1 101 apple 5.2
b1 101 blackberry 10.2
bs1 102 orange 11.2
bs2 105 melon 8.2
说明：如上所示，导入后目标端数据与源端数据保持一致.- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139.
- 140.
- 141.
- 142.
- 143.
- 144.
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.
- 152.
- 153.
- 154.
- 155.
- 156.
- 157.
- 158.
- 159.
- 160.
- 161.
- 162.
- 163.
- 164.
- 165.
- 166.
- 167.
- 168.
- 169.
- 170.
- 171.
- 172.
- 173.
- 174.
- 175.
- 176.
- 177.
- 178.
- 179.
- 180.
- 181.
- 182.
- 183.
- 184.
- 185.
- 186.
- 187.
- 188.
- 189.
- 190.
- 191.
- 192.
- 193.
- 194.
- 195.
- 196.
- 197.
- 198.
- 199.
- 200.
- 201.
- 202.
- 203.
- 204.
- 205.
- 206.
- 207.
- 208.
- 209.
- 210.
- 211.
- 212.
- 213.
- 214.
- 215.
- 216.
- 217.
- 218.
- 219.
- 220.
- 221.
- 222.
- 223.
- 224.
- 225.
- 226.
- 227.
- 228.
- 229.
- 230.
- 231.
- 232.
- 233.
- 234.
- 235.
651
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
