海量数据迁移之外部表并行抽取(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 条评论
登录 后参与评论

相关文章

来自专栏解Bug之路

MySql协议详解-CRUD与Result篇

一般对DB的CRUD操作都由com_query报文封装并发送给DB。com_query报文如下图所示:

893
来自专栏张善友的专栏

SQL Injection的深入探讨

SQL injection可以说是一种漏洞,也可以说成是一种攻击方法,程序中的变量处理不当,对用户提交的数据过滤不足,都可能产生这个漏洞,而攻击原理就是利用用户...

1677
来自专栏MYSQL轻松学

MySQL系列优化(一)

MYSQL优化是一个非常大的课题,这篇文章主要介绍了跟MYSQL相关的4个方面,如果想深入研究可以查下相关资料。 ---- 一、服务器级别优化 二、操作系统级别...

2765
来自专栏Java3y

移动商城第七篇【购物车增删改查、提交订单】

把商品加入购物车 接下来我们要做的就是将商品加入到购物车中。我们这次使用的是Cookie来将用户的信息存储起来。那为什么要用cookie呢?? 如果将购物车存储...

75513
来自专栏MongoDB中文社区

Mongoing中文社区的精华文章推荐

一直以来想做一个关于MongoDB的系列专题,但又没有中心思想,所以一直拖着,但现在想想大可不必。完全可以东打一枪,西放一炮。

662
来自专栏猿人谷

【黑魔法】Covering Indexes、STRAIGHT_JOIN

今天给大家介绍两个黑魔法,这都是压箱底的法宝。大家在使用时,一定要弄清他们的适用场景及用法,用好了,就是一把开天斧,用不好那就是画蛇添足。自从看过耗子哥(左耳朵...

942
来自专栏帘卷西风的专栏

MySQL存储过程where条件执行失败的问题

      前几天对服务器实体做了属性缓存机制,当时测试也没有出现大的问题,昨天有人跟我说,登陆的时候角色等级显示错误,我复测了一下,发现不只是等级错误,进入...

682
来自专栏杨建荣的学习笔记

关于CPU使用率高的awr分析(r8笔记第46天)

今天看到一个报警信息,大体是CPU使用异常。 ZABBIX-监控系统: ------------------------------------ 报警内容: ...

3125
来自专栏陈满iOS

[iOS学习笔记]·FMDB:第三方本地数据库处理框架(官方文档翻译篇)

创建FMDatabase对象时参数为SQLite数据库文件路径。该路径可以是以下三种之一:

612
来自专栏数据和云

触类旁通:那些关于 TBL$OR$IDX$PART$NUM 的诡异案例和知识

你是否留意过数据库中有一个奇怪的函数 TBL$OR$IDX$PART$NUM ,你是否留意过很多场景下都出现过它的身影?

1144

扫码关注云+社区