酱油DBA奉献expdp,impdp多用户迁移数据(二)
作为一个甲方酱油DBA,怎么可能不迁移数据呢?下面讲讲甲方酱油DBA是怎么数据泵多用户迁移的。
本文用途:异构平台数据迁移,数据泵多用户迁移。
涉及到多个批量SQL:生成表空间建立批量SQL、expdp批量导出SQL、impdp批量导入SQL、数据校验用户表数目批量SQL
本人精华:“
批量”两字概括,希望对酱油的DBA们有用
源环境:
操作系统: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/orcl11g/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','UNDOTBS2')
and contents not in ('TEMPORARY')) a,
(select tablespace_name from dba_tablespaces where
tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1','UNDOTBS2')
and contents not in ('TEMPORARY') ) b
where a.tablespace_name=b.tablespace_name
order by 1 ;
生成所有除temp外临时表空间创建脚本SQL
select 'create temporary tablespace '||a.tablespace_name||' tempfile ''+DATA/orcl11g/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
生成所有用户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
数据库校验