【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';

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 ;

根据主机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;

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 ：passwd_unlimited

导入207库上面的数据

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

导入数据
impdp 'system/"#system"'@pdb2 DIRECTORY=DATA_PUMP_DIR dumpfile=MED_2023-11-17.dmp logfile=MED_2023-11-17.log SCHEMAS=MED remap_tablespace=TSP_MED:MED
对于导入中断，重新导入的场景：加TABLE_EXISTS_ACTION=REPLACE

impdp 'system/"#system"'@pdb2 DIRECTORY=DATA_PUMP_DIR dumpfile=MED_2023-11-17.dmp SCHEMAS=MED remap_tablespace=TSP_MED:MED exclude=statistics TABLE_EXISTS_ACTION=REPLACE

跨版本的迁移：使用数据泵导出成11G dmp文件
expdp 'system/"xxx"'@pdb1 schemas=med directory=DATA_PUMP_DIR dumpfile=med_20240719.dmp logfile=med_20240719.log version=11.2.0.4.0

posted on 2023-11-17 15:45 HelonTian 阅读(325) 评论(0) 编辑收藏举报