Oracle XE as local recovery database and Oracle Standard as main db

Question

Hi, I just wanted to know what you guys think about this.
I have an app written in Visual Basic .Net as my front end and and Oracle 11g Standart database as the back-end. So I have like 20 pc's running this app locally. They're all inserting, updating, deleting data on this single database. I want to develop a solution in the case that the server database crashes or cannot stay on line. So i think of having oracle 10g XE on each pc. Thus all the data will be stored in the local db. I think about running a proccess once in a while (ex. every 15 minutes) to send/get the data to/from the main server. What do you think about this strategy?

Answer 1

Hi jbendahan,

This sounds like an horrendous idea. Duplicating data from one database to another is a complex subject. The process you're describing involves 20 duplications !

To be of any use in the event of a crash, you will also need a two-way replication mechanism: the 20 clients will continue to update their local DB. How do you deal with concurrent updates? The merging process alone with 20 databases will cost so much in resources it would have been cheaper to have a tried and tested professional DR (Disaster Recovery) process.

A true standby database on the other hand would be simpler to deploy, simpler to test, simpler to maintain and will cost less in resources. I suggest you don't reinvent the wheel :)

Edit:

By the way if you just want a backup and recovery plan, duplicating the database is NOT the solution. I suggest you read the online documentation about recovery:

• Oracle Database Backup and Recovery Basics
• Oracle Database Backup and Recovery Advanced User's Guide

Answer 2

I had the "pleasure" of trying to make exactly this sort of solution more robust on a SQL Server based POS system. As Vincent says, it's a complex process, fraught with unforseen nightmare scenarios and difficult to maintain code (e.g., ugly DOS .bat files I had to write). I would have to agree with him that it's a more robust solution to use a standby scenario.

That said, if your client won't spring for another license (and I do see their point) you seem to be stuck doing exactly this sort of thing. It can be done, but let your client know that the homegrown replication system is going to be a costly one, and will likely take quite some time to get the wrinkles worked out. It also probably won't scale well as the number of retail sites increases.

Answer 3

Oracle does have a mechanism for sharing data between databases, called Replication. Oracle XE supports Basic Replication (read-only and updateable materialized view site only). Obviously it depends on the specifics of your requirements, but from the little you have given us this might be a viable solution for you. Run each POS off its own Oracle XE database with regular synchronisations to the main (master) database.

Each POS has its data in updatable materialized views. That is, it can read and write its own data to the local XE database. These materialized views are part of a replication group which synchronizes their data with a master table in the main database. Going the other way the main database pushes its product data to read-only materialized views in the POS databases. The value of this architecture is that the POS always connect to their local XE databases, and never connect to the master database. This is a lot cleaner than connecting to the central database most of the time and switching to local databases in an emergency.

Unfortunately the documentation is a bit confusing, because it is called Advanced Replication and doesn't really mention "basic replication" at all. But Basic Replication covers most things - Advanced Replication is mainly Writeable Materialized Views and Multi-Master replication, neither of which you need anyway. I'm not saying Replication is easy, because it does cover some tricksy concepts. But using Oracle's built-in functionality has surely got to be better than rolling your own.

Note that your system would still be extremely exposed to the failure of the main database. Your client may think another Oracle license is a bit pricy (I wouldn't disagree). However, in extreme cases, failure to recover a database can kill a company.