ITPub博客

首页 > 数据库 > Oracle > 酱油DBA奉献expdp,impdp多用户迁移数据(二)

酱油DBA奉献expdp,impdp多用户迁移数据(二)

Oracle 作者:531968912 时间:2018-06-20 22:12:35 0 删除 编辑

酱油DBA奉献expdp,impdp多用户迁移数据(二)

作为一个甲方酱油DBA,怎么可能不迁移数据呢?下面讲讲甲方酱油DBA是怎么数据泵多用户迁移的。

本文用途:异构平台数据迁移,数据泵多用户迁移。
     涉及到多个批量SQL:生成表空间建立批量SQL、expdp批量导出SQL、impdp批量导入SQL、数据校验用户表数目批量SQL

本人精华:“批量”两字概括,希望对酱油的DBA们有用

酱油DBA奉献ORACLE数据库监控健康脚本(一)
http://www.itpub.net/thread-1841912-1-1.html

源环境:
 操作系统:HP-UX (B.11.31 U ia64)
 数据库:单实例(11.2.0.3)
目标环境:
 操作系统:AIX6.1 TL 08
 数据库:RAC数据库(11.2.0.4)

总结:本文记录了多个生成批量脚本的SQL,勉记!有助于提高工作效率。


一、查看源库所有表空间和数据文件                                                       
select * from                                                                                                                                
(select tablespace_name,file_name from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1','TEMP') order by 1 )
union all                                                                                                                                    
(select tablespace_name,file_name from dba_temp_files where tablespace_name not in ('TEMP')  )  ;

二、创建目标表空间批量的SQL

生成所有数据表空间创建脚本SQL      
select 'create tablespace '||a.tablespace_name||' datafile ''+DATA/capdb/datafile/'||b.tablespace_name||'01.dbf''  size 5g autoextend on;' from
(select tablespace_name from  dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1') and contents not in ('TEMPORARY')) a,                  
(select tablespace_name from  dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1') and contents not in ('TEMPORARY') ) b                   
where a.tablespace_name=b.tablespace_name                                                                                                                   
order by 1 ;

生成所有临时表空间创建脚本SQL
select 'create temporary  tablespace '||a.tablespace_name||' tempfile ''+DATA/capdb/tempfile/'||b.tablespace_name||'01.dbf''  size 5g autoextend on;' from
(select tablespace_name,file_id from dba_temp_files where tablespace_name not in ('TEMP') ) a,
(select tablespace_name,file_id from dba_temp_files where tablespace_name not in ('TEMP') ) b 
where a.file_id=b.file_id
order by 1 ;


目标与源端创建 directory dump
create directory dump as '/oracle/rac/oracle/dump/' ;
grant read,write directory dump to system;

三、expdp,impdp迁移数据
                                                                                                                                  
生成所有用户expdp导出脚本SQL
select 'expdp system/system directory=dump dumpfile='||a.username||'01.dmp logfile='||b.username||'_exp_01.log schemas='||c.username|| ' cluster=N parallel=4'from
(select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) a,
(select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) b,
(select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) c 
where  a.username=b.username and b.username=c.username ;

把所有dmp文件迁移到目标端数据库服务器上
scp *.dmp oracle@10.10.89.17:/oracle/rac/oracle/dump

生成所有用户impdp导入脚本SQL
select 'impdp system/system directory=dump dumpfile='||a.username||'01.dmp logfile='||b.username||'_imp_01.log schemas='||c.username|| ' cluster=N parallel=4'from
(select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) a,
(select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) b,
(select username from dba_users where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX')) c 
where  a.username=b.username and b.username=c.username;

四、检验每个用户表数量脚本SQL(目标与源对比验证)
select 'SELECT count(*) FROM ALL_TABLES WHERE OWNER='''||username||''';' from dba_users
where account_status='OPEN' and default_tablespace not in ('SYSTEM','SYSAUX');

附表:
expdp 大小:约50G
expdp 数据时间为:1小时
scp dmp文件时间为:20分钟
impdp 数据时间为:1小时30分钟
编译数据库无效对象:@?\rdbms\admin\utlrp.sql
数据库校验

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25462274/viewspace-2156409/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-09-24

  • 博文量
    574
  • 访问量
    835771