在线重定义（online redefinition）真的在线吗？

原创

布衣530

修改于 2025-01-26 16:03:07

8200

代码可运行

文章被收录于专栏：Oracle DBAOracle DBA

运行总次数：0

代码可运行

背景

最近用Online Redefinition解决了一批普通大表（超千万级别）转换成分区表，后期方便做数据裁剪（truncate partition）,Online Redefinition的优点是支持在线操作，但在操作过程中还是发生了一些生产操作的事故，分享出来让大家避坑。

问题一：表在线重定义完成后，在备库查询相关表时报错

Wed Jul 21 04:43:20 2021
Errors in file /u01/oracle/diag/rdbms/db_standby1/db/trace/db_ora_25064.trc (incident=96450):
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [263768], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/diag/rdbms/db_standby1/db/incident/incdir_96450/db_ora_25064_i96450.trc
Wed Jul 21 04:43:45 2021
Dumping diagnostic data in directory=[cdmp_20210721044345], requested by (instance=1, osid=25064), summary=[incident=96450].
Wed Jul 21 04:43:45 2021
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Jul 21 04:43:45 2021
Sweep [inc][96450]: completed
Sweep [inc2][96450]: completed
Wed Jul 21 05:03:17 2021

解决方案：重启备库恢复正常，目前尚未排查到原因，目前仅偶遇一次。

问题二：dbms_redefinition.sync_interim_table 操作异常慢，导致订单超时失败

回溯操作流程：每天凌晨3-7点之间4小时窗口期，计划需要执行1周。第一天我就把需要转换表的临时表（分区表）全部创建完成了，然后第一天操作很顺利。但当第二天操作转换的时候，在dbms_redefinition.sync_interim_table执行异常的慢，考虑到表的数据可能很多于是只能等，但当执行1个小时后仍然没有结果，而且有开发人员反馈有接收到订单超时导致失败率在不停的上升。于是只能ctrl + c 取消操作，但好像是hang住了，ctrl + c没有反应，可能是回退需要时间。此时此刻到了进退两难的情况，ctrl + c没有反应，kill 会话又怕有不良的反应，因为此表涉及业务核心表，没办法只能生抗了半小时终于ctrl + c有了反应，然后订单超时也就没有了。这真是一次惨痛的经历，后面被领导痛批一顿，要求测试明白后再上线操作。回想第一天与第二天的操作再往前回溯之前的操作经历，然后通过AWR报告分析发现在dbms_redefinition.sync_interim_table执行计划不对的情况。于是考虑是不是我第一天把临时表创建完以后，当天晚上的Oracle 的维护任务把临时表给收集信息了，然后再dbms_redefinition.sync_interim_table操作时根据收集的空表的统计信息生成的执行计划。为了验证这个猜想决定复现一下。

测试<普通表转换分区表>

普通表数据量：

TWO@two>select count(*) from t1;
  COUNT(*)
----------
 294158338

创建自动分区表：

create table t1_part
 ( id         varchar2(50),
   ......
   create_date date 
)partition by range (create_date )INTERVAL (NUMTOYMINTERVAL ( 1, 'MONTH' ))  -- 按月分区
(
  partition PART2024_01 values less than (TIMESTAMP' 2024-02-01 00:00:00'),
  partition PART2024_02 values less than (TIMESTAMP' 2024-03-01 00:00:00')
);

语法：NUMTOYMINTERVAL ( n , ‘char_expr’ ) char_expr：日期描述，可以是YEAR和MONTH；作用：可以将数字转换成相应的日期单位时间比如：NUMTOYMINTERVAL ( 1, ‘MONTH’ ) 表示一个月,NUMTOYMINTERVAL ( 1, ‘YEAR’ ) 表示一年对于day、hour、minute、second使用的是numtodsinterval函数，方法和numtoyminterval一样。
记录当前Session的SPID,方便查看trc

SYS@two>select spid from v$process p join v$session s on s.paddr=p.addr where s.sid=USERENV('SID') ;

SPID
------------------------
29058

还未收集统计信息

TWO@two> select TABLE_NAME,NUM_ROWS, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED 
 from user_tables 
 where TABLE_NAME='T1_PART';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T1_PART

手动执行自动收集任务：此操作可翻看《Oracle 自动收集统计信息任务策略测试》

SYS@two>exec dbms_stats.gather_database_stats_job_proc();
PL/SQL procedure successfully completed.

T1_PART已经被收集

TWO@two> select TABLE_NAME,NUM_ROWS, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED 
 from user_tables 
 where TABLE_NAME='T1_PART';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T1_PART                                 0 2024-12-17 15:39:31

-- 查看分区未分析
TWO@two> select table_name,object_type,stale_stats,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED 
from user_tab_statistics where  table_name='T1_PART'; 

TABLE_NAME                     OBJECT_TYPE  STA LAST_ANALYZED
------------------------------ ------------ --- -------------------
T1_PART                        TABLE        NO  2024-12-17 15:39:31
T1_PART                        PARTITION
T1_PART                        PARTITION

检查下这张表是否可以在线重定义，无报错表示可以

SQL> exec dbms_redefinition.can_redef_table('TWO', 'T1');
PL/SQL procedure successfully completed.

启动在线重定义：

SQL> exec dbms_redefinition.start_redef_table('TWO', 'T1','T1_PART');
PL/SQL procedure successfully completed.

匿名块模拟往myTable插入数据

begin
    for x in 1..50000 loop
        insert into myTable (id, session_id,CREATE_DATE) 
        values(myTable_seq.NEXTVAL,USERENV('SID'),to_date('2024-12-04','yyyy-mm-dd'));
        commit;
    end loop;
end;

开启10046追踪

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.

刷新数据

SQL> exec dbms_redefinition.sync_interim_table('TWO','T1','T1_PART');
PL/SQL procedure successfully completed.

结束重定义，被卡住了，此时访问此表的应用报超时（生产经验）

SQL> exec dbms_redefinition.finish_redef_table('TWO', 'T1','T1_PART');
PL/SQL procedure successfully completed.

关闭10046追踪

SQL> alter session set events '10046 trace name context off';
Session altered.

finish_redef_table 按正常来说应该是秒级完成，但这次消耗：7min

过程中如有报错使用此命令终止：

 exec DBMS_REDEFINITION.abort_redef_table('TWO', 'T1','T1_PART');

查看trace 文件

SQL> select spid 
  2  from v$process p 
  3  join v$session s on s.paddr=p.addr
  4  where s.sid=USERENV('SID') ;
SPID
------------------------
21766

查看trc：cat /u01/oracle/diag/rdbms/two/two/trace/two_ora_21766.trc | less

正常操作流程

T1_PART 表未被收集，最后转换完成：28s

ash报告分析

ash 时间范围

等待事件：

负载：SQL_ID：7c7227vc0k3mw

分析Trc文件

格式化trc

[oracle@db ~]$ tkprof /u01/oracle/diag/rdbms/two/two/trace/two_ora_21766.trc  10046.trc
[oracle@db ~]$ cat 10046.trc | less

查看TRC文件中的SQL_ID：7c7227vc0k3mw 对比

查看RC文件中的SQL_ID：8wm20squh144m 对比

小结

因为临时表T1_PART被收集后，使7c7227vc0k3mw 执行计划发生了变化“全表扫描”，IO等待事件：db file scattered read “Times：62970”、direct path write temp “Times：64808”；
最后的转换操作：8wm20squh144m，多了个latch: row cache objects事件:“当用户进程试图访问或更新缓存的数据字典值时，此锁存器就会发挥作用”;
综上分析：因临时表分析导致执行计划失真导致以上问题；