Recently one of my database shows ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x46F1D9E] [PC:0x46F1D9E] [Illegal operand] while running one report.
This is how i resolved the issue.
I raised the SR and Oracle Support told me that the failing function is associated with ORA-07445 is "evadcd" , which shows that the error comes from decode.
Identify the failing statement: Trace file generated at the time of ORA-07445 will contain the SQL caused the error. Identify all the tables used in the SQL statement and analyze the tables to ensure that there is no corruption on the tables using analyze table <owner>.<table_name> validate structure cascade;
None of the tables reported any corruption, all tables analyzed successfully, So the error must be related to invalid data in some of the colums.
So, i have to identify the record(s) which causing the error. For that, to narrow down, i have added additional condition to the query the records for a prticular time period in my case.
For Example:
In my query on of the condition used was
where (txn_date) BETWEEN TO_DATE ('17-APR-2013 07:00:00',
'DD-MON-YYYY HH24:MI:SS')
AND TO_DATE ('18-APR-2013 08:00:00',
'DD-MON-YYYY HH24:MI:SS')
In order to narrow down to the erroring record, i have tested the query by narrowing down to smaller date period to locate the exact row which throws the error. Once i have identified the row which gives the error, look for the colums used in the query and look for invalid value and correct it.
This is how i resolved the issue.
I raised the SR and Oracle Support told me that the failing function is associated with ORA-07445 is "evadcd" , which shows that the error comes from decode.
Identify the failing statement: Trace file generated at the time of ORA-07445 will contain the SQL caused the error. Identify all the tables used in the SQL statement and analyze the tables to ensure that there is no corruption on the tables using analyze table <owner>.<table_name> validate structure cascade;
None of the tables reported any corruption, all tables analyzed successfully, So the error must be related to invalid data in some of the colums.
So, i have to identify the record(s) which causing the error. For that, to narrow down, i have added additional condition to the query the records for a prticular time period in my case.
For Example:
In my query on of the condition used was
where (txn_date) BETWEEN TO_DATE ('17-APR-2013 07:00:00',
'DD-MON-YYYY HH24:MI:SS')
AND TO_DATE ('18-APR-2013 08:00:00',
'DD-MON-YYYY HH24:MI:SS')
In order to narrow down to the erroring record, i have tested the query by narrowing down to smaller date period to locate the exact row which throws the error. Once i have identified the row which gives the error, look for the colums used in the query and look for invalid value and correct it.
