oracle数据泵从11g迁徙数据库到19c的PDB(expdp/impdp)

1、oracle11g数据导出
–创建逻辑目录
create or replace directory expdp as '目录';
–授权
grant read,write on directory expdp to system;
–查询
select * from dba_directories;
–导出
expdp system/oracle DIRECTORY=expdp dumpfile=orclfull.dmp logfile=orcl.log full=y
–删除
drop directory expdp;
--统计表空间文件大小
select 'CREATE TABLESPACE ' || b.NAME || chr(39)||'LOGGING'|| ' DATAFILE ' || chr(39) || a.NAME || chr(39) || ' SIZE ' || a.BYTES/1024/1024 || 'M'||chr(39) ||'AUTOEXTEND ON NEXT 5M MAXSIZE 16000M ;'
  from v$datafile a, v$tablespace b
 where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP = 'YES'
Union All
select 'CREATE TEMPORARY TABLESPACE' || b.NAME || ' TEMPFILE ' || chr(39) || a.NAME || chr(39) || ' SIZE ' || a.BYTES/1024/1024||'M'||chr(39) ||'AUTOEXTEND ON NEXT 5M MAXSIZE 16000M ;'
  from v$tempfile a, v$tablespace b
  where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP = 'NO'
Union All
select 'ALTER DATABASE DATAFILE ' || chr(39) || a.NAME || chr(39) || ' AUTOEXTEND ON ;'
from v$datafile a, v$tablespace b where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP = 'YES'
Union All
select 'ALTER DATABASE TEMPFILE ' || chr(39) || a.NAME || chr(39) || ' AUTOEXTEND ON ;'
  from v$tempfile a, v$tablespace b
 where a.ts# = b.TS# And b.INCLUDED_IN_DATABASE_BACKUP = 'NO'

2、数据导入Oracle19C
–登录cdb
sqlplus / as sysdba
–查询当前容器
show con_name
–查询容器中全部pdb
show pdbs
–创建pdb数据库
CREATE pluggable DATABASE pdborcl admin USER pdbadmin identified BY oracle roles=(dba) file_name_convert=('/opt/oracle/oradata/ORA19C/pdbseed', '/opt/oracle/oradata/ORA19C/pdborcl'); 
–切换到pdb数据库
alter session set container=pdborcl;
–打开数据库
alter database open
–创建目录
mkdir /home/oracle/dmp
并将导出的DMP文件存放到该目录下
create or replace directory expdp as '/home/oracle/dmp';
grant read,write on directory expdp to pdbadmin;
–查询
select * from dba_directories;

–建表空间
create tablespace qin datafile '/opt/oracle/oradata/ORA19C/pdborcl/qin01.DBF' size 20M autoextend on;
create tablespace wang datafile '/opt/oracle/oradata/ORA19C/pdborcl/wang01.DBF' size 20M autoextend on;

–使用impdp导入数据
impdp pdbadmin/oracle@pdborcl directory=expdp dumpfile=ORCLFULL.DMP log=impdp.log schemas=qin,wang version='11.2.0.3.0' TABLE_EXISTS_ACTION=replace;


impdp system/system DIRECTORY=DIR_DMP DUMPFILE=exp_schema5_0730.dmp REMAP_SCHEMA=WM9:WM15,WM11:WM16,WM12:WM17,WM13:WM18,WM14:WM19 REMAP_TABLESPACE=WM9_DATA:WM15_DATA,WM11_DATA:WM16_DATA,WM12_DATA:WM17_DATA,WM13_DATA:WM18_DATA,WM14_DATA:WM19_DATA logfile=imp_5.log job_name=impdp15 transform=OID:N

impdp system/system DIRECTORY=DIR_DMP DUMPFILE=exp_schema5_0730.dmp REMAP_SCHEMA=BFSCITIC:mytest,GBSCITIC:mytest,IFCCITIC:mytest REMAP_TABLESPACE=BFS:BFS,GBS:GBS,DATAATA logfile=imp_5.log job_name=impdp15 transform=OID:N



impdp mytest/yyqwer1234@ORCLPDB1 directory=dp_dir dumpfile=ifcfull2023111000.dmp logfile=20231116impdp.log parallel=2 REMAP_SCHEMA=BFSCITIC:mytest,GBSCITIC:mytest,IFCCITIC:mytest REMAP_TABLESPACE=BFS:BFS,GBS:GBS,DATAATA

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。