How do you patch standalone Oracle Database and how long it takes?
Each patch you download from Oracle of course has its README where you see the exact steps. But in general applying any patch on standalone Oracle Database consists of following high level steps:
Stop all Oracle Database processes
Apply the opatch part of the patch (patch oracle database software), for example:
unzip -d <PATCH_TOP_DIR> p36233263_<version>_<platform>.zip cd <PATCH_TOP_DIR> 36233263 $ORACLE_HOME/OPatch/opatch apply .
Startup all Oracle Database processes
Apply the datapatch part of the patch (patch oracle database dictionary), for example:
cd $ORACLE_HOME/OPatch ./datapatch -verbose
Estimate length can be about 20-30 mins for the opatch part and about the same for datapatch part, but this heavily depends on your environment and on the patch you apply. You should always have a test environment at hand to figure out how long the apply of specific patch will take on your production.
What patches you need to apply?
Oracle documentation provides us with very good summary:
Long story short, for Oracle database you have MRP, RU and OJVM patch. OJVM Patches are bit more complicated, so we’ll leave them out of this article.
RU = Release Updates available quarterly which contain highly tested bundles of pro-active patches. They always change the release number: 19.17, 19.23 etc ..
MRP = Monthly Recommended patches available monthly which contain only critical fixes and are always based on specific RU. These don’t change the revision number.
In most cases, you should be applying RUs only as they have about right frequency and contain most important and proactive fixes.
Do you need database downtime for RU/MRP apply?
Yes, always.
When I have Dbvisit Standby Database in place, can I apply first patch on standby and then do a switchover and apply patch on old primary to have zero-downtime patching?
No, sadly that’s not possible. Although you can shorten the downtime a bit with your standby database.
How do I patch my environment with Dbvisit Standby database?
Patching primary and standby database can done partly online, but downtime will always be needed. In this example we won’t make any attempt to shorten the downtime.
A. ONLINE: First step is to stop Dbvisit automated standby update on primary server:
$ /usr/dbvisit/standbymp/bin/dbvagentmanager auto-update modify --disable Trace log will be written to '/usr/dbvisit/standbymp/log/trace/dbvagentmanager_auto-update_modify_826809.trace.log'. Will update configuration SLASH (Oracle) between czlin0231 and czlin0232 (currently ENABLED with an interval of 300 seconds) to be DISABLED with an interval of 300 seconds ? Update 1 configuration(s) - are you sure? Yes Successfully updated configuration SLASH (Oracle) between czlin0231 and czlin0232
B. ONLINE: second step is to stop all oracle processes on standby server:
$ echo "shu abort" | sqlplus / as sysdba; lsnrctl stop
C. ONLINE: run OPATCH part of the patch on standby server:
$ cd $PATCH_TOP_DIR $ cd 36233263 $ $ORACLE_HOME/OPatch/opatch apply .
This was the last step which you can done online, now you have to start downtime for your production. You can of course wait till you proceed with next step (even few days). In that case for the duration of the wait you should start the standby database back to MOUNT mode and enable again the automated standby update.
D. DOWNTIME: you will need to stop all oracle processes on your primary server now (Make sure the automated standby update is stopped before):
$ echo "shu immediate" | sqlplus / as sysdba; lsnrctl stop
E. DOWNTIME: once done, run the OPATCH part on your primary server:
$ cd $PATCH_TOP_DIR $ cd 36233263 $ $ORACLE_HOME/OPatch/opatch apply .
When the OPATCH part finishes, you’ll be able to start back your primary database online and end the downtime.
F: ONLINE: Start Primary Database and apply the DATAPATCH part on primary server:
$ echo "startup" | sqlplus / as sysdba; lsnrctl start
cd $ORACLE_HOME/OPatch ./datapatch -verbose
You should run the DATAPATCH part without any delay after the OPATCH part and starting up the primary database. Clients can start connecting to the database while the DATAPATCH is running.
The DATAPATCH modifies database dictionary and there can be invalidation/recompilation of database objects which can cause some issues for clients.
This depends on your exact application and database. You’ll have to test this and make a call whether to allow clients to the production database before the DATAPATCH completes or not.
G. ONLINE: Start Dbvisit automated standby update on primary server:
$ /usr/dbvisit/standbymp/bin/dbvagentmanager auto-update modify --enable Trace log will be written to '/usr/dbvisit/standbymp/log/trace/dbvagentmanager_auto-update_modify_827529.trace.log'. Will update configuration SLASH (Oracle) between czlin0231 and czlin0232 (currently DISABLED with an interval of 300 seconds) to be ENABLED with an interval of 300 seconds ? Update 1 configuration(s) - are you sure? Yes Successfully updated configuration SLASH (Oracle) between czlin0231 and czlin0232
The automated standby update will afterwards apply the changes done with DATAPATCH part on the primary to standby database with the archived logs. Patching is now done.
NOTE: Steps provided above are generic and you should always consult patch README and check if there are any additional steps required.
How can Dbvisit Standby database help to shorten the downtime for patching?
You can apply the OPATCH part on the standby oracle database home (this is done online) and then use Dbvisit Switchover process to start the standby database read-write. So the only downtime needed will be for the Dbvisit switchover (step OS command details are the same as above):
A. ONLINE: First step is to stop Dbvisit automated standby update on primary server
B. ONLINE: second step is to stop all oracle processes on standby server
C. ONLINE: run OPATCH part of the patch on standby server
D. DOWNTIME: now you will need to stop your application and initiate Dbvisit database switchover
stop your production application and database listener on primary server
restart primary database
initiate Dbvisit switchover which will gracefully reverse the roles of primary and standby databases:
$ cd /usr/dbvisit/standbymp/oracle
$ ./dbvctl -d SLASH -o switchover
=============================================================
Dbvisit Standby Database Technology (11.7.1RC) (pid 828722)
dbvctl started on czlin0231: Wed Jun 12 16:00:30 2024
=============================================================
>>> Starting Switchover between czlin0231 and czlin0232
Running pre-checks ... done
=>Do you want to proceed with Graceful Switchover? [no]: yes
Pre processing ... done
Processing primary ... done
Processing standby ... done
Converting standby ... done
Converting primary ... done
Completing ... done
Synchronizing ... done
Post processing ... done
>>> Graceful switchover completed.
Primary Database Server: czlin0232
Standby Database Server: czlin0231
Wait until switchover completes which typically takes ~ 5-10 minutes. Now your new primary database which is running from former standby oracle home which has already applied the OPATCH part is ready for running the DATAPATCH and can be opened to clients.
F: ONLINE: Apply the DATAPATCH part on new primary server (formerly standby)
G: ONLINE: Apply the OPATCH part on new standby server (formerly primary)
H: ONLINE: Start Dbvisit automated standby update on new primary server (formerly standby)
The patching is now done and clients are connected to the patched primary database which runs on former standby server.
The benefit of this approach is that the DOWNTIME we need is only for the duration of the switchover - we don’t have the DOWNTIME for applying the OPATCH part on the primary server database home. Which implies that this approach is useful only in cases when OPATCH part takes really long and you can afford only few minutes of downtime.
The OPATCH part takes typically 20-30 minutes, so you need to consider if the added effort is worth the difference in DOWNTIME duration.
Is there any possibility at all for zero downtime patching for Oracle Database?
Yes, it’s possible to patch your Oracle Database without downtime, but not without interruption. If you have Enterprise edition and RAC then you can perform „RAC Rolling patch installation“. RAC system are composed of two or more nodes with running Oracle Instance.
From high level perspective (and considering 2 node RAC), the process looks approximately like so:
some time before patching (can be day(s) or hours), you can stop the database service on 1st RAC node or stop the whole Oracle Listener process. This will force all new application connections to 2nd node. This may result in 1st node being absent of any users, but can result in overloading of 2nd node. Also, this works only if your application refreshes its connections from time to time.
Stop Oracle Database instance on 1st RAC node. Any existing user sessions on 1st RAC node will get disconnected and will be able to immediately reconnect to 2nd RAC node
Apply OPATCH part of the patch on 1st RAC node
Start Oracle Database instance on 1st RAC node
Stop Oracle Database instance on 2nd RAC node. Any existing user sessions on 2nd RAC node will get disconnected and will be able to immediately reconnect to 1st RAC node. You can’t really use the stop service approach as you don’t want your system to be in half-patch state for a day. At this point if you have the possibility you could somehow „gracefully“ re-connect user sessions to 1st RAC node (but that would have to be managed by your application)
Apply OPATCH part of the patch on 2nd RAC node
Start Oracle Database instance on 2nd RAC node
Apply DATAPATCH part of the patch
Oracle defines downtime as situation when „sessions are unable to connect to the database“. Even with RAC, when patching user sessions WILL get disconnected and transactions rolled back (best case only when patching the 2nd RAC node), but sessions will be able to reconnect immediately, which Oracle doesn’t define as downtime.
I don’t have Enterprise Edition nor any Standby database, can I still somehow decrease my downtime for patching?
In SE2 there's „poor man’s RAC“ called Standard Edition High Availability (SEHA). SEHA is active/passive while RAC is active/active, but uses the same cluster technology as RAC.
You can perform same „rolling“ manner patch installation as with EE and RAC, but instead of nearly 0 downtime for only user session reconnection, oracle cluster will need to „move“ the database to 2nd node, which can result in about 3-5 minutes downtime. Application sessions will have to wait until the database gets started on the 2nd node.
It’s stop database on 1st node and start on 2nd. It’s somewhere in between EE RAC and SE2 + Dbvisit. But there’s no DR capability in SEHA concept – although you can add Dbvisit for having DR database, so you’ll get HADR with SE2.