Currently, if you use a credential file to store the username/password of the Windows service, then AutoUpgrade delete it afterwards. We consider that the most secure approach.
But that’s not suitable for automation, so in the next version of AutoUpgrade, a config file parameter will allow you to re-use the credential file repeatedly.
Missing Registry Entries
If you use the credential file – depending on your configuration – you might see that entries are missing in Windows Registry for the database instance.
You can add those manually after patching by executing:
%NEW_ORACLE_HOME%\bin\oradim.exe -edit -sid %ORACLE_SID% -startmode auto
Oradim will see that registry keys are missing and add those.
This will also be automated in a future version of AutoUpgrade. We’re also working on adding support for virtual users.
Happy Patching
AutoUpgrade patching makes it easier to patch your Oracle Database.
Give it a try on Windows, and let us know if something is missing.
Patching Oracle Database should be as easy as patching a smartphone.
I want to apply the 19.25 Database Release Update. In addition, I must also apply the OCW Release Update to the database home. I take those from the GI Release Update that I used earlier. In addition, I want to apply the OJVM and Data Pump bundle patches. Those I must extract.
I install it in silent mode, but I could use the wizard instead.
You need to install the new database home in a way that matches your environment.
For inspiration, you can check the response file used in the previous database home on setting the various parameters.
If I have additional one-off patches to install, I add them to the comma-separated list.
I run the database root script:
[root@node1]$ $NEW_ORACLE_HOME/root.sh
I run just the database root script. Not the GI root script.
3. Prepare Database
I can do this in advance. It doesn’t affect my current environment and doesn’t cause any downtime.
I will move the database into a new Oracle home, so I need to ensure the database configuration files are either outside the Oracle home or move them to the new Oracle home.
I verify that my SP file and password file are stored in ASM – or at least outside the Oracle home:
I update any profiles (e.g., .bash_profile) and other scripts referring to the GI home.
As oracle, I update any profiles (e.g., .bash_profile) and other scripts referring to the database home.
5. Complete Patching
I complete patching of the database by running Datapatch (ensure the environment is set correctly):
[oracle@node1]$ env | grep ORA
[oracle@node1]$ $ORACLE_HOME/OPatch/datapatch
Most likely, there are other changes that you need to make in your own environment:
Update Enterprise Manager registration
Upgrade RMAN catalog
Update other scripts
Update /etc/oratab
That’s it! I have now patched my Oracle Restart deployment.
Happy Patching!
Appendix
Deinstall
In the future, I should remove the old Oracle homes. I use the deinstall tool in the respective Oracle homes.
I would recommend waiting a week or two until I’m confident the new Release Updates are fine.
CRS-0245: User doesn’t have enough privilege to perform the operation
If you get the following error:
[oracle@node1]$ srvctl modify database -d $ORACLE_UNQNAME -o $NEW_ORACLE_HOME
PRCD-1163 : Failed to modify database DB19
PRCR-1071 : Failed to register or update resource ora.db19.db
CRS-0245: User doesn't have enough privilege to perform the operation
Be sure to include patch 29326865 in GI and database home.
Run the srvctl modify database command as grid instead.
Be sure that the Oracle user is still set to oracle after running the command as grid:
[oracle@node1]$ srvctl config database -db $ORACLE_UNQNAME | grep user
Rollback
If you need to roll back, you more or less reverse the process. The switch home method works to a newer and lower patch level.
OCW Release Update
Thanks to Jan for commenting on the blog post. The initial version didn’t include the OCW Release Update into the database home, which is needed when the database is managed by Grid Infrastructure in any way.
Incorrect Information in ocr.loc
In the ocr.loc file for Oracle Restart, only the local_only property is used. All other properties can be ignored (like ocrconfig_loc).
In Oracle Database 23ai, the file will be cleaner in Oracle Restart. But for Oracle Database 19c there will be these superfluous properties.
With the latest release of Oracle AutoUpgrade, patching an Oracle Database is much easier. Once I’ve configured AutoUpgrade, it takes just one command. That includes finding and downloading the right patches and creating a new Oracle home.
Currently, AutoUpgrade can’t download the Oracle Database 19c base release (LINUX.X64_193000_db_home.zip). I must do that manually and place it in the folder specified by the config file entry folder. I expect the next version to handle it automatically.
$ cd /home/oracle/autoupgrade-patching/patch
$ ls -l
total 2987996
-rw-r--r--. 1 oracle oinstall 3059705302 Mar 18 2020 LINUX.X64_193000_db_home.zip
I need to load my My Oracle Support credentials into the AutoUpgrade keystore so AutoUpgrade can use them to download patches. The first time AutoUpgrade creates the keystore, and I must provide a keystore password:
$ java -jar autoupgrade.jar -config FTEX.cfg -patch -load_password
Processing config file ...
Starting AutoUpgrade Patching Password Loader - Type help for available options
Creating new AutoUpgrade Patching keystore - Password required
Enter password:
Enter password again:
AutoUpgrade Patching keystore was successfully created
I add my credentials. This is the username and password that I use to connect to My Oracle Support:
MOS> add -user daniel.overby.hansen@oracle.com
Enter your secret/Password:
Re-enter your secret/Password:
I save the keystore and convert it to an auto-login keystore:
MOS> save
Convert the AutoUpgrade Patching keystore to auto-login [YES|NO] ? YES
With one command, I will patch my Oracle Database:
$ java -jar autoupgrade.jar -config FTEX.cfg -patch -mode deploy
AutoUpgrade Patching 24.7.241021 launched with default internal options
Processing config file ...
Loading AutoUpgrade Patching keystore
AutoUpgrade Patching keystore was successfully loaded
Connected to MOS - Searching for specified patches
------------------------------------------------------------
Downloading files to /home/oracle/autoupgrade-patching/patch
------------------------------------------------------------
DATABASE RELEASE UPDATE 19.25.0.0.0
File: p36912597_190000_Linux-x86-64.zip - VALIDATED
DATAPUMP BUNDLE PATCH 19.25.0.0.0
File: p37056207_1925000DBRU_Generic.zip - VALIDATED
OJVM RELEASE UPDATE 19.25.0.0.0
File: p36878697_190000_Linux-x86-64.zip - VALIDATED
OPatch 12.2.0.1.44 for DB 19.0.0.0.0 (Oct 2024)
File: p6880880_190000_Linux-x86-64.zip - VALIDATED
------------------------------------------------------------
+-----------------------------------------+
| Starting AutoUpgrade Patching execution |
+-----------------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
patch> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs restored [0]
Jobs pending [0]
---- Drop GRP at your convenience once you consider it is no longer needed ----
Drop GRP from FTEX: drop restore point AU_PATCHING_9212_FTEX1921000
Please check the summary report at:
/home/oracle/autoupgrade-patching/log/cfgtoollogs/patch/auto/status/status.html
/home/oracle/autoupgrade-patching/log/cfgtoollogs/patch/auto/status/status.log
That’s it! My database is now running on Oracle Database 19.25.0.
This is the first release, and naturally, it comes with restrictions. We’re working hard behind the scenes to lift those requirements.
Will AutoUpgrade support other platforms?
Yes. AutoUpgrade supports most of the platforms where you run Oracle Database, including Windows, AIX and SPARCH Solaris.
What about in-place patching, using cloned Oracle homes, and support for gold images?
It’s in the plans.
Will you support other Oracle Database releases?
Yes – anything newer than Oracle Database 19c
What about Grid Infrastructure?
We’re focusing on Oracle Database. You should have a look at Oracle Fleet Patching and Provisioning.
What about …
We’re looking for new, great ideas. Please create an enhancement request in My Oracle Support and send me the number. I’ll check it and add it to our backlog. Feel free to also send me an email with your suggestion.
Reduce downtime to the time it takes to perform a switchover.
Test the patching procedure on the standby database.
I received an interesting question the other day:
I have the following two Data Guard configurations. I want to patch all the databases using standby-first patch apply. How do I do that when I have primary and standby databases running out of the same Oracle home on the same machine?
Requirements
In this case, the databases are on 19.17.0, and the customer wants to patch them to 19.23.0.
To use standby-first patch apply, you must meet a set of requirements, one being:
Data Guard Standby-First Patch Apply is supported between database patch releases that are a maximum of one year (1 year) apart based on the patch release date.
Here are the release dates of the following Release Updates:
19.17.0: October 2022
19.23.0: April 2024
So, in this case, the customer can’t use standby-first patch apply directly. There is a year and a half in between. They need to patch cycles in this case:
Patch to 19.21.0 (release October 2023)
Patch to 19.23.0 (release April 2024)
In the future, they should apply patches more often to avoid ending up in this situation again.
Patching Oracle Home
The customer has one Oracle home on each server from where both databases run. On any server, there is a primary and a standby database (from two different Data Guard configs).
The customer uses in-place patching. If they patch the entire Oracle home, it means one of the primary databases is now on a higher Oracle home than its standby database, which is not allowed.
The standby database is the only one which may run on a higher patch level.
Using the above configuration with primary and standby databases running out of the same Oracle home, you can’t use in-place patching and standby-first patch apply.
The customer must switch to out-of-place patching to achieve this. Then you can patch standby databases first, then the primaries.
The most important reason why you must patch your Oracle Database is security. The threats any company faces today is very different than 10 or 20 years ago. Especially with the introduction of ransomware, everyone is a target.
When I talk to database specialists, there is consensus about the importance of applying patches. Luckily, it’s rare nowadays that you have to argue with people over it. Further, I see more and more companies putting this higher on the agenda. I know patching is extra work, but you must do it. You must avoid the embarrassment and potentially devasting effect on your company, as explained by Connor McDonald.
How do you patch the Oracle Database? Release Updates are the vehicle for delivering security fixes to your Oracle Database; so far, so good. But what about the client? How often do you patch your Oracle Database clients? Do you need to patch the client?
As always, the answer is: It depends… on which clients you are using.
The Critical Patch Updates
The place to look for information about security issues is the critical patch updates (CPU). Be sure to check it every quarter when the new one comes out.
If a specific client is affected, it is listed. For example, check the CPU from January 2023. It lists a vulnerability in Oracle Data Provider for .NET.
If you look back a few more quarters, vulnerabilities in the client appears to be rare. But they do occur.
Client-Only
If you use client-only installations, you can check the Oracle Database Server Risk Matrix in the CPU. In the text, Oracle states whether the vulnerabilities affect client-only installations.
Here is an overview of the last three years showing whether the vulnerabilities affected the client-only installation.
To patch a client-only installation, you download the latest Release Update and apply it to the Oracle home, just like if it was the Oracle Database itself (the server).
You can use ORAdiff to find a list of included fixes.
Instant Client
For instant client, you download a new package and overwrite the existing instant client.
For OCI (Oracle Call Interface), you get the latest instant client and extract the relevant files from there.
ODAC
The Oracle Data Access Components (ODAC) package also contains other clients. You download the latest version and follow the instructions to unpack it.
Recommendation
For the database server, I strongly recommend:
Applying patches every quarter.
Using the latest Release Updates, although I do also understand why some people prefer to use the second latest Release Update (an N-1 approach).
For the database clients, I recommend:
Having a structured process to evaluate the risk when the Critical Patch Update Advisories come out every quarter.
Asses not only the security issues but also functional issues. Some drivers have a bugs fixed list. Use it to determine whether you use functionality that could benefit from the bug fixes.
Applying patches to your clients periodically. This ensures you have a structured and well-tested process. When it becomes urgent to patch your client, it’s easy because you’ve already done it so many times.
Oracle Grid Infrastructure (GI) uses some functionality from Apache Tomcat. You can find Apache Tomcat in the GI Home. How do you ensure that Apache Tomcat is up to date?
$ cd $ORACLE_HOME/tomcat/lib
$ java -cp catalina.jar org.apache.catalina.util.ServerInfo
Server version: Apache Tomcat/8.5.84
Server built: Nov 16 2022 13:34:24 UTC
Server number: 8.5.84.0
OS Name: Linux
OS Version: 4.14.35-2047.510.5.5.el7uek.x86_64
Architecture: amd64
JVM Version: 1.8.0_371-b11
JVM Vendor: Oracle Corporation
Oracle is continuously monitoring TOMCAT fixes for CVEs, once a fix is found and the fix the fix is in a object in one of the JAR files of the compact distribution, we start the process to incorporate the TOMCAT version with the fix in GI.
…
Patching outside of GI Release Updates is NOT supported.
My Security Team Flags Tomcat as Out-of-date
Many customers use tools to scan for vulnerabilities. Such tools might scan a GI home and find an out-of-date Apache Tomcat. To update Apache Tomcat, you must apply a newer Release Update.
If the latest Release Update does not contain a fix for a specific issue in Apache Tomcat:
You can find CVU in the GI home, but I recommend always getting the latest version from My Oracle Support.
3. Roll Back Node 1
The GI stack (including database, listener, etc.) needs to restart on each instance. But I do the rollback in a rolling manner, so the database stays up all the time.
I drain connections from the first node, copenhagen1.
This step restarts the entire GI stack, including resources it manages (databases, listener, etc.). This means downtime on this node only. The remaining nodes stay up.
In that period, GI marks the services as OFFLINE so users can connect to other nodes.
If my database listener runs out of the Grid Home, GI will move it to the new Grid Home, including copying listener.ora.
In the end, GI restarts the resources (databases and the like).
I update any profiles (e.g., .bashrc) and other scripts referring to the GI home.
I verify that the active GI home is the new GI home:
I roll back the second node, copenhagen2, using the same process as the first node, copenhagen1.
I double-check that the CURRENT_NODE environment variable gets updated to copenhagen2.
When I use crsctl query crs activeversion -f to check the cluster upgrade state, it will now be back in NORMAL mode, because copenhagen2 is the last node in the cluster.
5. Cluster Verification Utility
I use Cluster Verification Utility (CVU) again. Now I perform a post-rollback check. I do this on one node only:
My cluster is now operating at the previous patch level.
Appendix
SwitchGridHome Does Not Have Dedicated Rollback Functionality
OPatchAuto has dedicated rollback functionality that will revert the previous patch operation. Similar functionality does not exist when you use the SwitchGridHome method.
There is no real rollback option as this is a switch from OLD_HOME to NEW_HOME
To return to the old version you need to recreate another new home and switch to that.
Keeping the GI and database patch in sync is a good idea. But when you need to roll back, you are in a contingency. Only roll back the component that gives you problems.
Then, you will be out of sync for a period of time until you can get a one-off patch or move to the next Release Update. Being in this state for a shorter period is perfectly fine – and supported.
Like with Oracle Database, I strongly recommend patching Oracle Grid Infrastructure using the out-of-place method. It has many advantages over in-place patching
The two methods also move listener.ora as part of the process.
If you use opatchauto you should note that the tool moves listener.ora when preparing the new GI using opatchauto apply ... -prepare-clone. You can run that command hours or days before you move the listener. If you add things to listener.ora in between, you must also add it to listener.ora in the new GI home.
Conclusion
There is really nothing to worry about when you patch Oracle Grid Infrastructure out-of-place. The above-mentioned two tools will take care of it for you.
Standby-First Patch Apply allows you to minimize downtime to the time it takes to perform a Data Guard switchover. Further, it allows you to test the apply mechanism on the standby database by temporarily converting it into a snapshot standby database.
The scenario:
Oracle Grid Infrastructure 19c and Oracle Database 19c
Patching from Release Update 19.17.0 to 19.19.0
Vertical patching – GI and database at the same time
Data Guard setup with two RAC databases
Cluster 1: copenhagen1 and copenhagen2
Cluster 2: aarhus1 and aarhus2
DB_NAME: CDB1
DB_UNIQUE_NAME: CDB1_COPENHAGEN and CDB1_AARHUS
Using Data Guard broker
Patching GI using SwitchGridHome method
Let’s get started!
Step 1: Prepare
I can make the preparations without interrupting the database.
I can also test my application on the standby database.
At the end of my testing, I revert the standby database to a physical standby database. The database automatically reverts all the changes made during testing:
DGMGRL> convert database CDB1_AARHUS to physical standby;
Step 4: Switchover
I can perform the previous steps without interrupting my users. This step requires a maintenance window because I am doing a Data Guard switchover.
I check that my standby database is ready to become primary. Then, I start a Data Guard switchover:
DGMGRL> connect sys/<password> as sysdba
DGMGRL> validate database CDB1_AARHUS;
DGMGRL> switchover to CDB1_AARHUS;
A switchover does not have to mean downtime.
If my application is configured properly, the users will experience a brownout; a short hang, while the connections switch to the new primary database.
Step 5: Restart New Standby in New Oracle Homes
Now, the primary database runs on aarhus1 and aarhus2. Next, I can move the new standby hosts, copenhagen1 and copenhagen2, to the new GI and database homes.
I repeat step 2 (Restart Standby In New Oracle Homes) but this time for the new standby hosts, copenhagen1 and copenhagen2.
Step 6: Complete Patching
Now, both databases in my Data Guard configuration run out of the new Oracle Homes.
Only proceed with this step once all databases run out of the new Oracle Home.
I need to run this step as fast as possible after I have completed the previous step.
I complete the patching by running Datapatch on the primary database (CDB1_AARHUS). I add the recomp_threshold parameter to ensure Datapatch recompiles all objects that the patching invalidated:
Databases Are Fun 