ORA-07445:[SIGFPE] [Integer divide by zero]内部错误一例

一套SUNOS 5.10上的单节点10.2.0.3系统出现了ORA-07445: exception encountered: core dump [SIGFPE] [Integer divide by zero] [42788866] [] [] []内部错误,具体trace日志如下:

mon_ora_17633.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/oracle/product/10.2.0
System name: SunOS
Node name: monitor-a
Release: 5.10
Version: Generic_139556-08
Machine: i86pc

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [SIGFPE] [Integer divide by zero] [42788866] [] [] []
Current SQL statement for this session:
select req_time into :b0 from t_FIX_TranSerial where
((((tran_bank=:b1 and tran_type=:b2) and term_no=:b3)
and trace_no=:b4) and local_time='0')
----- Call Stack Trace -----

sigsetjmp <- call_user_handler
<- sigacthandler <- kpopfr <- kposdi <- kpopsdi <- opiefn0
<- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino
<- opiodr <- opidrv <- sou2o <- opimai_real <- main
<- 0000000000E54FE7

PROCESS STATE
-------------
O/S info: user: monitor, term: pts/5, ospid: 17621, machine: monitor-a
program: CTPDATA@monitor-a (TNS V1-V3)
application name: CTPDATA@monitor-a (TNS V1-V3), hash value=0
last wait for 'SQL*Net message to client' blocking sess=0x0 seq=6213 wait_time=1 seconds since wait started=0
driver id=62657100, #bytes=1, =0

通过在MOS上查询以上ORA-07445错误的arguement可以发现Note <ORA-7445 [KPOPFR] [SIGFPE] [INTEGER DIVIDE BY ZERO] When Repeatedly Executing a Query (Doc ID 421203.1)> :

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms

1. Repeatedly executing a query can lead to the following error: 

ORA-7445 [kpopfr] [SIGFPE] [INTEGER DIVIDE BY ZERO]

2. The call stack from the ORA-07445 trace file should contain the following functions:
kposdi  kpopsdi

The error is caused by BUG 5753629.
Abstract: QUERY FAILS BY ORA-7445 [KPOPFR]
Repeatedly executing a query can lead to an ORA-7445[kpopfr] error.

Solution
To implement the solution, do one of the following: 

1. Upgrade to 11.1 or 10.2.0.4, when available.
At the time of writing the article these version were not yet available. (July 2007).
2. Apply one-off Patch 5753629 from MetaLink, if available for your platform and version.

There is no known workaround available for this bug.

References

BUG:5753629 - QUERY FAILS BY ORA-7445 [KPOPFR].

Hdr: 5753629 10.2.0.2 RDBMS 10.2.0.2 PRG INTERFACE PRODID-5 PORTID-23 ORA-7445
Abstract: QUERY FAILS BY ORA-7445 [KPOPFR].

*** 01/09/07 06:12 pm ***
TAR:
----

PROBLEM:
--------
When executing query again and again from one session, query fails
by ORA-7445[kpopfr].

  ====================================================================
  Sat Dec 30 00:22:39 2006
  Errors in file /var/log/oracle/trace/felica2_ora_6156.trc:
  ORA-7445: exception encountered: core dump [kpopfr()+536] [SIGFPE]
  [Integer divide by zero] [0x1023BCE18] [] []
  ====================================================================

DIAGNOSTIC ANALYSIS:
--------------------
From disassemble, %o3 is devided by %o0 and %o0 seems to be 0x0.

  0x1023f43d0 :       umul  %g5, %g4, %o0
  0x1023f43d4 :       mov  %g0, %y
  0x1023f43d8 :       udiv  %o3, %o0, %o3

%o0 is calcurated by %g5 X %g4 at kpopfr+528.

From our trace file, this value(%g5) is 0x100000.

  ub4 kponc_p [FFFFFFFF7B22AAAC, FFFFFFFF7B22AAB0) = 00100000

And %g4 seems to be 0x1000 from below trace file output.
 ========== FRAME [6] (kpopsdi()+148 -> kposdi()) ==========
  %l0 0000000100302B00 %l1 0000000000000002 %l2 FFFFFFFF7B263CA8
  %l3 00000003C36AFB80 %l4 0000000105D7CE20 %l5 0000000000000001
  %l6 0000000000000007 %l7 0000000105D7A920 %i0 0000000000000000
  %i1 FFFFFFFF7FFFB9EC %i2 0000000000001000 %i3 0000000105E03220
                       ~~~~~~~~~~~~~~~~~~~~ <--(*) here
  %i4 0000000000800000 %i5 0000000000105C00 %fp FFFFFFFF7FFFB241 

