【EXPDP】使用EXPDP备份数据时ORA-39125、ORA-04031错误原因分析与排查

如果数据库服务器的shared pool过小或碎片过多会导致EXPDP工具无法完成备份，通常伴随如下错误信息：
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE:"SEC"."T3"]
ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...","sql area","idndef*[]: qkexrPackName")

模拟再现一下这个问题，并给出处理方法，供参考。

1.问题再现
1）确认操作系统信息
ora10g@secDB /expdp$ uname -a
Linux secDB 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux

2）确认数据库版本
sys@ora10g> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

3）使用EXPDP工具备份sec用户数据
ora10g@secDB /expdp$ rm -f sec.dmp sec.log
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 9:45:57

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_02":  sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE:"SEC"."T3"]
ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...","sql area","idndef*[]: qkexrPackName")

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6234

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x664d6bc8     14916  package body SYS.KUPW$WORKER
0x664d6bc8      6293  package body SYS.KUPW$WORKER
0x664d6bc8      2339  package body SYS.KUPW$WORKER
0x664d6bc8      6854  package body SYS.KUPW$WORKER
0x664d6bc8      1259  package body SYS.KUPW$WORKER
0x6661f1a8         2  anonymous block

Job "SEC"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 09:46:04

2.问题原因
参考MOS的OERR: ORA 4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" [ID 19837.1]
Error:  ORA 4031
Text:   unable to allocate %s bytes of shared memory (%s,%s,%s)
-------------------------------------------------------------------------------
Cause:  More shared memory is needed than was allocated in the shared
        pool.
Action: Either use the dbms_shared_pool package to pin large packages,
        reduce your use of shared memory, or increase the amount of
        available shared memory by increasing the value of the
        init.ora parameter "shared_pool_size".

原因是：shared pool过小或因为碎片太多导致没有大段的内容可用
处理方法：增加shared pool。

3.问题处理
1）调整一下SGA的配置
sys@ora10g> alter system set sga_max_size=300m scope=spfile;

System altered.

sys@ora10g> alter system set sga_target=300m scope=spfile;

System altered.

2）重启数据库
sys@ora10g> startup force;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2072448 bytes
Variable Size             113246336 bytes
Database Buffers          192937984 bytes
Redo Buffers                6316032 bytes
Database mounted.
Database opened.

3）再次完成备份，成功。
ora10g@secDB /expdp$ rm -f sec*
ora10g@secDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log

Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 9:56:14

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_SCHEMA_03":  sec/******** directory=dump_dir dumpfile=sec.dmp logfile=sec.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 704 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "SEC"."SYS_EXPORT_SCHEMA_01"                117.4 KB    1055 rows
. . exported "SEC"."SYS_EXPORT_SCHEMA_02"                113.9 KB    1055 rows
. . exported "SEC"."T1"                                  4.914 KB       1 rows
. . exported "SEC"."T2"                                  4.914 KB       1 rows
. . exported "SEC"."T3"                                  4.914 KB       1 rows
Master table "SEC"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_SCHEMA_03 is:
  /expdp/sec.dmp
Job "SEC"."SYS_EXPORT_SCHEMA_03" successfully completed at 09:56:23

4.小结
ORA-04031错误多数是由shared pool过小或碎片太多引起的。
处理方法有两种：第一种就是适当的增大shared pool的大小，另外一种方法就是清理shared pool的碎片。

Good luck.

secooler
10.03.09

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-629040/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-629040/