Using Datapump Restart Capability for ORA-39095

I couldn’t update my blog for 4 days because I was struggling with installing RAC on my new laptop with my colleague Orhan BIYIKLIOGLU who is the best technology man and sysadmin I have ever met (He is something like walking wikipedia of technology). If he didn’t help me I would be still working on the error messages of ASM and Linux etc. Thank you Orhan :). I will tell the story of installing RAC on my laptop soon but todays topic is about restart capability of datapump jobs. Yesterday night I had a call from the operator about failed export job messages on Open View console.

When I look at the export log of daily routine schema exports, I saw that only one table skipped with error code

ORA-31693: Table data object ****** failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists

After a little search on Metalink I found only a bug which did not suit enough (Metalink Note:3853023.8).

I decided to take another data pump export for only the skipped table. After 30 seconds from expdp command data pump job took the error below

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job “SYSTEM”.”SYS_EXPORT_TABLE_03″ stopped due to fatal error at 23:51

I checked the free space on data_pump_dir and saw that there was enough space.

The jobs descriptions says the action for the solution is to reattach the job and add additional dump files.
Error: ORA-39095 (ORA-39095)
Text: Dump file space has been exhausted: Unable to allocate %s bytes
—————————————————————————
Cause: The Export job ran out of dump file space before the job was
completed.
Action: Reattach to the job and add additional dump files to the job
restarting the job.

I looked to the manual and thought that this is something about the unsetted filesize parameter on parfile. My last decision about the problem; the table was huge (over 80GB) so the datapump couldn’t calculate an initial size and thought that there wasn’t enough space on the data pump directory.

I queried the dba_data_pump_jobs view about the status of the job

SQL> select job_name,state from dba_datapump_jobs;

JOB_NAME STATE
—————————— ——————————
SYS_EXPORT_TABLE_03 NOT RUNNING

To solve the problem I have to add another file to the job and restart it. But how ?? All the answer is in the online Oracle® Database Utilities 10g Release 1 (10.1) documentation . The commands attach , add_file,continue_client . (Do not put ; after the commands !!! )

so I connected to the data pump console and run the commands below

expdp system/***** attach=SYS_EXPORT_TABLE_03

Export: Release 10.1.0.4.0 – 64bit Production on Tuesday, 20 March, 2007 23:53

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Job: SYS_EXPORT_TABLE_03
Owner: SYSTEM
Operation: EXPORT
Creator Privs: FALSE
GUID: 2C22B38216C04297E044000E7FEDD1FD
Start Time: Tuesday, 20 March, 2007 23:53
Mode: TABLE
Instance: *****
Max Parallelism: 4
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** parfile=/backup/****/PARFILES/userdata_tablename.dat
DATA_ACCESS_METHOD AUTOMATIC
ESTIMATE BLOCKS
INCLUDE_METADATA 1
LOG_FILE_DIRECTORY DATA_PUMP_DIR2
LOG_FILE_NAME expdp_tablename_20032007.log
TABLE_CONSISTENCY 0
State: IDLING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /backup/****/expdp_tablename_20032007.dmp
bytes written: 180,224

Worker 1 Status:
State: UNDEFINED

Worker 2 Status:
State: UNDEFINED
Object Schema: *****
Object Name: *****
Object Type: TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1

Worker 3 Status:
State: UNDEFINED

Worker 4 Status:
State: UNDEFINED

Export>add_file=expdp_tablename_20070320_2.dmp

Export> continue_client
Job SYS_EXPORT_TABLE_03 has been reopened at Tuesday, 20 March, 2007 23:53
Restarting “SYSTEM”.”SYS_EXPORT_TABLE_03″: system/******** parfile=/backup/******/PARFILES/userdata_tablename.dat
. . exported “********”.”*******” 72.46 GB 208475338 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_03″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
/backup/*****/expdp_tablename_20070320.dmp
/backup/****/expdp_tablename_20070320_2.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_03″ completed with 1 error(s) at 00:47

References Used :

Oracle® Database Utilities 10g Release 1 (10.1)

Metalink Note:289404.1

Metalink Note: