Hello,

We have two Baan servers, one production and the other pre-production. To keep the pre-production server up to date with production data we currently perform an offline backup of the production system and then use this backup to perform a restore on the pre-prod server.

I am looking for a quicker/easier way to do this data copy and I'm hoping somebody here may have a better solution for me.

I have looked at using transportable tablespaces but I believe you still need to run import/export for metadata?

What I want is to have a job set-up that overnight will replicate the production system on to our pre-prod server.

I would be grateful for any suggestions you may have.

Thanks

PJ

We've been working on the same thing and I would be interested in seeing other responses to this.

What we are looking at doing is using RMAN to create and maintain a standby database. Look in Oracle's support site and RMAN documentation for the ways to do this. If you have Oracle Data Guard (part of the Enterpirse edition), use that. If not, you can create a more manual process with RMAN.

With this process, you first create a database duplicate. Once this is initialized, use RMAN to copy the archivelogs from production to pre-production to keep the two databases in sync. You need to be using archivelog mode, or course. The archivelogs copy only the changes made since the last sync.

If that is no good, we have found using bdbpre and bdbpost much faster than using Oracle's expdp and impdp tools. I have not looked into why this is. In one test, impdp took about 6hrs to restore the database, bdbpost took less than 2hr. The files created by bdbpre are also much smaller and more compressible than expdp's, if that is an issue.

As I said, we are still trying to find our own way to doing this. This is just what we are thinking of so far.

Rob

first - the datapump should blow bdbpre/bdbpost away. so i'd spend some time looking into that.


The best solution i've ever seen is is purely a hardware solution.

Using a SAN -

Triple mirror your HDs for your datafiles in prod

1. Throw the Prod DB into hot backup
2. Break the 3rd mirror.
3. Take Prod DB out of hot backup.
4. Shutdown pre-prod
5. Take that set of disks and link it to a set of disks sitting on pre-prod
6. Force a 1 way mirror (overwriting the old disks with the new disks)
7. Once they are synced, break that mirror and re-mirror with prod (forcing prod to over-write the new set)
8. Start up pre-prod
9. Take Pre-prod out of hotbackup mode.

On my EMC array 10 years ago this entire process took 5 minutes for 120G of DB..

There are a couple of variations you can use by not re-syncing up the disks until later in the day (instant recovery/rollback is one of them, or taking those disks and attaching them to some other device for the nightly backup so that prod isn't impacted)

Dave

I have been able to do it by cloning the oracle database. Disadvantage of this is that your db must be down.
Only this can be done by making a backup with rman. The export can be used to rebuild your test database.

Transportable TS can only be used in an enterprise edition.

Regarding using the archived logs: I think you need a enterprise edition as well.

Regards,
Han

The SAN solution looks like a good one, if it is available. In our case, the second server is also a DR server, so it is in another location, in another country. So we need something that works over the WAN.

If you use RMAN to backup the database, you can create a clone of it from the RMAN backups. You have to copy the backup files to the other server. Follow the instructions here: http://www.orafusion.com/art_rman3.htm. This worked perfectly with our Baan database.

But this would be kind of hard to do over the WAN every night. So we are looking at using the archivelogs or incremental backups to keep the data current, and according to what I've read, RMAN can do this and it does not need the enterprise edition.

See the Oracle Advance Backup and Recovery Guide: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/title.htm#BEGIN

See especially part 3. While getting the link, I notice there is a chapter on using RMAN to create a Transportable TS, which lets you leave the database running while you do it.

In a past life, I used DataMirror (now an IBM product) for this purpose (www.datamirror.com).

We cost-justified it by providing a near-realtime (~ 15 minutes) backup copy of the production database for failover purposes.

The ability to keep a development database current in near-realtime was icing on the cake.

In SQl server there is an option to move transactions logs to another server to keep the two servers in sync. Basically what this will do is move the net changes in the data base to other server. This could be automated.
I would think oracle will have something like this

To go with database you wil have to sync up $BSE as well. This could be done through a batch job to copy the changed files..

By the way we at our company synch up our test machine with production, by restoring the backup and copying $BSE as you mentioned in your post

(Sujith)

Hi pjohns,
since you are running on linux you could use rsync.
What I do, nightly:

0. rsync $BSE
1. rsync the complete $ORACLE_HOME and all datafiles, redofiles, tempfiles, from the production machine to the pre-production. Be carefull to set rsync block size (for crc) equal to oracle block size.
2. put all oracle tablespace in "begin backup";
3. sync again oracle files (this sync will be quite fast, probably about 30 minutes)
4. put all oracle tablespace in "end backup".

I seem to remember that when you will start oracle in pre-prod you need a quick recorver because tablespace are in backup mode.

Bye,
Giuseppe

what i do when creating/refreshing our test env is the following


mount -o rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,tcp live-db:/backup /backup
mount -o rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,tcp live-db:/u02/archive on /u02/archive

export ORACLE_SID=baandup (this is the SID for the test/dev oracle instance)
$ORACLE_HOME/bin/rman TARGET sys/password@tns_name_for_live_box AUXILIARY /


then run this script :


run {
allocate auxiliary channel dupdb1 type disk;
duplicate target database to baandup NOFILENAMECHECK;
}


and about 20 - 25mins later i have a perfect copy of the live system