醬油DBA奉獻expdp,impdp多使用者遷移資料（二）

醬油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

生成所有使用者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
資料庫校驗