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