oracle11g数据库pdb,Oracle11g数据库迁移到Oracle12C的PDB（使用impdp/expdp）

Oracle11g数据库迁移到Oracle12C的PDB(使用impdp/expdp)

发布时间：2020-07-05 21:13:13

来源：51CTO

阅读：22658

作者：PengChonggui

Oracle11g数据库迁移到Oracle12C的PDB(使用impdp/expdp)

alter pluggable database hrdb close immediate;

DROP PLUGGABLE DATABASE hrdb INCLUDING DATAFILES;

--一、数据备份

mkdir /oradata/dumpdir

--创建目录

create or replace directory exp_dir as '/oradata/dumpdir';

--授权

grant read,write on directory exp_dir to hradm;

--查询

select * from dba_directories;

expdp admin/admin file=hr.dmp directory=exp_dir schemas=hradm;

--删除

drop directory exp_dir;

---二、数据导入Oracle12C

--登录cpcdb

$sqlplus sys/Oracleedw13@192.168.2.218:1531/hrdb as sysdba

--创建目录

create or replace directory exp_dir as '/oradata/dumpdir';

--授权

grant read,write on directory exp_dir to hradm;

--查询

select * from dba_directories;

--创建tbscpc表空间

create tablespace tbshr

datafile '/oradata/edw/hrdb/hrdb-001.dbf'

size 2G

autoextend on

next 64m maxsize 20G

segment space management auto

extent management local;

alter tablespace tbshr

add datafile '/oradata/edw/hrdb/hrdb-002.dbf' size 2G

autoextend on

next 50m maxsize 20G;

alter tablespace tbshr

add datafile '/oradata/edw/hrdb/hrdb-003.dbf' size 2G

autoextend on

next 50m maxsize 20G;

--给cpcadm和admin授权，并修改默认表空间

grant dba,resource to hradm;

select * from dba_users where username='HRADM';

alter user hradm default tablespace tbshr;

alter database default tablespace tbshr;

create user admin identified by admin;

grant connect,dba to admin;

--使用impdp导入hrdb数据

impdp admin@hrdb directory=exp_dir dumpfile=hr20180401004000.DMP  remap_tablespace=tbs_hr:tbshr   remap_schema=hr:hradm schemas=hr table_exists_action=replace transform=segment_attributes:n

remap_tablespace=tbs_hr:tbshr  原来表空间:新的表空间

remap_schema=hr:hradm          原来的schema:现在的schema

transform=segment_attributes:n 去掉表空间和存储子句，加上这个参数后，remap_tablesapce参数就会失效，就会倒进用户默认的表空间，