Online schema changes

ron

ron

Member
OE 11.7.17 -- RHEL -- Development on 9.3, Production on 7.9.

We are planning to move from OE 11.7.17 with "Classic AS" and RHEL 7.9 to OE 12.8 with PASOE and RHEL 9.3. Since our systems run 24/7, getting any down-time at all is really hard and getting a window of more than about 3 hours is close to impossible. So, the migration needs lots of careful planning and testing.

We are also developing a new set of SLDC procedures to be in place when the upgrades are done. One particular issue that has always been a problem is schema changes. We (like everyone, I suppose) wants to do them online. In our current OE 11.7.17 there are a number of schema changes that "can" be done online -- but they nearly all require every user to be disconnected. Our system has several always-connected applications and shutting-down even one of them requires about the same impact as if the whole system is brought-down. So -- we never do it -- we always shut-down the whole system -- make the changes and bring it all backup up. All of that needs a system down window of one hour.

We have read Progress' articles about online schema changes with OE12 and they "suggest" that online schema changes are a piece of pie. But is that correct? If using the "SCHEMA-CHANGE" attribute only means that processing will be paused for a while that would be fine ... so long as we don't have to shut-down the whole system.

Has anyone has experience with this?

Ron.
 
Dear Ron,

You can achieve the Upgrade to 12 version with minimal downtime, even you can get to around less than an hour downtime

I would suggest You to first upgrade your applications from 11.7 to 12.8 while connecting to same old database. As in progress world you can connect from 12 version application to 11 version database but the other way is not correct. Once you have the Applications already in 12 version, so the only part of the puzzle is the database upgrade. You can do an inplace upgrade of 11.7 to 12.8 on RHEL 7.9 with conv1112 utility which takes less than 5 minutes then have a replacement server RHEL 9.3, do an Backup restore while your production is up and running and then while and applying after image extents continuously and have a cut-over time where you shutdown your current production and restore the remaining after image extents and lastly restoring the last busy ai extent and Voila you have the system up and running on another server less than 1 hour

But in reality it will be a lot of work in background

Schema changes Mostly are online for 11 version for any addition's Like a new table index of a field, Modifying existing is not supported in 11 version but in 12 version they added more schema changes online


What schema changes are you targeting?

Regards,
 
Thanks very much for you reply!

I guess my post was a bit confusing -- we're all organised as far as the migration from 11.7 to 12.8 -- and PASOE -- and RHEL 7 to 9. It is a very big job and lots of testing is needed -- but I'm sure we can handle that.

The actual problem I'm focusing on is our SLDC process. Releases of new/changed software come along from time-to-time and can be anything from just adding or changing a few programs to large new sub-systems with new programs, new tables new indexes and maybe even a new trigger or two.

At present, if a schema change is needed it's a "very big deal" because we have to schedule a down-time window of anything from an hour to five hours to shut-down -- apply the new df -- and bring the system back up. Since it's a 24x7 system, getting a down-time maintenance window is really hard to organise and it tends to be very late at night and we need a team of testers on the system in the middle of the night to validate the change. If a schema change can be done online that would allow us to adopt a very different approach.

And so -- that's what I'm trying to determine -- exactly what changes can we do online in OE 12 with the system running and users connected and external interfaces 'active'.

The documentation suggests that we can do almost anything online -- but I think it would not allow any changes to existing indexes. New indexes can be created -- but inactive. So -- it all looks good except that it seems that sometimes (when index changes are involved) a re-index would be needed, and (I suppose) that means taking the system down (albeit for a fairly short period).

I was wanting to know what experiences others have had with making schema changes online in OE12.

Ron.
 
Dear Rob,

I need to know more details what kind of changes will require 1-5 hour downtime, in my experience even if i am doing the changes that are not online it will hardly take me a couple of min

Regards,
 
Hi Ron,

So -- it all looks good except that it seems that sometimes (when index changes are involved) a re-index would be needed, and (I suppose) that means taking the system down (albeit for a fairly short period).
=> Idxactivate could be used to avoid the downtime.

I have a question that could change a bit the "how to deal with this process" ,
Do you populate the new fields or do you leave thoses new fields to the default values ?

Patrice
 
The actual schema change rarely takes more than a few minutes -- as you say. That's no problem. "The" problem is coordinating with the external systems and 3rd parties that we connect-to ... then then shutting-down everything -- more than one DB and separate AppServers. (We plan to put PASOE on the server -- not on separate Windows servers as happens now.) I guess you could condense all that into simply saying that ANY down-time is a very significant problem for us and that's why I'm hoping that with OE12.8 and PASOE we can eliminate the need to shut-down at all.

Patrice -- I do not recall any time when we have populated values in new fields.

Ron.
 
OE 11 allows a number of online schema changes but in my experience they nearly all require no user session connections to be active when the change is made. Getting human users off the system is quite easy, but stopping the various external connections to our other systems and especially to external systems is really difficult and requires that to be planned and scheduled well in advance -- so much so that there is hardly any difference between shutting all of those system connections down and shutting-down the entire system.

Anyway -- all of this, I think, has wandered off the question. The Progress documentation says that nearly all schema changes can be done online with all connections to the DB unaffected (other than, perhaps, a short pause). I'm just interested to know if this is actually the case or whether their claim is a bit over-blown. I appreciate that they do say there are a few exceptions, like new indexes to existing tables will be inactive.

Ron.
 
It sounds like you might have -usernotify (or -dbnotify, they changed the name and I can never recall which one goes with which version and I am far too lazy to look it up right now…) set to zero. Aka “disabled”. That results in the behaviour that you describe of needing to restart stuff. Set it to a non zero value and you will be much happier with online schema changes.
 
You must log in or register to reply here.
Back
Top