Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Question Unanswered: What is the fastest way to load data betwen two databases

    Where I work we are experiencing problems with the time it requires to refresh a test system with a copy of the Production systems data.

    Here is what we currently do

    Once a week we refresh our test system with data from production tables.

    We are working with approximately 20 gb of data

    The time it is taking us is around 8 hours

    The way the data is being transferred is through links to the prod DB from the test db ( insert into x (select * from x@y) )

    We are being told this is the best and oinly way to do this can someone offer some help?

    We have asked about export/imports and we have been informed it is too slow to create the dmp file, move it to the other server, and then import the data.

    We are running Oracle

    Any information would be greatly appreciated.

  2. #2
    Join Date
    Jul 2003
    don't believe someone else.

    If someone says it takes 12 hours to export/import, then prove it.
    Look into params for exp/imp like DIRECT PATH, etc.

    You should look into Oracle Data Guard or
    replication using RMAN.

    How about two different development areas that get updated
    alternatively every other day?
    This way every day you have a new copy of production with no
    wait time.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2004
    I do not believe them, I know real DBA must laugh when I say I have 20 gb of data and it takes me all night to replicate.

    We have tools in house that I know can provide us with help.

    We have Quest Central, OMS, PLSQL Developer, etc...

    Is there any one particular method anyone would recommend?

    The key to success in this area is speed, I need to get the data and have it there fast.

    I do not believe replication or data placement everynight is neccasary.

    We run with a specific schedule in mind and with the testing phase they like to keep that data static for a week , which is why we only refresh every weekend.

    Another concern is that as our projects grow and expand the data is just going to grow. I can not imagine trying to explain or put up with a 24 hour refresh .

  4. #4
    Join Date
    Jul 2003
    transportable tablespace?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    If you can afford downtime on production (ie for 10 minutes in the middle of the night), there is a quick and simple way.

    Assuming TEST can accurately reflect PROD (in pathnames etc), shut down PROD, do an exact file copy from PROD to TEST and start up test again.

    I have Oracle here, server based. All pathnames point to a single directory (control files, redo etc etc etc). I shut it down, do a full copy to my laptop, and then start up on the laptop, fully mobile. Total time taken is however long it takes to copy 3Gb over a 100Mb LAN. I can also copy back to the server when I return to the office if needed.

    This does require 'identical' instances on both machines. Naturally, on a PROD environment you are unlikely to have all datafiles, control and redo on the same drive, or under the same path. I would assume that as long as you can duplicate this on TEST it wouldn't matter anyway? I'm not a DBA, but I would also assume that as long as you know what you are doing, the actual path names and drive specifiers could be different, but there would be a bit of manual intervention to bring it back up. Maybe someone on here with more thorough knowledge of the database startup procedure/parameters can help there?

    To minimise downtime on PROD while taking the copy, you can copy it to local space on PROD, restart PROD and then do the LAN copy.

    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts