[07/11] Ora-7445 [kssadf_stage:1003[kss.c]] From Create Table As Select ...

[JonathanRead2000]

Hi,

One of our live london trading database instance ,crashed with the following message ..and there was a huge loss to the organization .
[07/11] ORA-7445 [KSSADF_STAGE:1003[KSS.C]] FROM CREATE TABLE
ORA-07445: exception encountered: core dump [kssadf_stage:1003[kss.c]] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []AS SELECT ... The database is in Veriatas three node cluster ,one in London, and failover nodes in Boson .Can anyone help on this ?

Cheers ,

J

[Vinod Sadanandan]

Hi,

Most of the scenario's ORA-7445 will be due to process failures ,looks like this bug is not reported in Oracle ,well ..if this is a process failure ORA-7445 this can be ignored else if the instance aborted this should be considered with high priority ,as a temp fix toggle the cluster and bring the database in the failover node in boston to avoid outage .please paste the exact alertlog error and trace file with intrim patch details .


Thanks & Regards,
Vinod Sadanandan
Oracle DBA

[amitpatel66]

Hi,

One of our live london trading database instance ,crashed with the following message ..and there was a huge loss to the organization .
[07/11] ORA-7445 [KSSADF_STAGE:1003[KSS.C]] FROM CREATE TABLE
ORA-07445: exception encountered: core dump [kssadf_stage:1003[kss.c]] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []AS SELECT ... The database is in Veriatas three node cluster ,one in London, and failover nodes in Boson .Can anyone help on this ?

Cheers ,

J

ORA-07445: exception encountered: core dump [string] [string] [string] [string] [string] [string]

Cause: An OS exception occurred which should result in the creation of a core file. This is an internal error.

Symptoms

Using a WHERE clause on queries against the V$SQL_PLAN table causes sessions to disconnect.

SQL> select * from v$sql_plan where address='000000038DAEB370';
select * from v$sql_plan where address='000000038DAEB370'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


A glance in the alert.log shows an ORA-07445 error and a trace file left in user_dump_dest. Here is an extract from the trace file:

*** 2003-09-16 11:35:57.019
*** SESSION ID:(25.3760) 2003-09-16 11:35:57.010
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x1f8, PC: [0x101648efc, 0000000101648EFC]
*** 2003-09-16 11:35:57.024
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [0000000101648EFC] [SIGSEGV] [Address not mapped to object] [0x0000001F8] [] []
Current SQL statement for this session:
select * from v$sql_plan where address='000000038DAEB370'

[amitpatel66]

Workaround

You can workaround this problem by creating a temporary version of the V$SQL_PLAN table and excluding the FILTER_PREDICATES column which is a VARCHAR2(4000). Cut and paste the code provided here and use the temporary table SQL_PLAN_FIX in place of V$SQL_PLAN.

create global temporary table SQL_PLAN_FIX
on commit preserve rows
as
select
kqlfxpl_phad ADDRESS,
kqlfxpl_hash HASH_VALUE,
kqlfxpl_chno CHILD_NUMBER,
substr(kqlfxpl_oper, 1, 30) OPERATION,
substr(kqlfxpl_oopt, 1, 30) OPTIONS,
substr(kqlfxpl_tqid, 1, 10) OBJECT_NODE,
to_number(decode(kqlfxpl_objn, 0 , NULL, kqlfxpl_objn)) OBJECT#,
case when kqlfxpl_objname is not null
then 'SYS'
else u.name
end OBJECT_OWNER,
nvl(p.kqlfxpl_objname, o.name) OBJECT_NAME,
substr(kqlfxpl_opti, 1, 20) OPTIMIZER,
kqlfxpl_opid ID,
to_number(decode(kqlfxpl_opid, 0, NULL, kqlfxpl_paid)) PARENT_ID,
kqlfxpl_depth DEPTH,
kqlfxpl_pos POSITION,
kqlfxpl_scols SEARCH_COLUMNS,
to_number(decode(kqlfxpl_cost, 0, NULL, kqlfxpl_cost)) COST,
to_number(decode(kqlfxpl_card, 0, NULL, kqlfxpl_card)) CARDINALITY,
to_number(decode(kqlfxpl_size, 0, NULL, kqlfxpl_size)) BYTES,
substr(kqlfxpl_otag, 1, 35) OTHER_TAG,
substr(kqlfxpl_psta, 1, 5) PARTITION_START,
substr(kqlfxpl_psto, 1, 5) PARTITION_STOP,
to_number(decode(kqlfxpl_pnid, 0, NULL, kqlfxpl_pnid)) PARTITION_ID,
kqlfxpl_other OTHER,
substr(kqlfxpl_dist, 1, 20) DISTRIBUTION,
to_number(decode(kqlfxpl_cpuc, 0, NULL, kqlfxpl_cpuc)) CPU_COST,
to_number(decode(kqlfxpl_ioct, 0, NULL, kqlfxpl_ioct)) IO_COST,
to_number(decode(kqlfxpl_temp, 0, NULL, kqlfxpl_temp)) TEMP_SPACE,
kqlfxpl_keys ACCESS_PREDICATES,
'FILTER PREDICATES UNAVAILABLE DUE TO BUG' FILTER_PREDICATES
from
x$kqlfxpl p, obj$ o , user$ u
where
p.kqlfxpl_hadd != p.kqlfxpl_phad
and p.kqlfxpl_objn = o.obj#(+)
and o.owner# = u.user#(+);
 

[JonathanRead2000]

Hi,

Thanks for the effort ..ORA 744 SIGSEGV is a deffrent scnario but mine is kssadf_stage:1003[kss.c]] .I have many developer guys with me but I won't be able to change the code on the FLY .

Vinod - Much appreciated ,would like to keep in touch with you .

Cheers

J