Best way to update DB nightly with data from another DB

ChaosAD

Good morning. This is my first trip into the land of replication with DBs. I'm used to just dealing with standalone DBs that work in their own little world. Now I need to have 2 DBs that are identical in every way. One is live and on the production side gathering data everyday in a transaction style environment. The other sits offline, but needs to have the data from the production DB updated to it every night. What is the best way to first setup the off network DB and then what is the best way to update it every night with the data from the production DB? One thing to take note of is that these servers can not talk to each other so I couldn't do any network talking. This would be burning a cd/dvd and then going to the other server and performing actions on it. Below is some info on the servers:

11.1.0.6G Oracle
AMD64 Servers running 64bit Linux (Debian / Ubuntu)
Database is 2 schemas and size is around 3GB

My ideas for right now would be to do a expdp/impdp but I haven't quite figured out how to just update the information and not have it add it again without dropping the schema first then adding it again via the impdp.

Thanks in advance.

Florian W.

Hi user7031399!

Do you know about RMAN? RMAN is able to clone a database. With this feature you can initiate the off network database for the first time. The kind of nightly updates depend on how many Mega Bytes of data you've to ship from one DB to the other. Export and Import is a simple way if the nightly update is not too big. If you've very big updates then I think that transportable tablespaces can be an alternative to exp and imp.

Hope this helps!

ChaosAD

Ah, thank you. That will work nicely to create for the first.

How would I use the expdp/impdp to update data nightly without having to drop the schema first? I tried doing DATA_ONLY and it just added the content to the content that was already there. So I ended up with basically 2 of all the old data along with anything new.

Florian W.

Hi again!

Look here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref364

ChaosAD

Thank you very much! I'll give that a reading at lunch today, but it looks very promising

I'll post with results.

JustinCave

Just to be clear, if you are using export and import (classic or DataPump), you would have to export the entire database every day. There is no option to export just the changed rows (barring cases where entire tables are static).

What is the business reason for having this separate server? It seems rather odd that you would want to have a database that is kept up to date with production but that cannot communicate with the production database. The fact that it is being updated nightly implies that it is some sort of stnadby system, but the fact that it is on a different network segment would tend to limit the effectiveness of the standby. The business rationale will heavily influence the technical choices you need to make depending on what people are going to do with this standby database. If users are going to be running tests against this other database, for example, the technical options would be radically different than the technical options if the other database were some sort of failover.

Justin