This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Dec 6, 2012 1:25 PM by khallas301 RSS

Export error - expdp

khallas301 Newbie
Currently Being Moderated
Hello gurus...

DB v: 11.2.0.3
expdp file estimation: 70GB
Excludes: 500 tables
Using parfile
Target: Schema refresh

I am trying to export schema excluding 500 odd tables with expdp estimation of 70GB dump file size..
created temp table listing all table which needed to exclude from export and using select query in EXCLUDE parameter... e.g. EXCLUDE=TABLE:'IN (SELECT tbl FROM list_of_tables)
also using FLASHBACK_TIME=TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') in parfile..

Tried few different things:
1. Used expdp with 3 channels
error: export writes to 3 files till 50GB then stops and only writes to 1 file after 2 hours. First 2 hours writing speed is quick then suddenly it really really slows down... and ora-0155 after waiting for 5 hours..

2. Used expdp without any channels
error: same as above... performance issue occurs after 2 hours... after 5 hours many tables skipped and ora-0155 snapshot old happens....

Trace file mentions about wait on different block but it's not consistent on 1 block...
read 100's of metalink doco but all points different location..

any other ways to tackle this problem??
  • 1. Re: Export error - expdp
    User286067 Journeyer
    Currently Being Moderated
    khallas301 wrote:
    Hello gurus...

    DB v: 11.2.0.3
    expdp file estimation: 70GB
    Excludes: 500 tables
    Using parfile
    Target: Schema refresh

    I am trying to export schema excluding 500 odd tables with expdp estimation of 70GB dump file size..
    created temp table listing all table which needed to exclude from export and using select query in EXCLUDE parameter... e.g. EXCLUDE=TABLE:'IN (SELECT tbl FROM list_of_tables)
    also using FLASHBACK_TIME=TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') in parfile..

    Tried few different things:
    1. Used expdp with 3 channels
    error: export writes to 3 files till 50GB then stops and only writes to 1 file after 2 hours. First 2 hours writing speed is quick then suddenly it really really slows down... and ora-0155 after waiting for 5 hours..

    2. Used expdp without any channels
    error: same as above... performance issue occurs after 2 hours... after 5 hours many tables skipped and ora-0155 snapshot old happens....

    Trace file mentions about wait on different block but it's not consistent on 1 block...
    read 100's of metalink doco but all points different location..

    any other ways to tackle this problem??
    I'll hazard a guess, since it writing to only one file, it means it is still writing your largest tablel.

    Couple of ideas,
    1. try using access_method=external_table (it may or may not help but worth a try)
    2. exclude your largest table as well, and run a seperate expdp just for that one table with parallel option.

    Oracle does creates internal list of all tables to export in the desc order of size and starts allocating one table for each worker thread. Which is why you see 3 files being written in the beginning, then 2 stop and only one writes since probably your largest table is still being written. It will also help if your stats are accurate (or relatavely new) for Oracle to make a good judgement call on size. Also why only 3 files?

    Raj
  • 2. Re: Export error - expdp
    Dean Gagne Expert
    Currently Being Moderated
    Since there is only 1 worker working, it is either finishing the metadata or finishing the data. You could type a ctrl-c and get to the

    Export>

    prompt. At that prompt, you can type a status command to see what is happening. It will tell you what each worker is doing. Two of the workers should be idle and the other should either be doing the metadata or the data.

    Typing the ctrll-c does not stop the job, it just stops printing the status to the x-term. You can get back to your normal window by typing continue. If you don't want to type ctrl-c you can attach to the job in another x-term. Do this:

    expdp <user>/<password> attach=<job_name>

    The job name is either something you specified on the expdp command line, or it will be something like:

    sys_export_full_01

    After attaching, you will get to the Export> prompt and you can type status there.

    Hope this helps.

    Dean
  • 3. Re: Export error - expdp
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    I've seen this behaviour commonly with lob columns - they are much slower - if you interrupt the export as Dean says you can probably see which table it is 'stuck' on. There is probably not a lot you can do other than increase undo retention to avoid 1555 error later on.

    Regards,
    Harry
  • 4. Re: Export error - expdp
    Dean Gagne Expert
    Currently Being Moderated
    You can perform this query to see what data is left to export:

    select sum(dump_orig_length), processing_state
    from "SYSTEM"."SYS_IMPORT_FULL_01“ -- < your master table/ job name goes here
    where process_order > 0 and duplicate = 0 and
    object_type = 'TABLE_DATA‘
    group by processing_state;

    SUM(DUMP_ORIG_LENGTH) P
    --------------------- -
    2525400 R -- exported
    2324944 E -- estimated

    Rows with 'R' have already been exported
    Rows with 'E' still have to be exported.

    Hope this helps

    Dean
  • 5. Re: Export error - expdp
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    And by the way you can just say
    flashback_time=systimestamp

    much easier than all that other code.....

    Harry
  • 6. Re: Export error - expdp
    khallas301 Newbie
    Currently Being Moderated
    Experts... thanks for all valuable inputs which really gives me extra idea...

    Try-3
    /* parfile */
    directory= data_pump_refresh
    dumpfile= abc.dmp
    logfile= abc.log
    schemas= abc
    exclude=statistics, object_grant, table:"in(select tbl from list_of_tables)"
    flashback_time="systimestamp"

    Read on metalink regarding excluding object_grant and try expdp again.. but no use
    parallel = 3 used earlier as experiment but then decided to go without it and try..

    Status:
    % done = 62
    completed obj = 24
    total obj = 1,736

    Issue:
    1. Export goes really slow after 2 hours and at same table export
    2. I am certain after waiting for few more hours it will fail again with snapshot error...

    Q:
    1. Is there issue with PGA size?
    2. How about trying original exp imp method?
    3. Does invalid user object has any effects?
    4. Including obj grants again?
  • 7. Re: Export error - expdp
    khallas301 Newbie
    Currently Being Moderated
    Raj...

    Couple of ideas,
    1. try using access_method=external_table (it may or may not help but worth a try)
    will give it a try...

    2. exclude your largest table as well, and run a separate expdp just for that one table with parallel option.
    how to identify which table to run export separately?
    but problematic table/ large table does gets export properly when parallel parameter has been used...
  • 8. Re: Export error - expdp
    khallas301 Newbie
    Currently Being Moderated
    Another thing I have noticed that export goes to possible hang during executing below sql;

    begin sys.kupw$worker.main('sys_export_schema_01','sys',0); END;
  • 9. Re: Export error - expdp
    Dean Gagne Expert
    Currently Being Moderated
    This is not a hang, this is the call that starts the Data Pump worker process that does all of the actual exporting and importing. You can ignore this sql when looking at awr reports.

    Dean
  • 10. Re: Export error - expdp
    khallas301 Newbie
    Currently Being Moderated
    Still no luck with anything...

    I noticed problem only starts after 2 hours of job running...
  • 11. Re: Export error - expdp
    User286067 Journeyer
    Currently Being Moderated
    anything in alert logs?
  • 12. Re: Export error - expdp
    User286067 Journeyer
    Currently Being Moderated
    khallas301 wrote:
    Raj...

    Couple of ideas,
    1. try using access_method=external_table (it may or may not help but worth a try)
    will give it a try...
    I mentioned that because we had a situation where one table caused lot of grief for out backup, it just wasn't too big but expdp was too slow on that and it was hurting out EOD performance. Changing access_method basically fixed the issue, hence I suggested you try with it.

    Raj
  • 13. Re: Export error - expdp
    khallas301 Newbie
    Currently Being Moderated
    nothing interesting or ora- errors in alert log....

    if we analyze schema before export will that makes difference in export performance?
  • 14. Re: Export error - expdp
    Dean Gagne Expert
    Currently Being Moderated
    if we analyze schema before export will that makes difference in export performance?
    Probably not. If you export and don't specify estimate=statistics, then nothing will change in how parallel is decided. If you specify estimate=statistics, then the statistics will be used to determine what tables get exported with what degree of parallelism. The block count would have to be way off in order for that to change though.

    Dean
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points