Exp Error exp-56 ORA-24324 and ora-7445 in alert When shared pool is very small

近期有个朋友咨询了个exp hang 和错误的问题，在这里记录一下。
环境: RHEL 6 + oracle rdbms 10.2.0.4.0 EE
exp 时提示如下（有时直接exp hang），无任何数据导出
exp error
==========================
...
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
 
该类错误通常都是由于其它数据库原因导致，先查看一下alert log. log 中有记录错误，下面是多次exp 日志记录。
 
alert log
==========================
Errors in file /mnt/oracle/admin/oradb/udump/oradb_ora_6293.trc:
ORA-07445: exception encountered: core dump [kgghstfel()+15] [SIGSEGV] [Address not mapped to object] [0x000000018] [] []
Fri Mar  7 11:11:49 2014
Errors in file /mnt/oracle/admin/oradb/udump/oradb_ora_7013.trc:
ORA-07445: exception encountered: core dump [kgghstfel()+15] [SIGSEGV] [Address not mapped to object] [0x000000018] [] []
Fri Mar  7 13:28:14 2014
Errors in file /mnt/oracle/admin/oradb/udump/oradb_ora_11223.trc:
ORA-00600: internal error code, arguments: [qmxi_badref], [], [], [], [], [], [], []
Fri Mar  7 13:28:16 2014
Errors in file /mnt/oracle/admin/oradb/udump/oradb_ora_11223.trc:
ORA-07445: exception encountered: core dump [kgghstfel()+15] [SIGSEGV] [Address not mapped to object] [0x000000018] [] []
Fri Mar  7 13:54:23 2014
Errors in file /mnt/oracle/admin/oradb/udump/oradb_ora_12064.trc:
ORA-07445: exception encountered: core dump [qmxiManifestVArray()+4330] [SIGSEGV] [Address not mapped to object] [0x10BECF0CA0] [] []
Fri Mar  7 14:00:06 2014
Errors in file /mnt/oracle/admin/oradb/bdump/oradb_j001_12256.trc:
ORA-12012: error on auto execute of job 8949
ORA-04031: unable to allocate ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","MERGE /*+ dynamic_sampling(S...","sql area","idndef*[]: qkexrPackName")
</pre>
 
TIP:
对于内部错误在MOS先 search一下，发现与1052052.1 很是相似,原因是有xdb 相关的对象引起的，即使dba_register和dba_objects 中的KU$_%的对象都是valid,还有ORA-00600 [qmxi_badref]的trace 文件中记录
*** MODULE NAME:(EXP.EXE) 2014-03-07 13:28:14.354
*** SERVICE NAME:(oradb) 2014-03-07 13:28:14.354
*** SESSION ID:(524.597) 2014-03-07 13:28:14.354
*** 2014-03-07 13:28:14.354
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qmxi_badref], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('XMLSCHEMA_T', '7')), 0 FROM SYS.KU$_XMLSCHEMA_VIEW KU$ WHERE KU$.OWNER_NAME=:SCHEMA1
----- PL/SQL Call Stack -----
object      line  object
handle    number  name0xbe8689b8      1052  package body SYS.DBMS_METADATA
那看一下最初ora-7445的error 中的trace是否符合？这些XDB会不会也是受害者呢？还有ora-7445 [qmxiManifestVArray] 的内存memory corruption when use XDB, 有一点ORA-4031这个错误很可疑，毕竟这只是一个2个开发人员用的库。下面是最被ora-7445的trace 文件：

trace file oradb_ora_6293.trc
============================
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 = /mnt/oracle/product/10.2.0/db_1
System name:	Linux
Node name:	ora-db
Release:	2.6.32-358.6.2.el6.x86_64
Version:	#1 SMP Thu May 16 20:59:36 UTC 2013
Machine:	x86_64
Instance name: oradb
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 6293, image: oracle@ora-db
*** ACTION NAME:() 2014-03-07 10:48:48.460
*** MODULE NAME:(EXP.EXE) 2014-03-07 10:48:48.460
*** SERVICE NAME:(oradb) 2014-03-07 10:48:48.460
*** SESSION ID:(521.117) 2014-03-07 10:48:48.460
*** 2014-03-07 10:48:48.460
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors.  This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
=====================================
Allocation Request Summary Informaton
=====================================
Current information setting:  04014fff
  SGA Heap Dump Interval=3600 seconds
  Dump Interval=300 seconds
  Last Dump Time=03/07/2014 10:48:47
  Dump Count=1
Allocation request for:       kgghteInit 
  Heap: 0xbe536eb0, size: 4120
******************************************************
HEAP DUMP heap name="sga heap"  desc=0x60000058
 extent sz=0x47c0 alt=216 het=32767 rec=9 flg=-126 opc=0
 parent=(nil) owner=(nil) nex=(nil) xsz=0x1
 ds for latch 1: 0x60034b70                                 
 reserved granule count 0 (granule size 16777216)
******************************************************
HEAP DUMP heap name="Typecheck"  desc=0xbe536eb0
 extent sz=0xfc0 alt=32767 het=32767 rec=0 flg=2 opc=0
 parent=0xbeaa9528 owner=(nil) nex=(nil) xsz=0x1000000
 Subheap has 2368 bytes of memory allocated
 ... had truncated
 ===========================
Current Instatiation Object
===========================
-------------------------------------
INSTANTIATION OBJECT: object=0x7f3b618c6ce8
type=""[240] lock=0x2049577d6d11d7de handle=(nil) body=0xbe537000 level=0
flags=NST/NBD/[618c] executions=0
 sqltxt(0xbeaaa0d0)=select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
  hash=6d11d7de2049577d933d2385337fc737
  parent=0xbeaa9dc0 maxchild=02 plk=0xbecb3d18 ppn=n
cursor instantiation=0x7f3b618c6ce8 used=1394160527
 child#1(0xbfb126e0) pcs=0xbeaa9430
  clk=0xbe6def50 ci=0xbe4b5888 pn=0xbecd2210 ctx=0xbe537000
 kgsccflg=1 llk[0x7f3b618c6cf0,0x7f3b618c6cf0] idx=38
 xscflg=20028 fl2=0 fl3=22000 fl4=0
 Frames pfr (nil) siz=0 efr (nil) siz=0
----- Call Stack Trace -----
ksm_4031_dump()+1399 > ksedst >ksmasg >ksm_4031_dump >kghnospc >kghalp >kghalf >kghssgai >kghalp > kgghteInit
确认下目前的SGA 分配大小
select * from v$sgainfo;
NAME                                                              BYTES RES
------------------------------------------------------------ ---------- ---
Fixed SGA Size                                                  2084456 No
Redo Buffers                                                   14692352 No
Buffer Cache Size                                              50331648 Yes
Shared Pool Size                                              100663296 Yes
Large Pool Size                                                       0 Yes
Java Pool Size                                                 33554432 Yes
Streams Pool Size                                                     0 Yes
Granule Size                                                   16777216 No
Maximum SGA Size                                             1677721600 No
Startup overhead in Shared Pool                                83886080 No
Free SGA Memory Available                                    1476395008
set heading on
set feedback on
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;
CLASS                     NUM                  SIZ AVG SIZE
-------- -------------------- -------------------- ------------
freeabl                 5859            12346496        2.06k
perm                     483            73932616      149.48k
recr                    4688            8415104        1.75k
free  		        1788 		934000		.51k
...

tip:
shared pool 确实有点小，在9i r2版本时就已经推荐shared pool 要大于150M了，本实例没用使用ASMM，因为是4G的阿里云服务器，开始内存也设的太保守了些，后来我建议把shared_pool_size 加达到600M, exp 可以正常导出，且alert日志 没有再出现之前的ORA-XX 错误。另外推荐有兴趣的可以看一下tanelpoder article about ora-4031