RE: Application architecture & prod/pre-prod switcheroos

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]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!

--
http://www.freelists.org/webpage/oracle-l