电信数据迁移的工作分析与总结

背景： 2 月份的一天电信系统突然夯死，业务应用缓慢无比，经过分析发现一张大表（业务明细表 order_detail_l ）的执行计划变更了，本来应该走索引的，结果变更为全表扫描，该表有 5 亿条记录，全表扫描绝对是个噩梦。

分析原因发现统计信息采集率不够，数据库的自动采集功能已经开启，但是默认的采集率是 5% ，貌似对于这样的大表来说远远不够，重新按照 100% 采集后执行计划恢复正常，

语句如下：
Exec dbms_stats.gather_table_stats(OWNNAME=> 'LIOMUSER' ,TABNAME=> 'ORDER_DETAIL_L' , DEGREE => 6 ,ESTIMATE_PERCENT=> 100 );

注： dbms_stats.gather_table_stats 进行统计信息采集时， cascade 默认为 yes ，即对表分析的同时，索引等其他关联对象也进行了分析， ESTIMATE_PERCENT 参数为采集率。

对于由于采集率过低导致的执行计划便跟问题， oracle 官方给出的解释是 bug

 

但是效率任然很低，因为数据量多索引过大，及时走索引，效率仍然不高，最后决定将生产库数据挪到历史查询库，生产保留 6 个月数据。

生产库需要迁移将近 4 年的数据， 85 张表共 320G ，最大的表有 6 亿记录，大小为 40G ，其中大小查过 20G 的表有 20 张。

局方要求不能影响生产环境， Mygod ！这绝对是个折磨人的活，局方要求所有执行脚本包括业务逻辑判断都精确到分钟。

迁移环境： HP-UX B.11.23 U 9000/800

          Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

 

迁移步骤：

1、  编写逻辑处理包 PKG_DUMP_UTIL ， 主要功能是：

（1）      处理判断各表的归档临界点。

（2）       切割归档 dump 文件，（由于系统对 dump 问题有大小的限制，需要通过表分组实现来将数据割接开来）

（3）       自动生产导出语句

（4）       逻辑判断，导出数据是否业务完整性

2、  采用自动生成的语句导出表数据，（采用数据库泵 expdp ）

导出技术点滴：

（1）      只导出数据 CONTENT=DATA_ONLY

（2）       指定每个 dump 包含的表， tables=(t_name1,t_name2,t_name3)

导出时间统计：

（1）       共 320G 数据，共耗时 6 个小时，最大 dump 文件是 48G

（2）        最大表导出耗时 2 个小时，共 37G

3、  历史查询库数据导入，该环节是最麻烦的环节，最大的历史表 serv_field_change_l 共 14 亿数据，无论是数据导入还是索引维护都是一个漫长的过程，按照要求还不能影响生产。

我决定冒险一试，与局方讨价还价的结果就是，我按照预定时间做操作，但是不能保证在预定时间完成，如果出现超时的情况，需要停止全省的历史查询功能，确保操作顺利完成，最后局方妥协。

 

导入技术点滴：

(1) impdp 在导入时会自动维护统计信息和索引，但是其维护索引的速度比单独创建索引效率低，故后面很多表都是先置索引失效然后再重建索引 :

操作语句：

Impdp liomuser/liomuser directory=dir_name logfile=SERV_FIELD_CHANGE_L.log content=DATA_ONLY TABLE_EXISTS_ACTION=APPEND DUMPFILE=iom_dump_01.dmp TABLES=SERV_FIELD_CHANGE_L

TABLE_EXISTS_ACTION=APPEND 指当表存在时，数据库泵只是附加数据

另外几个需要注意的参数：

  Remap_schema ：当需要将一个 schema 的数据导入到另一个 schema 时，

需要只出原 schema 和新的 schema ，不过貌似 impdp 不

能在表名不同的表间导数据。

                Parallel ：原本想增加这个参数，开并行提高速度，但是加上后语句报“ dump

文件无法找到”，狂晕

             索引置失效语句：

                 alter index LIOMUSER.IDX_SERV_FIELD_CHANGE_LIS unusable;

             索引重建语句：

                   alter index LIOMUSER.IDX_SERV_FIELD_CHANGE_LIS rebuild;

             主键置失效语句：

                  alter table LIOMUSER.SERV_FIELD_CHANGE_L

                         disable constraint PK_PRODUCT_RELATION_I_P_LNJ2;

             主键恢复语句：

                   alter table LIOMUSER.SERV_FIELD_CHANGE_L    enable constraint PK_SERV_FIELD_CHANGE_LIS;

(2)    历史库查询库和生产库出现表结构不一致的情况，这个问题需要重视，已经给公司发了邮件。

(3)    历史查询库和当前生产库的索引不一致情况，（历史库索引少），我的分析认为缺少的索引在历史查询中没有用到，索引不需要和生产一致。

(4)    导入时间记录：

以最大的表为例， SERV_FIELD_CHANGE_L ，

   数据导入： 2 个小时

   索引重建： 4 个小时

   主键恢复： 6 个小时（该表主键有 5 个字段，疯掉了！）

   数据大小： 37G

   历史表总数据量： 14 亿条（每个中国人一条记录，还有多的，晕！）

   关于导入环节的一点总结：

         1 、预留足够多的时间，本次迁移时间非常紧张，出现了一些意料外的问题，另外

完成操作后一定要测试性能。

         2 、实践证明，将索引置失效再导入数据，然后再重建索引，比利用数据库泵维护

索引要快很多。

3、  在生产环境进行操作前，一定要比对表结构和索引，对于差异的要补充，对于索引的差异，一定要考虑到迁移后数据量的变化对执行计划的影响，完善和分析索引以及压力测试是很必要的。

4、  生产库清理，将已经归档的数据重生产环境清除：

       实施策略：

（1）       建立临时表，原表名 _TMP ，将保留数据插入临时表

（2）       临时表统计信息采集

（3）       将临时表切换为生产表

           注意事项：提取表的 ddl 语句，建议最好使用 dbms_metadata 工具，因为在实际的操作中发现 plsql 提取的 ddl 语句有不完整的地方

 

总结：迁移事关生产系统，一定要仔细规划，提前固定好执行脚本，做好测试工作，确保在执行中不用临时修改，任何一个参数的变动都会产生不可预期的后果。

      规划好时间，明确时间点和责任。

 

最后给出一个比较有用的 sql ，监控事务进度：

select username,

      sid,

      opname,

      round(sofar * 100 / totalwork, 0) || '%' as progress,

      time_remaining,

      sql_text

  from gv$session_longops, gv$sql

where time_remaining <> 0

  and sql_address = address

  and sql_hash_value = hash_value

其中 gv$session_longops 是 oracle 的动态性能视图，记录执行超过 10s 的事务执行情况。