欢迎光临
我们一直在努力

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",共同学习,共同成长!!!










赞(0)
【声明】:本博客不参与任何交易,也非中介,仅记录个人感兴趣的主机测评结果和优惠活动,内容均不作直接、间接、法定、约定的保证。访问本博客请务必遵守有关互联网的相关法律、规定与规则。一旦您访问本博客,即表示您已经知晓并接受了此声明通告。