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.

Connecting MySQL Database to SQL Developer

If you're one of those few people struggling to connect MySQL database (local or remote) to SQL*Developer, then this post is for you.

Step 1: Download the connector from Oracle/MySQL website.

Here is a link to the connector. You can actually download without registering on the website (MySQL allows this). However, if you are already a registered Oracle user, your credentials are also useable on this website.

Step 2: Locate the "jar" file

After the download, click on "Run" and the jar file will be automatically placed in the MySQL folder under Program Files directory.

Look under

C:\Program Files (x86)\MySQL



jar file is C:\Program Files(x86)\MySQL\MySQLConnectorJ\mysql-conector-java-5.1.28-bin.jar

Step 3: launch SQL Developer



Step 4: Configure the connector

The first step here is to click on "Tools" under the main menu



Second step is to click on "preferences"



Third step is to click on "database" and select "Third Party JDBC Driver"



Click on "Add entry" and locate the jar file in "C:\Program Files(x86)\MySQL\MySQLConnectorJ\mysql-conector-java-5.1.28-bin.jar"

Click "OK"

From the main page, click on the connect icon.

Step 5: Click on the "connect" icon (in green) and fill in the required login information


Connection name: Any name you want to call the connection
Username: This is the user account on the MySQL server, i.e. root ('root'@'localhost')
Password: user's password
Hostname: you can leave this as localhost or replace with the name of the host server. If this is a remote server, replace with IP address or hostname of the remote server.
Port: Leave this as 3306 (default). If your port number has been changed from default, you can replace with the value


Step 6: Connect to MySQL database


You can "Test Connection" before you save the credentials.

Thanks for reading and happy 2014!