This discussion is archived
9 Replies Latest reply: Apr 8, 2013 4:32 AM by Richard Harrison . RSS

Script to execute impdp using SILO concept

JP88 Newbie
Currently Being Moderated
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 . Expert
    Currently Being Moderated
    Hi,
    Excuse my ignorance - but what do you mean by SILO?

    Regards,
    Harry
  • 2. Re: Script to execute impdp using SILO concept
    JP88 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points