V1.0
|
Revision |
Author/Modifier | Comments | |
|
No. |
Date |
||
| 1.0 | 2012/2/15 | 张中靖 | |
一、 目的
说明使用sqlldr进行数据迁移的前置条件、操作步骤，降低对对应用造成的影响及避免故障
二、 适用范围
l 所有线上库：数据迁移，数据初始化，以及应用几乎只读的表
三、 风险评估
使用sqlldr迁移数据时，普通方式加载数据本质是insert，对表的结构影响不大。使用需要rows控制事务大小。风险主要在使用direct方式加载上：
l 默认情况下会同时维护索引，如果设置参数SKIP_INDEX_MAINTENANCE=true，则不会维护索引。导致索引处于处于unusable状态。
l direct方式加载数据时，表上unique约束始终有效，unique约束的校验是在表加载数据完毕后rebuild索引时完成的，如果索引build时发现有违反unique约束的数据，会导致该索引处于unusable状态。
l direct方式加载数据时，索引结构相当于rebuild，对临时空间需求比较大。如果空间不足，会导致索引处于unusable状态。
l 如果数据空间不足导致加载报错，也会导致索引处于unusable状态。
l 如果启用savepoint机制，sqlldr进程被中断失败时，会使索引处于unusable状态。
l direct方式加载数据时会自动disable表上的触发器和约束(check、外键)，在加载完成再次enable，由于enable时需要获得一个互斥的表锁，如果获得表锁失败，会导致无法成功enable。
l 表上的约束如果以enable novalidate方式启用时，direct方式加载数据时不会校验该约束，可能导致非法数据出现。
四、 操作流程
1. 准备工作
a) sqlldr不支持cluster表，及有global index的分区表。检查加载数据的目标表结构，尤其是触发器和约束，以及表上索引状态。如果有处于unusable的索引，需要评估是否rebuild生效，或者删除。
SELECT INDEX_NAME, STATUS FROM USER_INDEXES
WHERE TABLE_NAME = ‘tablename’ and status<>‘VALID’;
SELECT INDEX_NAME, PARTITION_NAME, STATUS
FROM USER_IND_PARTITIONS
WHERE STATUS <> ‘VALID’;
b) 如果有enable novalidate方式启用的约束，需要提前评估是否再对该约束enable validate一次。
select constraint_name, status, validated
from user_constraints
where table_name = upper(‘tablename’);
enable validate约束:该操作会锁表，需要在业务低峰期处理
alter tablename enable validate constraint constraint_name;
c) 估算表数据需要的空间和加载过程索引需要的临时空间大小。索引需要的临时空间计算方法：
1.3*(number_of_rows)*( 10 + sum_of_column_sizes + number_of_columns )
一般情况下是1.3倍，安全起见可以使用2倍系数【参考】
d) 根据加载的文本文件内容编写controlfile，sqlldr命令使用的各个参数。SKIP_INDEX_MAINTENANCE 这个参数默认值是false，不建议修改。
e) 检查表上是否有活动事务
select *
from v$locked_object a, dba_object b
where a.object_id = b.object_id
and b.owner = ’schema’
and b.object_name = ’tablename’;
2. 执行过程
如果目标表是已存在数据，跟应用确认后，可以先进行exp导出备份，以防后面需
要回退。
这里给出一个示例，从算法提供的文本文件加载数据。
先根据需求编辑的control文件：’-‘后面是参数说明，实际使用时去掉
more ld_ws.ctl
load data
infile ‘totaldata_ae.txt’
append into table X_table_name
(
id sequence(200000000,1), –这里产生表id列的数据，自增长。2亿是起始值，1代表步长
GMT_CREATE “sysdate”, –对gmt_create使用sysdate填充，下同
GMT_MODIFIED “sysdate”,
PRODUCT_ID POSITION(1) CHAR TERMINATED BY ‘\^’ “TO_NUMBER(:PRODUCT_ID)”, –这里开始读取正式数据，注意position(1)，指定从文本各行的第一个字符开始。如果不指定product_id数据的起始位置，上面的gmt_create,gmt_modified列数据初始化会报错
group_ID TERMINATED BY ‘\^’,
FINGER_PRINT TERMINATED BY ‘\^’,
IMG_VERSION TERMINATED BY ‘\^’,
IMG_URL TERMINATED BY ‘\^’,
COMPANY_ID TERMINATED BY ‘\^’,
OFFLINE_DATE TERMINATED BY ‘\^’ “to_number(:OFFLINE_DATE)/3600/24+to_date(’1970-01-01 00:00:00′, ‘yyyy-mm-dd hh24:mi:ss’)”,–这里时间数字转成日期格式
grade TERMINATED BY ‘\t’ “TO_NUMBER(:grade)”
)
–加载数据
sqlldr userid=username/password@dbname direct=true control=ld_ws.ctl log=ld_ws.log bad=ld_ws.err
1
2
ld_ws.err保存不符合上面control文件格式的数据
3. 验证方案
a) 检查sqlldr的日志，确认是否报错，以及非法的数据。并在数据库上检查数据量和导入的数据量是否一致。
比如上面的数据迁移，检查数据量跟日志显示是否一致。
select count(*) from zzzzzz.x_table_name;
b) 检查是否有处于unusable状态的索引及索引分区，如果有，rebuild该索引。
SELECT INDEX_NAME, STATUS FROM USER_INDEXES
WHERE TABLE_NAME = ‘tablename’ and status<>‘VALID’;
SELECT INDEX_NAME, PARTITION_NAME, STATUS
FROM USER_IND_PARTITIONS
WHERE STATUS <> ‘VALID’;
c) 检查是否存在disable状态约束和触发器，如果存在手工enable
select trigger_name, status from user_triggers
where table_name = ‘tablename’
and status <> ‘ENABLED’;
select constraint_name, status from user_constraints
where table_name = ‘tablename’
and status <> ‘ENABLED’;
五、 核心对象风险
由于核心表访问、变更频繁，不宜直接使用sqlldr对核心表大量导入数据。需要先导入一个同构的中间表，再把数据分批insert到正式表。
六、 回退方案
sqlldr加载后可能数据有误，需要进行回退操作。
如果目标表本来就是空表，跟应用确认后，直接清空即可。
如果目标表原有数据，跟应用确认是否使用原有备份数据进行恢复。
七、 历史故障及教训
略。
