I got a service running on a classic 3 tier model: 2 web servers and 2 application servers connecting to a single Oracle database instance. Most of the business logic are written as stored procedures in the Oracle DB. The problem I am facing is that the service has to be taken down to update the stored proc each time bugs need to be fixed or new features have to be introduced.

I'm just wondering if there is any way to keep the service running while we are updating the stored proc. Approaches involving getting new hardware / software are also welcome.

  • You should have a look at pl/sql editions. – steve Nov 28 '13 at 3:42

Why does the service need to be taken down when you fix a bug?

You can deploy a new PL/SQL package without shutting anything down. If you are changing the interface between packages or to the middle tier this may be complicated by needing to keep a backwards compatible version of some procedures or functions around.

If someone is running the existing PL/SQL package then your install of a new package will block until the user exits as it needs to obtain a lock at the package level.

| improve this answer | |
  • But almost every package has some sort of session state. Re-installing the package will drop that session state and likely lead to ORA-04068: existing state of packages has been discarded. – Jon Heller Nov 28 '13 at 6:36
  • We have architected our application to retry if we get that error. We don't actually store anything we need in package state. – WW. Nov 28 '13 at 11:00

You want to use Edition-based redefinition.

The documentation, linked above, says it all really:

Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.

There are plenty of tutorials online that cover the functionality - Google is your friend.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy