expdp数据泵迁移实例

在上一篇文章我们确认了迁移方案，这篇就来描述方案的具体实施步骤

1.检查源库和目标库中转磁盘空间是否足够

2.检查网络是否畅通，scp/ftp

3.检查数据是否有lob，评估是否需要拆分

4.检查是否有较大的表，评估是否需要

5.检查cpu个数和负载情况，评估parallel参数值

6.检查sql_profile，profile，角色，权限，dblink等

--源库cpu

--目标库cpu

[[email protected] ~]$ cat /proc/cpuinfo| grep "processor"| wc -l

40

--源库空间足够

--dmp文件放在f盘就可以 了

--创建目录并赋权

--Schema的大小

目标库：

目标库中转空间足够

--目标库表空间足够

目标库创建目录并赋权

SQL> create directory impdp_dir as '/dump';

 

Directory created.

 

SQL> grant read,write on directory impdp_dir to system;

 

Grant succeeded.

为了保证数据的一致性，需要停止应用

--停监听

lsnrctl stop

--清理会话

ps -ef|grep LOCAL=NO | grep -v grep |awk '{print $2}'| xargs kill -9

--查杀job

ALTER SYSTEM SET job_queue_processes=0 scope=both sid='*';

ps -ef|grep ora_j | grep -v grep |awk '{print $2}'| xargs kill -9

--检查大事物

sqlplus / as sysdba<<EOF

alter system checkpoint;

alter system switch logfile;

alter system switch logfile;

alter system checkpoint;

alter system switch logfile;

alter system switch logfile;

exit;

EOF

--导入profile

参考命令：

expdp system/password directory=exp_dir dumpfile=profile.dmp logfile=profile.log metrics=yes  include=profile  full=y content=metadata_only 

--未使用自定义profile

--导入角色

expdp system/password directory=exp_dir dumpfile=role.dmp logfile=role.log  metrics=yes include=role full=y content=metadata_only

--无特殊角色，目标库中已授权connect，resource

  select grantee,GRANTED_ROLE from dba_role_privs where grantee in ('user1','user2','user3','user4','user5')

--导入dblink，导入时需要注意tns

expdp system/password directory=exp_dir dumpfile=dblink.dmp logfile=dblink.log include=DB_LINK full=y CONTENT=METADATA_ONLY 

--没有业务上的dblink

--lob字段

set pagesize 2000

select a.owner,a.table_name,sum(b.bytes)/1024/1024/1024

from dba_lobs a,dba_segments b where a.SEGMENT_NAME=b.SEGMENT_NAME and a.owner=b.owner

and b.owner in ('user1','user2','user3','user4','user5')

group by a.owner,a.table_name order by 3 desc,2,1 

--表空间.users表空间需要导入到默认表空间下

--导出脚本

 --导出without大lob的数据,10.2.0.1没有cluster参数

expdp system/[email protected] parallel=6 directory=exp_dir dumpfile=schemas_wout_clob_%U.dmp logfile=schemas_wout_clob.log schemas=\('user1','user2','user3','user4','user5'\)  exclude=user1.tab1,user2.tab2,user3.tab3,user4.tab4,user5.tab5

 --导出大lob的数据

expdp system/[email protected] parallel=6  directory=exp_dir dumpfile=schemas_with_clob_%U.dmp logfile=schemas_with_clob.log TABLES=user1.tab1,user2.tab2,user3.tab3,user4.tab4,user5.tab5

--ftp

...

--导入schemas without 大lob。因为有多个schema在users表空间中，需要使用多个impdp语句来引导remap_tablespace参数

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir schemas=user1 dumpfile=schemas_wout_clob_%U.dmp logfile=schemas_wout_clob_user1.log   TABLE_EXISTS_ACTION=REPLACE  version=10.2.0.1  remap_tablespace=users:user1  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir schemas=user2 dumpfile=schemas_wout_clob_%U.dmp logfile=schemas_wout_clob_user2.log   TABLE_EXISTS_ACTION=REPLACE  version=10.2.0.1  remap_tablespace=users:user2  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir schemas=user3 dumpfile=schemas_wout_clob_%U.dmp logfile=schemas_wout_clob_user3.log   TABLE_EXISTS_ACTION=REPLACE  version=10.2.0.1  remap_tablespace=users:user3  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir schemas=user4 dumpfile=schemas_wout_clob_%U.dmp logfile=schemas_wout_clob_user4.log   TABLE_EXISTS_ACTION=REPLACE  version=10.2.0.1  remap_tablespace=users:user4  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir schemas=user5 dumpfile=schemas_wout_clob_%U.dmp logfile=schemas_wout_clob_user5.log   TABLE_EXISTS_ACTION=REPLACE  version=10.2.0.1  remap_tablespace=users:user5  &

 

 

--导入大lob

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir tables=user1.tab1 dumpfile=schemas_with_clob_%U.dmp logfile=schemas_with_clob_user1.log  TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1 remap_tablespace=users:user1  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir tables=user2.tab2 dumpfile=schemas_with_clob_%U.dmp logfile=schemas_with_clob_user2.log  TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1 remap_tablespace=users:user2  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir tables=user3.tab3 dumpfile=schemas_with_clob_%U.dmp logfile=schemas_with_clob_user3.log  TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1 remap_tablespace=users:user3  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir tables=user4.tab4 dumpfile=schemas_with_clob_%U.dmp logfile=schemas_with_clob_user4.log  TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1 remap_tablespace=users:user4  &

nohup impdp system/password@oa parallel=6 cluster=N directory=impdp_dir tables=user5.tab5 dumpfile=schemas_with_clob_%U.dmp logfile=schemas_with_clob_user5.log  TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1 remap_tablespace=users:user5  &

以上就是整个迁移步骤