记录一次 windows oracle 11.2.0.1 数据泵迁移到 Linux oracle 19c

源库环境：oracle windows 11.2.0.1
数据量：172G
字符集：AMERICAN_AMERICA.ZHS16GBK

目标库：Version 19.22.0.0.0 linux
字符集：AMERICAN_AMERICA.ZHS16GBK

源库表空间信息：

远端创建目录

create or replace directory dmpbak as 'F:\dmpbak';
select directory_name,directory_path from dba_directories;

源库导出：

expdp luan/luan1 directory=dmpbak LOGFILE=HFMS.log dumpfile=HFMS.dmp SCHEMAS=HFMS COMPRESSION=ALL job_name=HFMS01  CLUSTER=N

目标端创建表空间信息就行,用户信息包含在dump文件中

  create temporary tablespace HFMS_TEMP tempfile '+DATA' 
  size 20480m autoextend off
  extent management local; 
                       
  create tablespace HFMS datafile 
  '+DATA' size 65535M，
  '+DATA' size 65535M   
  reuse autoextend off logging online permanent                           
  blocksize 16384 
  extent management local default nocompress
  segment space management auto;
     
  create user HFMS identified by "xxxx"                         
  default tablespace HFMS temporary tablespace HFMS_TEMP;                
                                                                                           
  grant connect,resource to HFMS;                     
  alter user HFMS quota unlimited on HFMS ;                                                 
                                   

目标端创建dump路径

create or replace directory dmpbak as '/oracle/dmpbak';
select directory_name,directory_path from dba_directories;

导入：

--因为跨版本了，这里指定了目标数据库版本
impdp \'/ as sysdba\' directory=DMPBAK dumpfile=HFMS.DMP parallel=8 schemas=HFMS version=19.0.0.0.0

我们这里没有按原数据库的tablespace_name 去创建，这里映射下,就导入正常了

nohup impdp \'/ as sysdba\' directory=DMPBAK dumpfile=HFMS.DMP parallel=8 schemas=HFMS REMAP_TABLESPACE=TS_DB_DATA:HFMS version=19.0.0.0.0 &

导入过程中，即使关注目标端的表空间大小，及时扩容,不过oracle 数据泵导入支持断点续传，还是很不多的，资源不够等待扩容后可以接着执行。

set line 200
set pagesize 9999
col tablespace_name for a20
col SUM_SPACE(G) for a15
col FREE_SPACE(G) for a15
col USED_SPACE(M) for a15
col USED_SPACE(G) for a15
col USED_RATE(%) for a15
SELECT a.tablespace_name,
round(total / (1024*1024*1024),2) || 'G' "SUM_SPACE(G)",
round((total - free) / (1024*1024*1024),2)||'G' "USED_SPACE(G)",
round(free / (1024*1024*1024), 2)|| 'G' "FREE_SPACE(G)",
round((total - free) / total, 4) * 100 || '%' "USED_RATE(%)"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by 5 desc;

--增加数据文件
alter tablespace HFMS add datafile            
'+DATA' size 65535M 
autoextend off; 

查看导入job

select * from dba_datapump_sessions;
select * from dba_datapump_jobs