数据泵：19c PDB数据泵迁入

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 ODPS
temporary 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_datetiR_127_11845@E VARCHAR(255),DOM_id number(10),DOM_StriNG VARCHAR2()
);

insert into DXP_QGM_test_THREE (ID,DOM_datetiR_127_11845@E,DOM_ID,DOM_StriNG) SELEct rownum as ID,TO_CHAR(sysdate + rownum / 24 / 3600,yyyy-mm-dd hh24:mi:ss') as DOM_datetiR_127_11845@E,trunc(DBMS_random.value(0,1)">100)) as DOM_ID,DBMS_random.String(x',1)">20) DOM_StriNG from dual connect by level <= 1000;

SYS@PROD> SELEct * from directorIEs;
DATA_PUMP_DIR：/u01/app/Oracle/admin/PROD/dpdump/

expdp "'sys/Oracle as sysdba'" scheR_127_11845@as=global dumpfile=global20210528.dmp DIRECTORY=DATA_PUMP_DIR logfile=global20210528.log  compression=all parallel=4;

sql> !mkdir -p $OracLE_BASE/oradata/CDB2/pdb4

sql> 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               MOUNTED
sql> alter pluggable database pdb4 open;

Pluggable database altered.

sql>6 PDB4               READ WRITE NO

[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

copyright (C) 1997,2019,Oracle. All rights reserved.

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)

sql> alter session set container=pdb4;

Session altered.

 dba_directorIEs;

DATA_PUMP_DIR
/u01/app/Oracle/admin/cdb2/dpdump/C36EAB45FFE40BA4E0530BA3A8C0F174

create tablespace pdb4_odps
DATAFILE /u01/app/Oracle/oradata/CDB2/pdb4/CDB2/C36EAB45FFE40BA4E0530BA3A8C0F174/DATAFILE/pdb4_odps.dbf
size 1024m
autoextend on
next 50m maxsize 20480m
extent 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;

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

[Oracle@rac1 ~]$ echo $oracle_sid
test
[Oracle@rac1 ~]$ export  oracle_sid=cdb2

[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_scheR_127_11845@a=:wangyong

import: Release 19.0.0.0.0 - Production on Sat May 29 09:43:11 2021
Version 19.3.0

copyright (C) 1982,1)">2019,Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 0 - Production
Master table SYS".SYS_import_FulL_01" successfully loaded/unloaded
StarTing ":  sys/********@pdb4 AS SYSDBA12.2.0.1.0 remap_tablespace=ODPS:pdb4_odps remap_scheR_127_11845@a=:wangyong 
Processing object type scheR_127_11845@A_EXPORT/USER
ORA-31684: Object type USER:WANGYONG" already exists

Processing SYstem_GRANT
Processing RolE_GRANT
Processing DEFAulT_RolE
Processing object type scheR_127_11845@A_EXPORT/PRE_scheR_127_11845@A/PROCACT_scheR_127_11845@A
Processing object type scheR_127_11845@A_EXPORT/table/table
Processing table_DATA
. . imported DXP_QGM_test_THREE"             23.35 KB    1000 rows
. . imported T1"                             4.789 KB       1 rows
Processing object type scheR_127_11845@A_EXPORT/table/CONSTraiNT/CONSTraiNT
Processing object type scheR_127_11845@A_EXPORT/table/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type scheR_127_11845@A_EXPORT/table/STATISTICS/table_STATISTICS
Processing object type scheR_127_11845@A_EXPORT/STATISTICS/@H_752_18@mARKER
Job " completed with 1 error(s) at Sat May 28 2021 elapsed 0 16

sql> show con_name

CON_name
------------------------------
PDB4
sql> alter session cdb$root;

Session altered.

sql> conn wangyong/wangyong@pdb4
Connected.
sql> desc t1;
 name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                            numbER(38)
 name                            VARCHAR2(39)

sql> SELEct count(*)  DXP_QGM_test_THREE;

  COUNT(*)
----------
      1000