ITPub博客

首页 > 数据库 > Oracle > ORA-00600[kluinit:new add column in directpath 2]

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

原创 Oracle 作者:chenoracle 时间:2018-12-05 12:51:24 0 删除 编辑

 

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);

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论
纸上得来终觉浅,绝知此事要躬行!

注册时间:2014-08-05

  • 博文量
    200
  • 访问量
    637487