【Oracle数据迁移】Oracle19C数据泵数据迁移

【Oracle数据迁移】Oracle19C数据泵数据迁移
版本：Oracle19C
需求：将10.64.147.207业务数据库PDB2上面的业务数据迁移至10.64.87.206数据库实例下

• 1、在10.64.147.207做数据导出工作

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB2 READ WRITE NO
SQL> alter session set container=pdb2;

Session altered.

SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
--------------- ------------------------- -------------------------------------------------------------------------------- -------------
SYS DATA_PUMP_DIR /data/app/oracle/admin/icucdb1/dpdump/0682123AEFD04CFEE065C6EF668A642B 1

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where account_status = 'OPEN';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SYS SYSTEM TEMP
SYSTEM SYSTEM TEMP
PDBADMIN USERS TEMP
MED TSP_MED TEMP_MED
MED TSP_MED TEMP_MED
这里迁移SCHEMA：MED、MED,数据泵导出数据至/data/app/oracle/admin/icucdb1/dpdump/0682123AEFD04CFEE065C6EF668A642B
expdp 'system/"#system"'@pdb2 schemas=MED dumpfile=MED_`date +"%Y-%m-%d"`.dmp logfile=expdp_MED_`date +"%Y-%m-%d"`.log DIRECTORY=DATA_PUMP_DIR
expdp 'system/"#system"'@pdb2 schemas=MED dumpfile=MED_`date +"%Y-%m-%d"`.dmp logfile=expdp_MED_`date +"%Y-%m-%d"`.log DIRECTORY=DATA_PUMP_DIR

关闭pdb数据库
alter pluggable database pdb2 close;

• 2、在主机10.64.87.206导入业务数据，将导出的DMP文件上传至 DATA_PUMP_DIR

在10.64.87.206上，获取数据库信息
SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 pdb1 READ WRITE YES
SQL> alter session set container=pdb1;

Session altered.
确认PDB的永久表空间
SQL> SELECT NAME, VALUE$ FROM PROPS$ WHERE NAME='DEFAULT_PERMANENT_TABLESPACE';
NAME VALUE$
-------------------------------------------------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE USERS

SQL> SELECT NAME, VALUE$ FROM PROPS$ WHERE NAME='DEFAULT_TEMP_TABLESPACE';
NAME VALUE$
-------------------------------------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/data/oradata/HAICDB1/pdb1/temp01.dbf TEMP
/data/oradata/HAICDB1/pdb1/temp_nis01.dbf TEMP_NIS
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
--------------- ------------------------- -------------------------------------------------------------------------------- -------------
SYS DATA_PUMP_DIR /data/app/oracle/admin/haicdb1/dpdump/0667C91C40A66181E065020843523EE2 1

SQL>

新建10.64.147.207同名的数据库PDB2
-- 其中PDB2是需求创建的可插接式数据库，pdbadmin是创建的用户，Learning是密码。file_name_convert换成相应目录就OK了
create pluggable database PDB2 admin user pdbadmin identified by PDB2 roles=(connect) file_name_convert=('/data/oradata/HAICDB1/pdbseed','/data/oradata/HAICDB1/pdb2')
default tablespace users datafile '/data/oradata/HAICDB1/pdb2/user01.dbf' size 512m autoextend on;

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN RESTRICTED;

根据主机207的业务用户信息，在10.64.87.206上面创建业务账号
MED TSP_MED TEMP_MED
MED TSP_MED TEMP_MED
SQL> alter session set container=pdb2;
Session altered.

create tablespace TSP_MED datafile '/data/oradata/HAICDB1/pdb2/tsp_med01.dbf' size 2G autoextend on;
create temporary tablespace TEMP_MED tempfile '/data/oradata/HAICDB1/pdb2/temp_med.dbf' size 2G autoextend on;
create user MED identified by "password" account unlock;
alter user MED default tablespace TSP_MED temporary tablespace TEMP_MED;

create tablespace TSP_MED datafile '/data/oradata/HAICDB1/pdb2/tsp_med01.dbf' size 2G autoextend on;
create temporary tablespace TEMP_MED tempfile '/data/oradata/HAICDB1/pdb2/temp_med.dbf' size 2G autoextend on;
create user MED identified by "password" account unlock;
alter user MED default tablespace TSP_MED temporary tablespace TEMP_MED;

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where account_status = 'OPEN';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ------------------------------ ------------------------------
SYS SYSTEM TEMP
SYSTEM SYSTEM TEMP
PDBADMIN USERS TEMP
MED TSP_MED TEMP_MED
MED TSP_MED TEMP_MED

创建Profile

导入207库上面的数据

SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
--------------- ------------------------- -------------------------------------------------------------------------------- -------------
SYS DATA_PUMP_DIR /data/app/oracle/admin/haicdb1/dpdump/0A5418B685F66979E065020843523EE2 1

导入数据
impdp 'system/"#system"'@pdb2 DIRECTORY=DATA_PUMP_DIR dumpfile=MED_2023-11-17.dmp SCHEMAS=MED exclude=statistics
impdp 'system/"#system"'@pdb2 DIRECTORY=DATA_PUMP_DIR dumpfile=MED_2023-11-17.dmp SCHEMAS=MED exclude=statistics