一错误提示
> EXP: ORACLE error encountered
> ORA: unable to allocate bytes of shared memory
(shared poolBEGIN:EXEC_STR := SYSDBMSPL/SQL MPCODEBAMIMA: Bam Buffe
二错误原因
共享内存太小存在一定碎片没有有效的利用保留区造成无法分配合适的共享区
三解决步骤
查看当前环境
SQL> show sga
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
show parameter shared_pool
NAME TYPEVALUE
shared_pool_reserved_sizestring
shared_pool_size string
SQL> select sum(free_space) from v$shared_pool_reserved;
SUM(FREE_SPACE)
我们可以看到没有合理利用保留区
SQL> SELECT SUM(RELOADS)/SUM(PINS) FROM V$LIBRARYCACHE;
SUM(RELOADS)/SUM(PINS)
不算太严重
SQL> SELECT round((BValue/AValue)*) hardpaseperc
FROM V$SYSSTAT A
V$SYSSTAT B
WHERE AStatistic# =
AND BStatistic# =
AND ROWNUM = ;
hardpaseperc
查看保留区使用情况
SQL> SELECT FREE_SPACE
FREE_COUNT
REQUEST_FAILURES
REQUEST_MISSES
LAST_FAILURE_SIZE
FROM V$SHARED_POOL_RESERVED;
FREE_SPACE FREE_COUNT REQUEST_FAILURES REQUEST_MISSES LAST_FAILURE_SIZE
最近一次申请共享区失败时该对象需要的共享区大小
select name from v$db_object_cache where sharable_mem = ;
name
dbms_lob
dbms_lob正是exp时申请保留区的对象
查看导致换页的应用
SQL> select * from x$ksmlru where ksmlrsiz>;
ADDR INDXINST_ID KSMLRCOMKSMLRSIZKSMLRNUM KSMLRHON KSMLROHV KSMLRSES
A BAMIMA: Bam Buffer DBMS_DDL DEBC
ACC BAMIMA: Bam Buffer DBMS_SYS_SQL D
B BAMIMA: Bam Buffer STANDARD DE
B BAMIMA: Bam Buffer DBMS_LOB DAC
B BAMIMA: Bam Buffer DBMS_UTILITY CFC
BDC BAMIMA: Bam Buffer begin : := dbms_lobgetLeng CFFCC
C state objects
C library cache EXUVEW CC
CA state objects CB
CEC state objects D
分析各共享池的使用情况
SQL> select KSPPINMKSPPSTVL
from x$ksppi
x$ksppcv
where x$ksppiindx = x$ksppcvindx
and KSPPINM = _shared_pool_reserved_min_alloc;
KSPPINM KSPPSTVL
_shared_pool_reserved_min_alloc (门值)
我们看到INDX=DBMS_LOB造成换页(就是做exp涉及到lob对象处理造成的换页情况)换出
最近未使用的内存但是换出内存并合并碎片后在共享区仍然没有合适区来存放数据说明共享
区小和碎片过多然后根据_shared_pool_reserved_min_alloc的门值来申请保留区而门值为
所以不符合申请保留区的条件造成错误我们前面看到保留区全部为空闲状态所以我们可以
减低门值使更多申请共享内存比小的的对象能申请到保留区而不造成错误
解决办法
)增大shared_pool （在不DOWN机的情况下不合适）
)打patch （在不DOWN机的情况下不合适）
)减小门值 （在不DOWN机的情况下不合适）
因为LAST_FAILURE_SIZE<_shared_pool_reserved_min_alloc所以表明没有有效的使用保留区
SQL> alter system set _shared_pool_reserved_min_alloc = ;
alter system set _shared_pool_reserved_min_alloc=
*
ERROR at line :
ORA: specified initialization parameter cannot be modified
i的使用方法alter system set _shared_pool_reserved_min_alloc= scope=spfile;
)使用alter system flush shared_pool; (不能根本性的解决问题)
)使用dbms_shared_poolkeep
由于数据库不能DOWN机所以只能选择)和)
运行dbmspoolsql
SQL> @/home/oracle/products//rdbms/admin/dbmspoolsql
找出需要keep到共享内存的对象
SQL> select aOWNER
aname
asharable_mem
akept
aEXECUTIONS
baddress
bhash_value
from v$db_object_cache a
v$sqlarea b
where akept = NO and
(( aEXECUTIONS >
and aSHARABLE_MEM > )
oraEXECUTIONS > )
and SUBSTR(bsql_text) = SUBSTR(aname);
OWNERNAMESHARABLE_MEM KEP EXECUTIONS ADDRESSHASH_VALUE
—
SELECT COUNT(OBJECT_ID) NO BF
FROM ALL_OBJECTS
WHERE OBJECT_NAME = :b
AND OWNER = :b
STANDARD NO
DBMS_LOB NO
DBMS_LOB NO
DBMS_LOB NO
DBMS_PICKLERNO
DBMS_PICKLER NO
SQL> execute dbms_shared_poolkeep(STANDARD);
SQL> execute dbms_shared_poolkeep(BFC);
SQL> execute dbms_shared_poolkeep(DBMS_LOB);
SQL> execute dbms_shared_poolkeep(DBMS_PICKLER);
SQL> select OWNER name sharable_memkeptEXECUTIONS from v$db_object_cache where kept = YES ORDER BY sharable_mem;
SQL> alter system flush shared_pool;
System altered
SQL> SELECT POOLBYTES FROM V$SGASTAT WHERE NAME =free memory;
POOL BYTES
shared pool
large pool
java pool
[oracle@alisolution oracle]$ sh /home/oracle/admin/dbexpsh
[oracle@alisolution oracle]$ grep ORA /tmp/exptmp
未发现错误导出数据成功
四建议
由于以上解决的方法是在不能DOWN机的情况下所以没能动态修改初始化参数
但问题的本质是共享区内存过小需要增加shared pool使用绑定变量才能根本
的解决问题所以需要在适当的时候留出DOWN机时间对内存进行合理的配置
