Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › exp和expdp导出用户报ORA-31093

lai

45 积分	0 好友	6 主题

发消息

exp和expdp导出用户报ORA-31093

1^#

发表于 2012-2-9 10:55:33 | 查看: 11075| 回复: 8

背景：solaris上11.1.0.7.0数据库，使用exp和expdp导出用户下的一张表是没有问题的，但是如果导出用户却报错了，如下所示：

bash-3.00$ expdp lhf/lhf directory=dir1 dumpfile=0209_lhf.dmp logfile=0209_lhf.log schemas=lhf
Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 09 February, 2012 10:05:29
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "lhf"."SYS_EXPORT_SCHEMA_01":  [email=lhf/********@OLTPDB]lhf/********@OLTPDB[/email] directory=dir1 dumpfile=0209_lhf.dmp logfile=0209_lhf.log schemas=lhf
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.988 GB
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/SEQUENCE/SEQUENCE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [SEQUENCE:"lhf"."S_PO_CONDITIONID"]
ORA-31093: null or invalid value specified for parameter : privilege
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 7839
----- PL/SQL Call Stack -----
  object    line  object
  handle number  name
833e866b8    18237  package body SYS.KUPW$WORKER
833e866b8    7866  package body SYS.KUPW$WORKER
833e866b8    2744  package body SYS.KUPW$WORKER
833e866b8    8504  package body SYS.KUPW$WORKER
80bd55f20       1  anonymous block
835f27948    1575  package body SYS.DBMS_SQL
833e866b8    8342  package body SYS.KUPW$WORKER
833e866b8    1545  package body SYS.KUPW$WORKER
7b889b438       2  anonymous block
Job "lhf"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 10:08:23

bash-3.00$ exp [email=lhf/lhf@OLTPDB]lhf/lhf@OLTPDB[/email] owner=lhf feedback=10000 buffer=65536 compress=n  volsize=0 file=lhf_201202092_.dmp log=lhf_201202092_.log
Export: Release 11.1.0.7.0 - Production on Thu Feb 9 09:57:27 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user lhf
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user lhf
About to export lhf's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 31093 encountered
ORA-31093: null or invalid value specified for parameter : privilege
EXP-00000: Export terminated unsuccessfully

alert日志中有如下错误：
ORA-07445: exception encountered: core dump [dbgrmqmqpk_query_pick_key()+2072] [SIGSEGV] [ADDR:0xB38F0000000049] [PC:0x106727D98] [Address not mapped to object] []
ORA-1652: unable to extend temp segment by 128 in tablespace                TEMP
WARNING: inbound connection timed out (ORA-3136)
WARNING: inbound connection timed out (ORA-3136)
ORA-1652: unable to extend temp segment by 128 in tablespace                TEMP

麻烦大家帮忙解决下，谢谢了

[ 本帖最后由 lai 于 2012-2-9 11:10 编辑 ]

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-2-9 16:18:39

SEQUENCE:"lhf"."S_PO_CONDITIONID"

用dbms_metadata.get_ddl 把这个sequence的定义找出来

尝试用exclude=sequence 来排除掉sequence对象

回复只看该作者道具举报

lai

3^#

发表于 2012-2-13 09:29:10

回复 1# 的帖子

现在只要是导出用户下所有对象，都报错，但是导出表，却不会报错，如下所示：
testserver% exp servicesupport/password owner=servicesupport  file=lhf_20120213_servicesupport.dmp log=lhf_20120213_servicesupport.log

Export: Release 11.1.0.7.0 - Production on Mon Feb 13 09:26:00 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SERVICESUPPORT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SERVICESUPPORT
About to export SERVICESUPPORT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 31093 encountered
ORA-31093: null or invalid value specified for parameter : privilege
EXP-00000: Export terminated unsuccessfully
使用expdp导出servicesupport用户下的所有对象，报错如下：
bash-3.00$ expdp servicesupport/password schemas=servicesupport directory=dir1 dumpfile=0213.dmp logfile=0213.log
Export: Release 11.1.0.7.0 - 64bit Production on Monday, 13 February, 2012 9:36:05
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SERVICESUPPORT"."SYS_EXPORT_SCHEMA_02":  servicesupport/******** schemas=servicesupport directory=dir1 dumpfile=0213.dmp logfile=0213.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.24 GB
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/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [SEQUENCE:"SERVICESUPPORT"."W_USERSTOSERVER_SEQUENCE"]
ORA-31093: null or invalid value specified for parameter : privilege
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 7839
----- PL/SQL Call Stack -----
  object    line  object
  handle number  name
833e866b8    18237  package body SYS.KUPW$WORKER
833e866b8    7866  package body SYS.KUPW$WORKER
833e866b8    2744  package body SYS.KUPW$WORKER
833e866b8    8504  package body SYS.KUPW$WORKER
80bd55f20       1  anonymous block
835f27948    1575  package body SYS.DBMS_SQL
833e866b8    8342  package body SYS.KUPW$WORKER
833e866b8    1545  package body SYS.KUPW$WORKER
7a2516b20       2  anonymous block
Job "SERVICESUPPORT"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 09:41:33
麻烦了，是否是数据字典出现了问题？有没有官方的文档说明ORA-31093，多谢了

[ 本帖最后由 lai 于 2012-2-13 09:46 编辑 ]

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-2-13 15:35:10

action plan:

尝试使用 system 用户导出，如仍不成功则对exp/expdp 做10046 trace。

回复只看该作者道具举报

lai

5^#

发表于 2012-2-13 15:58:19

10046trace

system没有去试，sys是可以导出的，附件为10046trace

OLTPSID_ora_26085.rar

61.86 KB, 下载次数: 2243

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-2-13 16:05:19

ODM FINDing:

PARSING IN CURSOR #13 len=42 dep=1 uid=0 oct=3 lid=0 tim=25075037196546 hv=2179052769 ad='81e3b34d0' sqlid='g41n1r60y3d71'
SELECT count(rowid) FROM XDB.XDB$ROOT_INFO
END OF STMT
PARSE #13:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=4189120091,tim=25075037196546
EXEC #13:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=4189120091,tim=25075037196546
FETCH #13:c=0,e=0,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=4,plh=4189120091,tim=25075037196546
STAT #13 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)'
STAT #13 id=2 cnt=1 pid=1 pos=1 obj=206112 op='TABLE ACCESS FULL XDB$ROOT_INFO (cr=7 pr=0 pw=0 time=0 us)'
CLOSE #13:c=0,e=0,dep=1,type=0,tim=25075037196546
*** 2012-02-13 11:35:19.683
----- Error Stack Dump -----
ORA-31093: null or invalid value specified for parameter : privilege
----- Current SQL Statement for this session (sql_id=5jsqknp845sav) -----
SELECT URL, LOCAL, STRIPPED_VAL FROM SYS.KU$_XMLSCHEMA_VIEW WHERE OWNER_NAME = :OWNER
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+88 CALL skdstdst() 00000000A ? 000000001 ?
FFFFFFFF7FFF2630 ?
000000013 ? 000000001 ?
FFFFFFFF7FFE2B30 ?
ksedst()+56 CALL ksedst1() 000000000 ? 00010A000 ?
00010A208 ? 00010A000 ?
10A200000 ? 00010A200 ?
dbkedDefDump()+988 CALL ksedst() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
10A208000 ? 00010A208 ?
dbkdaKsdActDriver() PTR_CALL dbkedDefDump()+988 000000000 ? 10A21A000 ?
+2404 000000000 ? 10908BF94 ?
000000002 ? 000109000 ?
dbgdaExecuteAction( PTR_CALL dbkdaKsdActDriver() 000108C00 ? 000000001 ?
)+344 +2404 10A204000 ? 20000000000800 ?
000000001 ? 000000001 ?
dbgdaRunAction()+13 CALL dbgdaExecuteAction( FFFFFFFFFEB9D778 ?
28 ) 1091C1238 ? 1091C1238 ?
FFFFFFFF7FFF4B88 ?
10A200A18 ? 10A31F820 ?
dbgdRunActions()+52 CALL dbgdaRunAction() 10A31F820 ?
FFFFFFFF7FFF4C08 ?
1091C1238 ? 000000005 ?
FFFFFFFF7B47CC58 ?
FFFFFFFF7FFF4B88 ?
dbgdProcessEventAct CALL dbgdRunActions() 10A31F820 ?
ions()+528 FFFFFFFF7B47CDD8 ?
10A200BC8 ? 003964F8C ?
000000000 ?
FFFFFFFF7B47CDA8 ?
dbgdChkEventKgErr() CALL dbgdProcessEventAct 001462800 ?
+2876 ions() FFFFFFFF7B47D6C0 ?
000000009 ? 10A1FA050 ?
000000001 ?
4000000000000000 ?
ksfpec()+92 CALL dbkdChkEventRdbmsEr 10A31F820 ?
r() FFFFFFFF7B47D4E8 ?
FFFFFFFF7B47D580 ?
10A31F820 ? 080000000 ?
000000000 ?
dbgePostErrorKGE()+ PTR_CALL ksfpec()+92 000000000 ? 2000000001 ?
884 0000001E5 ?
FFFFFFFFFEB9D8C0 ?
10A21A000 ? 00010A21A ?
kgesev()+212 PTR_CALL dbgePostErrorKGE()+ 10A1FA050 ? 000000001 ?
884 108D97900 ? 000001460 ?
10A3206D8 ? 10A3206D8 ?
ksesec1()+64 CALL kgesev() 10A200A18 ? 10A32E7D0 ?
000007975 ? 000000001 ?
FFFFFFFF7FFF5B58 ?
10A200BD8 ?
qmcParseAccessPriv( CALL ksesec1() 000007975 ? 00010A208 ?
)+664 00010A200 ? 10A208000 ?
000000001 ? 000000000 ?
qmcXDBAclCheckType( CALL qmcParseAccessPriv( 10A200A18 ? 000001400 ?
)+548 ) FFFFFFFF7B4E70F8 ?
000000002 ? 10A200000 ?
FFFFFFFF7ADF25D8 ?
qmcAclCheckInit()+3 CALL qmcXDBAclCheckType( 7B0EAAC78 ?
24 ) FFFFFFFF7AE024C0 ?
108E30000 ? 000000202 ?
00000FFFF ?
FFFFFFFF7ADF25D8 ?
qmxtcAclCheckInit() CALL qmcAclCheckInit() FFFFFFFF7AE024C0 ?
+32 002420000 ? 002420000 ?
002000000 ? 00000003A ?
000000003 ?
xtyopncb()+280 PTR_CALL qmxtcAclCheckInit() FFFFFFFF7AE024C0 ?
+32 000000000 ?
FFFFFFFF7ADF25D8 ?
00000021E ? 000109015 ?
FFFFFFFF7AE024C0 ?
qctcopn()+336 PTR_CALL xtyopncb()+280 7B0EAAC78 ? 00000021E ?
FFFFFFFF7AE024C0 ?
00010A200 ? 109020B20 ?
10A2006D8 ?
qctcopn()+552 CALL qctcopn() 000120000 ? 001462000 ?
1042D60A0 ?
FFFFFFFF7AE024C0 ?
1042D60A0 ?
FFFFFFFFFEB9DF88 ?
qctclog()+80 CALL qctcopn() 000000002 ? 1066466A0 ?
000000000 ?
FFFFFFFF7AE022C0 ?
000000000 ? 000400000 ?
qctclog()+48 CALL qctclog() 7B0EAAC78 ? 10A200A18 ?
FFFFFFFF7AE02338 ?
000000000 ? 003BBCC1C ?
10A1FA050 ?
qctclog()+48 CALL qctclog() 7B0EAAC78 ? 10A200A18 ?
FFFFFFFF7B48F140 ?
002000000 ? 003BBCC1C ?
10A1FA050 ?
qctclog()+48 CALL qctclog() 7B0EAAC78 ? 10A200A18 ?
FFFFFFFF7B48F788 ?
001000000 ? 003BBCC1C ?
10A1FA050 ?
qctclog()+48 CALL qctclog() 7B0EAAC78 ? 10A200A18 ?
FFFFFFFF7B48F6C8 ?
000000000 ? 003BBCC1C ?
10A1FA050 ?
qctcpqb()+408 CALL qctclog() 7B0EAAC78 ? 10A200A18 ?
FFFFFFFF7B61A830 ?
000000001 ? 003BBCC1C ?
10A1FA050 ?
opitca()+944 CALL xtydrv() 000002378 ?
FFFFFFFF7B616630 ?
108D97FD8 ? 1091BC650 ?
FFFFFFFFFEB9DF88 ?
000000000 ?
kksFullTypeCheck()+ CALL opitca() 002002000 ? 000000008 ?
12 001000000 ? 7B0EAAEB8 ?
000001114 ? 108D97000 ?
rpiswu2()+504 PTR_CALL kksFullTypeCheck()+ FFFFFFFF7FFF7C88 ?
12 000108C00 ? 000001114 ?
0FFEFFC00 ?
FFFFFFFF7FFF7D00 ?
108D97000 ?
kksSetBindType()+22 CALL rpiswu2() 103DA7CC0 ? 000000000 ?
16 000000000 ? 10A200A18 ?
10A200BC8 ? 000108C00 ?
kksfbc()+9816 CALL kksSetBindType() 000000000 ?
FFFFFFFF7B629438 ?
000000000 ? 10A204000 ?
00010A204 ? 000000000 ?
opiexe()+2180 CALL kksfbc() 002002400 ? 000109800 ?
7F447F0D0 ? 00000001F ?
FFFFFFFF7FFFA754 ?
000000041 ?
kpoal8()+2536 CALL opiexe() 10A217000 ? 00010A000 ?
000000000 ? 000000003 ?
00010A213 ? 10A200A18 ?
opiodr()+1136 PTR_CALL kpoal8()+2536 000000000 ? 000000100 ?
000000000 ? 000001000 ?
FFFFFFFF7FFFE0F8 ?
FFFFFFFF7ACCCD10 ?
ttcpip()+1060 PTR_CALL opiodr()+1136 0000011E0 ? 00010A000 ?
00000005E ?
FFFFFFFF7B6285A8 ?
10A200000 ? 000006468 ?
opitsk()+1484 CALL ttcpip() 00000005F ? 108D78500 ?
10A213B80 ?
FFFFFFFF7FFFE0F8 ?
FFFFFFFF7FFFCB38 ?
10A200A18 ?
opiino()+988 CALL opitsk() 000000000 ? 000000000 ?
00000000A ? 108D7C79C ?
10A213998 ? 000000000 ?
opiodr()+1136 PTR_CALL opiino()+988 000380000 ? 10A4DF658 ?
000000000 ? 10A213A08 ?
0000000FA ?
FFFFFFFF7FFFF8B8 ?
opidrv()+1060 CALL opiodr() 10A213000 ? 00010A213 ?
00000003C ? 00010A000 ?
10A200000 ? 0000011E0 ?
sou2o()+80 CALL opidrv() 000000000 ? 10A200A18 ?
00000003C ? 00010A000 ?
000000000 ? 10A200BC8 ?
main()+648 CALL sou2o() 109AFA000 ? 00000003C ?
000000004 ?
FFFFFFFF7FFFF8B8 ?
000109AFA ?
FFFFFFFF7FFFF890 ?
_start()+380 CALL main() 000109694 ?
FFFFFFFF7FFFF9E8 ?
000000000 ? 000000000 ?
FFFFFFFF7FFFF9F8 ?
000000002 ?

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2012-2-13 16:06:33

action plan:

1. 重建 XDB component , 运行一下脚本后再导出

sqlplus /nolog

connect / as sysdba
@$ORACLE_HOME/rdbms/admin/catmetx.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

2.
或者授予导出用户dba 权限后重试

grant dba to EXPUSER;

回复只看该作者道具举报

lai

8^#

发表于 2012-2-13 16:32:28

授予dba，仍旧一样

如下所示：
SQL> grant dba to isap2;
Grant succeeded.
bash-3.00$ exp isap2/xxx owner=isap2 file=isap2_20120213.dmp log=isap2_20120213.log
Export: Release 11.1.0.7.0 - Production on Mon Feb 13 16:27:24 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ISAP2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ISAP2
About to export ISAP2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 31093 encountered
ORA-31093: null or invalid value specified for parameter : privilege
EXP-00000: Export terminated unsuccessfully

至于重建xdb组件，需要一点时间。毕竟是现网数据库，先谢谢您，后面可能还要麻烦您

回复只看该作者道具举报

Maclean Liu(刘相兵

9^#

发表于 2012-2-13 18:14:22

ODM Data:

Export Receives Errors EXP-56 ORA-24324 In Log File And ORA-07445 [kgghstfel()+15] / ORA-00600 [qmxiUnpPacked2] In Alert Log [ID 1052052.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 24-OCT-2011***
Symptoms
When running any type of traditional export (full, user or table) you are getting in the log file the errors:
...
. 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
In the alert log file is reported:
ORA-07445: exception encountered: core dump [kgghstfel()+15] [SIGSEGV] [Address not mapped to object] [0x000000018] [] []
or
ORA-00600: internal error code, arguments: [qmxiUnpPacked2], [111], [], [], [], [], [], []
The trace files generated by both internal errors show the failure is in:
SELECT URL, LOCAL, STRIPPED_VAL FROM SYS.KU$_XMLSCHEMA_VIEW WHERE OWNER_NAME = :OWNER
Call Stack for ORA-07445:
-------------------------
ksedst ksedmp ssexhd funlockfile kgghstfel qmtLookup qmtRemoveRef qmxtrCleanRewriteCt qmxtrxopn qmxtrxopt qmxtrxq qmxtrxq qmxtrxq qmxtrxop koksspend ...
Call Stack for ORA-00600:
-------------------------
ksedst ksedmp ksfdmp kgerinv kgeasnmierr qmxiUnpicklePackedXobs qmxiManifestVArray qmxiManifestFromImage_int qmxiManifestFromImage qmxManifest0 ...
XDB and XML components in DBA_REGISTRY are valid as well the rest of components.
There are no invalid SYS.KU$_% objects.
There are no events set in database.
Cause
The export errors are reported when accessing a XDB related object SYS.KU$_XMLSCHEMA_VIEW. This object even if reported as valid in DBA_OBJECTS is having problems most probable caused by a previous upgrade.
Solution
Recreate the view SYS.KU$_XMLSCHEMA_VIEW by executing the following scripts connected to database as SYSDBA user from sqlplus:
sqlplus /nolog
connect / as sysdba
@$ORACLE_HOME/rdbms/admin/catmetx.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
and then re-run the export.

复制代码

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册