IMPDP hangs... because FRA is full

I got a call from one of my DBA friends about a datapump (IMPDP) that hanged for almost 4hours without an error. The DBA had to cancell the job and retry again. The same thing happened; the job would hanged and not complete.

When contacted about this issue, I asked some pertinent questions that helped in resolving this issue: Is the database in archivelog mode? Is the Flash Recovery Area (FRA) full? When the DBA checked, both answers were YES. The Flash Recovery Area was filled so Datapump could not process the remaining part of the data. While I still don't understand the reason this is happening, I will go ahead and provide you with the steps I took to help this friend overcome the problem.

Flash Recovery Area  is a central location where files, especially archivelog files and backupsets are kept. Usually, datapump generate logs during its operations unlike some direct load mechanism (e.g, SQL*Loader). What my DBA friend didn’t know is that the size of the flash recovery area can actually stop an import (imp or impdp) operation.

Let’s use this as a case study:

Task: Import a schema called DENTIST

Check the size of the Flash Recovery Area

SQL> select space_used/1024/1024/1024, space_limit/1024/1024/1024 from v$recovery_file_dest;

SPACE_USED          SPACE_LIMIT
------------------------- --------------------------
3.75916004                3.79101563

Increase the Flash Recovery Area

SQL> alter system set db_recovery_file_dest_size=30G;

System altered.

SQL> exit

Run datapump again

[oracle@DENTIST]$ impdp system/*****@DENTIST directory=DENTIST schemas=DENTIST dumpfile=DENTIST.DMP logfile=DENTIST.log

Import: Release 11.2.0.1.0 - Production on Wed Jan 15 11:25:23 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@DENTIST directory=DENTIST schemas=DENTIST dumpfile=DENTIST.DMP logfile=DENTIST.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DENTIST"."ATTACHMENTS" 2.543 GB 1259 rows
. . imported "DENTIST"."PATIENT_PROFILE_PHOTOS" 919.1 MB 1489 rows
. . imported "DENTIST"."MEDICAL_RECORDS" 56.54 MB 33309 rows
. . imported "DENTIST"."ORDER_STATUS_HISTORY" 27.90 MB 257077 rows
. . imported "DENTIST"."NOTES" 18.03 MB 87500 rows
. . imported "DENTIST"."ORDER_DETAILS_AUDIT" 0 KB 0 rows
. . imported "DENTIST"."ORDER_STATUS_HISTORY_AUDIT" 0 KB 0 rows
. . imported "DENTIST"."PATIENTS_AUDIT" 0 KB 0 rows
. . imported "DENTIST"."ROLE_REPORTS" 0 KB 0 rows
. . imported "DENTIST"."SURVEY_RESPONSES_AUDIT" 0 KB 0 rows
. . imported "DENTIST"."SURVEY_RESPONSE_SETS_AUDIT" 0 KB 0 rows
. . imported "DENTIST"."USERS_AUDIT" 0 KB 0 rows
. . imported "DENTIST"."USERS_ENCRYPTED" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed at 13:13:24

At this time, the datapump job was successfully done.

Check the size of the Flash Recovery Area

SQL> select space_used/1024/1024/1024, space_limit/1024/1024/1024 from v$recovery_file_dest;

SPACE_USED         SPACE_LIMIT
------------------------- --------------------------
5.5317564                 30

The size of the Flash Recovery Area has increased from 3.7G to 5.5G. This means that the increment in the Flash Recovery Area caused the hanging of the datapump tool.