在之前的章節中分享過一些資料遷移中並行抽取的細節，比如一個表T 很大，有500G的資料，如果開啟並行抽取，預設資料庫中並行的最大值為64，那麼生成的dump檔案最50多為64個，每個dump檔案就是7.8G，還是不小，況且在做資料抽取的時候，資源被極大的消耗，如果資源消耗緊張，可能可用的並行資源還不到64個。那麼dump檔案可能比7G還要大得多。
如果換一步來說，我們嘗試調高並行的引數，可以支援100個並行，那麼每個dump檔案也有5G，也沒有太大的改善。
所以自己在斟酌後考慮使用分割槽加並行的思想來做大表的切分。
生產中500G的大表肯定是做了分割槽操作，而且分割槽數可能還比較多。我們就設定為100個吧。
分割槽表的資料基本都是分散在各個分割槽的，考慮資料的不均勻分佈，那麼每個分割槽的資料可能在5~10G吧。
參照這個思想，假設開啟並行，比如200M為一個基準點來切分分割槽表，比如分割槽表的某個分割槽含有5G的資料，那麼需要開啟25個並行即可，檔案就會被切分為200M的很多細粒度的dump檔案。按照10G來算，最多也是50個並行，比預設提供的並行引數還要低一些。

按照這個思想，對比較大的分割槽表才做分割槽+並行，如果是普通表就需要最大程度的應用並行，如果分割槽表比較小，那就可以不用使用分割槽+並行了。
目前我設定的基準為1G，比如一個分割槽表T，大小在1.5G,那麼可以考慮開啟分割槽+並行，如果分割槽表的大小為500M，那麼就可以不用考慮使用分割槽+並行了，因為在每個分割槽中的資料可能相對比較少。

今天寫了如下的指令碼來做做分割槽抽取的判斷指令碼，如果分割槽表的大小在1G以上，才抽取分割槽資訊，否則和普通表一樣對待。

tablst=`cat ../parfile/tablst|awk '{print "'\''" $1 "'\'',"}'`
#for segment within 50M, will use parallel 1, and parallel will calculated with segment_bytes_size_MB/50M
page=200
sqlplus -s $1 <
set feedback off
set head off
set line 100
set pages 0
set long 10000
set termout off
col segment_name for a40
col parallel format 9999
spool tab_parall_temp.lst
select segment_name,nvl(partition_name,'x'),ceil(sum(bytes/1024/1024)/$page) parallel from user_segments
where segment_name in
(
select segment_name from user_segments
where segment_name in (select table_name from user_tables where table_name in ($tablst'x') and table_name not in (select table_name from user_external_tables))
group by segment_name having sum(bytes/1024/1024)>=1000
)group by segment_name,nvl(partition_name,'x')
union
select segment_name,'x',ceil(sum(bytes/1024/1024)/$page) parallel from user_segments
where segment_name in
(
select segment_name from user_segments
where segment_name in (select table_name from user_tables where table_name in ($tablst'x') and table_name not in (select table_name from user_external_tables))
group by segment_name having sum(bytes/1024/1024)<1000
) group by segment_name,'x' ;

spool off;
EOF
sed '/^$/d' tab_parall_temp.lst |sort > ../parfile/tab_partition_parall.lst
rm tab_parall_temp.lst

生成的引數檔案內容如下，對於普通表和較小的分割槽表而言，就預設補充了一個字元‘x'，便於稍後的處理時統一管理
MEMO P9_A3000_E1 1
MEMO P9_A3000_E2 1
MEMO P9_A3000_E3 1
MEMO P9_A3000_E4 1
MEMO P9_A3000_E5 1
MEMO PMAXVALUE_AMAXVALUE_EMAXVALUE 1
SERVICE x 36
SUBSCRIBER_HISTORY x 11
SUBSCRIBER x 5

對於大表的分割槽+並行抽取，可以考慮如下的指令碼。

#### source owner $1
#### tab_name $2
#### target owner $3
#### dump directory $4
#### partition_name $5

function check_conn {
Num=`echo show user | $ORACLE_HOME/bin/sqlplus -s $1| grep -i 'USER ' | wc -l`
if [ $Num -gt 0 ]
        then
        echo DB details is accessible from $2 schema ...
        else
          ## inst is inaccessible
                echo Instance: DB detailsIs Invalid Or UserName/PassWord Is Wrong
                echo '***********************************************'
        exit
        fi
}

check_conn $1 source
echo .
check_conn $3 target
echo .

source_owner=`echo "$1" |awk -F@ '{print $1}'|awk -F/ '{print $1}'|tr '[a-z]' '[A-Z]'`
target_owner=`echo "$3" |awk -F@ '{print $1}'|awk -F/ '{print $1}'|tr '[a-z]' '[A-Z]'`
tab_name=`echo "$2"|tr '[a-z]' '[A-Z]'`
partition_name=$5
tablst=`cat ../parfile/tablst|awk '{print "'\''" $1 "'\'',"}'`

