oracle多用户导出,酱油DBA奉献expdp,impdp多用户迁移数据（二）

酱油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/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

生成所有用户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

数据库校验