ORA-00600[kluinit:new add column in directpath 2]

ORA-00600[kluinit:new add column in directpath 2]

环境说明：

DB:Oracle 11.2.0.1.0

OS:Windows Server 2012

问题说明：

---alert_cjcorcl.log( 告警日志)

早上数据库巡检时发现报错如下：

ORA-00600: internal error code, arguments: [kluinit:new add column in directpath 2]

--- cjcorcl_dw00_7236.trc(TRACE 日志)

查看对应的trace 日志发现该报错和expdp 自动备份有关；

----CHENJCH_expdp_20181203213000.log(expdp 备份日志)

在查询expdp 备份日志，发现是在备份T_XXX_CJC 表时触发的ORA-00600 报错，报错如下：

ORA-31693: Table data object "CHENJCH"."T_XXX_CJC" failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-00600: internal error code, arguments: [kluinit:new add column in directpath 2], [], [], [], [],

问题分析：

通过报错信息怀疑在进行expdp 备份时，T_XXX_CJC 表在进行添加列操作，从而触发了ORA-00600 错误；

通过MOS 可以查看到该报错详细说明：

ORA-00600 [kluinit:new add column in directpath 2] While Running Expdp ( 文档 ID 1298313.1)

适用：

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.

问题原因：

在通过expdp 备份时，同时某一张表正在新增带有default 值的列；

A column with "DEFAULT n NOT NULL" is added while the data pump export is running in direct path mode, e.g:
SQL> alter table TAB1 add (COL7 NUMBER(1,0) DEFAULT 0 NOT NULL)

The error has been investigated in  Bug 10209354 : ORA-600[KLUINIT:NEW ADD COLUMN IN DIRECTPATH 2] OCCURS IN EXPDP, closed as not a bug. 
The exception is added intentionally to prevent the table to export in  inconsistent state. It is not advisable to alter the table when the direct path export is running.

解决方案：

direct path export 时，尽量避免进行新加列的操作；

Do not add new column when the direct path export is running. 
From  Bug 10209354 , it is not advisable to alter the table when the direct path export is running.

新加哪个列导致的问题呢？

通过logmnr 分析问题时间段的归档文件：

(1) 查看时间点为2018/12/3 21:36:19 附近的归档；

---LAST_DDL_TIME 2018/12/3 21:36:19

select * from user_objects where object_name = 'T_XXX_CJC' ;  

(2)查看问题时间段归档原文件已经删除了；

select name , sequence# , first_time from v$archived_log order by first_change# desc ;

需要通过归档的备份文件恢复出需要的归档文件；

异机恢复：

恢复归档备份、logmnr 分析操作等 都不能在正式环境进行，需要把文件拷贝到测试服务器上在进行操作；

将归档备份日志、归档备份文件、正式数据库参数文件、正式数据库控制文件、正式数据库dict数据字典上传到测试服务器上：

C:\Users\Administrator>sqlplus / as sysdba

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20181214.cjcorcldic',dictionary_location => 'E:\backup\dict');

PL/SQL 过程已成功完成。

(1)通过rman备份日志找到需要的归档备份文件名称

---rman_full_2018-12-04.log

段句柄=E:\BACKUP\RMAN\RMAN_ARC_CJCORCL_20181204_7FTJT4QA_1_1.BAK 标记=ARCH_ORCL 注释=NONE

(2)上传归档备份日志，归档备份文件，正式数据库参数文件，正式数据库控制文件，正式数据库dict数据字典到测试服务器上；

(3)参数文件更改目录位置，并将控制文件放到指定目录，挂载数据库；

(4)在测试服务器上，将归档目录，归档备份文件目录重新指定，并还原出需要的归档文件；

RMAN >

run{

catalog backuppiece 'E:\arch\backup\RMAN_ARC_CJCORCL_20181204_7FTJT4QA_1_1.BAK' ;

set archivelog destination to 'E:\arch' ;

restore archivelog   sequence between 48306 and 48310 ;

}

(5)测试服务器上

SQL > EXECUTE DBMS_LOGMNR.ADD_LOGFILE ( LogFileName => 'E:\arch\CJCORCL_1_48306_954797105.ARC' , Options => dbms_logmnr.new );

SQL > EXECUTE DBMS_LOGMNR.ADD_LOGFILE ( LogFileName => 'E:\arch\CJCORCL_1_48307_954797105.ARC' , Options => dbms_logmnr.addfile );

SQL > EXECUTE DBMS_LOGMNR.ADD_LOGFILE ( LogFileName => 'E:\arch\CJCORCL_1_48308_954797105.ARC' , Options => dbms_logmnr.addfile );

SQL > EXECUTE DBMS_LOGMNR.ADD_LOGFILE ( LogFileName => 'E:\arch\CJCORCL_1_48309_954797105.ARC' , Options => dbms_logmnr.addfile );

SQL > EXECUTE DBMS_LOGMNR.ADD_LOGFILE ( LogFileName => 'E:\arch\CJCORCL_1_48310_954797105.ARC' , Options => dbms_logmnr.addfile );

SQL > EXECUTE dbms_logmnr.start_logmnr ( dictfilename => 'E:\arch\backup\dict20181214.cjcorcldic' );

PL / SQL procedure successfully completed

SQL > create table log_20181205 as select * from v$logmnr_contents ;

Table created

SQL > EXECUTE dbms_logmnr.end_logmnr ;

PL / SQL procedure successfully completed

(6)查询

SQL >

select scn , timestamp , sql_redo , sql_undo

  from log_20181205

  where upper ( sql_redo ) like '%T_XXX_CJC%'

   and upper ( sql_redo ) like '%ALTER%'

  order by 2 ;  

结论： 是如下SQL在expdp备份时触发了ORA-00600: [kluinit:new add column in directpath 2]错误，建议在expdp时尽量避免增加列的操作；

ALTER TABLE T_XXX_CJC ADD (COLXXX1 NUMBER (10) DEFAULT 1 NOT NULL);

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！