首页 » ORACLE 9i-23ai » impdp 挂起 tatement suspended, wait error to be cleared
impdp 挂起 tatement suspended, wait error to be cleared
今天在开发环境copy一个schema时遇到了这个问题，方法是未导出直接用impdp+network remap_schema 具体查看http://www.anbob.com/?p=985
[oracle@dev-db ~]$ impdp system/oracle network_link=dln_self schemas=icme2 remap_schema=icme2:icme3 remap_tablespace=icme:icmetbs
表导入成功
...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
在这等了一个多小时吃饭回来还没结束才开始怀疑
[oracle@dev-db ~]$ top
top - 14:06:19 up 98 days, 1:52, 2 users, load average: 1.21, 0.67, 0.32
Tasks: 293 total, 1 running, 292 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.0% us, 4.1% sy, 0.0% ni, 86.7% id, 8.0% wa, 0.0% hi, 0.2% si
Mem: 16407000k total, 16360436k used, 46564k free, 9248k buffers
Swap: 16386292k total, 93800k used, 16292492k free, 14017652k cached
PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
8879 oracle 16 0 33 21:47.82 23.7 7525m 3.7g 3.5g D oracle
121 root 15 0 4 1:47.71 0.0 0 0 0 S kswapd0
7563 oracle 15 0 2 2:01.34 17.8 7297m 2.8g 2.8g S oracle
SQL> select addr from v$process where spid=8879;
ADDR
----------------
000000021DB5CFE0
Elapsed: 00:00:00.01
SQL> select sid,serial#,event from v$session where paddr='000000021DB5CFE0';
SID SERIAL# EVENT
---------- ---------- ----------------------------------------------------------------
2140 1609 statement suspended, wait error to be cleared
Elapsed: 00:00:00.00
SQL> select * from v$session where paddr='000000021DB5CFE0';
SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA#
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ---------------- -------- --------- ----------
SCHEMANAME OSUSER PROCESS MACHINE TERMINAL
------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ------------------------------
PROGRAM TYPE SQL_ADDRESS SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER PREV_SQL_ADDR PREV_HASH_VALUE PREV_SQL_ID PREV_CHILD_NUMBER
------------------------------------------------ ---------- ---------------- -------------- ------------- ---------------- ---------------- --------------- ------------- -----------------
MODULE MODULE_HASH ACTION ACTION_HASH CLIENT_INFO FIXED_TABLE_SEQUENCE
------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- --------------------
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP PDML_STA PDDL_STA PQ_STATU
------------- -------------- --------------- ------------- ------------------- ------------ --- ------------- ---------- --- -------------------------------- -------- -------- --------
CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION SEQ# EVENT#
---------------------- ---------------------------------------------------------------- ----------- ----------------- ---------------- ---------- ----------
EVENT P1TEXT P1 P1RAW
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------
P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID
---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- -------------
WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE SERVICE_NAME SQL_TRAC
----------- ---------------------------------------------------------------- ---------- --------------- ------------------- ---------------------------------------------------------------- --------
SQL_T SQL_T
----- -----
000000021A33E868 2140 1609 0 000000021DB5CFE0 220 ICME3 0 2147483644 00000002163B4E00 ACTIVE DEDICATED 220
ICME3 oracle 8879 dev-db UNKNOWN
oracle@dev-db (DW01) USER 00 0 0 00 0 0
Data Pump Worker 2733574425 SYS_IMPORT_SCHEMA_03 1586351999 33556731
1012044 4 3416169 0 2012-03-06 11:20:15 10022 NO NONE NONE NO DISABLED ENABLED ENABLED
0 UNKNOWN 1726 192
statement suspended, wait error to be cleared 0 00
0 00 0 00 3290255840
2 Configuration 0 0 WAITING SYS$USERS DISABLED
FALSE FALSE
--可以看出正是 Data Pump Worker ,sys_import_schema_03,正是刚才的导入作业，event:statement suspended, wait error to be cleared
也可以查看v$session_event
SQL> select file_name,bytes,autoextensible,status,tablespace_name from dba_data_files order by 5
2 ;
FILE_NAME BYTES AUT STATUS TABLESPACE_NAME
------------------------------------------------------------ ---------- --- --------- ------------------------------
/backup/oradata/cme02.dbf 5408555008 YES AVAILABLE CME
/oradata/devdb/cme01.dbf 1.0469E+10 YES AVAILABLE CME
/oracle/oradata/tmpts01.dbf 9168748544 YES AVAILABLE ICME
/backup/oradata/icmetbs_1.dbf 3087007744 YES AVAILABLE ICMETBS
/oradata/devdb/olat01.dbf 1408237568 YES AVAILABLE OLAT
/backup/oradata/sanji01.dbf 314572800 YES AVAILABLE SANJI
/oradata/devdb/sysaux01.dbf 608174080 YES AVAILABLE SYSAUX
/oradata/devdb/system01.dbf 650117120 YES AVAILABLE SYSTEM
/oradata/devdb/topbox.dbf 2.3893E+10 YES AVAILABLE TOPBOX
/oracle/oradata/topbox2.dbf 1215299584 NO AVAILABLE TOPBOX
/backup/oradata/topbox02.dbf 3.4320E+10 YES AVAILABLE TOPBOX
/oracle/oradata/topbox_xj01.dbf 352321536 YES AVAILABLE TOPBOX_XJ
/oradata/devdb/undotbs01.dbf 3316645888 YES AVAILABLE UNDOTBS1
/oradata/devdb/users02.dbf 1.1721E+10 YES AVAILABLE USERS
/oradata/devdb/users03.dbf 1.1197E+10 YES AVAILABLE USERS
/oradata/devdb/users01.dbf 3.4360E+10 YES AVAILABLE USERS
/oracle/oradata/xnh01.dbf 2.4805E+10 YES AVAILABLE XNH
/oradata/devdb/zyy01.dbf 1310720000 YES AVAILABLE ZYY
18 rows selected.
SQL> ho df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda3 84757216 3660356 76791440 5% /
/dev/sda5 135049868 42254128 85935588 33% /backup
/dev/sda1 101086 13342 82525 14% /boot
none 8203500 0 8203500 0% /dev/shm
/dev/sda7 50394964 42205472 5629536 89% /oracle
/dev/sda6 135049868 128182920 6796 100% /oradata
SQL> SELECT SESSION_ID, STATUS, TIMEOUT, SUSPEND_TIME, RESUME_TIME, ERROR_NUMBER
2 from DBA_RESUMABLE;
SESSION_ID STATUS TIMEOUT SUSPEND_TIME RESUME_TIME ERROR_NUMBER
---------- --------- ---------- -------------------- -------------------- ------------
2140 SUSPENDED 7200 03/06/12 14:07:17 1659
1999 NORMAL 7200 0
--看来是RESUMABLE 的特性进了作用，这个特性就是在导入时如果遇到错误不会立马终止，可以通过DBA_RESUMABLE view查看error_number 的错误信息，并且如果是在resumable_timeout 的时间范围内，在错误解决后，导入进程还可以继续工作
SQL> show parameter timeout
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout integer 60
log_checkpoint_timeout integer 1800
resumable_timeout integer 0
--虽然resumable_timeout=0，但是默认还是和以前9I一样是7200秒（两小时），查看了users表空间的使用为99%，怀疑index是不是建立在了USERS表空间
SQL> alter tablespace users add datafile '/backup/oradata/users04.dfb' size 1g autoextend on;
Tablespace altered.
SQL> SELECT SESSION_ID, STATUS, TIMEOUT, SUSPEND_TIME, RESUME_TIME, ERROR_NUMBER
2 from DBA_RESUMABLE;
SESSION_ID STATUS TIMEOUT SUSPEND_TIME RESUME_TIME ERROR_NUMBER
---------- --------- ---------- -------------------- -------------------- ------------
2140 NORMAL 7200 03/06/12 14:15:02 0
1999 NORMAL 7200
SQL> select sid,serial#,event from v$session where paddr='000000021DB5CFE0';
SID SERIAL# EVENT
---------- ---------- ----------------------------------------------------------------
2140 1609 direct path read temp
SQL> select sid,serial#,event from v$session where paddr='000000021DB5CFE0';
SID SERIAL# EVENT
---------- ---------- ----------------------------------------------------------------
2140 1609 Data file init write
--tatement suspended, wait error to be cleared 事件消失
但是还是出现了超时错误，
ORA-39083: Object type INDEX failed to create with error:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01659: unable to allocate MINEXTENTS beyond 59 in tablespace ICMETBS
Failing sql is:
CREATE INDEX "ICME3"."IDX_ACTIVITY_DATE" ON "ICME3"."ICME_NOPROJECT_SCORE" ("SCORE_ACTIVITY_DATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 520093696 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ICMETBS" PARALLE
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [INDEX:"ICME3"."IDX_STUDENT_RANK_YEARID"]
ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1891
ORA-06512: at "SYS.DBMS_METADATA", line 3956
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6241
----- PL/SQL Call Stack -----
object line object
handle number name
0x1f14c37b8 14916 package body SYS.KUPW$WORKER
0x1f14c37b8 6300 package body SYS.KUPW$WORKER
0x1f14c37b8 2340 package body SYS.KUPW$WORKER
0x1f14c37b8 6861 package body SYS.KUPW$WORKER
0x1f14c37b8 1262 package body SYS.KUPW$WORKER
0x2082cb3f8 2 anonymous block
Job "SYSTEM"."SYS_IMPORT_SCHEMA_03" stopped due to fatal error at 14:24:27
第二次重新导入，没有在遇到这个错误，只是在导入统计信息时等了有1个多小时，最后是手动统计。
这个现象和上面报的ORA-39125怀疑10G可能存在BUG。
老杨http://yangtingkun.itpub.net/post/468/475503
“
ps:第二次导入还遇到了
[oracle@dev-db ~]$ impdp system/oracle network_link=dln_self schemas=icme2 remap_schema=icme2:icme3 remap_tablespace=icme:icmetbs remap_tablespace=users:icmetbs
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 06 March, 2012 15:03:24
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_IMPORT_SCHEMA_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM
原因是system表空间不足
根据错误信息和错误位置判断，Bug发生在导入表的统计信息处。要解决这个bug也许很困难，要绕过这个bug并不难。
只需要在导入的时候EXCLUDE=TABLE_STATISTICIS就可以避免错误的产生了
”
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。