impdp导入时报ora-39082：错并且卡住了

shunzi

在一套RAC上创建了俩个实例，expdp导出其中一个实例的数据，impdp到另一个实例时报错，并且impdp卡死不动了。
ORA-39082: Object type ALTER_PROCEDURE:"GDCM"."PROC_RPT_UPLOAD_LOG" created with compilation warnings
在线等，急 谢谢

Maclean Liu(刘相兵

什么版本的DB?

“创建了俩个实例，expdp导出其中一个实例的数据，impdp到另一个实例时报错”

是建了 2个DB ， expdp 导出一个DB的数据， impdp到另一个 DB吧？　

shunzi

对，版本是10.2.0.4  os是AIX的

Maclean Liu(刘相兵

在hang的时候 收集一下信息:
sqlplus / as sysdba
oradebug setmypid;
oradebug unlimit;
oradebug dump hanganalyze 4;
oradebug dump systemstate 266;
oradebug tracefile_name;                           =========> 会输出trace文件的名字


把以上生成的trace 文件和 alert.log  压缩打包后上传

shunzi

这个上传了。

Maclean Liu(刘相兵

ODM Finding:

kupprdp: master process DM00 started with pid=27, OS id=345042
         to execute - SYS.KUPM$MCP.MAIN('IMPDP_20120306', 'SYS', 'KUPC$C_1_20120306213320', 'KUPC$S_1_20120306213320', 0);
kupprdp: worker process DW02 started with worker id=1, pid=30, OS id=111090
         to execute - SYS.KUPW$WORKER.MAIN('IMPDP_20120306', 'SYS');
kupprdp: worker process DW03 started with worker id=2, pid=36, OS id=327742
         to execute - SYS.KUPW$WORKER.MAIN('IMPDP_20120306', 'SYS');
kupprdp: worker process DW04 started with worker id=3, pid=37, OS id=221646
         to execute - SYS.KUPW$WORKER.MAIN('IMPDP_20120306', 'SYS');


DM00           => Datapump manager process
DW01          =>  Datapump worker  1 process
DW02          => Datapump worker  2 process


在 trace中仅找到DW01  的信息，且没有open的 hang chain :

==============
HANG ANALYSIS:
==============
Open chains found:
Other chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/115/5508/0x8144340/315748/Streams AQ: qmn slave idle wait>


DW01:

1.   SO: 700000109137fe8, type: 2, owner: 0, flag: INIT/-/-/0x00
   
2.   (process) Oracle pid=28, calls cur/top: 70000010f1e7868/70000010f1e8640, flag: (0) -
   
3.             int error: 2147522767, call error: 0, sess error: 0, txn error 0
   
4.   (post info) last post received: 2147515321 136 12
   
5.               last post received-location: ksusig
   
6.               last process to post me: 70000010f183e50 16 0
   
7.               last post sent: 0 0 24
   
8.               last post sent-location: ksasnd
   
9.               last process posted by me: 700000108143380 1 6
   
10.     (latch info) wait_event=0 bits=0
    
11.     Process Group: DEFAULT, pseudo proc: 700000108153878
    
12.     O/S info: user: oracle, term: UNKNOWN, ospid: 233778
    
13.     OSD pid info: Unix process pid: 233778, image: oracle@ydyxdw (DW01)
    
14.     Short stack dump:
    
15. ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000044BC<-nttcni+01b8<-nttcon+04f4<-ntconn+0160<-nsopen+0960<-nscall1+01b0<-nscall+049c<-niotns+0880<-nigcall+0028<-osncon+0540<-kpkiadef+006c<-upiini+0408<-upiah0+00ac<-kpuatch+0808<-OCIServerAttach+0114<-kpnconn+02a4<-npicon0+042c<-npicon+0054<-opikgl+0370<-opiodr+0ae0<-rpidrus+01bc<-skgmstack+00c8<-rpidru+0088<-rpiswu2+034c<-rpidrv+095c<-rpikgl+01b0<-kqlrlk+0280<-kqlrld+0140<-kqllod+05e0<-kglobld+05ac<-kglobpn+09f8<-kglpim+0294<-kglpin+0e60<-kglgob+02b0<-qcdlgbo+0534<-qcdlgob+0568<-qcsfgob+0220<-qcsprfro+049c<-qcsprfro_tree+03f0<-qcsprfro_tree+0228<-qcspafq+0068<-qcspqbDescendents+03a0<-qcspqb+00ac<-qcsdrv+0108<-qcitrans+06d8<-qcisem+013c<-ph2csql_analyze+03c4<-ph2stm+26a8<-ph2sms+0224<-ph2blo+026c<-ph2obl+0088<-ph2uni+0930<-ph2dr2+01fc<-ph2drv+01fc<-phpsem+006c<-phpcmp+0a40<-pcicms2+02d4<-pcicmp2+0048<-kkxcmp0+0324<-rpiswu2+034c<-kkxcmp+0104<-kkpalt+03e4<-opiexe+2978<-opiosq0+19f0<-opipls+0614<-opiodr+0ae0<-rpidrus+01bc<-skgmstack+00c8<-rpidru+0088<-rpiswu2+034c<-rpidrv+095c<-psddr0+02bc<-psdnal+01d0<-pevm_EXIM+0120<-pfrinstr_EXIM+0034<-pfrrun_no_tool+005c<-pfrrun+1014<-plsql_run+06b4<-peicnt+0224<-kkxexe+0250<-opiexe+2f08<-kpoal8+0edc<-opiodr+0ae0<-kpoodr+0220<-upirtrc+04e0<-kpurcsc+006c<-kpuexecv8+1188<-kpuexec+1240<-OCIStmtExecute+0020<-kupprdp+2fd8<-opirip+0534<-opidrv+0458<-sou2o+0090<-opimai_real+0150<-main+0098<-__start+0098
    
16. 
    
17. 
    
18.    SO: 70000010f1c2568, type: 4, owner: 700000109137fe8, flag: INIT/-/-/0x00
    
19.     (session) sid: 143 trans: 70000010acdcb60, creator: 700000109137fe8, flag: (48100041) USR/- BSY/-/-/-/-/-
    
20.               DID: 0001-001C-000117CD, short-term DID: 0001-001C-000093C2
    
21.               txn branch: 0
    
22.               oct: 0, prv: 0, sql: 0, psql: 0, user: 98/DCDSS
    
23.     service name: SYS$USERS
    
24.     O/S info: user: oracle, term: UNKNOWN, ospid: 233778, machine: ydyxdw
    
25.               program: oracle@ydyxdw (DW01)
    
26.     application name: Data Pump Worker, hash value=2733574425
    
27.     action name: IMPDP_20120306, hash value=1407238116
    
28.     waiting for 'single-task message' blocking sess=0x0 seq=10994 wait_time=0 seconds since wait started=9378
    
29.                 =0, =0, =0
    
30.     Dumping Session Wait History
    
31.      for 'SQL*Net message from dblink' count=1 wait_time=612
    
32.                 driver id=0, #bytes=1, =0
    
33.      for 'SQL*Net message to dblink' count=1 wait_time=2
    
34.                 driver id=0, #bytes=1, =0
    
35.      for 'library cache pin' count=1 wait_time=57
    
36.                 handle address=70000010b7f83d0, pin address=7000000fbf3ba40, 100*mode+namespace=c9
    
37.      for 'row cache lock' count=1 wait_time=61
    
38.                 cache id=f, mode=0, request=3
    
39.      for 'rdbms ipc reply' count=1 wait_time=35
    
40.                 from_process=15, timeout=147ae14, =0
    
41.      for 'row cache lock' count=1 wait_time=58
    
42.                 cache id=8, mode=0, request=3
    
43.      for 'library cache pin' count=1 wait_time=60
    
44.                 handle address=7000000ec469550, pin address=7000000fbf3c678, 100*mode+namespace=c9
    
45.      for 'row cache lock' count=1 wait_time=59
    
46.                 cache id=b, mode=0, request=5
    
47.      for 'row cache lock' count=1 wait_time=47
    
48.                 cache id=8, mode=0, request=5
    
49.      for 'library cache pin' count=1 wait_time=48
    
50.                 handle address=7000000ec683fa0, pin address=7000000fbf3dff8, 100*mode+namespace=12d

复制代码

Maclean Liu(刘相兵

SQL*Net message from dblink
single-task message

=> 说明在访问dblink 远程节点


最近的一次 library cache pin handle 70000010b7f83d0

          SO: 7000000fbf3ba40, type: 54, owner: 70000010f1e4ec8, flag: INIT/-/-/0x00
          LIBRARY OBJECT PIN: pin=7000000fbf3ba40 handle=70000010b7f83d0 mode=S lock=7000000fc8a7250
          user=70000010f1c2568 session=70000010f1c2568 count=0 mask=0005 savepoint=0x1d47b0f flags=[00]
            ----------------------------------------
            SO: 7000000fb530df8, type: 55, owner: 7000000fbf3ba40, flag: INIT/-/-/0x00
            LIBRARY OBJECT LOAD LOCK: lock=7000000fb530df8
            session=70000010f1c2568 object=7000000f4bb5dd8 mode=X mask=0005 count=1
          ----------------------------------------
          SO: 7000000fc8a7250, type: 53, owner: 70000010f1e4ec8, flag: INIT/-/-/0x00
          LIBRARY OBJECT LOCK: lock=7000000fc8a7250 handle=70000010b7f83d0 mode=S
          call pin=7000000fbf3ba40 session pin=0 hpc=0000 hlc=0000
          htl=7000000fc8a72d0[7000000fda79ac0,7000000fda79ac0] htb=7000000fda79ac0 ssga=7000000fda789c8
          user=70000010f1c2568 session=70000010f1c2568 count=1 flags=PNC/[0400] savepoint=0x1d47b0f
          LIBRARY OBJECT HANDLE: handle=70000010b7f83d0 mtx=70000010b7f8500(0) cdp=0
          name=DCDSS.DC_JLD_POWER_96@ESCA
          hash=40dc870335d5753802ba0f85f2c077fe
          namespace=TABL flags=REM/KGHP/XLR/[00020020]
          kkkk-dddd-llll=0000-0000-0000 lock=S pin=S latch#=1 hpc=0002 hlc=0002
          lwt=70000010b7f8478[70000010b7f8478,70000010b7f8478] ltm=70000010b7f8488[70000010b7f8488,70000010b7f8488]
          pwt=70000010b7f8440[70000010b7f8440,70000010b7f8440] ptm=70000010b7f8450[70000010b7f8450,70000010b7f8450]
          ref=70000010b7f84a8[70000010b7f84a8,70000010b7f84a8] lnd=70000010b7f84c0[70000010b7f84c0,70000010b7f84c0]
            LOCK INSTANCE LOCK: id=LB40dc870335d57538
            PIN INSTANCE LOCK: id=NB40dc870335d57538 mode=S release=F flags=[00]
            LIBRARY OBJECT: object=7000000f4bb5dd8
            DATA BLOCKS:
            data#     heap  pointer    status pins change whr
            ----- -------- -------- --------- ---- ------ ---
                0 70000010b9de540        0 I/P/A/-/-    0 NONE   00


DCDSS.DC_JLD_POWER_96@ESCA 这是一个什么对象？

shunzi

那个是远程连接对象吧。。。

在impdp_20120306.log3日志里，
现在遇到这个错误：
ORA-39083: Object type REFRESH_GROUP failed to create with error:
ORA-23421: job number 505 is not a job in the job queue
我把日志上传上去

Maclean Liu(刘相兵

DW01 这个Datapump work进程之前在等 DCDSS.DC_JLD_POWER_96@ESCA这个远程对象的 library cache pin ，之后在等SQL*Net message from dblink
single-task message    ，很明显是 远程对象导致了这些等待

建议你排除这些 涉及到dblink的对象 后 重试