Coskan’s Approach to Oracle

March 21, 2007

Using Datapump Restart Capability for ORA-39095

Filed under: Tips — coskan @ 8:28 am

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:

About these ads

11 Comments »

  1. 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

  2. Try SQL>PURGE SYS_EXPORT_TABLE_03.

    Comment by luzz — March 5, 2008 @ 11:04 am

  3. 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

  4. 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

  5. excellent tip Ayoup. Thank you very much for your comments .

    Comment by coskan — July 4, 2008 @ 4:10 pm

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers

%d bloggers like this: