RE: Application architecture & prod/pre-prod switcheroos
- From: <Peter.Hitchman@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Jan 2006 13:18:06 -0000
Hi,
Could you just leave it at changing the synonyms and then remember which one is
pre-prod? You could have a 3rd schema that simply stores the fact about which
of the other schema's is pre-prod and which is prod, and then use this during
the pre-prod loading process.
Pete
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Murching, Bob
Sent: 25 January 2006 00:48
To: 'Oracle-L Freelists'
Subject: Application architecture & prod/pre-prod switcheroos
Been racking my brain to see if there is a more... elegant architecture for the
following:
- Largish Oracle-based web app... fairly heavy traffic, up to 200GB of
application data. 10g, pretty fast storage, etc.
- Users query data from this application all the live long day, downtime not
possible
- Daily (or more often) we load/delete data into a "pre-prod" copy of the
current production (say, a couple of hours of sqlldr and pl/sql processing),
customer validates and authorizes it for production, then we must switch the
"pre-prod" environment (aka a 200GB schema) into production, then turn the old
production environment into the next "pre-prod" environment.
We have this in place, and it works, but it's not very elegant and requires
more DBA supervision than we'd like:
- Schema XYZ1 and XYZ2 in the same database. XYZ1 = production, XYZ2 is
pre-production, both are identical in the morning. Current production app
hitting XYZ1 schema via synonyms.
- We load data into XYZ2. Customer verifies and approves through an instance
of the web app hitting XYZ2 tables via synonyms.
- We swap the synonyms... now production web site connections are hitting XYZ2
and pre-prod web site connections are hitting XYZ1.
- We use expdp/impdp to empty XYZ1 and refresh it with the full contents of
XYZ2. It's scripted, but dangerous, so DBAs run this manually at least daily.
We thought about rman, transportable tablespaces, etc., but those required even
greater privileged access. At least the expdp/impdp process can run as a
regular user and therefore be handed off to a developer/operator/etc in some
packaged form... someday.
That's the system in a nutshell. I guess my question is, is there a better way
to architect a solution like this, so as to produce the prod-preprod swapping
w/o doing things as invasive as cloning schemas or tablespaces?
Thoughts appreciated!
Other related posts:
