Corruption: Bad Number data? What is it? But my dbverify didn’t say anything about it.

Statement/session crashing with Few ora-07445 and a ora-600

(Publishing the statement is irrespective hence skipping)

ORA 600 [rworupo.2]
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382]
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2AD2FD908088], [], [], [], [], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smboQbNxt()+103] [SIGSEGV] [ADDR:0x0] [PC:0x8F22023] [SI_KERNEL(general_protection)] []

Well our good luck chuck DBA has access to Metalink and found that similar to following the stack in our trace files too.

... intel_new_memcpy <- qersoSORowP <- qervwRowProcedure <- qeruaRowProcedure <- qerixFetchUniqueIndex <- qerjotRowProc <- qertbFetchByRowID <- qerjotRowProc <- qervwRowProcedure <- qersoFetch <- qervwFetch <- qerjotFetch <- qerjotFetch <- rwsfcd <- qeruaFetch <- qervwFetch <- qersoProcessULS <- qersoFetch ...

and this note:-

Query causes various ORA-07445 and ORA-600 errors [ID 1385197.1]

Apparently the db is upgraded to 11g from 10g and symptoms are matching, now we are on warpath to remove those corruptions.

But what is bad number data? How does it occur? According to my permissible knowledge

1) Numeric columns some how got chars into it?

2) Application interface accepts anything varchar into number column and inserts the same

for example: ‘1 ’, note the space after the digit.?

3) Conversion of columns :(?

4) etc reasons?

and how to resolve it?

1) First identify the rows that marked as corrupted

a) Identify the rows that are not equal to number values

select rowid from schema.table where rowid in (select rowid from schema.table where (numcolumn!=numcolumn/1);

this will list you all the rowid’s for that number column not equal to divisible/1 itself), this another way of checking your number column data.

alternatively you can download script from

b) Download the script (procedure) and execute in your database.

NOTE:428526.1 - Baddata Script To Check Database For Corrupt column data

c) Update the column with same value of the row that is marked as corrupted, but with multiply into one. (Exactly, you heard it correct, 1*1=(always)1, 2*1=2(always)

Recommendation:- Take a backup before you proceed

update schema.table set NUMCOLUMN = NUMCOLUMN * 1 where rowid in (select rowid from schema.table where ("NUMCOLUMN"!="NUMCOLUMN"/1))

Match the count of rows appeared in step a and then

commit;

d) Rerun the statement that is failing earlier, the above should fix the bad number data as of now. Remember this is something about data (type) corruption, not logical nor the physical corruption

-Thanks

Geek DBA