RE: ORA-600 Error [Possible Data Loss]
We have found the cause of the problem. If populate the varray for a row with 100
values, copy that data to a temporary table and then perform split and exchange
partition operations everything is fine. However if we populate the varray with 1000
values for a row and then perform the same operations. Selecting from the partition
results in the error.
For those wanting details ...
create or replace type int_values as varray(16384) of number(38);
create table arch_wave_i
(pv_id number(38),
timestamp date,
nanosecs number(9),
stat number(8),
sevr number(8),
ostat number(16),
value int_values)
partition by range (timestamp)
( partition first values less than
(TO_DATE('01/01/2003 00:00:00', 'mm/dd/ hh24:mi:ss')),
partition bin values less than (MAXVALUE) );
create index archive_wave_index on archive_wave_i (timestamp, pv_id) local;
create table arch_wave_i
(pv_id number(38),
timestamp date,
nanosecs number(9),
stat number(8),
sevr number(8),
ostat number(16),
value int_values)
partition by range (timestamp)
( partition first values less than
(TO_DATE('01/01/2003 00:00:00', 'mm/dd/ hh24:mi:ss')),
partition SEP1803 values less than
(TO_DATE('09/19/2003 00:00:00', 'mm/dd/ hh24:mi:ss')),
partition bin values less than (MAXVALUE) );
create index arch_wave_i_index on arch_wave_i (timestamp, pv_id) local;
To create the error
To recreate the problem:
1. Populate arch_wave_i with one or more rows that have the
following characteristics:
a. the timestamp field is >= '09/18/2003 00:00:00' and
< '09/19/2003 00:00:00'
b. the value field is an array of 1000 or more integer
values (the actual values do not matter)
c. the other fields (pv_id, nanosecs, stat, sevr, ostat)
may be set to 0
2. create table temp_wave_i as select * from arch_wave_i;
3. create index temp_wave_i_index on temp_wave_i (timestamp, pv_id);
4. alter session set nls_date_format = 'mm/dd/ hh24:mi:ss';
5. alter table archive_wave_i split partition bin at
('09/19/2003 00:00:00') into
(partition SEP1803, partition bin);
6. alter table archive_wave_i exchange partition SEP1803 with
table temp_wave_i including indexes with validation;
7. select * from archive_wave_i partition (SEP1803);
This results in one of the following messages:
a. ORA-00600: internal error code, arguments:
[koxsisz1], [1], [], [], [], [], [], []
b. ORA-00600: internal error code, arguments:
[kcbgtcr_4], [2094072], [0], [1], [], [], [], []
Sometimes the second argument is 2094096 or 2094120
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, September 18, 2003 1:35 PM
To: Multiple recipients of list ORACLE-L
Ian,
I was going to recommend to try exporting your problematic partition with direct=y,
that way normal SQL query processing layer is bypassed in Oracle kernel & all data
belonging to segment is read directly (thus hopefully avoiding the koxsisz1 crash) ...
but I'm not sure whether varrays don't turn exp to conventional as is the case with
objects and LOBs...
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 18, 2003 8:29 PM
> The error was first discovered when we tried to move a partition. (
> alter
table archive_wave_i move partition SEP1103 tablespace
CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i
once it was determined that no new varray data was accessible. My theory is that this
is logical corruption of the data dictionary, but I have not worked out the
particulars.
>
> Selecting via an index or FTS does not matter. What type of error do
> you
believe koxsisz1 to be?
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]
>
>
>
> -Original Message-
> Sent: Thursday, September 18, 2003 4:13 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi!
>
> If even Oracle hasn't seen this error, then probably we can't help
> much
here either. Just a wild guess, try to move this partition to another location and
select from it then (although koxsisz1 isn't a data layer error as far as I
understand). Try to read using index if available, then using full hint etc..
>
> Tanel.
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Thursday, September 18, 2003 3:04 AM
>
>
> > This is not my week ...
> >
> > Has anyone seen anything like this
> >
> > select value from chanarch_nlc.old_archive_wave_i partition
> > (SEP1603)
> where pv_id =
> >*
> > ERROR at line 1:
> > ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [],
> > [],
> [], [],
> > []
> >
> > I have Oracle support working on the problem.They say they have
never
> seen such an error before. Oracle though often tells me that. The
> table
looks like
> >
> > Name Null?Type
> > -
> > ---
> -
>
RE: ORA-600 Error [Possible Data Loss]
The varray data is stored in a LOB. So I expect direct export will not work. But I'll give it a try. Ian -Original Message- Sent: Thursday, September 18, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Ian, I was going to recommend to try exporting your problematic partition with direct=y, that way normal SQL query processing layer is bypassed in Oracle kernel & all data belonging to segment is read directly (thus hopefully avoiding the koxsisz1 crash) ... but I'm not sure whether varrays don't turn exp to conventional as is the case with objects and LOBs... Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 8:29 PM > The error was first discovered when we tried to move a partition. ( > alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. > > Selecting via an index or FTS does not matter. What type of error do > you believe koxsisz1 to be? > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > > > -Original Message- > Sent: Thursday, September 18, 2003 4:13 AM > To: Multiple recipients of list ORACLE-L > > > Hi! > > If even Oracle hasn't seen this error, then probably we can't help > much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, September 18, 2003 3:04 AM > > > > This is not my week ... > > > > Has anyone seen anything like this > > > > select value from chanarch_nlc.old_archive_wave_i partition > > (SEP1603) > where pv_id = > >* > > ERROR at line 1: > > ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], > > [], > [], [], > > [] > > > > I have Oracle support working on the problem.They say they have never > seen such an error before. Oracle though often tells me that. The > table looks like > > > > Name Null?Type > > - > > --- > - > > PV_ID NUMBER(38) > > TIMESTAMP DATE > > NANOSECS NUMBER(9) > > STAT NUMBER(8) > > SEVR NUMBER(8) > > OSTAT NUMBER(16) > > VALUE CHANARCH_NLC.INT_VALUES > > > > The last column is a varray. I can retrieve any of the other > > columns from > that partition. > > I can also retrieve the value column from any partition before > > September > 11, 2003. > > Any partition after that fails with the 0ra-600 error when the > > value > column is selected. > > > > DBV at first gave errors along the lines of > > > > BV-00102: File I/O error on FILE > > (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op > > eration (-1) > > > > The file is 2018 MB in size. But resizing the files downwards and > > back up > again fixed > > That problem. The files are allowed to autoextend with 2018 being > > the > maximum size. > > > > This is not the same database which had the RAID problem. > > > > Ian MacGregor > > Stanford Linear Accelerator Center > > [EMAIL PROTECTED] > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: MacGregor, Ian A. > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L (or the > > name of mailing list you want to be removed from). You may also send > > the HELP command for other information (like subscribing). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT s
Re: ORA-600 Error [Possible Data Loss]
Ian, I was going to recommend to try exporting your problematic partition with direct=y, that way normal SQL query processing layer is bypassed in Oracle kernel & all data belonging to segment is read directly (thus hopefully avoiding the koxsisz1 crash) ... but I'm not sure whether varrays don't turn exp to conventional as is the case with objects and LOBs... Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 8:29 PM > The error was first discovered when we tried to move a partition. ( alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. > > Selecting via an index or FTS does not matter. What type of error do you believe koxsisz1 to be? > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > > > -Original Message- > Sent: Thursday, September 18, 2003 4:13 AM > To: Multiple recipients of list ORACLE-L > > > Hi! > > If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, September 18, 2003 3:04 AM > > > > This is not my week ... > > > > Has anyone seen anything like this > > > > select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) > where pv_id = > >* > > ERROR at line 1: > > ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], > > [], > [], [], > > [] > > > > I have Oracle support working on the problem.They say they have never > seen such an error before. Oracle though often tells me that. The table looks like > > > > Name Null?Type > > - > > --- > - > > PV_ID NUMBER(38) > > TIMESTAMP DATE > > NANOSECS NUMBER(9) > > STAT NUMBER(8) > > SEVR NUMBER(8) > > OSTAT NUMBER(16) > > VALUE CHANARCH_NLC.INT_VALUES > > > > The last column is a varray. I can retrieve any of the other columns > > from > that partition. > > I can also retrieve the value column from any partition before > > September > 11, 2003. > > Any partition after that fails with the 0ra-600 error when the value > column is selected. > > > > DBV at first gave errors along the lines of > > > > BV-00102: File I/O error on FILE > > (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op > > eration (-1) > > > > The file is 2018 MB in size. But resizing the files downwards and > > back up > again fixed > > That problem. The files are allowed to autoextend with 2018 being the > maximum size. > > > > This is not the same database which had the RAID problem. > > > > Ian MacGregor > > Stanford Linear Accelerator Center > > [EMAIL PROTECTED] > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: MacGregor, Ian A. > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L (or the > > name of mailing list you want to be removed from). You may also send > > the HELP command for other information (like subscribing). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L
Re: ORA-600 Error [Possible Data Loss]
Hi! I'm on thin ice here, but I think KO means some sort of object layer (KOC is Kernel-Object-Cache module as far as I understand), so koxsisz1 has probably something to do with objects as well (thus not caching, x might mean execute or transaction or anything else:). On the other hand, varrays are somewhat related to objects as well, at least in sense that they are data structures, not basic datatypes. (This one probably won't work, but try to temporarily increase OBJECT_CACHE_MAX_SIZE_PERCENT and OBJECT_CACHE_OPTIMAL_SIZE in your session (put first one to 1000 and second one to 1048576 for example). Maybe there are some specific problems with caching your varray in client side, but as I said I doubt it will help (but it's easy to try out).) Otherwise, try to isolate those varrays causing you problem. Are all varrays in SEP1603 partition unusable? Only some of them? Only new ones? Are any other partitions affected? One more way would be to take one working record and insert it to new partition. If now the new record gets unusable again, it's possible to take relevant blockdumps for both these records, to see whether theres any difference in low-level data. For summary, since you had some file IO errors (as shown by DBV), you might have corrupted your data during the move operation. Maybe some disk controller or memory caused this error... Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 8:29 PM > The error was first discovered when we tried to move a partition. ( alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. > > Selecting via an index or FTS does not matter. What type of error do you believe koxsisz1 to be? > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > > > -Original Message- > Sent: Thursday, September 18, 2003 4:13 AM > To: Multiple recipients of list ORACLE-L > > > Hi! > > If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, September 18, 2003 3:04 AM > > > > This is not my week ... > > > > Has anyone seen anything like this > > > > select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) > where pv_id = > >* > > ERROR at line 1: > > ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], > > [], > [], [], > > [] > > > > I have Oracle support working on the problem.They say they have never > seen such an error before. Oracle though often tells me that. The table looks like > > > > Name Null?Type > > - > > --- > - > > PV_ID NUMBER(38) > > TIMESTAMP DATE > > NANOSECS NUMBER(9) > > STAT NUMBER(8) > > SEVR NUMBER(8) > > OSTAT NUMBER(16) > > VALUE CHANARCH_NLC.INT_VALUES > > > > The last column is a varray. I can retrieve any of the other columns > > from > that partition. > > I can also retrieve the value column from any partition before > > September > 11, 2003. > > Any partition after that fails with the 0ra-600 error when the value > column is selected. > > > > DBV at first gave errors along the lines of > > > > BV-00102: File I/O error on FILE > > (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op > > eration (-1) > > > > The file is 2018 MB in size. But resizing the files downwards and > > back up > again fixed > > That problem. The files are allowed to autoextend with 2018 being the > maximum size. > > > > This is not the same database which had the RAID problem. > > > > Ian MacGregor > > Stanford Linear Accelerator Center > > [EMAIL PROTECTED] > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: MacGregor, Ian A. > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail mess
RE: ORA-600 Error [Possible Data Loss]
The error was first discovered when we tried to move a partition. ( alter table archive_wave_i move partition SEP1103 tablespace CHANARCH_NLC_2003_09_DATA). The table was renamed to OLD_archive_wave_i once it was determined that no new varray data was accessible. My theory is that this is logical corruption of the data dictionary, but I have not worked out the particulars. Selecting via an index or FTS does not matter. What type of error do you believe koxsisz1 to be? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:13 AM To: Multiple recipients of list ORACLE-L Hi! If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 3:04 AM > This is not my week ... > > Has anyone seen anything like this > > select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = >* > ERROR at line 1: > ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], > [], [], [], > [] > > I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like > > Name Null?Type > - > --- - > PV_ID NUMBER(38) > TIMESTAMP DATE > NANOSECS NUMBER(9) > STAT NUMBER(8) > SEVR NUMBER(8) > OSTAT NUMBER(16) > VALUE CHANARCH_NLC.INT_VALUES > > The last column is a varray. I can retrieve any of the other columns > from that partition. > I can also retrieve the value column from any partition before > September 11, 2003. > Any partition after that fails with the 0ra-600 error when the value column is selected. > > DBV at first gave errors along the lines of > > BV-00102: File I/O error on FILE > (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op > eration (-1) > > The file is 2018 MB in size. But resizing the files downwards and > back up again fixed > That problem. The files are allowed to autoextend with 2018 being the maximum size. > > This is not the same database which had the RAID problem. > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: MacGregor, Ian A. > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L (or the > name of mailing list you want to be removed from). You may also send > the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-600 Error [Possible Data Loss]
Hi! If even Oracle hasn't seen this error, then probably we can't help much here either. Just a wild guess, try to move this partition to another location and select from it then (although koxsisz1 isn't a data layer error as far as I understand). Try to read using index if available, then using full hint etc.. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 3:04 AM > This is not my week ... > > Has anyone seen anything like this > > select value from chanarch_nlc.old_archive_wave_i partition (SEP1603) where pv_id = >* > ERROR at line 1: > ORA-00600: internal error code, arguments: [koxsisz1], [1], [], [], [], [], [], > [] > > I have Oracle support working on the problem.They say they have never seen such an error before. Oracle though often tells me that. The table looks like > > Name Null?Type > - --- - > PV_ID NUMBER(38) > TIMESTAMP DATE > NANOSECS NUMBER(9) > STAT NUMBER(8) > SEVR NUMBER(8) > OSTAT NUMBER(16) > VALUE CHANARCH_NLC.INT_VALUES > > The last column is a varray. I can retrieve any of the other columns from that partition. > I can also retrieve the value column from any partition before September 11, 2003. > Any partition after that fails with the 0ra-600 error when the value column is selected. > > DBV at first gave errors along the lines of > > BV-00102: File I/O error on FILE > (/u9/oradata/NLCO/chanarch_nlc_active_data01.dbf) during end read op > eration (-1) > > The file is 2018 MB in size. But resizing the files downwards and back up again fixed > That problem. The files are allowed to autoextend with 2018 being the maximum size. > > This is not the same database which had the RAID problem. > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: MacGregor, Ian A. > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
