1 2 Previous Next 27 Replies Latest reply: May 2, 2012 9:39 AM by User286067 RSS

    expdp gets stuck

    Pritam
      Hi,

      We are trying to take an export from out existing database. But somehow, everytime we give the expdp command, it created a 4KB dmp file and gets stuck there. We have tried exporting a single table as well, but still the same. It does not even throw any error and nothing is written in the log file.

      The status in dba_datapump_jobs shows that this job is executing. But no progress. I have even kept this job running for more than 12 hrs but still no progress.

      DB Version is : 11.2.0.1.0
      OS      : Windows 2003 Server (64-bit) SP2

      Following is the status in the command window

      C:\Documents and Settings\sysfmg>expdp system/******** full=y directory=UTF_CONVERT parallel=4 dumpfile=New_Full_Dump_%U.dmp logfile=New_Full_Dump.log

      Export: Release 11.2.0.1.0 - Production on Thu Apr 26 09:36:35 2012

      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
      Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** full=y directory=UTF_CONVERT parallel=4 dumpfile=New_Full_Dump_%U.dmp logfile=New_Full_Dump.log



      Export status is shown as below

      Export> status

      Job: SYS_EXPORT_FULL_01
      Operation: EXPORT
      Mode: FULL
      State: EXECUTING
      Bytes Processed: 0
      Current Parallelism: 4
      Job Error Count: 0
      Dump File: F:\UTF_CONVERT\New_Full_Dump_01.DMP
      bytes written: 4,096
      Dump File: F:\UTF_Convert\New_Full_Dump_%u.dmp

      Worker 1 Status:
      State: UNDEFINED


      Request some help regarding this issue.
        • 1. Re: expdp gets stuck
          Niket Kumar
          Can you provide the output of New_Full_Dump.log file.
          • 2. Re: expdp gets stuck
            Pritam
            Niket,

            It is not writing anything in the log file. It is still 0KB size.
            • 3. Re: expdp gets stuck
              Niket Kumar
              Try with sys user.

              if sys user works there may be access permission issue on directory.
              • 4. Re: expdp gets stuck
                Pritam
                I'd tried with sys user as well but no success.

                Infact, i even tried to export a single table from one of the schema that we have created, but still it gets stuck.
                • 5. Re: expdp gets stuck
                  Girish Sharma
                  In addition to points which I have mentioned in below link (see my last post in the link)
                  export hangs

                  6.expdp directly on the server, instead of doing it from my local machine.
                  7.Maybe due to a compatibility problem between my Oracle client (10.2) and server versions (11.2)
                  8.Try to google with "expdp hangs oracle 11g". Just double check the reasons (not assume) which you will get from the link's replies.

                  Regards
                  Girish Sharma
                  • 6. Re: expdp gets stuck
                    Niket Kumar
                    you have only one database on server?
                    • 7. Re: expdp gets stuck
                      Pritam
                      Yes Niket. There's only one database.
                      • 8. Re: expdp gets stuck
                        AliD
                        Check the database session for the wait event. If I recall correctly, either MODULE or PROGRAM column of v$session will be expdp.
                        • 9. Re: expdp gets stuck
                          amardeep.sidhu
                          Try running it with ACCESS_METHOD=EXTERNAL_TABLE (Default is AUTOMATIC), for one table may be. I remember reading about a bug.

                          Stuck somewhere right now. Will try to find and post the details.
                          • 10. Re: expdp gets stuck
                            Pritam
                            @Girish,

                            I followed all the points mentioned by you. it is still the same.
                            All the while I was trying this expdp directly from the server.

                            following was the expdp command that I had given

                            * expdp system/******** full=y directory=UTF_CONVERT parallel=4 dumpfile=New_Full_Dump_%U.dmp logfile=New_Full_Dump.log *

                            On my server, the SDU is set to *30000* and MTU is *1500*

                            Listener is working fine. I can connect to the DB properly and can fire any queries.


                            @Amardeep
                            Even single table export with ACCESS_METHOD=EXTERNAL_TABLE does not solve the issue. it still gets stuck.

                            Edited by: 887983 on Apr 26, 2012 12:58 AM
                            • 11. Re: expdp gets stuck
                              amardeep.sidhu
                              In that case you need to see what data pump jobs are doing. Run the expdp for one table and check wait events for data pump job's session. You may want to expdp a really small table. Eg few MBs. It could be some bug.

                              Edited by: amardeep.sidhu on Apr 26, 2012 2:26 PM
                              • 12. Re: expdp gets stuck
                                amardeep.sidhu
                                Tracing the data pump process is another thing you may want to try. Metalink note 286496.1
                                • 13. Re: expdp gets stuck
                                  Girish Sharma
                                  I followed all the points mentioned by you. it is still the same.
                                  Ok, no problem, but you are not showing / answering my question(s) i.e.

                                  1.What happens if you start exptp with trace=y and show the contents of trace file.
                                  2.What is output of below command (to see invalid objects in SYS schema), by SYSDBA user :
                                  select count(*) from all_objects where status='INVALID' and owner='SYS';
                                  3.Just try to see that there is no issue with expdp binaries by creating another test db and then run expdp for this new db. And then run catalog.sql after creation.
                                  4.How are you checking that there is no compatibility problem between Oracle client (10.2) and server versions (11.2) ?
                                  5.Is there anything in alert log ?
                                  6.What is status in dba_datapump_jobs ?
                                  7.check v$session_wait for that particular program.
                                  8.Check for BLOCKING_SESSION_STATUS in v$session for particular SID.
                                  9.What happens if you just try to give EXP_FULL_DATABASE role to another user and then starts expdp with another user ?

                                  At least I will not further reply, if I don't see copy and paste answer of above questions. Please don't say that "it is not working" or "it is stuck"; these seems rude as well as frustrating, show us what and how you are doing to get the solution and what and how Oracle is responding.

                                  Regards
                                  Girish Sharma

                                  Edited by: Girish Sharma on Apr 26, 2012 2:35 PM
                                  Typo corrected.
                                  • 14. Re: expdp gets stuck
                                    amardeep.sidhu
                                    4.How are you checking that there is no compatibility problem between Oracle client (10.2) and server versions (11.2) ?
                                    But this one doesn't apply in his case. He is doing it on the server. No ?
                                    1 2 Previous Next