Re: Minimizing downtime for 9i to 10g upgrade
From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 15 Sep 2008 14:02:30 -0700
Message-ID: <1221512550.960909@bubbleator.drizzle.com>
>
> I am the OP and it sounds pretty much like what I expected. There is no
> magic bullet solution. Whatever is done to minimize down time must be
> done above the RDBMS layer.
>
> The database is not extremely large - perhaps 35g. However there are
> approx 500 tables, 1000 indexes, 25 lobs, 100 sequences, but the real
> pain is the 300+ foreign key constraints.
>
> I'm not sure the effort and risk are worth saving perhaps 30 minutes of
> down time.
>
> Are transportable tablespaces compatible from 9.2.0.6 to 10.2.0.4 (same
> o/s and hardware)?
Date: Mon, 15 Sep 2008 14:02:30 -0700
Message-ID: <1221512550.960909@bubbleator.drizzle.com>
Chuck wrote:
> DA Morgan wrote:
>> Chuck wrote: >>> DA Morgan wrote: >>>> Chuck wrote: >>>>> I need to upgrade a 9i database to 10g but the business unit cannot >>>>> tolerate any downtime. I've advised them the cleanest way is to upgrade >>>>> the db in place which takes about 90 minutes. They are uncomfortable >>>>> with that much down time. An exp/imp to a new 10g database would take >>>>> even longer. >>>> Why? you can do TB systems in seconds. >>>> >>>> First set up your 10g database. >>>> Then copy over all of the rows except for the last 24 hours. >>>> >>>> Then copy over all of the information not previously copied except for >>>> the last hour. >>>> >>>> Repeat for everything except the previous 2-5 minutes. >>>> >>>> Then switch them over. This is the few seconds. >>>> >>>> Then bring in that last five minutes worth of transactions which likely >>>> will never be missed in the minute or two it will take to capture them. >>>> >>>> This technique is aided by the use of partitioning and I highly >>>> recommend several practice runs to perfect your technique. >>> I think you must mean "terabyte" by tb. At first I thought you were >>> talking about some software product by a company called "tb systems" :-). >>> >>> How do you automate the copy of rows to know which ones were updated in >>> previous hour, 2-5 minutes, etc.? Are you suggesting converting every >>> table in the database to a materialized view, with an mview log, and >>> refreshing periodically? Is this supported across different major >>> releases? >>> >>> I would think whatever you're talking about needs to be SQL based as I >>> dont believe log shipping is supported between major releases. >>> >>> >>> Can you please clarify? Thanks. >> One way is to add a column to each table. Not pretty. Another is the >> temporary use partitioning since the database will be blown away in >> a week or less. A third way is with DBMS_WM (workspace management). >> Give me a minute or two and I can likely come up with a few more. >> >> Again it all depends on what is going on, the size, dependencies, etc. >> None of which were stated by the OP.
>
> I am the OP and it sounds pretty much like what I expected. There is no
> magic bullet solution. Whatever is done to minimize down time must be
> done above the RDBMS layer.
>
> The database is not extremely large - perhaps 35g. However there are
> approx 500 tables, 1000 indexes, 25 lobs, 100 sequences, but the real
> pain is the 300+ foreign key constraints.
>
> I'm not sure the effort and risk are worth saving perhaps 30 minutes of
> down time.
>
> Are transportable tablespaces compatible from 9.2.0.6 to 10.2.0.4 (same
> o/s and hardware)?
I am slightly embarrassed to say this but I don't know since I haven't seen 9.2.0.6 in about 5 years. It might be that it is and it might be that this was handled in 9.2.0.8 so check the docs.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Mon Sep 15 2008 - 16:02:30 CDT