9 Replies Latest reply: Apr 8, 2013 6:32 AM by Richard Harrison . RSS

    Script to execute impdp using SILO concept

    JP88
      Hi ,

      Could any one help me to write a shell script to perform impdp using dbms_datapump using SILO concept .

      Thanks
      JP
        • 1. Re: Script to execute impdp using SILO concept
          Richard Harrison .
          Hi,
          Excuse my ignorance - but what do you mean by SILO?

          Regards,
          Harry
          • 2. Re: Script to execute impdp using SILO concept
            JP88
            Hi Harry,

            SILO is one of oldest concept used in oracle .. SILO means schema name prefix with SILO value

            For eg: SILOA_SCHEMANAME,SILOB_SCHEMANAME both in same DB.

            we have 5 schemas running in source database without SILO concept.While importing the dump file of those 5 schemas we need to import the schema with prefix SILO

            Regards
            JP
            • 3. Re: Script to execute impdp using SILO concept
              Richard Harrison .
              Hi JP,
              Never heard it called that before (I've seen Silo used for lots of other concepts in IT but never in this particular way). Anyway back to your question, I'm still not sure why you want to use dbms_datapump rather than just impdp for this process - just using the command line makes for a far simple script i think?

              All you need to do is wrap the impdp command in a standard shell script wrapper.

              The impdp command would just need to be something like
              impdp user/pass dumpfile=xxx.dmp directory=xxx remap_schema=SCHEMANAME:SILOA_SCHEMANAME
              you can of course do this in dbms_datapump but its a lot more code to do that.

              Cheers,
              Harry
              • 4. Re: Script to execute impdp using SILO concept
                JP88
                Hi Harry ,

                Thanks for your reply .

                In my company for performing schema refresh we are using expdp/impdp using dbms_datapump through network link .Since it uses n/w link it takes 8 to 10 hours to complete the refresh .

                Now we shouldn't use n/w link and time should be decresed 4 to 5 hours and we are planning for another solutions like performing export only 1 time in source db and moving the dump file into respective boxes and then perform impdp using dbms_datapump have to use SILO concept to perform impdp.

                I have wrote the script to perform export now i need to write the script to perform import using SILO concept and the SILO value should call trough shell script

                Thanks
                JP

                Edited by: JP88 on Apr 4, 2013 3:39 AM
                • 5. Re: Script to execute impdp using SILO concept
                  Richard Harrison .
                  Hi,
                  How big is the schema? How far apart are the 2 servers? What exact version of oracle are you on? 8 to 10 hours sounds are very log time (unless we are talking hundreds of GB or TB of data) - most of the import time is usually index building rather than data copying - are you doing it in parallel?

                  We do a very similar thing where we import into one schema and then duplicate that out to other schemas in the same database using transportable tablespaces - this seems to be the quickest way to do schema duplication (and does allow for the schema name to be changed) - is that an option for you?

                  Cheers,
                  Harry
                  • 6. Re: Script to execute impdp using SILO concept
                    JP88
                    Hi Harry ,

                    We are using 11.2.0.3 and need to import 13 schemas totally 13 schemas wil be around 50 to 80 gb in size .

                    Could you pls provide me an example how to perform expdp/impdp using transportable tablespace.

                    Thanks
                    JP
                    • 7. Re: Script to execute impdp using SILO concept
                      Richard Harrison .
                      Hi,
                      Bit of a long example but should give you what you need - mentions doing it with/without needing the source tablespace in read only.

                      http://dbaharrison.blogspot.de/2012/08/transportable-tablespace-with-rman-no.html

                      Cheers,
                      Harry
                      • 8. Re: Script to execute impdp using SILO concept
                        JP88
                        Hi Harry ,

                        Does TTS support LOB datatype . we have a differtent tablespace for LOB datatype and separated tablespace for index .

                        Could you pls help me on this

                        Thanks
                        JP
                        • 9. Re: Script to execute impdp using SILO concept
                          Richard Harrison .
                          Hi Jp,
                          TTS works fine for lobs .
                          As long as the multiple tablespaces you're talking about only have relationships between each other and are not dependant on other tablespaces then you can do all of them as a set and it will work just fine. Just list the multiple tablespaces in the command. There is a routine to check if the tablespace grouping is valid or not (i.e. if they are missing dependencies of only they are copied) - you can find info on this here:

                          http://docs.oracle.com/cd/B14117_01/appdev.101/b10802/d_tts.htm#996814

                          Cheers,
                          Harry