If %g4=0x1000 and %g5=0x100000, %g4 X %g5 = 0x100000000.
0x100000000 is 0x0 as ub4, and this may bring 0 divide and ORA-7445.

I can reproduce the similar problem in my house, so I'll upload testcase.
Problem reproduce at the following case.

 * sum of all column size is 1048576(0x100000)
 * run query again and again from one session (about 4096(0x1000) times)

From this results, above guess seems to be correct.

WORKAROUND:
-----------
n/a

RELATED BUGS:
-------------
n/a

REPRODUCIBILITY:
----------------
I have confirmed that this problem reproduces at the below env.

 * Linux x86 32bit, 10.2.0.3 : ORA-7445[kpopfr()+300]
 * Linux x86 64bit, 10.2.0.2 : ORA-7445[kpopfr()+339]
 * Solaris 64bit, 10.2.0.2   : ORA-7445[kpopfr()+536]
 * HP-UX Itanium, 10.2.0.2   : ORA-7445[_div32U()+34]

TEST CASE:
----------
At first, creating table like follows.

conn scott/tiger
drop table test;
create table test
( c000 char(2000),
  c001 char(2000),
    ... 
  c523 char(2000),
  c524 char(576));

  --> sum of all column size is 1048576(0x100000).

Run next shell script.

  while [ 1 ]
  do
  echo "set feedback off"
  echo "select * from test where c001 = 'A';"
  done | sqlplus -s scott/tiger

It takes 3-10 minutes to reproduce the problem.
Required time for reproducing depends on hardware spec.

STACK TRACE:
------------
 ksedmp ssexhd sighndlr call_user_handler kposdi kpopsdi kpoal8
 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real
 main start

具体向Oracle GCS提交SR以后确认为 BUG 5753629. Oracle GCS给出了2种解决方案:
1.升级到10.2.0.4或更高版本
2.应用Apply one-off Patch 5753629

ora-00600[kkocxj:pjpCtx]内部错误一例

一套HP-UX上的10.2.0.4系统在运行某条 select查询语句时出现ORA-00600[kkocxj:pjpCtx]内部错误,TRACE文件信息如下:


FILE VERSION
------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/10.2
System name:    HP-UX
Node name:      crmdb1
Release:        B.11.31
Version:        U
Machine:        ia64
Instance name: cbssnm
Redo thread mounted by this instance: 1

TRACE FILE
---------------
Filename = cbssnm_ora_29061.trc

*** ACTION NAME:(SQL 窗口 - 新建) 2010-07-02 15:59:46.238
*** MODULE NAME:(PL/SQL Developer) 2010-07-02 15:59:46.238
*** SERVICE NAME:(SYS$USERS) 2010-07-02 15:59:46.238
*** SESSION ID:(770.4341) 2010-07-02 15:59:46.237
*** 2010-07-02 15:59:46.237
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkocxj : pjpCtx], [], [], [], [], [], [], []
Current SQL statement for this session:
select p.access_number, aa.name
 from crm.product p,
      (select aa.prod_id, os.name, os.staff_number
         from (select *
                 from (select prod_id,
                              party_id,
                              row_number() over(partition by prod_id order by start_dt desc) num
                         from crm.party_2_prod
                        where end_dt > sysdate
                          and party_product_rela_role_cd = 3)
                where num = 1) aa,
              crm.our_staff os
        where aa.party_id = os.staff_id) aa
where p.prod_id = aa.prod_id(+)
  and p.access_number = '15335581126'
----- Call Stack Trace -----
    ksedst <- ksedmp <- ksfdmp <- kgerinv <- kgeasnmierr        
<- $cold_kkocxj <- kkoiqb <- kkooqb <- kkoqbc <- apakkoqb         
<- apaqbdDescendents <- apaqbd <- kkqctCostTransfQB <- kkqctdrvJP 
<- kkqjpdttr          <- kkqctdrvTD <- kkqjpddrv <- kkqdrv <- kkqctdrvIT 
<- apadrv           <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild 
<- kxsGetRuntimeLock            <- kksfbc <- kkspsc0 <- kksParseCursor 
<- opiosq0 <- kpooprx             <- kpoal8 <- opiodr <- ttcpip <- opitsk 
<- opiino              <- opiodr <- opidrv <- sou2o <- opimai_real <- main               
<- main_opd_entry

根据错误代码和stack trace可以在metalink上匹配到如下Bug:

