Re: Manual update / replication on a live database?

Oh.. And for details, I think this will work.. We're dealing only with about half a million rows - the database size is about 700mb. Not too crazy at all.

Most of these are inserts, I will wager that there are no deletes save for the occasional development / administrative one, and few updates.

I think this process will work - of course incremental would be better, but from what I'm hearing, copying a redo log over and restoring it won't meet my zero downtime requirement -- but it could meet a "low" downtime requirement if reasonable.. (Unfortunately that's not where I stand.)

Thanks again for the advice...

-Chris

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:36:46 CST