Shulou(Shulou.com)06/01 Report--

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

Environment description:

DB:Oracle 11.2.0.1.0

OS:Windows Server 2012

Description of the problem:

-alert_cjcorcl.log (alarm log)

During the morning database inspection, it was found that the error was as follows:

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

-cjcorcl_dw00_7236.trc (TRACE log)

Check the corresponding trace log and find that the error is related to the automatic backup of expdp.

-CHENJCH_expdp_20181203213000.log (expdp backup log)

When querying the expdp backup log, it is found that the ORA-00600 error triggered when backing up the T_XXX_CJC table is as follows:

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], []

Problem analysis:

Through the error message, it is suspected that the T_XXX_CJC table is adding columns during the expdp backup, which triggers the ORA-00600 error.

A detailed description of the error can be found through MOS:

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

Applicable:

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.

The cause of the problem:

When backing up through expdp, a table is adding a column with a default value

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 (1p0) 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.

Solution:

When direct path export, try to avoid adding new columns.

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.

Which column is added to cause the problem?

Analyze the archived files for the problem period through logmnr:

(1) View the archives around 21:36:19 on 2018-12-3.

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

Select * from user_objects where object_name = 'Tunable XXXThe CJC'

(2) the original file has been deleted during the problem period.

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

The archived files need to be recovered from the archived backup files.

Different machine recovery:

Recovery of archived backups and logmnr analysis operations cannot be carried out in a formal environment, and files need to be copied to the test server for operation.

Upload archived backup logs, archived backup files, formal database parameter files, formal database control files, and official database dict data dictionary to the test server:

C:\ Users\ Administrator > sqlplus / as sysdba

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

The PL/SQL process completed successfully.

(1) find the name of the archive backup file you need through the rman backup log

-rman_full_2018-12-04.log

Segment handle = E:\ BACKUP\ RMAN\ RMAN_ARC_CJCORCL_20181204_7FTJT4QA_1_1.BAK tag = ARCH_ORCL comment = NONE

(2) upload archive backup log, archive backup file, formal database parameter file, formal database control file, official database dict data dictionary to the test server

(3) change the directory location of the parameter file, place the control file in the specified directory, and mount the database.

(4) on the test server, reassign the archive directory and archive backup file directory, and restore the required archive files.

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) on the test server

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

SQL >

Select scn, timestamp, sql_redo, sql_undo

From log_20181205

Where upper (sql_redo) like'% Tunable XXXCJC%'

And upper (sql_redo) like'% ALTER%'

Order by 2

Conclusion: the following SQL triggered the ORA-00600: [kluinit:new add column in directpath 2] error during expdp backup. It is recommended to avoid adding columns during expdp.

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

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.