操作环境:
1. OS : CentOS release 6.5 (Final)
DB: 11g(11.2.0.1.0)
2. 异常现象: 对Oracle 数据库3个用户(test1,test2,test3), 做expdp导出,sql如下.
2.1 expdp system/systemtest directory=dump_dir schemas=test1,test2,test3 dumpfile=expdp_testusers_`date +%F`.dmp logfile=expdp_testusers_`date +%F`.log
2.2 如上expdp备份后,报错如下.
Export: Release 11.2.0.1.0 - Production on Thu Jan 15 11:53:41 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** directory=dump_dir schemas=test1,test2,test3 dumpfile=expdp_testusers_2015-01-15.dmp logfile=expdp_testusers_2015-01-15.log EXCLUDE=STATISTICS
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 440.9 MB
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [COMMENT]
ORA-01690: sort area size too small
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
0x1e5f262a0 19028 package body SYS.KUPW$WORKER
0x1e5f262a0 8191 package body SYS.KUPW$WORKER
0x1e5f262a0 2814 package body SYS.KUPW$WORKER
0x1e5f262a0 8846 package body SYS.KUPW$WORKER
0x1e5f262a0 1651 package body SYS.KUPW$WORKER
0x1e56ce588 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 11:54:26
--------------------------------------------------------------------------------------------------------------------
2.3 在meta_link上查询报错, metalink给出解决方法.
2.3.1升级DB版本由11.2.0.1.0升级到更高级的版本,如12.1.0.2.0,在网上查11G版本这个是BUG.
2.3.2 对DB打补丁.
2.3.3 增大PGA_AGGREAGTE_TARGET大小.
3 解决方法.
按照metalink介绍, 增大PGA_AGGREGATE_TARGET为1000M(1G),设置PGA时,要看DB总内存大小(MEMORY_TARGET),不要PGA设置太大,导致SGA减少.
3.1 expdp再一次导出故障依旧, 查询参数 workarea_size_policy值为MANUAL.
SQL> show parameter workarea;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string MANUAL
SQL>
3.2 设置workarea_size_policy值为AUTO,后expdp备份OK.
SQL> ALTER SYSTEM SET WORKAREA_SIZE_POLICY = AUTO;
System altered.
SQL> show parameter workarea;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AU
3.3 expdp system/systemtest directory=dump_dir schemas=test1,test2,test3 dumpfile=expdp_testusers_`date +%F`.dmp logfile=expdp_testusers_`date +%F`.log
susers_2015-01-15.dmp logfile=expdp_xysusers_2015-01-15.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 440.9 MB
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
----------导出之间的很多表在此省略-------------------------------
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
/data/backup/expdp/expdp_testusers_2015-01-15.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at 14:03:46
1. OS : CentOS release 6.5 (Final)
DB: 11g(11.2.0.1.0)
2. 异常现象: 对Oracle 数据库3个用户(test1,test2,test3), 做expdp导出,sql如下.
2.1 expdp system/systemtest directory=dump_dir schemas=test1,test2,test3 dumpfile=expdp_testusers_`date +%F`.dmp logfile=expdp_testusers_`date +%F`.log
2.2 如上expdp备份后,报错如下.
Export: Release 11.2.0.1.0 - Production on Thu Jan 15 11:53:41 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** directory=dump_dir schemas=test1,test2,test3 dumpfile=expdp_testusers_2015-01-15.dmp logfile=expdp_testusers_2015-01-15.log EXCLUDE=STATISTICS
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 440.9 MB
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [COMMENT]
ORA-01690: sort area size too small
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
0x1e5f262a0 19028 package body SYS.KUPW$WORKER
0x1e5f262a0 8191 package body SYS.KUPW$WORKER
0x1e5f262a0 2814 package body SYS.KUPW$WORKER
0x1e5f262a0 8846 package body SYS.KUPW$WORKER
0x1e5f262a0 1651 package body SYS.KUPW$WORKER
0x1e56ce588 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 11:54:26
--------------------------------------------------------------------------------------------------------------------
2.3 在meta_link上查询报错, metalink给出解决方法.
2.3.1升级DB版本由11.2.0.1.0升级到更高级的版本,如12.1.0.2.0,在网上查11G版本这个是BUG.
2.3.2 对DB打补丁.
2.3.3 增大PGA_AGGREAGTE_TARGET大小.
SOLUTION
1. Upgrade to 12.1.0.2.0 (or 12.2. once this has been released)
or
2. Apply Patch:17605620.
Please use My Oracle Support to check availability of Patch:17605620, otherwise contact Oracle Global Support.
or
3. Use the workaround
A possible workaround could be to increase PGA_AGGREGATE_TARGET.
Please note that workarounds are not fully comparable to code fixes (patches), therefore, whenever possible apply solution 1 or 2.
3 解决方法.
按照metalink介绍, 增大PGA_AGGREGATE_TARGET为1000M(1G),设置PGA时,要看DB总内存大小(MEMORY_TARGET),不要PGA设置太大,导致SGA减少.
3.1 expdp再一次导出故障依旧, 查询参数 workarea_size_policy值为MANUAL.
SQL> show parameter workarea;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string MANUAL
SQL>
3.2 设置workarea_size_policy值为AUTO,后expdp备份OK.
SQL> ALTER SYSTEM SET WORKAREA_SIZE_POLICY = AUTO;
System altered.
SQL> show parameter workarea;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AU
3.3 expdp system/systemtest directory=dump_dir schemas=test1,test2,test3 dumpfile=expdp_testusers_`date +%F`.dmp logfile=expdp_testusers_`date +%F`.log
susers_2015-01-15.dmp logfile=expdp_xysusers_2015-01-15.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 440.9 MB
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
----------导出之间的很多表在此省略-------------------------------
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
/data/backup/expdp/expdp_testusers_2015-01-15.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at 14:03:46
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28569596/viewspace-1402833/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28569596/viewspace-1402833/