Reducing downtime for database patching

[Clara Rowe]

Hi experts! I???m trying to find out how other companies are reducing the downtime required to apply Oracle database patches. We need our database up 24x7, but patching can take hours. We simply can???t afford the downtime to apply necessary patches. How are all of you handling this problem?

Clara

[Caesar_3]

Hello!

After we bring good working system we
try to not make patch update.
If we have no other option so we preparing
every thing for update and choose the best
time for and do it fast!

Another option is work in cluster so you
update one server, sync it and the you work
on the other.

Caesar

[Steven E. Protter · Accepted solution]

We do the following:

1) Quickly down the databse, split the mirror its sitting, on bring it back up.

2) Handle the database upgrade on a non-production server.

3) Down the database again.

4) Transfer in the revised version of the database to production, along with all system tables and such that were modified for the upgrade. We don't transfer back the data which has gotten out of date due to new transactions while we were working. Only database instance system data is migrated back in.

5) Bring the database up, resynch the mirrors.

The two down times for the production database are only for how long it takes to bounce the instance.

This doesn't work if a patch or process requires all data to be upgraded, but it works for most patches that change the executables and data in the oracle database systems tables.

I don't think oracle supports this methodology, but we actually have done it, so it does work.

SEP

[Michael Steele_2]

With a failover node in an MC/SG environment, of course. That's what 24x7 high availability is all about.

Sound's like an infra structure issue.

[Bryan D. Quinn]

Hey Clara,

On our production systems, as a general rule we do not make patch changes unless it is absolutely necessary. If we find that it is, the patch or patches are installed on our development and QA boxes first. Then those systems are monitored for any fluctuations in operation. If everything is sucessful then we perform the install on our production app servers first, if they are in need of the patch(s). Then we perform the install on our production box. As far as the time to do that, we have the luxury of a four hour maintenance window starting at 7pm every Sunday night. If there is no maintenance to be done, then we just reboot all of our boxes and let them go back to humming along. We the system was constructed and put into place, a maintenance window was one of the provisions that was bargained into the deal. That way we do not have to fight to schedule a downtime. We are supporting operations around the globe...so I am sure it is an inconvenience at some locations.

That is our spin on it.

-Bryan

[Bryan D. Quinn]

ooops...

Sorry Clara,

I missed the Oracle patches part! duhhhhh!!!

I apologize...but I think we handle the Oracle patches in roughly the same manor I described. We are an extremely conservative crew here, so we only make changes if it is absolutely necessary. No ifs ands or buts about it. The Oracle changes are handled during our Sunday night outage also.


-Bryan

[Yogeeraj_1]

hi clara,

patch only if it is necessary!

We prefer to install the patch on a test (development) server first. Test everything. After that, identify a suitable downtime period that would less affect our 24x7 operations. Then proceed as Mr. Protter mentioned about (well, in our own way ;) ).

We take no risks since our business depends on it.

regards
Yogeeraj

[twang]

I assume that for now you want to patch it up to 8.1.7.3 and later you want to install 9i
and migrate 8.1.7.3 to 9i.
If you have enough hardware resources I would suggest one thing - Create a new database with the patched up software. Then export the running database and import into the new database. This would not require a downtime for the live database.But you'll have to plan to incorporate the changes that are made in the source after the export has been taken into the new database.And it'll involve more work also though it descreases the downtime required.
If you are migrating to 9i this is infact one of the migration methods.

Further patching time depends on your processor speed and all.If you can upgrade to a more powerful processor that'll help in reducing the downtime.

[Massimo Bianchi]

Hi,
apart from others suggestion, each time we need to apply a patch we take all the time needed.

We know this means night or week-end, of even more easy on holidays, but we feel patch very risky and data alignig even more risky.

So, in case of updates, we take a test on develop and test, measure the time, and then we decide with the cutomer an appropriate time-frame.


This shall include, for every server if a cluster:
- backup offline of DB- executables
- install patch O.S. if needed
- kernel recompile if needed
- DB executable patching
- DB data (catalog, For example) updating
- DB backup tools test of backup POST- ACTIVITY
- test from key users

We cannot risk to forgot some data or to have some non-updated cluster.


You said that you cannot afford downtime for the patching.

The patch is necessary? Than take your time.

HTH,
Massimo

[Clara Rowe]

I don't think you guys are getting the whole picture here. We are applying Oracle application upgrades/patches. You can't resync mirrors, user Service Guard, or patch one instance and copy it to production because the upgrade/patch changes the database tables/schema. This means you can't successfully replay log files because the original tables have changed. So how can this be handled with little or no down time?

[Massimo Bianchi]

Clara,
i think that you answered by yourself the question.

If the patch changes schema and objects, you must get your downtime.

You can save time with the ricompilation, recompiling it on a second server with the exact same patch level, software and so on, and finally reporting it.

But with the oracle db... no way.
unless you can export/import data, but if your db is large, you get no advantage.

Massimo

[Yogeeraj_1]

hi again,

maybe you can consider having the database as read-only during the time you are installing the patch...Users being able to query, run reports etc...but no insert, update, deletes.

BTW, installation of patch did not take that much time on my RP5430 - less than an hour. The patch will never update your own data! only Oracle data...

regards
Yogeeraj

[Massimo Bianchi]

Hi,
Yogeeraj I'm not completely with you regarding the read-only mode during an patch, for many reasons.

You are absolutely right that oracle modifies only its data (the dictionary), and not the user data, but:

- oracle requires access to the database in read/write mode, to change the data dictionary;

- oracle requires esclusive access to the database;

- if a user issue a query involving system tables, i think the result is not straightforward;

Massimo

[bob hollis]

Think about what you are asking.
You want to patch oracle while the production database is running!
I think you have to bite the bullet and take an outage.
For small patches - take the db off line and do it then.
If the "patch" is an upgrade - then you build the new version take the production db off line and do an export and import it into the new version - no getting around it.

As others have mentioned - test all this first on a different server.

One thing we are doing to shorten outages is to "split" our production database into parts - multiple databases. Our application just wants data - doesn't care where it is stored. Our total upgrade time is longer, but it can be done in smaller chunks. Instead of one very long outage, we have a series of scheduled shorter ones.

[Clara Rowe]

Thanx guys. I appreciate your time and expertise. We have never had a database that was quite so critical and with Oracle application patches/upgrades taking 12 + hours, we are just looking for a way around it. We will take your advice and see what we can come up with.

Thanx again.

Clara

[bob hollis]

One thing I forgot to mention - I did this when I was upgrading an oracle financial system. That upgrade was a combination of changing both the database and the application and took more than a long weekend.
I don't know what your application is like, but I did this.
I let everyone know that any data entered after Wednesday would have to be "redone" in the new version. I made a copy of the production system Wednesday night onto a test server and pointed everyone at that. This let them run reports, etc., while I worked on the production upgrade. I had already done test runs and had all my scripts and stuff ready to go. I think we had production back online on Tuesday. Upgrading the Oracle Financial application was quite involved back then and this was the only way to do it.
Just one more idea for you to consider - good luck!

[Clara Rowe]

Thanx Bob. That sounds like exactly what we have to do, or should I say what our DBA's have to do.

I appreciate your help.

Clara