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

相关文章

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

关于db link权限分配的苦旅(一) (r7笔记第42天)

昨天接到一个开发的需求,内容看起来非常简单。 申请数据库192.168.1.118:1522:TEST下用户APP_TE_FLOW_128赋予对表testore...

3346
来自专栏cloudskyme

使用oracle的大数据工具ODCH访问HDFS数据文件

软件下载 Oracle Big Data Connectors:ODCH 下载地址: http://www.oracle.com/technetwork/bdc...

3478
来自专栏乐沙弥的世界

Oracle 闪回特性(Flashback Query、Flashback Table)

--==================================================

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

备库归档删除策略失效的问题分析 (r7笔记第6天)

最近碰到了一个有些奇怪的问题,自己当时排查问题时间紧,没有细细琢磨,今天抽空看了下,终于发现了端倪。 首先是在早晨收到了报警邮件,报警邮件内容如下: ZABBI...

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

使用impdp不当导致的数据丢失问题(r5笔记第1天)

今天有个朋友问我们一个问题,说他在使用了impdp导入数据的时候,使用了TABLE_EXISTS_ACTION=REPLACE这个选项,结果现在数据都给覆盖了。...

3445
来自专栏乐沙弥的世界

Heap size 80869K exceeds notification threshold (51200K)

      前阵子的alert日志获得了所需堆尺寸的大小超出指定阙值的提示,即Heap size 80869K exceeds notification thr...

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

执行计划的偏差导致的性能问题(r3笔记第12天)

在生产环境中有一条sql语句,查看执行计划来看,效果还是可以接受的。 sql语句类似下面的样子,可以看到里面还使用了比较纠结的外连接。从执行计划来说,默认是走n...

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

只言片语分析datapump的工作原理(r2第18天)

datapump是从oracle 10g推出的新的数据导入导出工具,可以说是exp/imp的加强版,主要的亮点在于服务端,结合了direct+parallel,...

2223
来自专栏程序猿

Oracle_12C的新特性

这里我们来领略下Tom眼中的12个特性增强: ? #1 Even better PL/SQL from SQL, 直接在SQL中嵌入PL/SQL对象并运行,猜测...

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

通过shell脚本快速定位active session问题(r4笔记第65天)

如果你得到反馈,数据库突然间性能下降了好多,希望你能够尽快的定位出问题来,有一些思路和方法可以参考。分别从数据库层面,系统层面来定位,但是个人感觉而言还是不够快...

3335

扫码关注云+社区