数据泵：19c PDB数据泵迁入_非关系型数据库

概述1.问题描述用数据泵进行pdb的迁入迁出，模拟测试将其他库的数据导入到19cpdb中 2.环境介绍 source：12.2.0.1.0 target：19.0.0.0.0 3.源端制造数据创建表空 1.问题描述

用数据泵进行pdb的迁入迁出，模拟测试将其他库的数据导入到19cpdb中

2.环境介绍

source：12.2.0.1.0

target：19.0.0.0.0

3.源端制造数据

创建表空间

create tablespace ODPS logging datafile '/u01/app/oracle/oradata/PROD/ODPS.dbf' size 500m autoextend on next 50m maxsize 20480m extent management local;

创建用户

create user global IDentifIEd by global default tablespace ODPStemporary tablespace temp profile default;

授权用户

grant connect,resource to global;grant dba to global;

建表

conn global/create table DXP_QGM_TEST_THREE(ID NUMBER(4) PRIMARY KEY,DOM_DATETIME VARCHAR(255),DOM_ID NUMBER(10),DOM_STRING VARCHAR2());insert into DXP_QGM_TEST_THREE (ID,DOM_DATETIME,DOM_ID,DOM_STRING) select rownum as ID,to_char(sysdate + rownum / 24 / 3600,yyyy-mm-dd hh24:mi:ss') as DOM_DATETIME,trunc(dbms_random.value(0,1)">100)) as DOM_ID,dbms_random.string(x',1)">20) DOM_STRING from dual connect by level <= 1000;

4.导出数据

SYS@PROD> select * from directorIEs;DATA_PUMP_DIR：/u01/app/oracle/admin/PROD/dpdump/expdp "'sys/oracle as sysdba'" schemas=global dumpfile=global20210528.dmp DIRECTORY=DATA_PUMP_DIR logfile=global20210528.log  compression=all parallel=4;

5.target创建pdb

sql> !mkdir -p $ORACLE_BASE/oradata/CDB2/pdb4sql> create pluggable database pdb4 admin user pdbadmin IDentifIEd by Welcome1 roles =(connect) create_file_dest=/u01/app/oracle/oradata/CDB2/pdb4';  2  Pluggable database created.sql> show pdbs    CON_ID CON_name              OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------      PDB$SEED              READ ONLY  NO     3 PDB1               MOUNTED      PDB2               MOUNTED     5 PDB3               MOUNTED     6 PDB4               MOUNTEDsql> alter pluggable database pdb4 open;Pluggable database altered.sql>6 PDB4               READ WRITE NO

配置tnsname.ora,添加pdb4的tns解析字符串

[oracle@rac1 ~]$ vim $ORACLE_HOME/network/admin/tnsnames.ora

PDB4 =
(DESCRIPTION =
(ADDRESS = (PROTOCol = TCP)(HOST = rac1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_name = PDB4)
)
)

确保能够被解析

[oracle@rac1 ~]$ tnsPing pdb4

TNS Ping Utility for linux: Version 19.0.0.0.0 - Production on 29-MAY-2021 09:14:02

Used parameter files:

Used TNSnameS adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCol = TCP)(HOST = rac1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_name = PDB4)))
OK (0 msec)

6.恢复dump文件

切换为pdb4


sql> alter session set container=pdb4;Session altered.

查询数据字典

 dba_directorIEs;DATA_PUMP_DIR/u01/app/oracle/admin/cdb2/dpdump/C36EAB45FFE40BA4E0530BA3A8C0F174

创建表空间

create tablespace pdb4_odpsdatafile /u01/app/oracle/oradata/CDB2/pdb4/CDB2/C36EAB45FFE40BA4E0530BA3A8C0F174/datafile/pdb4_odps.dbfsize 1024mautoextend onnext 50m maxsize 20480mextent management local;

创建用户并指定表空间

create user wangyong IDentifIEd by wangyong default tablespace pdb4_odps;

给用户授权

grant connect,resource,dba to wangyong;grant read,write on directory DATA_PUMP_DIR to wangyong;

将dump文件传输到pdb的DATA_PUMP_DIR目录中

[oracle@enmoedu1 dpdump]$ scp global20210528.* oracle@rac1:/u01/app/oracle/admin/cdb2/dpdump/C36EAB45FFE40BA4E0530BA3A8C0F174oracle@rac1s password: global20210528.dmp                                                               100%  120KB 120.0KB/s   00:00    global20210528.log                                                               100% 1572     1.5KB/s   00

impdp恢复数据，恢复界面要查看当前的实例名是否是pdb下的cdb

[oracle@rac1 ~]$ echo $ORACLE_SIDtest[oracle@rac1 ~]$ export  ORACLE_SID=cdb2

前面已经加入了pdb4的tns解析，所以在导入的时候@pdb4就可以指定导入所需要的pdb中了

[oracle@rac1 ~]$ impdp 'sys/Welcome1@pdb4 as sysdba'" directory=DATA_PUMP_DIR dumpfile=global20210528.dmp logfile=global20210528.log version=12.2.0.1.0' remap_tablespace=ODPS:pdb4_odps  remap_schema=:wangyongimport: Release 19.0.0.0.0 - Production on Sat May 29 09:43:11 2021Version 19.3.0copyright (c) 1982,1)">2019,Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 0 - ProductionMaster table SYS".SYS_import_FulL_01" successfully loaded/unloadedStarting ":  sys/********@pdb4 AS SYSDBA12.2.0.1.0 remap_tablespace=ODPS:pdb4_odps remap_schema=:wangyong Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:WANGYONG" already existsProcessing SYstem_GRANTProcessing RolE_GRANTProcessing DEFAulT_RolEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/table/tableProcessing table_DATA. . imported DXP_QGM_TEST_THREE"             23.35 KB    1000 rows. . imported T1"                             4.789 KB       1 rowsProcessing object type SCHEMA_EXPORT/table/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/table/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/table/STATISTICS/table_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob " completed with 1 error(s) at Sat May 28 2021 elapsed 0 16

7.验证数据

sql> show con_nameCON_name------------------------------PDB4sql> alter session cdb$root;Session altered.sql> conn wangyong/wangyong@pdb4Connected.sql> desc t1; name                       Null?    Type ----------------------------------------- -------- ---------------------------- ID                            NUMBER(38) name                            VARCHAR2(39)sql> select count(*)  DXP_QGM_TEST_THREE;  COUNT(*)----------      1000