How to handle one-way synchronization of two Oracle databases with no network connection?

Question

Here's the situation:

I have two Oracle 10g databases (with the same tables, etc.) that cannot be connected by network. At some regular intervals (say, once a day) I need to be able to update the data on database B to match that database A. Database B is for reading only, so there shouldn't be any changes to data made on it.

Since there's no network connection between them, I need to create some kind of file from the one that can be taken to the other and loaded easily. I'd like both exporting this file and importing it to the other database to be as fast as possible... ideally, it would only contain the minimum necessary information on exactly what changed since the last sync.

Obviously, I could do a full export of one database and replace the other with its data. However, this would take an unacceptable amount of time. I've also found some things about incremental export, but it seems like that is outdated and also exports all the data in any table that changed. This would be problematic since one particular table (that is very likely to change) contains large BLOBs and would take by far the most time to export and import fully.

I'm not a DBA so I don't have much expertise in this area. Is there anything well suited to doing this?

Answer 1

The simplest solution is to connect them via a non-network network. One way is to have them both connected to the same back-end storage (i.e. SAN/NAS) that allows you to have them either share the same volume or allows you to replicate one's volume to the other.

Beyond that, it really depends on the size of your DB and/or the size of the transaction logs if you're going to resort to sneakernet or RFC1149 for syncing up your DBs.

I would really question the "no network" requirement. I find it hard to believe that creating a VPN or other such tunnel isn't a viable option. Even under PCI and SOX requirements, there are network connectivity allowances for backups and HA cluster designs.

Answer 2

You might consider a logical standby database as the read-only copy, and cobble together a manual archive log transfer process that copies and registers all the logfiles each sync interval from your transfer media. This has the advantage that you're only using files naturally created by Oracle, and it's a pretty speedy process. It also incurs no downtime on the logical standby.

A handy section I found in the Data Guard documentation which could be helpful is the section on resolving archive log gaps.

EDIT: I just noticed you're not a DBA. You'll need a competent DBA's help to do this, not to mention the privileges required. However, most competent DBA's would consider this fun ;-)