首页 » ORACLE » 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就可以避免错误的产生了

打赏

对不起,这篇文章暂时关闭评论。