Bug:7014646
Abstract: ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KKOCXJ : PJPCTX], [], [], [], [], []
Affects:
    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected	Versions < 11.2
    Versions confirmed as being affected	
        * 10.2.0.4
        * 11.1.0.6 
    Platforms affected	Generic (all / most platforms affected)
Fixed:
    This issue is fixed in	
        * 10.2.0.4 Patch 7 on Windows Platforms
        * 10.2.0.5 (Server Patch Set)
        * 11.1.0.7 (Server Patch Set)
        * 11.2 (Future Release) 
Symptoms:
    * Internal Error May Occur (ORA-600)
    * ORA-600 [kkocxj : pjpCtx] 
Related To:
    * Optimizer
    * _OPTIMIZER_PUSH_PRED_COST_BASED 
Description
    A complex query can fail during parse with 
    ORA-600 [kkocxj : pjpCtx]
    Workaround
     Set  "_optimizer_push_pred_cost_based"=false

该bug可以通过实施one off Patch 7014646修复,也可以尝试通过修改隐式参数_optimizer_push_pred_cost_based禁用基于成本的谓词前置特性(WORKAROUND: disable cost based push predicate)来规避该[KKOCXJ:PJPCTX]内部错误发生,具体的修改方法:

SQL> conn / as sysdba
SQL> alter system set "_optimizer_push_pred_cost_based"=false;
SQL> exit
/* 设置该隐式参数无需重启实例 */

Oracle GCS更推荐通过应用补丁7014646的方法来解决问题,而修改以上隐式参数则不一定百分之百能解决问题。

single quote needed in expdp query?

如果在使用数据泵时不采用parfile参数文件的话,query参数指定的查询条件是需要使用单引号括起来的,而当使用parfile时则不需要加上单引号,加上后反而会出现LPX-314: an internal failure occurred错误:

[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:"where t1<2000"

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:32:33

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:where t1<2000
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-00936: missing expression
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:32:36

/* 不采用参数文件形式,没有加单引号的情况下出现ORA-00936: missing expression错误*/

[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:33:39

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:33:43

/* 不采用参数文件,在query参数中加上单引号则导出成功 */

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:'"where t1<2000"'
directory=dump

[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp

[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par 

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:35:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:35:11

/* 采用parfile选项,参数文件中query参数加入单引号出现LPX-314: an internal failure occurred错误,导出失败*/

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:"where t1<2000"
directory=dump
[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp
[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par           

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:36:27

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:36:30

/* 采用parfile,参数文件中query未加入单引号,导出成功 */

1. QUERY in Parameter file.

Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

* from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and
* from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more.

File: expdp_q.par
—————–
DIRECTORY = my_dir
DUMPFILE = exp_query.dmp
LOGFILE = exp_query.log
SCHEMAS = hr, scott
INCLUDE = TABLE:”IN (‘EMP’, ‘DEPARTMENTS’)”
QUERY = scott.emp:”WHERE job = ‘ANALYST’ OR sal >= 3000″
# place following 3 lines on one single line:
QUERY = hr.departments:”WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = ‘ANALYST’ OR e.salary >= 3000)”

— Run Export DataPump job:

%expdp system/manager parfile=expdp_q.par

Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema.
2. QUERY on Command line.

The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

* table scott.dept; and
* from table scott.emp all employees whose name starts with an ‘A’

— Example Windows platforms:
— Note that the double quote character needs to be ‘escaped’
— Place following statement on one single line:

D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp
LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\”WHERE ename LIKE ‘A%’\”

— Example Unix platforms:
— Note that all special characters need to be ‘escaped’

% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\”WHERE ename LIKE \’A\%\’\”

— Example VMS platform:
— Using three double-quote characters

$ expdp scott/tiger DIRECTORY=my_dir –
DUMPFILE=exp_cmd.dmp LOGFILE=exp_cmd.log TABLES=emp,dept –
QUERY=emp:”””WHERE ename LIKE ‘A%'”””

Note that with the original export client two jobs were required:
— Example Windows platforms:
— Place following statement on one single line:

D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp
QUERY=\”WHERE ename LIKE ‘A%’\”

D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

— Example Unix platforms:

> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \
QUERY=\”WHERE ename LIKE \’A\%\’\”

> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

— Example VMS platform:

$ exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp –
QUERY=”””WHERE ename LIKE ‘A%'”””

$ exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

Note that with original export client in Oracle8i on VMS, the syntax was different (also note the extra space that is needed between two single quotes):
… QUERY=”‘WHERE ename LIKE \’A%\’ ‘”
That is: [double_quote][single_quote]WHERE ename LIKE [backslash][single_quote]A%[backslash][single_quote][space][single_quote][double_quote]

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569