文档课题：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

 

说明：如上所示，导入后目标端数据与源端数据保持一致.