阿里巴巴数据库操作手册 19-数据迁移工具之copy data

V1.0

 

Revision

Author/Modifier Comments

No.

Date

1.0 2012/2/15 张中靖  
       

一、 目的

说明使用copy data进行数据迁移的前置条件、操作步骤,降低对对应用造成的影响及避免故障

二、 适用范围

l 开发查问题需要访问线上大量数据时,可以将线上数据拉到线下方便开发处理

l 开发在线上做数据订正时,需要从导入一些业务提供的关联数据

l 配合项目从线上拉数据做环境初始化

l 线上库之间的数据迁移不建议使用

三、 copy data简介

l 基本语法:

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query

a) database:username/password@dbname

from database|to database可以任选一,缺少的一方默认使用当前sqlplus登录的数据库代替。

b) query:普通的select语句,多行时行尾使用连字符 ’-‘ 换行标记

l 支持的数据类型:CHAR,DATE,LONG,NUMBER,VARCHAR2

l copy data把sqlplus作为数据流的管道,不需要中间文件,数据库名使用tns中的配置。query子句在数据源库上执行,结果直接发到目标库。

l 四种模式:

c) append:目标表不存在时先建表再插入数据,若存在直接加载数据。

d) create:直接建表,若目标表存在,报错退出。

e) insert:直接插入数据,若目标表不存在,报错退出。

f) replace:目标表存在时会先删除表,再重建及加载数据,一般很少用这个模式

注意:create、replace 后面没有table关键字,insert后面没有into和values关键字

l 参数:使用sqlplus参数,跟数据库无关。这些参数可以在sqlplus里,show 参数名来查看具体值。

a) arraysize:一个批次传输的数据量,默认值是100,可以在sqlplus里,使用show arraysize查看。

b) copycommit:定义多少批次自动commit,copycommit*arraysize=一次commit的数据量,这个乘积对事务大小有影响。默认是0,就是所有数据加载完毕才做一次commit,建议使用非0数字。

c) long:可以传输long数据的长度,默认值是20000.

四、 风险评估

l copy data对带精度的数字、压缩的大字段处理有缺陷,使用前需要了解表数据格式

l 使用copy data时,如果没有设置批量commit,会出现长事务,导致undo资源占用过大,影响其他应用

l copy data的query子句没有控制好执行计划,导致性能很差,对线上库产生影响。

五、 操作流程

1. 准备工作

a) 检查数据源的表结构,确认是否有带小数的number列、比较长的varchar2列。如果存在带小数的number列,为防止精度丢失,可以提前仿照源表结构在目标库上建一个空表。对于不确定源表中是否有带小数的数据时,也可以这样处理。

如果存在长字段的varchar2类型,跟开发确认该字段的数据应用是否压缩后存储的,如果非压缩可以使用copy data迁移数据。

b) 使用explain 检查query子句的执行计划,可以加hint调整。

c) 预先执行query子句估算数据量,以确定copycommit、arraysize的大小。并预估数据所需空间。

d) Create模式建表没法指定表空间,会自动在用户的默认表空间下,可以先在指定的表空间上建空表,再copy 数据

2. 执行过程

这里给出一个示例,把拉线上数据到开发库,方便开发查问题。

>show user

USER is “ZZZZZ”

>show arraysize

arraysize 100

>show copycommit

copycommit 0

>show long

long 20000

>set copycommit 10

>copy to zzzzzz/password@oindeve create -

> zzzzzz.ws_anti_punish_det_zzj0223 -

> using -

> select a.product_id product_id,a.SPAM_TYPE SPAM_TYPE, -

> a.PUNISH_TYPE PUNISH_TYPE,a.DOWN_RANK DOWN_RANK, -

> b.PUNISH_DETAIL PUNISH_DETAIL -

> from zzzzzz.zzzz_zzzzz_zzzzz a, -

> zzzzzz.yyyy_yyyy_yyyy b -

> where a.SPAM_TYPE=‘repost’ -

> and b.PUNISH_DETAIL like ‘%downRank%’ -

> and a.product_id=b.PRODUCT_ID;

 

Array fetch/bind size is 100. (arraysize is 100)

Will commit after every 10 array binds. (copycommit is 10)

Maximum long size is 20000. (long is 20000)

 

1

2

上面脚本是由开发提供查询sql,将结果数据拉到线下开发库上。

直接登录到admin用户下,默认当前库作为数据源。admin用户有所有表的查询权限,不需要登录到表的属主schema下。create创建的表在用户的默认表空间下。

copycommit设为10,arraysize使用默认值100。这个语句执行后可以到开发库上查新表ws_anti_punish_det_zzj0223的数据量,可以看到每次以1000递增。表明是每1000条数据插入commit一次。

copy data执行完毕后会提示处理了多少数据,可以到目标库上核对数据量。

 

下面这个例子是用其他库拉数据到当前库。

sys@OINDEV>@conn zhangzj

Enter password:

zhangzj@OINDEV>set copycommit 10

zhangzj@OINDEV>copy from zhangzj/password@ointest create my_objs0223 using select * from dba_objects;

 

Array fetch/bind size is 100. (arraysize is 100)

Will commit after every 10 array binds. (copycommit is 10)

Maximum long size is 20000. (long is 20000)

Table MY_OBJS0223 created.

 

40310 rows selected from zhangzj@ointest.

40310 rows inserted into MY_OBJS0223.

40310 rows committed into MY_OBJS0223 at DEFAULT HOST connection.

 

有时开发根据业务方提供的一批数据清单做数据订正,清单一般是id、email一类的excel或者文本文件,数据量又比较大。可以让开发把清单导入线下库的临时表。然后把临时表拉到线上进行数据订正。

3. 验证方案

请开发核对数据量,重点核对带小数位的数字列、比较长的varchar2列等数据。

六、 核心对象风险

线上库之间的数据迁移不建议使用copy data。

七、 回退方案

使用copy data从线上拉数据时不需要回退,如果发现query子句的性能有问题,直接kill即可。从线下拉数据做数据订正时,需要先将数据放入中间表。除非是新表数据初始化。

八、 历史故障及教训

略。

  1. da shang
    donate-alipay
               donate-weixin weixinpay

发表评论↓↓