Reducing downtime for database patching

[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