Oracle-L: Re: High availability and upgrades

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Thu, 10 Jul 2003 14:54:25 -0800
Message-ID: <F001.005C44ED.20030710145425@fatcity.com>

Hi!

> In the case of 1), not only have you not given yourself high
> availability, you've actually reduced the MTBF for your system, since
> you'll go down twice and often. In the second case, you've got data
> consistency problems - log mining is fine and dandy, but how do you deal
> with a situation where a database is intermittently available?

Note that you can't achieve ultra high availability with only doubling systems anyway. Nor can you achieve this kind of availability by only making physical infrastructure redundant. You have go all the way up to client and also think on software design level (i.e. what happens when doing rolling upgrades etc..)

Let say we have a 2 databases with this transaction multiplexing system. Since we already have a change logging and detection system for rolling upgrades, we should use the same one for disaster recovery. 1) When DML 1 is issued, it's sent both to database A and B (it doesn't actually be the database but some middle data layer). App server gets acnowledge, that database A has committed and continues accepting requests. 2) Let say DML 2 is sent to data layers now, before doing any changes, the app server or data layer checks whether database B managed to commit previous DML (DML 1). If yes, it can proceed normally with DML 2. If no, it has to either send an error back and halting (possibly retrying) whole operations, which is of course unacceptable. But other way would be to activate this change logging mechanism, and record DML 1 to change log before DML 2 is handled. And since app server knows now that database B is down (and out of sync), it writes DML 2 to change log before to real table as well.

If database B comes up again and it can be recovered to last commit, then we can use the change logs of A to sync, because the DML 1 and DML 2 entries we have in change log are there exactly for that reason that database B was unavailable. And thanks to app server (which doesn't discard old transaction before it's committed in every mandatory database) we have all changes in A's change logs since the first change in B failed. (I hope I don't get too confusing, it's 1 AM here ;)

Or if we cant recover database B to latest commit, then we probably have to:

1) enable change logging in A
2) clone database A to B (possibly via BCV or snapshots)
3) purge (unneeded) change logs on B
4) sync from A->B
5) activate B

There's much more possibilities (and error-possibilities) when you have databases A, B and C for example ;)

Now that I read from OP's original message they wanted to use second database for reporting, I see that this solution is probably overkill.

Cheers,
Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Jul 10 2003 - 17:54:25 CDT