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

1、oracle11g数据导出

–创建逻辑目录

create or replace directory expdp as 'D:\dmp';

–授权

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;