1 2 Previous Next 18 Replies Latest reply on Oct 1, 2019 2:27 PM by Dean Gagne-Oracle

    TTS import running long

    3696858

      Dear Community,

       

      We are doing database migration from Solaris (12.1) to Linux (12.2) of 4 TB data.

       

      Stating the we are using TTS export and import mechanism. unfortunately for import we are having run time of 140 plus hours which is not acceptable by customer, so am looking at your advise to see what we can improve the action / speed up the process. Below is the par file which has parameters in it.

       

      File impdp_UF3_tts.par

       

      userid="/ as sysdba"

                directory=dpdir

                dumpfile=expdp_UF3_tts.dmp

                logfile=impdp_UF3_tts.log

                transport_datafiles='/oracle/UF3/oradata3/SOFDW_INDX_7.dbf',

      '/oracle/UF3/oradata3/tools01.dbf',

      '/oracle/UF3/oradata3/xdb01.dbf',

      '/oracle/UF3/oradata3/SOFDW_DATA_1.dbf',

      -

      -

      -

      -

       

      and

       

      par file -   impdp_UF3_full_norows.par

       

      userid="/ as sysdba"

                directory=dpdir

                dumpfile=expdp_UF3_full_norows.dmp

                PARALLEL=1

                logfile=impdp_UF3_full_norows.log

                full=y

                content=metadata_only

                table_exists_action=skip

                ACCESS_METHOD=INSERT_AS_SELECT

                exclude=table_statistics

       


      Thanks

        • 1. Re: TTS import running long
          Dean Gagne-Oracle

          I see a couple of things and I'm a bit confused on others.

           

          1. please don't use '/as sysdba' when running import.   This is documented to used only when directed by Oracle Support.   This can actually cause issues.

           

          2. Why do you have 2 impdp par files?  Are you running multiple imports?

           

          3. Can you list your expdp parfile(s).

           

          4. When you run content=metadata_only, I don't think acess_method=insert_as_select is needed. Also insert_as_select is going to be the slowest possible way for you to import any data that is in the dumpfile.

           

          Since you are coming from version 12 and going to version 12, I would think that you would want to user what we call 'full transportable'.  It is one import and one export command that does it all.  If you have a network link between the 2 database, it could be 1 singe import command.

           

          For the 2 command scenario it would be something like:

           

          expdp system/manager transportable=always full=y directory=my_dir dumpfile=mydump.dmp logfile=mylog.log ...

           

          This will export all data that is transportable using the transportable syntax but also grabs all of the other objects that you need for a migration.  (users, privs, grants, procedures, triggers, etc.)

           

          Then you have 1 import command

           

          impdp system/manager full=y directory=my_dir dumpfile=mydump.dmp logfile=mylog transport_datafile=a, b, c, d, e...  (list all your datafiles here).   "Best if in a par file".

           

          You can also add:     metrics=y

           

          to tell you what you are working on and how long each object type takes.

           

          Let me know if you have any questions.

           

          Dean

          • 2. Re: TTS import running long
            3696858

            Dear Dean,

             

            Thanks for replying and giving us your suggestions to reduce runtime of import, please see below you requested for,

             

            1. please don't use '/as sysdba' when running import.   This is documented to used only when directed by Oracle Support.   This can actually cause issues.

             

             

            Yes, you are right we should not supposed to use sysdba, we will be correcting that with system for next run onward.

             

            2. Why do you have 2 impdp par files?  Are you running multiple imports?

             

            We are using for tablespace import and second one for metadata import.

             

            3. Can you list your expdp parfile(s).

             

             

            more expdp_UF3_tts.par

            userid="/ as sysdba"

                      directory=dpdir

                      dumpfile=expdp_UF3_tts.dmp

                      logfile=expdp_UF3_tts.log

                      transport_full_check=y

                      transport_tablespaces=TOOLS, XDB, SOFDW_DATA, SOFDW_INDX, CV_DATA, SOFDW_DATA_ACTIVE, SOFDW_INDX_ACTIVE, SOFDW_DATA0, SOFDW_DATA1, SOFDW_DATA2, SOFDW_DATA3, SOFDW_DATA4, SOFDW_DATA5, SOFD

            W_DATA6, SOFDW_DATA7, SOFDW_DATA8 .......

             

             

             

             

            and

             

             

            $ more expdp_UF3_full_norows.par

            userid="/ as sysdba"

                      directory=dpdir

                      dumpfile=expdp_UF3_full_norows.dmp

                      logfile=expdp_UF3_full_norows.log

                      PARALLEL=8

                      EXCLUDE=SCHEMA:"= 'APEX_040200'"

                      EXCLUDE=SCHEMA:"= 'APEX_030200'"

                      EXCLUDE=SCHEMA:"= 'APEX_PUBLIC_USER'"

                      full=y

                      content=metadata_only

             

            4. When you run content=metadata_only, I don't think acess_method=insert_as_select is needed. Also insert_as_select is going to be the slowest possible way for you to import any data that is in the dumpfile.

             

             

            In next run we will try to import metadata with out below parameter in par file and will see if we can reduce any runtime hours.

             

             

            ACCESS_METHOD=INSERT_AS_SELECT

             

            Thanks

            Kris

            • 3. Re: TTS import running long
              Dean Gagne-Oracle

              Hi Kris,

               

              The method you are using now (2 exports and 2 imports" was the old way (prior to version 12)    You may want to look into full_transportable.    It does the same function but in one export and one import.  I would like to see the results of your import (if you can afford to do one) with metrics = y and then post the results from your log file.

               

              I can't imagine what is taking so long.

               

              Dean

              • 4. Re: TTS import running long
                3696858

                Dear Dean,

                 

                Thanks for acknowledging back, sure we will take your suggestion into consideration and will get back to you with results once we have dry run.

                In mean time if we have any questions, will revert back on the same.

                Thanks

                • 5. Re: TTS import running long
                  3696858

                  Dear Dean,

                   

                  We have started export using full_transportable with exclude statistics, but we do need statistics as well to be exported and imported, so do you know or suggest any notes or process for the same..?

                   

                  Thanks

                  • 6. Re: TTS import running long
                    Dean Gagne-Oracle

                    Just a couple of questions

                     

                    1. why did you exclude statistics?

                     

                    2. Do you need the exact statistics, or can you generate new statistics?  - some dba's modify statistics (I don't know how) to force plans to behave in certain ways.  Those dba's want their statistics exported and imported.   If you didn't do that, then you can just generate new statistics.  I don't know all of the bells and whistles of that, but look into dbms_stats.  If you have questions, I can try to answer them, but not my area of expertise.

                     

                    -- If you need to export and import your stats later, you can just say

                     

                    expdp ... include=statistics dumpfile=stats_only.dmp

                     

                    impdp ... dumpfile=stats_only.dmp

                     

                    I'm not sure why you excluded them from your export job.  It doesn't hurt anything, but they are supported in full transportable export/import jobs.

                     

                    Thanks

                     

                    Dean

                    • 7. Re: TTS import running long
                      3696858

                      Dear Dean,

                       

                      Excluding statistics is just to reduce runtime, to update you on run times, initially with earlier runs we got total runtime to export dump took 24 hours and now with excluding statistics and running with full_transportable took just 02:20 mins.

                       

                      Thanks

                      • 8. Re: TTS import running long
                        Dean Gagne-Oracle

                        what a huge gain for you using transportable tablespace.  There was major change in version 12 on how statistics are exported and imported.  It may be worth it, if you have time, to try to export statistics to see how long it now takes.   The compatible version needs to be version 12 or higher.   Thanks for sharing those times.

                         

                        Dean

                        • 9. Re: TTS import running long
                          3696858

                          Dear Dean,

                          Today we have completed TTS import with full transportable tablespace  and below is the runtimes which is awesome and unbelievable for me and my management.

                           

                          Runtime in total related to Dumps with Stats

                           

                          1. Export à 04:15 mins
                          2. Import à14:43 mins

                           

                          I have one question, how do we know whether all the objects / data is being migrated from solaris to Linux..? or is there a post validation task to compare data in solaris with Linux..?

                           

                          Thanks  a lot.

                          • 10. Re: TTS import running long
                            Dean Gagne-Oracle

                            Hi,

                             

                            Data Pump does not have any post-import validation tool.  That is something that you would have to come up with because only you know your database. 

                             

                            Glad to hear that your new export/import times are acceptable.  That is awesome.

                             

                            Dean

                            • 11. Re: TTS import running long
                              3696858

                              Dear Dean,

                               

                              understood thanks for that. One more question in related to APEX.

                               

                              Part of the old process what we do is upgrade APEX from 4.0 version to 5.0 version on solaris and export workspace / files etc and import the same on Linux with APEX 5.0 version.

                               

                              But in this full transportable run, I see even though in export par file mentioned EXCLUDE for APEX Schemas, it took APEX tablespaces/ datafiles and imported on Linux.

                               

                              So now do we need to delete the existing datafiles of APEX on Linux and follow the old process of upgrading APEX on Solaris and export and import workspace / files to Linux..?

                               

                              please suggest.

                               

                              Thanks

                              • 12. Re: TTS import running long
                                Dean Gagne-Oracle

                                I'm a bit rusty on that level, but here are my thoughts.

                                 

                                When you say full=y, you get everything.  When you say transportable=always, the checking code for transportable doesn't take filters into account.  It just grabs everything it can move using the transportable method and moves it.   So I would have expected it to get the APEX tablespaces.   When you said exclude=schema=APEX, I would have thought that the objects owned by the APEX schema would not have been imported.   Since the tablespaces are owned by 'SYS' the exclude you did would have no bearing on those tablespaces.   I'm not sure why the objects that were owned by the APEX schema were imported, like tables, indexes, etc.

                                 

                                Dean

                                • 13. Re: TTS import running long
                                  3696858

                                  Dear Dean,

                                   

                                  We are re-running the export and import now with out below parameters in par file of export.

                                   

                                  EXCLUDE=SCHEMA:"= 'APEX_040200'"

                                            EXCLUDE=SCHEMA:"= 'APEX_030200'"

                                            EXCLUDE=SCHEMA:"= 'APEX_PUBLIC_USER'"

                                   

                                  As some how after the import, while am comparing APEX related ones, I can see datafiles were being populated on import, but cant see schema APEX_040200 on target side and tables under tablespace SOF_APEX_FILE & SOF_APEX_USER, when compared to Solaris one.

                                   

                                  So now the par file for export will be like below

                                   

                                  {soforpdb02}-orapf3> more expdp_PF3.par

                                            userid="system/*********"

                                            directory=dpdir

                                            dumpfile=expdp_PF3.dmp

                                            logfile=expdp_PF3.log

                                            metrics=y

                                            full=y

                                            transportable=always

                                            version=12

                                  {soforpdb02}-orapf3>

                                   

                                  Thanks

                                  • 14. Re: TTS import running long
                                    3696858

                                    Dear Dean,

                                     

                                    We have completed export and import of full transportable table tablespace without any EXCLUDE statements in par files of export.

                                     

                                    But still after the import is completed successfully in 12:20 hrs of runtime, and while upgrading APEX from 4.0 to 5.0 version we are facing issues.

                                     

                                    Is there any specific steps needs to be done post import for APEX 5.0 upgrade..?

                                     

                                    Thanks

                                    1 2 Previous Next