如果資料庫伺服器的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 --

【EXPDP】使用EXPDP備份資料時ORA-39125、ORA-04031錯誤原因分析與排查

相關文章