Oracle XE as local recovery database and Oracle Standard as primary db
I just wanted to know what you guys think about this.
I have an application written in Visual Basic.Net as my frontend and Oracle 11g Standard database as source. So I have 20 computers running this application locally. They all insert, update, and delete data in this single database. I want to develop a solution in case the server database crashes or cannot stay on line. So I think every pc has 10g XE oracle. This way, all data will be stored in the local db. I am thinking of running a process once in a while (like every 15 minutes) to send / receive data to / from the main server. What do you think of this strategy?
a source to share
Oracle has a mechanism for exchanging data between databases called replication. Oracle XE supports Basic Replication (read-only and updatable materialized site views) . Obviously, this depends on the specifics of your requirements, but from what you gave us, this might be a viable solution for you. Run each POS from its own Oracle XE database with regular synchronizations to the master (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 that synchronizes its data with the master table in the master database. In other words, the main database outputs its product data to read-only materialized views in the POS database. The implication of this architecture is that the POS always connects to the local XE databases and never connects to the main database. This is much cleaner than connecting to a central database most of the time and switching to local databases in an emergency.
Unfortunately the documentation is a little confusing because it's called Advanced Replication and doesn't really mention "basic replication" at all. But basic replication covers most of the things - extended replication is mostly writeable materialized views and multi-master replication, neither of which you need. I am not saying that replication is easy because it covers some complex concepts. But using Oracle's built-in functions should certainly be better than rolling your own.
Please note that your system will still be extremely prone to main database failure. Your customer might think that another Oracle license is a little expensive (I would disagree). In extreme cases, however, failure to restore the database can kill the company .
a source to share
This sounds like a terrible idea. Duplicating data from one database to another is a complex issue. The process you are describing includes 20 duplicates!
To be helpful in the event of a failure, you also need a two-way replication mechanism: 20 clients will continue to update their local database. How do you feel about concurrent updates ? The merge process with only 20 databases will cost so much resources that it would be cheaper to have a proven professional DR (disaster recovery) process.
On the other hand, a true standby database will be easier to deploy, easier to test, easier to maintain, and less expensive. I suggest you don't reinvent the wheel :)
Edit:
By the way, if you just need a backup and recovery plan, database duplication is NOT the solution. I suggest you read the online documentation about recovery:
a source to share
I had the "pleasure" of trying to make such a solution more robust on a SQL Server-based POS system. As Vincent says, it is a complex process, fraught with unexpected nightmare scenarios and difficult to maintain code (like the ugly DOS.bat files I had to write). I have to agree with him that this is a more reliable solution for using a fallback script.
That said, unless your client is springing for a different license (and I can see their point), you seem to be stuck doing exactly this kind of thing. It can be done, but let your client know that a homebrew replication system will be expensive and it will likely take quite a while to get wrinkles. It also probably won't scale as the number of retail sites increases.
a source to share