【Oracle数据迁移】Oracle19C数据泵数据迁移
版本：Oracle19C
需求：将10.64.147.207业务数据库ICUPDB1上面的业务数据迁移至10.64.147.206数据库实例下
- 1、在10.64.147.207做数据导出工作
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ICUPDB1 READ WRITE NO
SQL> alter session set container=icupdb1;
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
MEDCOMM TSP_MEDCOMM TEMP_MEDCOMM
MEDICU TSP_MEDICU TEMP_MEDICU
这里迁移SCHEMA：MEDCOMM、MEDICU,数据泵导出数据至/data/app/oracle/admin/icucdb1/dpdump/0682123AEFD04CFEE065C6EF668A642B
expdp 'system/"#system"'@icupdb1 schemas=MEDCOMM dumpfile=MEDCOMM_`date +"%Y-%m-%d"`.dmp logfile=expdp_MEDCOMM_`date +"%Y-%m-%d"`.log DIRECTORY=DATA_PUMP_DIR
expdp 'system/"#system"'@icupdb1 schemas=MEDICU dumpfile=MEDICU_`date +"%Y-%m-%d"`.dmp logfile=expdp_MEDICU_`date +"%Y-%m-%d"`.log DIRECTORY=DATA_PUMP_DIR
关闭pdb数据库
alter pluggable database icupdb1 close;
- 2、在主机10.64.147.206导入业务数据，将导出的DMP文件上传至 DATA_PUMP_DIR
在10.64.147.206上，获取数据库信息
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HAIPDB1 READ WRITE YES
SQL> alter session set container=HAIPDB1;
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/haipdb1/temp01.dbf TEMP
/data/oradata/HAICDB1/haipdb1/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同名的数据库ICUPDB1
-- 其中ICUPDB1是需求创建的可插接式数据库，pdbadmin是创建的用户，Learning是密码。file_name_convert换成相应目录就OK了
create pluggable database ICUPDB1 admin user pdbadmin identified by ICUPDB1 roles=(connect) file_name_convert=('/data/oradata/HAICDB1/pdbseed','/data/oradata/HAICDB1/icupdb1')
default tablespace users datafile '/data/oradata/HAICDB1/icupdb1/user01.dbf' size 512m autoextend on;
SQL> ALTER PLUGGABLE DATABASE ICUPDB1 OPEN RESTRICTED;
根据主机207的业务用户信息，在10.64.147.206上面创建业务账号
MEDCOMM TSP_MEDCOMM TEMP_MEDCOMM
MEDICU TSP_MEDICU TEMP_MEDICU
SQL> alter session set container=icupdb1;
Session altered.
create tablespace TSP_MEDCOMM datafile '/data/oradata/HAICDB1/icupdb1/tsp_medcomm01.dbf' size 2G autoextend on;
create temporary tablespace TEMP_MEDCOMM tempfile '/data/oradata/HAICDB1/icupdb1/temp_medcomm.dbf' size 2G autoextend on;
create user MEDCOMM identified by "password" account unlock;
alter user MEDCOMM default tablespace TSP_MEDCOMM temporary tablespace TEMP_MEDCOMM;
create tablespace TSP_MEDICU datafile '/data/oradata/HAICDB1/icupdb1/tsp_medicu01.dbf' size 2G autoextend on;
create temporary tablespace TEMP_MEDICU tempfile '/data/oradata/HAICDB1/icupdb1/temp_medicu.dbf' size 2G autoextend on;
create user MEDICU identified by "password" account unlock;
alter user MEDICU default tablespace TSP_MEDICU temporary tablespace TEMP_MEDICU;
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
MEDCOMM TSP_MEDCOMM TEMP_MEDCOMM
MEDICU TSP_MEDICU TEMP_MEDICU
创建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"'@icupdb1 DIRECTORY=DATA_PUMP_DIR dumpfile=MEDCOMM_2023-11-17.dmp SCHEMAS=MEDCOMM exclude=statistics
impdp 'system/"#system"'@icupdb1 DIRECTORY=DATA_PUMP_DIR dumpfile=MEDICU_2023-11-17.dmp SCHEMAS=MEDICU exclude=statistics