Oracle在线重定义生产环境大表分区的惨烈踩雷记录

一、任务与方案

      公司有一批大表需要分区，由于业务是24X7，所以停服是不可能的，只能考虑在线重定义。在线重定义推出已经很久了（不算新功能），但在生产上，还是没用过，在测试环境进行模拟测试，很顺利，速度也很快，几千万的表，十几分钟就完成了，于是方案就这样敲定了；

二、开局还算顺利

      先从一张小表（1亿记录），按以下步骤：

      第一步、空间准备、创建中间表（先分好区）、检测是否可在线重定义；这个是准备工作；

      第二步、启动重定义（start_redef),这个会创建一些物化视图等对象，然后插入当前记录到中间表，这一步比较耗时间，如果开8个并行，一般150G的表15分钟左右；

      第三步、拷贝索引、约束、触发器、统计信息等对象(copy_dep)；这个也比较耗时，取决于表的大小与索引的多少，以150G的表，8个并行为例，索引约5分钟一个，按10个索引算，这一步要50分钟；

      第四步、数据同步(sync_inter)，原理是新的插入与更新都记录在物化视图中，然后同步到中间表去，如果不做这一步，切换时也会做这一个动作的；

      第五步、把原表与中间表做一个调换(Finish_redef)，这一步肯定会锁表的，所以可以预见是有阻塞会话风险的，而且也未必一定能成功；

     如果不成功或者中止了的话，就还有第六步:回退（Abort_redef),这一步目的是回滚到最初的状态，这样才会删除物化视图等中间对象，才可以重新进行重定义；

      这些算是标准的步骤，但实际根据生产环境的情况（比如存在ogg复制），还需要做以下调整：

     1、开始之前，最好是排除了中间表与物化表的复制，因为量太大，容易造成复制延时很长，宁可在目标环境也做一次重定义，如果能接受，也可以不排除；

     2、在完成切换之后，要马上重新加一下add trandata table_name,因为中间表切成了业务表，相当于是一个新表，不加的话，就会造成只有插入的记录能复制，而更新的记录的不能，时间隔得越久，需要修复的数据可能越多；

     3、在数据同步之前，最好给中间表进行一次表分析，可以避免同步时走错执行计划；

      按照以上步骤，前面几个1亿左右的小表都顺利地完成了分区切换，开局还算不错；

三、第一个雷：数据同步导致的锁

     完成了小表，便接着处理中等大小的表（1亿-10亿,50G-200G),开始时间都放在晚上22：00，但操作的时间明显增长了，数据同步用时也很久，久就多等下呗，我也没太在意，突然，手机短信报警，活动会话已达到50了，马上登录数据库查，活动会话已是300多了，都是行锁，而且阻塞源有多个，没有发现明显的根阻塞会话，为了尽快解除会话堆积问题，采取了kill全部阻塞源的方案，阻塞解决了，但也误杀了一些业务会话；事后分析发现，真正的阻塞源还是数据同步引发的，数据同步是把物化视图的数据merge到中间表，事实上的确是不阻塞业务表的，但一旦同步完成，sys用户会立即启动一个异步purgelog的任务，delete物化视图日志的已同步数据，如果数据量一大，这个SQL就很慢（执行计划不佳），它就会阻塞物化视图日志的update,而物化视图的update又会阻塞表的插入与update,造成了多层阻塞，如果层层追溯的话，还是可以找到根会话的，kill根会话即可；即便是吃过一次亏，后来我们也还是再上了一次当，出现了阻塞的情况但是仍然找不到阻塞源，原来这个purgelog的任务自动跑到另一个实例去运行了（RAC），而我们的检测脚本却是单实例的；

       这一个雷的总结就是，数据同步也有阻塞的风险！原先一直以为，只有切换才会有锁。

       避开的办法是，要么找到业务最低谷时段进行操作，小表晚上22点就OK，但更大的表则不行，要到凌晨2：00才是最低谷；然后，不要想先提前把前面3步做完，等到低谷时来做同步与切换，因为间隔时间越长，物化日志量就越大，purgelog就越久； 总之，就是在低谷期做，而且是一气呵成，要同步的量小，可能是最顺利的；

       这里，个人感觉oracle在这个地方完全可以加强一下，异步purgelog是否真的必要？能否允许设定不启动？或者能否先不delete，搞完后一次性truncate?如果数据同步没有阻塞问题，那就方便好多，把这些步骤分散进行，比如提前把多个表的初始化与索引弄好，并不断同步，只在低谷期一一进行切换，效率就高得多；

四、第二个雷：进退停，都不行

       在给某个表做切换时，到了数据同步这一步，发现数据同步没法完成，眼看着造成越来越多的会话阻塞，只有停止它，

