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:
This error may be caused by PARALLELISM in EXPDP jobs.
Don’t use parallel clause.
Eduardo Valentim
OCA
Fortaleza-CE
Brazil
Comment by Eduardo Valentim — February 3, 2008 @ 3:24 am
Try SQL>PURGE SYS_EXPORT_TABLE_03.
Comment by luzz — March 5, 2008 @ 11:04 am
Eduardo has right. It is really problem if you use PARALLEL. I had a same problem and without parallel everything just gone fine.
Than You very much.
Comment by Mladen — June 23, 2008 @ 12:39 pm
You have to create a dump file for each parallel process.
Best solution is DUMPFILE=expdata_%U.dmp.
Ayoub BENSEGHIR
FRANCE
Comment by Ayoub BENSEGHIR — July 3, 2008 @ 11:01 am
excellent tip Ayoup. Thank you very much for your comments .
Comment by coskan — July 4, 2008 @ 4:10 pm
Thanks Ayoub excellent Tip,
We have an alternative solution for those who have already started their expdp job.
what i did was i had given parallel=5 and dumpfile as only 1 file_name.
so i attached to that expdp job and added 4 more files.
eg.
expdp attach=SYS_EXPORT_SCHEMA_02
Export> add_file=exppd_09Feb_02.dmp
Export> add_file=exppd_09Feb_03.dmp
Export> add_file=exppd_09Feb_04.dmp
Export> add_file=exppd_09Feb_05.dmp
Comment by Nasir — February 9, 2009 @ 1:22 pm
hi everyone,
in my case, i just changed the number of parallel. (my english is vary bad!)
bye!
Luciano Barros Pires.
Comment by Luciano Barros Pires — February 14, 2012 @ 2:50 pm
I think the admin of this site is really working hard in favor
of his website, since here every stuff is quality based
information.
Comment by http://www.totalgolfmove.com/groups/vital-details-for-jailbreak — January 21, 2014 @ 3:08 am
Somewhat you need to get It from Cydia, which is an application mounted only on
jailbroken iPhones. Unfortunately, Installous is not outlined as an iOS 6-compatible cydia tweak however.
Comment by redsn0w jailbreak 6.1.3 — January 22, 2014 @ 6:50 am
This piece of writing will help the internet viewers for creating new webpage or even a weblog
from start to end.
Comment by Sylvia — February 5, 2014 @ 2:48 pm
[…] https://coskan.wordpress.com/2007/03/21/datapump-restart-capability-for-ora-39095/Export> status […]
Pingback by 11i/R12 Scripts | Shahzad Khan's — February 25, 2014 @ 9:17 am
use compression=all
Comment by Bram — October 6, 2016 @ 1:17 pm
about kiss918
Using Datapump Restart Capability for ORA-39095 | Coskan’s Approach to Oracle
Trackback by about kiss918 — March 11, 2021 @ 5:11 pm