Thursday, December 30, 2010

11gR2 DataPump Import hang during "Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX" stage

Environment
2-Nodes 11.2.0.2 Oracle RAC on OEL 5.5x64bit platform

Problem
I kicked off Datapump Import around 10 PM at night on 12/29/10, 12 hours later, the import log file shows that it's doing "Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX", the last time the log file was updated was at 23:31 on 12/29/10, so it means the import job has been waiting on this "Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX" for almost 11 hours.

Cause
1 Check v$session
select username,machine,module,sid,state,event,seq#,seconds_in_wait from v$session where type<>'BACKGROUND' and username='FPP'



Or
2 Query v$session_wait view

SELECT sid, event, seconds_in_wait 
FROM v$session_wait WHERE sid = $SID;


Or
3 Query v$system_event or v$session_event view

SELECT event, total_waits, time_waited FROM v$system_event WHERE event like '%suspend%';

select sid, event, total_waits, time_waited FROM v$session_event WHERE event like '%suspend%';

4 Check dba_resumable view to see when did the suspend happened
SQL> select user_id,session_id, status, start_time, suspend_time,resume_time, error_msg from dba_resumable;
So FPPI tablespace is out of space.

5 Check FPPI tablespace usage to check its status.
SQL> select tablespace_name, file_name, maxbytes/1024/1024, user_bytes/1024/1024 from dba_data_files where tablespace_name='FPPI';


So based on the above views, the FPPI tablespace is out of space, add more datafile to this tablespace would resume the Datapump Import job.


Solution
Add more datafiles to FPPI tablespace will resume the Datapump Import job.
Before adding more datafile:

SQL> alter tablespace fppi add datafile '/u08/oradata/racflo/FPPI04.dbf' size 1g autoextend on;


Tablespace altered.

After the above command:

Related Links

Resumable Space Management - (Oracle 9i) by Jeff Hunter


Labels: , , ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home