12c pdb可插拔数据库导入导出

环境12c容器-psz

wtyzcdb21

OEL6.6

10.33.194.149

wtyzcdb22

OEL6.6

10.33.194.150

wtyzcdb23

OEL6.6

10.33.194.151

(1)查看bims2的创建语句

set long 50000;

set linesize 200;

Set pagesize 0;

SELECT DBMS_METADATA.GET_DDL('USER','BIMS2')||';' FROM DUAL;

 

CREATE USER "BIMS2" DEFAULT TABLESPACE "TYZC_ZCXF_01" TEMPORARY TABLESPACE "TEMP";

 

drop user bims2;然后用查询出来的语句进行重建

 

(2)同属于一个表空间,不需要进行remap表空间

select username,account_status,default_tablespace,temporary_tablespace from dba_users where username in ('BIMS4','BIMS2')

 

 

(3)大小及表空间,数据量只有10g

select count(bytes)/1024/1024/1024,tablespace_name from dba_segments where owner='BIMS4' group by tablespace_name;

 

 

(4)

pdb需要指定tns,tnsnames.ora里面已经配置,注意如果你登录的是10.33.194.147的话,那tnsnames.ora的tns必须配置的是本台主机对应的vip--10.33.194.150,不然会连接到其他的vip到其他节点,这样导入导出的时候就会报找不到directory目录

 

[root@wtyzcdb22 ~]# cat /etc/hosts

10.33.194.147 wtyzcdb22

10.33.194.150 wtyzcdb22-vip

172.33.194.150 wtyzcdb22-priv

 

10.33.194.148 wtyzcdb23

10.33.194.151 wtyzcdb23-vip

172.33.194.151 wtyzcdb23-priv

 

psz1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.194.150)(PORT = 1521))

(LOAD_BALANCE=OFF)

(FAILOVER = ON)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = psz)

)

)

 

(5)将表置为read only

select 'alter table '||d.owner||'.'||d.table_name||' read only;' from dba_tables d where owner='BIMS4';

 

导出后再改会read write

 

 

(6)导出之前禁用外键约束

select 'alter table ' ||owner||'.'||table_name|| ' disable constraint '||constraint_name||';'

from dba_constraints

where constraint_type in ('R') and owner in ('BIMS4') and status = 'ENABLED';

 

(7)按照用户进行导出

create directory dump_dir as '/oracle/dump';

 

如果使用普通用户,需要授权

grant read,write on directory dump_dir to bims4;

grant read,write on directory dump_dir to system;

grant all on directory dump_dir to system;

 

sqlplus system/aSHmKxm1RDJCtaI@psztest

 

 

expdp system/aSHmKxm1RDJCtaI@psztest directory=DUMP_DIR dumpfile=expdp_bims4.dmp logfile=expdp_bims4.log schemas=bims4 parallel=2 cluster=no

 

(8)从一个导入到其他用户,需要remap_schemas

impdp system/aSHmKxm1RDJCtaI@psztest directory=dump_dir dumpfile=expdp_bims4.dmp logfile=impdp_bims4.log remap_schema=bims4:bims2 parallel=10 cluster=no

 

(9)收集统计信息

dbms_stats.gather_schema_stats(ownname =>'BIMS2' ,estimate_percent => 20,degree => 2,cascade => true)

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值