一、现场报告
导入数据到Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX的时候，hang住了，求救
[oracle@TestServer-RHAS-5 dmpdir]$ impdp system/ DIRECTORY=dmpdir DUMPFILE=cscnew.20111123.dmp LOGFILE=cscnew.20111123.log SCHEMAS=CSCNEW remap_schema=CSCNEW:TESTB remap_tablespace=CSC_TAB_1:TESTB table_exists_action=replace ………… . . imported "TESTB"."TAB_CS_SELF_WORKTIME" 0 KB 0 rows . . imported "TESTB"."TAB_CS_SELF_WORKTIME_DETAIL" 0 KB 0 rows . . imported "TESTB"."TAB_CS_USERMENU" 0 KB 0 rows . . imported "TESTB"."TAB_PUB_BANK" 0 KB 0 rows . . imported "TESTB"."TAB_PUB_BUSISRVINFO" 0 KB 0 rows . . imported "TESTB"."TAB_PUB_CONTACT" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
二、处理过程
1、分析是否是impdp是否因为网络等情况终止掉
[oracle@TestServer-RHAS-5 ~]$ ps -ef|grep impdp oracle 2520 1837 0 09:59 pts/8 00:00:00 grep impdp oracle 23819 20966 0 09:39 pts/6 00:00:00 impdp DIRECTORY=dmpdir DUMPFILE=cscnew.20111123.dmp LOGFILE=cscnew.20111123.log SCHEMAS=CSCNEW remap_schema=CSCNEW:TESTB remap_tablespace=CSC_TAB_1:TESTB table_exists_action=replace [oracle@TestServer-RHAS-5 ~]$ ps -ef|grep LOCAL=YES oracle 2692 1837 0 10:00 pts/8 00:00:00 grep LOCAL=YES oracle 10754 10694 0 09:15 ? 00:00:09 oraclemcrm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 23835 23819 0 09:40 ? 00:00:00 oraclemcrm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
通过上面的查询，证明impdp进程工作正常
2、查询等待事件
[oracle@TestServer-RHAS-5 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 24 10:00:26 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select event from v$session_wait where wait_class#<>6; EVENT ---------------------------------------------------------------- SQL*Net message to client statement suspended, wait error to be cleared
通过这个查询，发现一个异常等待事件:statement suspended, wait error to be cleared。
查询MOS，确定是表空间不足引起impdp suspended
Statement Suspended, Wait Error To Be Cleared Wait Event [ID 761848.1]
Oracle Database provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables you to take corrective action instead of the Oracle Database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The statements that are affected are called resumable statements. The time between suspending the execution till correction of the error is reported as "statement suspended, wait error to be cleared" wait event.
3、查看alert.log日志文件确认
[oracle@TestServer-RHAS-5 ~]$ cd /opt/oracle/admin/mcrm/bdump/
[oracle@TestServer-RHAS-5 bdump]$ tail -30 alert_mcrm.log
Thu Nov 24 09:29:20 2011
create tablespace testb
datafile '/opt/oradata/mcrm/testb.dbf'
size 1500M autoextend on next 50M maxsize 2000M
Thu Nov 24 09:29:51 2011
Completed: create tablespace testb
datafile '/opt/oradata/mcrm/testb.dbf'
size 1500M autoextend on next 50M maxsize 2000M
Thu Nov 24 09:40:00 2011
The value (30) of MAXTRANS parameter ignored.
kupprdp: master process DM00 started with pid=111, OS id=23858
to execute - SYS.KUPM$MCP.MAIN('SYS_IMPORT_SCHEMA_01', 'SYSTEM', 'KUPC$C_1_20111124094000', 'KUPC$S_1_20111124094000', 0);
kupprdp: worker process DW01 started with worker id=1, pid=112, OS id=23870
to execute - SYS.KUPW$WORKER.MAIN('SYS_IMPORT_SCHEMA_01', 'SYSTEM');
Thu Nov 24 09:43:11 2011
statement in resumable session 'SYSTEM.SYS_IMPORT_SCHEMA_01.1' was suspended due to
ORA-01652: unable to extend temp segment by 128 in tablespace TESTB
Thu Nov 24 10:00:45 2011
Thread 1 advanced to log sequence 4761 (LGWR switch)
Current log# 3 seq# 4761 mem# 0: /opt/oradata/mcrm/redo03.log
4、查询TESTB表空间使用情况
SQL> select bytes/1024/1024,maxbytes/1024/1024,user_bytes/1024/1024
2 from dba_data_files where tablespace_name='TESTB';
BYTES/1024/1024 MAXBYTES/1024/1024 USER_BYTES/1024/1024
--------------- ------------------ --------------------
2000 2000 1998.9375
5、解决问题
Thu Nov 24 10:04:21 2011 alter tablespace TESTB add datafile '/opt/oradata/mcrm/testb01.dbf' size 100m autoextend on next 1m maxsize 30g Thu Nov 24 10:04:25 2011 Completed: alter tablespace TESTB add datafile '/opt/oradata/mcrm/testb01.dbf' size 100m autoextend on next 1m maxsize 30g Thu Nov 24 10:04:26 2011 statement in resumable session 'SYSTEM.SYS_IMPORT_SCHEMA_01.1' was resumed
通过这个日志可以看出，表空间不足的问题解决后（可以添加数据文件，或者resize数据文件大小），impdp的job又开始运行