既然不能前进，那么我们就回退，结果发现，回退也是久久不能完成，阻塞达到一定程度之后，只有中止回退，暂时停在这里，但停下来并不代表就没有问题，物化视图与日志在不断增长，一天增长几千万，而且每次插入或更新都还要刷新物化视图，高并发下造成了一次活动会话造过100的险情，感觉就像踩着了地雷，进也不能，退也不能，停在这里又有被敌人扫射的风险。

       这下可扎心了，怎么办？请教同事朋友，以及相关的群，都没这方面的经验；开SR，从中文到英文，从二级到一级，也得不到有帮助的回答，oracle虽然文档很多，但有些问题，也不容易找到答案。只有去分析它的行为，最终发现是一个递归SQL走错了执行计划（这个SQL也只是我的一篇文章《 记一次增加分区要两个小时的优化案例 》里描述的那个SQL，，把这个SQL的执行计划固化后，操作就正常了，完成了回退后，重新进行了一次重定义，分区完成；

       对于这个坑，真的是很坑！而且还有可能不尽相同的入坑方式，一旦入了坑，可能面对的压力会很大，因为可能好几次变更都不能完成，进退两难，最关键的是缺少可供参照的解决方案，只能具体问题，具体分析了；

五、第三个雷：执行计划突变引发了宕机

      中等大小的表做完后，还有一个62亿记录600GB的大表，这个表我最担心的第一次同步数据时IO太大或时间太久，其实一些老数据是可以归档了，如果在线重定义能自定义初始化数据的范围，比如只初始化最近三个月的数据，那就太好了，可惜不能，只能全量插入，但插入之后，我可以用truncate分区的方法把老数据清掉，这样，后面建索引就快了，相当于同时实现了分区与归档的功能（老数据在原表以及ogg复制库仍存在）。

       这是特定场景的特殊用法，建立在两个基础以上：

       1、在线重定义是通过物化视图日志同步的，它通过merge的机制可以实现两边数据的一致性，但它并不强制要求两边一模一样；只要物化视图中insert与update能全部实施到中间表上，那么最新的数据能保证是一致的，哪怕老数据已经没有了；

       2、我们的业务恰好是跟时间紧密相关的，3个月之前的数据已经很冷了，可以确认不会再有更新了。

       最担心的IO消耗竟然还比较好，开8个并行，共用了50分钟，存储的IO使用率没有超过25%，之后清掉了老数据，建好索引，分析表，同步很快，切换很快，都顺利地完成了，以后大石落定，开始处理OGG的事，这个时候，发现活动会话又上来了，仔细一看，竟然是一个相关的SQL走错了执行计划，由索引变成了分区全扫描！最悲催的是，这个时候，操作已不正常，无法像平时一样，生成kill会话的SQL，内存在也没有原来正确的执行计划，想构造一个，任何操作一提交就变成了窗口“无响应”，接着，实例都连不上了，宕机了！马上转到另一个节点，有一些会话已经飘过来了，好在这个节点还可以正常操作，马上构造正确的执行计划，固化，然后KILL现有的全表扫描会话，情况得到了控制，再重启挂了的节点，然后，只能是暗自阿弥佗佛，如果没有控制住的话，那估计要跑路了；

      其实执行计划的变化，一开始就考虑过，所以每个表都要做一次统计，之前的表，就从来没有出现过执行计划的变化，但这个执行计划的变化，跟表的分区有关，跟SQL的写法不当也有关，总之就是没有充分评估到这方面的风险，这方面是有欠缺的；

六、第四个雷：OGG的所有抽取都挂空档了

       进行各方面检查后，看似一切正常了，但ogg的抽取任务却有些怪，状态是running的，但lag却是不断地增长，检查目标端，数据没有进来！纳闷啊纳闷，不只是这个表相关的extract,而是所有的extract都是这样，可是又不报错，反复STOP加START，还是一样的情况，从来没有遇到过这种现象，网上也找不到类似的案例，Strace也看起来是正常工作的，尝试跳过大事务，没有进展，尝试跳过一点时间，也没有解决，百思不得其解，陷入深深地困惑中......

       时间一分一秒地过去，延时一分一秒地增长，也是一个耽误不起的坑，于是决定拿一个表做测试，跳过较长的一段时间，发现报错了，redo日志不可用（不在本地节点的问题），拷贝过来后，发现正常了！只能这么干，都往前跳一段时间，这个抽取正常了，开始追日志了，但代价是跳过的这一段时间，就需要去比对与手工同步了；

       猜想这个跟600G的大表初始化生成了很多的日志文件有关，但具体怎么相关，暂不清楚，惹不起，只能躲了。

七、写在最后

      终于走出了地雷阵，感觉是松了一口气，尽管付出了惨痛的代价，但真的有很多值得吸取的经验与教训，如果我所付出的代价，能够给到其他人一些参考，从而少踩一些坑，那也算值得了^_^

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13365316/viewspace-2216307/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13365316/viewspace-2216307/