数据泵：19c PDB数据泵迁入
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 ODPS temporary tablespace temp profile default;
授权用户
grant connect,resource to global; grant dba to global;
建表
conn global/global create table DXP_QGM_TEST_THREE ( ID NUMBER(4) PRIMARY KEY, DOM_DATETIME VARCHAR(255), DOM_ID NUMBER(10),DOM_STRING VARCHAR2(255) ); 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, 100)) as DOM_ID, dbms_random.string('x', 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/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 ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB3 MOUNTED 6 PDB4 MOUNTED SQL> alter pluggable database pdb4 open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB3 MOUNTED 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
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)
6.恢复dump文件
切换为pdb4
SQL> alter session set container=pdb4; Session altered.
查询数据字典
select * from 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;
将dump文件传输到pdb的DATA_PUMP_DIR目录中
[oracle@enmoedu1 dpdump]$ scp global20210528.* oracle@rac1:/u01/app/oracle/admin/cdb2/dpdump/C36EAB45FFE40BA4E0530BA3A8C0F174 oracle@rac1's password: global20210528.dmp 100% 120KB 120.0KB/s 00:00 global20210528.log 100% 1572 1.5KB/s 00:00
impdp恢复数据，恢复界面要查看当前的实例名是否是pdb下的cdb
[oracle@rac1 ~]$ echo $ORACLE_SID
test
[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=global:wangyong Import: Release 19.0.0.0.0 - Production on Sat May 29 09:43:11 2021 Version 19.3.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 Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "sys/********@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=global:wangyong Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"WANGYONG" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "WANGYONG"."DXP_QGM_TEST_THREE" 23.35 KB 1000 rows . . imported "WANGYONG"."T1" 4.789 KB 1 rows Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat May 29 09:43:28 2021 elapsed 0 00:00:16
7.验证数据
SQL> show con_name CON_NAME ------------------------------ PDB4 SQL> alter session set container=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(*) from DXP_QGM_TEST_THREE; COUNT(*) ---------- 1000
