Refreshing dev from prod?

[Mr.Hanky]

Hey Kids, long time eh?

OS HP/UX 11.11
Oracle 10.2.0.3

I am trying to find a better way to refresh a dev database from prod.
This is a 1 terabyte database and we are currently using imp/exp which as you can imagine is painfully slow.

We need to do this on a quarterly basis so I'd like to implement something more betterer.

I'm looking at rman cloning but that seems like it will be a PITA since these are on different unix boxes and the file systems do not match up, there are nearly 300 datafiles as well.

Another option is datapump but from what I hear it is faster on the way out but no so much on the way in.

We have flash copy in place on other systems but that technology is expensive, it may be in the cards but not yet.

Suggestions?

[hrishy]

Hi

is this SAN or direct attached storage.

If SAN then you can might ask your sysadmins to mount your dev voulmes to the production box put 1TB database in begin backup mode and then copy in parallel the files to the dev disks mounted on prod .

I am not very sure but my hunch is that its the fastest method to do what you want.

regards
Hrishy

[ebrian]

RMAN's DUPLICATE command can definitely simplify the process almost to the point where it can be automated. You can simply do your normal backups with RMAN and then copy them to the remote site. You have a couple options in regards to handling the different directory structure (Duplicating a Database on a Remote Host with a Different Directory Structure).

With 10g, you can also catalog backup pieces if the backup locations themselves are different (Cataloging Backup Pieces).

[Mr.Hanky]

Thanks guys, I was reading on the duplicating with rman but it seems it would require a lot of manual intervention since the mount points are not the same. It would also require a lot of load balancing and that might be a deal breaker for me.

[pando]

you write a script, adapt the mount points and use it for long time

[stmontgo]

Thanks guys, I was reading on the duplicating with rman but it seems it would require a lot of manual intervention since the mount points are not the same. It would also require a lot of load balancing and that might be a deal breaker for me.

Create a local standby and then pop it open. Yeah RMAN is much more elegant and probably worth it in the end however I offer plan B

[Bore]

My vote for RMAN duplicate :-)
What load balancing? I did not got you

Cheers

[Mr.Hanky]

My vote for RMAN duplicate :-)
What load balancing? I did not got you

Cheers

Well in prod we have 30 or so mount points dev only has about 12.
So I would have to look at all the mount points available in dev and all the datafiles and figure out which goes where without blowing anything up.
We should be getting more DASD soon so that will help.

I may still do the RMAN duplicate, it would be a fun project but time is not always a luxury with this application. There are too many things going on, and coding new requirements has to take priority.

I also agree with using dulpicate for the long haul, the only snag with that is keeping on top of the dev environment and duplicate scripts as production grows and datafiles are added and/or increased.
We are only about 1/2 through our retention period so this DB is going to grow quite a bit in the next year.

[tamilselvan]

If the storage array is provided by EMC, then I would go for EMC's BCV process. This process is very efficient and fast also.

2nd option I am thinking of SQL*Backtrack tool to backup the database, and retsore it on any server.

3rd Option is RMAN duplicate database.

[hrishy]

Hi Tamil

Assuming i dont have the BCV options but my prod data is on a SAN box can i mount the DEV disks on the prod san box and copy the datafiles ?

regards
Hrishy