Re: Manual update / replication on a live database?

John, that REALLY helps. Thanks for all the details.

As for your query:

"Your requirements are for "continous" availability of the readonly copy are surprising. Is no downtime (not even a minute or so) allowed at all during the three "data freshening events" that occur each week? That seems surprising, given that the same requirements also seem to tolerate three very discontinous "jumps" in the data content seen by the users of the "read only" data. "

I agree with you; that sounds ridiculous - but where I stand now, Management has set down an edict for this project saying "no downtime".  I think that means that there should be no loss of service as far as users are concerned; discontinuity of data is not as much as an issue.

I agree that it makes no sense, but until I am able to get the "brass" to clarify on this and understand it from a technical point of view, I need to approach this "as-is".

Again, and also in reaction to the previous poster -- this is not my utopian scenario, and I know it's a bit ridiculous, but unfortunately it's not my decision to make. The isolation of this server is set in stone - it's a legal restriction, and there is no budging on that, period. The "no downtime" restriction may be one of managementese, that we can work through - but until I can solicit a viable solution with alternatives and timeframes, that's the word I unfortunately have to go by...

And so goes the world of work.. :)

John K. Hinsdale wrote:
> Oracle user with replication issues, tcp100 wrote:
>
> > We have two databases - one is our "active" database that takes in
> > transactions, and the other is a read-only "copy" at a remote location.
> > We'd like to update this "copy" database three times a week, but
> > since this remote database is not WAN connected, it'd have to be via CD
> > or other manual file transfer.
> >
> > The single requirement, however, is that we can't take either database
> > down to extract the data (on the "active" side) or to upload the data
> > (on the "read only copy" side). Both databases need to be able to be
> > accessed uninterrupted during any such maintenance procedures.
>
> Chris, a lot of how this might be approached will depend on size, both
> the raw size of your database in general as well as the incremental
> size (and nature) of the transactions applied between updates of the
> "copy" database. E.g., is is mostly INSERT's or is there a lot of
> UPDATE and DELETE.
>
> Knowing nothing else, though, below is one option I'd consider if it
> were me. It involves copying the ENTIRE database and not an
> incremental approach as you seem to be hoping for. I'm not sure an
> "offline" incremental solution is possible, but I would not know.
>
> Anyway, one way:
>
> (1) Run export ("exp") on LIVEDB, using CONSISTENT=Y to get a
> read-consistent snapshot of the database as of a point in time. Take
> this "exp" copy at a time of day where modification traffic
> (INSERT/UPDATE/DELETE) is at its lowest, if there is such a time of
> day. This will help avoid running out of redo space when you do a
> "consistent exp". Compress the dump file -- if you have a
> multiprocessor or multi-core machine, check out the awesome
> "pbzip2" utility; it will save you a factor of 8 or however many
> CPUs/cores you have. Copy this compressed export file to your CD/DVD
> burner, Commodore 64 cassette tape drive, or whatever totable storage
> media you plan to use. Don't forget to buckle your seat belt on the
> drive over to the non-WAN connected site ;)
>
> (2) On the "readonly" site, maintain either two separate schemas
> (SCHEMA1 and SCHEMA2) on a single instance, or two separate Oracle
> instances
> (INST1 or INST2). At any given time, call one of these
> schemas/instances the "active" read-only copy, and the other the "next
> to load" copy. Point client applications at the "active" copy.
>
> To freshen the "readonly" site's data, wipe clean all the objects in
> the "next-to-load" instance (or schema): you can DROP USER ... CASCADE
> and then just recreate the user. Uncompress and import ("imp") the
> file created in (1) to create a new version of the database with the
> fresher data.
>
> You'll need to keep track, perhaps in a config file somehwere, which
> of the two read-only instances/servers is the "active" one, and which
> is the "next-to-load" one, and to swap the two after each freshening.
> If it's a thrice-weekly thing it'd all be automated with scripts
> of course ;)
>
> (3) "Cut over" at the readonly site to access the new data. This can
> be done at one of two software "levels":
>
> (a) If you have two separate instances, you can update your TNS or
> Oracle names resolving info to "point" to the new instance. For
> example, if TNS alias clients use is "READONLY" and it resolves to
>
> host=somehost, port=1521, SID=INST2
>
> you can then update it so that "READONLY" resolves to
>
> host=somehost, port=1521, SID=INST1
>
> This will cause all clients that connect to the Oracle serivce
> "INST1" to now use the newer data on INST1. You now do your next
> re-load into server INST2. Note that you'll need unchanging TNS
> aliases for INST1 and INST2 to use for the freshening programs
> (incl. "imp") themselves!
>
> This is doing the cutover at the "Oracle service resolution"
> level.
>
> (b) If you have two separate schemas, you'd need to reconfigure ALL
> the client apps which connect to use, say SCHEMA2 now instead of
> SCHEMA1. This is probably much harder to do and definitely harder to
> maintain. This is doing the cutover at the "client application"
> level.
>
> (c) Another approach is to drop and re-create synonyms in some third
> schema whose name remains constant. But this will produced a momentary
> "hiccup" in availability.
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
>
> Your requirements are for "continous" availability of the readonly
> copy are surprising. Is no downtime (not even a minute or so) allowed
> at all during the three "data freshening events" that occur each week?
> That seems surprising, given that the same requirements also seem to
> tolerate three very discontinous "jumps" in the data content seen by
> the users of the "read only" data.
>
> Even so, approaches (3a) and (3b) above should ensure that no attempt
> to access the read-only copy is ever denied.
>
> Again, the nature of this approach is to dump and load the entire
> database. This will not work well if the database is so big it cannot
> be dumped (or re-loaded) in some sub-day amount of time (say, four -
> six hours). Then again, if the database is that huge you will also
> being to run into mundane issues of storing it on removable media for
> the transfer. You might consider DLT tape. And definitely look into
> "pbzip2". Where I work we have a 64-processor Linux box and that
> factor of 64 speedup in compressions is really nice ;)
>
> Hope that help,
>
> John Hinsdale
Received on Tue Jan 02 2007 - 16:31:06 CST