海量数据迁移之外部表并行抽取(99天)

在10g开始的新特性中,外部表是一个不容忽视的好工具。对于大型项目中海量数据使用sqlloader是一种全新的方式,不过很明显,sqlloader的可扩展性更强,但是基于oracle平台的数据迁移来说,外部表的性能也不错。对于数据迁移来说也是一个很好的方案。 使用外部表来做数据迁移,可以“动态”加载数据,能够很方便的从数据库中加载数据,对于数据校验来说就显得很有优势了,而对于sqlloader来说,可能得等到数据加载的时候才知道是不是有问题,如果对于数据的准确性要求极高,可以使用外部表动态加载数据到备库,和现有的数据做比对,减少在升级过程中带来的灾难。 还有关于数据类型,对于clob,blob的加载,大家都比较头疼,在sqlloader中可能需要做一些额外的工作,来外部表中就和操作普通的表没有什么区别。 先来说说数据抽取的部分。 一下是我今天完成的部分脚本,目录结构如下。 drwxr-xr-x 2 ora11g dba 4096 Jun 9 22:14 DUMP_LIST drwxr-xr-x 2 ora11g dba 4096 Jun 9 23:25 extract drwxr-xr-x 2 ora11g dba 4096 Jun 9 22:32 parfile [ora11g@rac1 ext_datapump]$ pwd /u01/ora11g/test/ext_datapump 对于一些比较大的表,如果占用的空间在好几十个G左右的时候,生成一个巨大的dump文件就有问题了,一来是关于io,顺序的写入dump文件,而且在加载的时候也没有其他的选择了,只能从头到尾一步一步来。 我的设想就是如果一个表有100G,可以把他切分为200个dump文件,每个500M,或者说生成1000个dump文件,每个dump文件100M,这样在加载的时候就可以很清楚的看到目前数据加载的进度了。 我使用了如下的脚本来生成多个dump文件,

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=50
sqlplus -s n1/n1 <<EOF
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 distinct segment_name,ceil(sum(bytes/1024/1024)/50) parallel 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 order by 2 desc;
spool off;
EOF
sed  '/^$/d' tab_parall_temp.lst |sort > ../parfile/tab_parall.lst
rm tab_parall_temp.lst

运行脚本后,结果如下所示。

BIG_INSERT                                      1                                                   
CLOB_TEST                                       1                                                   
SMALL_INSERT                                    1                                                   
T                                               1                                                   
TEMP_TEST                                       1                                                   
TEST                                            1                                                   
TEST_DATA                                       1                                                   
TT                                              2                                                   
T_TEMP                                          1 

可以看到表tt比较大,就需要分成了两个dump。加载的时候也可以分批加载。 当然,生成的dump的个数也和一个数据库参数密切相关,像我目前的库,最大的并行只有30. 意味着如果我要把一个表切分成50个dump,根据配置,只能最多切分为30个。 parallel_max_servers integer 30 在生面的基础上,可以采用下面的脚本来生成dump文件。

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

owner=`echo "$1"|tr '[a-z]' '[A-Z]'`
tab_name=`echo "$2"|tr '[a-z]' '[A-Z]'`
tablst=`cat ../parfile/tablst|awk '{print "'\''" $1 "'\'',"}'`


tmp_parallel=`grep -w $tab_name ../parfile/tab_parall.lst|awk '{print $2}'`
for i in {1..$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
sqlplus -s n1/n1 <<EOF
set serveroutput on
DECLARE
TABLE_FLAG number(2);
begin
execute immediate 'select count(*) from all_tables where owner=upper(''$1'') and table_name=upper(''$2'')' into table_flag;
if(table_flag>0) then 
dbms_output.put_line( 'TABLE '||'$tab_name'||' exists in owner account,proceed...');
else 
dbms_output.put_line( 'TABLE does not exists in owner account,please check again');
return;
end if;
end;
/

set timing on
DECLARE
TABLE_FLAG number(2);
begin
execute immediate 'select count(*) from all_external_tables where owner=upper(''$3'') and table_name=upper(''$2'')' into table_flag;
if(table_flag>0) then 
dbms_output.put_line( 'External table exists in owner account,proceed...');
execute immediate 'drop table $3.$2_ext';
end if;
end;
/
exec dbms_output.put_line('Get Dump file for $1.$2...');
create table $3.$2_ext
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY $4 
     LOCATION (
     $dump_list
    )
    )
    parallel $tmp_parallel 
    as
    select /*+ parallel(t $tmp_parallel) */ * from $1.$2 t;
set feedback off;
set timing off
drop table $3.$2_ext;
EOF
rm tmp_${tab_name}_par_dmp.lst 
exit

脚本运行效果如下:

Get Dump file for n1.TT...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Table created.
Elapsed: 00:00:03.07
TABLE T_TEMP exists in owner account,proceed...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Get Dump file for n1.T_TEMP...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
Table created.
Elapsed: 00:00:01.39

生成的dump文件如下:

[ora11g@rac1 expdp]$ ll *.dmp
-rw-r----- 1 ora11g dba   466944 Jun 10 01:43 BIG_INSERT_1.dmp
-rw-r----- 1 ora11g dba    12288 Jun 10 01:43 CLOB_TEST_1.dmp
-rw-r----- 1 ora11g dba    40960 Jun 10 01:43 SMALL_INSERT_1.dmp
-rw-r----- 1 ora11g dba 30531584 Jun 10 01:43 T_1.dmp
-rw-r----- 1 ora11g dba   524288 Jun 10 01:43 TEMP_TEST_1.dmp
-rw-r----- 1 ora11g dba   466944 Jun 10 01:43 TEST_1.dmp
-rw-r----- 1 ora11g dba    69632 Jun 10 01:43 TEST_DATA_1.dmp
-rw-r----- 1 ora11g dba 39018496 Jun 10 01:43 TT_1.dmp
-rw-r----- 1 ora11g dba 43634688 Jun 10 01:43 TT_2.dmp
-rw-r----- 1 ora11g dba 30531584 Jun 10 01:43 T_TEMP_1.dmp


在比较同样的数据量的情况下,sqlloader可能需要大约120G的容量,而对于外部表来说,大概只需要40G左右的空间。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-06-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏pangguoming

Spring Boot集成JasperReports生成PDF文档

由于工作需要,要实现后端根据模板动态填充数据生成PDF文档,通过技术选型,使用Ireport5.6来设计模板,结合JasperReports5.6工具库来调用渲...

1.2K7
来自专栏我和未来有约会

Kit 3D 更新

Kit3D is a 3D graphics engine written for Microsoft Silverlight. Kit3D was inita...

2516
来自专栏张善友的专栏

LINQ via C# 系列文章

LINQ via C# Recently I am giving a series of talk on LINQ. the name “LINQ via C...

2625
来自专栏张善友的专栏

Mix 10 上的asp.net mvc 2的相关Session

Beyond File | New Company: From Cheesy Sample to Social Platform Scott Hansel...

2547
来自专栏陈仁松博客

ASP.NET Core 'Microsoft.Win32.Registry' 错误修复

今天在发布Asp.net Core应用到Azure的时候出现错误InvalidOperationException: Cannot find compilati...

4828
来自专栏杨龙飞前端

scrollto 到指定位置

2494
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3095
来自专栏跟着阿笨一起玩NET

c#实现打印功能

2692
来自专栏闻道于事

js登录滑动验证,不滑动无法登陆

js的判断这里是根据滑块的位置进行判断,应该是用一个flag判断 <%@ page language="java" contentType="text/html...

6758
来自专栏java 成神之路

使用 NIO 实现 echo 服务器

4567

扫码关注云+社区