04-28-10, 05:30 #1Registered User
- Join Date
- Aug 2009
Unanswered: question for DBA's | database upgrade and switch
Here is what our system looks
Primary oracle database 10.2.0.2
logical standby orcale database:10.2.0.2
Our system is machine critical and we get an average of 7 transactions per second during noraml time and may be 20 transactions during peak hours. There are 5 impotant tables which we are concerned the data.
TRANS tables is about 100M+ Rows,
MASTER is 100M+ rows
AUTH is 7m+
CAPTis less 100,000
SETTLE is less then 5000 rows. Each time we may need a mximum of last 10 days of data to process incoming transactions which is about 100,000 rows in the big tables and less then that in other tables.
Our plan is to upgrade both database to 11gSR2. Options we have is doing SWITCHOVER and which invloves a minimum of 3 minutes down time to a maximum of 30 minutes. Other option I suggestes is bit complex. create a maintenence database of small size with populating last 10 days of data. then put database insert trigger on the parent database and insert them all in the maintenence database after it was setup initially. But I still expect a gap between the time the maintenence database was setup and the trigger was created
Also bringing them back to the primary database also may be a pain
Oracle provide a solution but waaay expensive.Oracle golden gate tool which is very nice but our company cannot afford it this time. Other option is advanced replication which I need to research more and find out or oracle streams or materialized view which I am affarid to do it from primary database as it may affect the performance and also bringing the data back from the maintenence database to the primary after successfull upgrades
any other better solution if any would be really appreciated
04-28-10, 10:46 #2Registered User
- Join Date
- Mar 2010
- Vienna, Austria
I'm afraid, you'll have to go with the SWITCHOVER/SWITCHBACK - option.
I had exactly the same problem about a year ago with a customer upgrading from 10.2 to 11.1.
This was the base for the decision on the customer side to switch over to RAC (where you can do rolling upgrades for each node separately) in the long run ..."There is always an easy solution to every problem - neat, plausible, and wrong."
-- H.L. Mencken