The short answer is: Yes! The longer answer is: Yes, but very busy systems or in certain situations, you might experience a few hiccups.
The obvious place to look for the answer would be in the documentation. Unfortunately, there is no Patching Guide similar to the Upgrade Guide. The information in this blog post is pieced together from many different sources.
A few facts about patching with Datapatch:
- The database must be open in read write mode.
- You can’t run Datapatch on a physical standby database – even if it’s open (Active Data Guard).
- A patch is not fully installed until you have executed Datapatch successfully.
First, let me state that it is fully supported to run Datapatch on a running database with users connected.
- Install a new Oracle Home and use OPatch to apply the desired patches.
- Shut down the database.
- Restart the database in the new, patched Oracle Home.
- Downtime is over! Users are allowed to connect to the database
- End of procedure. The patch is now fully applied.
Often users move step 3 to the end of the procedure. That’s of course also perfectly fine, but it does extend the downtime needed and often is not needed.
What About RAC and Data Guard
The above procedure is exactly what happens in a rolling patch apply on a RAC database. When you perform a rolling patch apply on a RAC database, there is no downtime at all. You use
opatchauto to patch a RAC database.
opatchauto restarts all instances of the database in the patched Oracle Home in a rolling manner. Finally, it executes
datapatch on the last node. Individual instances are down temporarily, but the database is always up.
It is a similar situation when you use the Standby First Patch Apply. First, you restart all standby databases in the patched Oracle Home. Then, you perform a switchover and restart the former primary database in the patched Oracle Home. Finally, you execute
datapatch to complete the patch installation. You must execute
datapatch on the primary database.
Either way, don’t use Datapatch until all databases or instances run on the new, patched Oracle Home.
Yes, but I did write initally that there might be hiccups.
Datapatch connects to the database like any other session to make changes inside the database. These changes could be:
- Creating new tables
- Altering existing tables
- Creating or altering views
- Recreating PL/SQL packages like
Imagine this scenario:
- Database is restarted in patched Oracle Home.
- A user connects and starts to use
- You execute
DBMS_STATSmust be recreated to fix a bug.
- Datapatch executes
CREATE OR REPLACE PACKAGE SYS.DBMS_STATS .....
- The Datapatch session will go into a wait.
- User is done with
- The Datapatch session will come out of wait and replace the package.
In this scenario, the patching procedure was prolonged due to the wait. But it was completed eventually.
From time to time, we are told that Datapatch hangs. Most likely, it is not a real hang, but just a wait on a lock. You can connect to the database and identify the blocker. You might even want to kill the blocking session to allow Datapatch to do its work.
What will happen in the above scenario if the user never releases the lock on
DBMS_STATS? After a while, the DDL statement executed by Datapatch will error out:
ORA-04021: timeout occurred while waiting to lock object
To resolve this problem, restart Datapatch and ensure that there are no blocking sessions.
Really Busy Databases
I recommend patching at off-peak hours to reduce the likelihood of hitting the above problems.
If possible, you can also limit the activity in the database while you perform the patching. If your application is using e.g.
DBMS_STATS and locking on that object is often a problem, you can hold off these sessions for a little while.
Similarly, if Advanced Queeing is causing problems, perhaps it helps temporarily set
aq_tm_processes to 0. Or, in the case of the scheduler,
If nothing helps your situation, you can patch in restricted mode. But that means downtime:
SQL> startup restrict
SQL> alter system disable restricted session;
I don’t recommend starting in upgrade mode. To get out of upgrade mode a database restart is needed extending the downtime window.
Datapatch And Resources
How much resources does Datapatch need? Should I be worried about Datapatch depleting the system?
No, you should not. The changes that Datapatch needs to make are not resource-intensive. However, a consequence of the DDL statements might be object invalidation. But even here, you should not worry. Datapatch will automatically recompile any ORACLE_MAINTAINED object that was invalidated by the patch apply. But the recompilation happens serially, i.e., less resources needed.
Of course, if you system is running at 99% capacity, it might be a problem. On the other hand, if your system is at 99%, patching problems are probably the least of your worries.
What About OJVM
If you are using OJVM and you apply the OJVM bundle patch, things are a little different.
|Oracle Database 21c||Fully||No||No Datapatch downtime.|
|Oracle Database 19c + 18c||Partial||No||No Datapatch downtime, but java system is patched which requires ~10 second outage. Connected clients using java will receive ORA-29548.|
|Oracle Database 12.2 + 12.1||No||No||Datapatch must execute in upgrade mode.|
|Oracle Database 126.96.36.199||No||No||Similar to 12.2 and 12.1 except you don’t use Datapatch.|
Mike Dietrich also has a good blog that you might want to read: Do you need STARTUP UPGRADE for OJVM?
What About Oracle GoldenGate
You should stop Oracle GoldenGate when you execute
datapatch is done, you can restart Oracle GoldenGate.
If you are manually recompiling objects after
datapatch, I recommend that you restart Oracle GoldenGate after the recompilation.
The above applies even if the patches being applied does not contain any Oracle GoldenGate specific patches.
Oracle GoldenGate uses several objects owned by SYS. When
datapatch is running it might change some of those objects. In that case, unexpected errors might occur.
- Before starting the patching procedure and downtime, I recommend you recompile invalid objects.
- Always execute Datapatch with the
-verboseflag. This will give you much better information about is going on.
$ $ORACLE_HOME/OPatch/datapatch -verbose
- Always use the latest OPatch.
- Always use out-of-place patching, even for RAC databases.
Go ahead and patch your database with Datapatch while users are connected.
- Datapatch User Guide (Doc ID 2680521.1)
- Troubleshooting Assistant:12c Datapatch Issues (Doc ID 2335899.2)
- Oracle Patch Assurance – Data Guard Standby-First Patch Apply (Doc ID 1265700.1)
- RAC Rolling Install Process for the "Oracle JavaVM Component Database PSU/RU" (OJVM PSU/RU) Patches (Doc ID 2217053.1)
- Grid Infrastructure Out of Place ( OOP ) Patching using opatchauto (Doc ID 2419319.1)
4 thoughts on “Can I Run Datapatch When Users Are Connected”
running datapatch while users are already connected sounds good as it reduces downtime. But in case datapatch fails and you have to rollback the database (e.g. flashback to a GRP) you will lose data (committed transactions) of these users or of the application.
Therefore, as long as datapatch succeeds, fine. In case datapatch fails, you might lose data. How do you solve this?
Andreas Becker External On behalf of SAP AG Oracle Server Technologies – SAP Development T +49 6227 748276 E firstname.lastname@example.org@sap.com ORACLE Deutschland B.V. & Co. KG Hauptverwaltung: Riesstr. 25, D-80992 München
[Green Oracle]http://www.oracle.com/commitmentOracle is committed to developing practices and products that help protect the environment
Pflichtangaben/Mandatory Disclosure Statements: http://www.sap.com/company/legal/impressum.epx Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank.
This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation.
That’s a good question.
First, you should always test changes in a test environment that is similar to the production environment.
Second, if you have Data Guard you can follow the “standby-first patch apply” procedure. When the standby database is running in the new Oracle Home, you can convert it to a snapshot standby database and run the datapatch part. This will now test datapatch on your live production data. If that goes fine, you can revert the snapshot standby back to a physical standby database and follow the patching procedure.
Finally, if datapatch fails on the production/primary database, you don’t have to roll back to the GRP. Datapatch has rollback functionality as well that can revert the changes made. Datapatch rollback should be sufficient to bring you back.
Rollback to a GRP can be a solution, but it will never be “the only solution”. You can always get help from support if the datapatch rollback fails.
I run opatchauto apply successful now datapatch gave me errors and timeouts. I need to wait for the next window and low load. The question is how long can I wait and state running with the new binary already patched.?
I couldn’t find a specific statement about this scenario. Personally, I would get it done as soon as possible. You can run datapatch with users connected. If you start datapatch and it runs into concurrency issues, it will wait until the locks can be acquired.
I can’t give you a specific time. I guess if the database runs fine with no errors then you can wait for the next low-peak period. But I would get it done as soon as possible.