1 Reply Latest reply on Aug 28, 2019 1:25 PM by Dean Gagne-Oracle

    Transaction consistency question on impdp with NETWORK_LINK


      I have a question that I have not been able to find the answer for in combing through the documentation so I am asking it here in hopes someone can point me in the right direction.


      Our shop is getting ready to do an upgrade of a very large database and we are hoping for minimal downtime (of course).  I was considering using impdp with NETWORK_LINK to do  a full import into the new instance.


      My question is:  Do we need to stop the users from performing any transactions on the source database during the entire duration of the import in order to get a consistent import?  Is there a benefit to setting a CONSISTENT parameter in conjunction with a NETWORK_LINK import, and if so what is the limit on the time it can be consistent to?  I am secretly hoping that impdp can keep the transactions consistent all the way up through the completion of the import, but I fear that is not the case


      I am open to suggestions - thanks


      Wylie Merritt
      DBA - faa.gov

        • 1. Re: Transaction consistency question on impdp with NETWORK_LINK
          Dean Gagne-Oracle

          With no additional parameters, export and network import are consistent only at the table level.   So if table scott.emp is being exported between scn 1000 and 1100, any changes to scott.emp between those scn values will not be included, but if table hr.employees was being exported between scn 1100 and 1200 and some changes were done at scn 1050, those changes would be exported.  In this scenario, table scott.emp and hr.employees are not consistent with each other but they are consistent within themselves.   If you need a consistent job, then you need to use something.   The consistent parameter is an exp/imp parameter.  I know it converts to a datapump paraameter, but I just don't remember what it does.   The Data Pump parameter you want to use is either flashback_time or flashback_scn.   Flashback_time selects an scn that is closest to that time and uses it for the complete job.   You can do something like:


          impdp user/password network_link=dblink1 ... flashback_time=sysdate ...


          This will choose the current scn and use it for all data in the database.   What is does not due is use this scn value for metaata.  So, if you export tables at time 12:34 and you create a new table and index at 12:36 and then export indexes at 12:54, then you will get the new index but not the table.   At least that is how I remember it working.


          Hope that helps.




          p.s.  If I had to guess, consistent converts to flashback_time.  If you use it, Data Pump will tell you what it converted to.