10 Replies Latest reply on Jul 5, 2018 11:03 AM by SPA09

    free tool for analyzing DMP file

    SPA09

      Hi all,

       

      does somebody know a free tool not (exp / imp) to analyze or repair or unload a Oracle DMP-File?

       

      Does somebody have experience with analyzing or repairing Oracle DMP-Files?

       

      regards

        • 1. Re: free tool for analyzing DMP file
          Dean Gagne-Oracle

          So you say not exp/imp, does this mean that you are using Data Pump export and import?  If you use Data Pump, (expdp/impdp) then you can use impdp to analyze your dumpfile.  You simply add:

           

          sqlfile=my_objects.sql

           

          to your import command and then you can edit my_object.sql to see what objects are going to be imported.  It will not show you anything about data.

           

          I don't know of anything that will repair the dumpfile.  Maybe if you tell us what you are seeing for errors, there may be a solution already out there for you.  Also, please verify what type of oracle dump file you are looking at.

           

          Thanks

           

          Dean

          • 2. Re: free tool for analyzing DMP file
            SPA09

            Hi,

            no I have a "may be" corrupted DMP file from exp created.

             

            I am searching for a free tool to check repair etc. the DMP file.

             

            This is not a expdp File.

            Oracle Release is 12.1.0.2

             

             

            regards

            • 3. Re: free tool for analyzing DMP file
              Andris Perkons-Oracle

              I do not think that a tool like that is available. The dmp file format is proprietary/undocumented. The best I have found is a tool by "Intelligent Converters" (https://www.convert-in.com/oracle-dump.htm) which claims to be able to read dump files. Still, it does not mention the ability to repair or check those files and it is not free. Your best bet IMHO is to try to import the "may be corrupted" dmp file into a newly created database and see what happens.

               

              Andris

              • 4. Re: free tool for analyzing DMP file
                SPA09

                Hi,

                okay, I saw this tool.

                 

                Yes and I know that I can do imp with option show=y

                 

                 

                regards

                • 5. Re: free tool for analyzing DMP file
                  Gaz in Oz

                  MAKE SURE YOU HAVE A BACKUP COPY OF THE FILE.

                   

                  Use a HEX editor. vi is one way, filtering through xxd:

                  vi -b thedumpfile.dmp
                  :%!xxd
                  

                  (If you don't currently have vi on your OS then: https://www.vim.org/)

                  notepad++ is another way. It has a HEX Editor plugin available for it.

                   

                  Examine the dmp using DBMS_DATAPUMP.GET_DUMPFILE_INFO()

                  Header reader example using dbms_datapump.get_dump_info():

                  https://databaseinternalmechanism.com/2016/09/13/how-to-read-datapump-dumpfile-header/

                  • 6. Re: free tool for analyzing DMP file
                    SPA09

                    Hi,

                     

                    that is all written in Note 462488.1 Get header Info but only from data pump.

                     

                    For Export files it does not work.

                     

                    regards

                    • 7. Re: free tool for analyzing DMP file
                      Gaz in Oz

                      SPA09 wrote:

                      Hi,

                       

                      that is all written in Note 462488.1 Get header Info but only from data pump.

                       

                      For Export files it does not work.

                       

                      regards

                      c:\Temp>exp userid=gaz/gaz@xe owner=gaz log=exp_gaz.log file=exp_gaz.dmp statistics=estimate
                      ...
                      

                       

                      ...then using dbms_datapump.

                      SQL> set serverout on
                      SQL> exec show_dumpfile_info('DIR_TEMP', 'exp_gaz.dmp');
                      --------------------------------------------------------------------------------
                      Purpose..: Obtain details about export dumpfile.        Version: 19-MAR-2015
                      Required.: RDBMS version: 10.2.0.1.0 or higher
                      .          Export dumpfile version: 7.3.4.0.0 or higher
                      .          Export Data Pump dumpfile version: 10.1.0.1.0 or higher
                      Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
                      Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
                      --------------------------------------------------------------------------------
                      Filename.: exp_gaz.dmp
                      Directory: DIR_TEMP
                      Disk Path: C:\Temp
                      Filetype.: 2 (Original Export dumpfile)
                      --------------------------------------------------------------------------------
                      ...Characterset ID.: 178 (WE8MSWIN1252)
                      ...Direct Path.....: 0 (Conventional Path)
                      ...Export Version..: EXPORT:V12.02.00
                      --------------------------------------------------------------------------------
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL>
                      

                      Note line 14.

                       

                      Before you say something doesn't work, you should really try it first or at least analyze the code to see if it is capable of what you are being advised.

                      • 8. Re: free tool for analyzing DMP file
                        Gaz in Oz

                        ...and don't forget, not everyone has access to support.oracle.com to view notes. If it really does mention vi, that's cool.

                        • 9. Re: free tool for analyzing DMP file
                          Andris Perkons-Oracle

                          Back to the original question: How does extracting header information from a dmp file help analyzing or repairing it?

                          Sifting through a dmp file with a hex editor, how will I see a corruption if I don't even know how a "normal" dmp file has to look like?

                          I guess my initial recommendation still stands: Try importing the data and see what happens.

                           

                          SPA09, would you mind telling us more about the use case?

                          • 10. Re: free tool for analyzing DMP file
                            SPA09

                            Hi,

                             

                            @Gaz in Oz

                            Yes, of course some users have no access to Metalink but these are users which normally use the Oracle XE Edition.

                             

                            Yes of course some more details.

                             

                            A Software Partner did a exp from a schema on his own machine. (Windows)

                            It was copied via binary mode. So far so good.

                            This DMP file came to us to imp the files but we get IMP-00008 unrecognized statement in the export file.

                             

                            I do a lot tests including imp show=y etc. I think the migration of this underlying database from 11.2 -> 12.1

                            was not done correctly and so we have character mismatch.

                             

                            But character scanner and DMU does not support exp dump files.

                             

                            regards