1 2 Previous Next 23 Replies Latest reply: Dec 6, 2012 3:25 PM by khallas301 RSS

    Export error - expdp

    khallas301
      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
          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
            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 .
              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
                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 .
                  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
                    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
                      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
                        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
                          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
                            Still no luck with anything...

                            I noticed problem only starts after 2 hours of job running...
                            • 11. Re: Export error - expdp
                              User286067
                              anything in alert logs?
                              • 12. Re: Export error - expdp
                                User286067
                                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
                                  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
                                    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