tmp_parallel=`grep -w $tab_name ../parfile/tab_partition_parall.lst|grep $partition_name| awk '{print $3}'`
echo $tmp_parall
tmp_dump_cnt=`ls -l ../DUMP/${tab_name}*.dmp|wc -l`
tmp_dump_cnt=`expr $tmp_dump_cnt + 1 `
echo $tmp_dump_cnt
tmp_parallel=`expr $tmp_parallel + $tmp_dump_cnt `

for i in {${tmp_dump_cnt}..${tmp_parallel}};
do
echo \'${tab_name}_$i.dmp\', >> tmp_${tab_name}_par_dmp.lst
done

sed -e '/^$/d' -e '$s/.$//' tmp_${tab_name}_par_dmp.lst > ../DUMP_LIST/${tab_name}_par_dmp.lst
dump_list=`cat ../DUMP_LIST/${tab_name}_par_dmp.lst`
#echo $dump_list
#echo $tmp_parallel
if [[ $partition_name = 'x' ]]
then
partition_name=''
else
partition_name='partition('$partition_name')'
fi

sqlplus -s $3 <
set feedback off
set serveroutput on
DECLARE
datapump_flag number(2);
begin
select count(*) into datapump_flag from user_tab_privs where table_name=upper('$4') and grantee=upper('$target_owner') and privilege in ('READ','WRITE');
if(datapump_flag=2) then
dbms_output.put_line( 'Directory '||'$4'||' has read,write permission ,proceed...');
else
dbms_output.put_line( 'WARNING! Directory '||'$4'||' does not have read,write permission to $target_owner ,Please check again...');
return;
end if;
end;
/
EOF

echo .

sqlplus -s $1 <
set feedback off
set serveroutput on
DECLARE
TABLE_FLAG number(2);
begin
select count(*)into table_flag from all_synonyms where owner=upper('$source_owner') and table_name=upper('$2') ;
if(table_flag>0) then
dbms_output.put_line( 'SYNONYM '||'$tab_name'||' exists in CONNECT account,proceed...');
else
dbms_output.put_line( 'SYNONYM does not exists in CONNECT account,please check again');
return;
end if;
end;
/
!echo .

DECLARE
TABLE_FLAG number(2);
begin
execute immediate 'select count(*) from all_external_tables where owner=upper(''$target_owner'') and table_name=upper(''$2'')' into table_flag;
if(table_flag>0) then
dbms_output.put_line( 'External table exists in mig account,proceed...');
--execute immediate 'drop table $3.$2_ext';
end if;
end;
/
exec dbms_output.put_line('Get Dump file for $source_owner.$2 $partition_name...');

conn $3
set timing on
create table $target_owner.${tab_name}_ext
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY $4
     ACCESS PARAMETERS( nologfile)
     LOCATION (
     $dump_list
    )
    )
    parallel $tmp_parallel
    as
    select /*+ parallel(t $tmp_parallel) */ * from $source_owner.$tab_name $partition_name t;
set feedback off;
set timing off
drop table $target_owner.$2_ext;
EOF
echo .
rm tmp_${tab_name}_par_dmp.lst
exit

指令碼生成的日誌如下：

Get Dump file for APP_TMP.MEMO partition(P5_A2000_E2)...
Elapsed: 00:00:00.52
DB details is accessible from source schema ...
DB details is accessible from target schema ...

447
Directory new_test has read,write permission ,proceed...
SYNONYM MEMO exists in CONNECT account,proceed...

Get Dump file for APP_TMP.MEMO partition(P5_A2000_E3)...
Elapsed: 00:00:00.73
DB details is accessible from source schema ...
DB details is accessible from target schema ...

449
Directory new_test has read,write permission ,proceed...
SYNONYM MEMO exists in CONNECT account,proceed...

Get Dump file for APP_TMP.MEMO partition(P5_A2000_E4)...

生成的dump檔案如下所示，可以看到生成了幾百個相關的dump檔案。
-rw-r----- 1 prodbuser dba 15826944 Aug 3 18:13 MEMO_460.dmp
-rw-r----- 1 prodbuser dba 13254656 Aug 3 18:13 MEMO_461.dmp
-rw-r----- 1 prodbuser dba 15044608 Aug 3 18:13 MEMO_462.dmp
-rw-r----- 1 prodbuser dba 15015936 Aug 3 18:13 MEMO_463.dmp
-rw-r----- 1 prodbuser dba 13135872 Aug 3 18:13 MEMO_464.dmp
-rw-r----- 1 prodbuser dba 13266944 Aug 3 18:13 MEMO_465.dmp
-rw-r----- 1 prodbuser dba 15003648 Aug 3 18:13 MEMO_466.dmp
-rw-r----- 1 prodbuser dba 20480 Aug 3 18:13 MEMO_467.dmp
-rw-r----- 1 prodbuser dba 20480 Aug 3 18:13 MEMO_468.dmp
-rw-r----- 1 prodbuser dba 20480 Aug 3 18:13 MEMO_469.dmp

海量資料遷移之分割槽並行抽取

相關文章