Oracle AutoUpgrade was made to make upgrading and patching easier. When doing so, there is a risk that we hide too much information and turn AutoUpgrade into a black box.
It has always been the intention that AutoUpgrade is fully transparent and enables you to see exactly what’s going on.
This new feature increases transparency by allowing you to get a list of all the checks that are performed before and after upgrading or patching.
How Does It Work?
Ensure that you have the latest version of AutoUpgrade:
Check : ORACLE_RESERVED_USERS
Description : Oracle occasionally adds new internal USERs and ROLEs as the database evolves. To avoid a name conflict in the upgraded version, a source database must not contain any USER or ROLE with a name that matches one reserved by Oracle in the target release.
Fixup Action : You must drop the following USERs or ROLEs from the database: {1}
Severity : ERROR
Fixup Stage : PRE
Min Version(inclusive) Check applies : NONE
Max Version(exclusive) Check applies : NONE
Check Introduced Version : NONE
Check Removed Version : NONE
Manual Fixup or Automatic : MANUAL
AutoUpgrade Only : NO
Run for Datapatch : NO
Severity may take one of the following values: INFO, WARNING, RECOMMEND, ERROR.
Fixup Stage tells you when AutoUpgrade executes the check: PRE (before), POST (after)
If Manual Fixup or Automatic is AUTO it means AutoUpgrade will fix any issues for you during fixups or deploy mode. Otherwise, it is something that the user must fix. Depending on the severity a fix is mandatory.
If AutoUpgrade also executes the check during patching, then Run for Datapatch is set to YES.
How Can I Use the Information?
First, this feature adds transparency. We don’t want AutoUpgrade to become a black box.
Second, it allows you to correlate the information with your own runbook. Perhaps you are performing some of the same checks and that’s an opportunity to trim your runbook. I have seen this countless times when I talk to customers. Their runbook has evolved over many years and often contain checks that are no longer needed or executed by AutoUpgrade.
Final Words
At the time of writing, there are more than 200 checks in AutoUpgrade:
I’ve patched my database to 19.25 using AutoUpgrade and out-of-place patching. How do I roll back, if needed?
Basically, a rollback is the same as patching the database. You just do it the other way around – from the higher to the lower Oracle home. But let’s look at the details.
AutoUpgrade
I’m glad to hear that the customer uses AutoUpgrade for patching. It’s my recommended method, and it has many benefits.
If you use AutoUpgrade to patch your Oracle Database, you can also use it to roll back, but only before going live:
java -jar autoupgrade.jar -restore -jobs <n>
n is the job ID of the patching job.
AutoUpgrade undoes everything it did.
AutoUpgrade relies on Flashback Database as its rollback mechanism. So, it’s no good if users have already connected to the database and added/changed data.
Allow me to repeat: Only use AutoUpgrade to roll back before go-live!
After go-live, you must roll back manually.
Manually
You can manually roll back at any time – even after go-live.
Imagine you want to roll back from 19.25 (the new Oracle home) to 19.24 (the old Oracle home). Here’s how to do it.
Update /etc/oratab and set the Oracle home to the old one.
Update your profile scripts to reflect the old Oracle home. It could be .bashrc.
Start the database in the old Oracle home.
export ORACLE_HOME=$OLD_ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba<<EOF
startup
EOF
Run Datapatch.
$ORACLE_HOME/OPatch/datapatch
Finally, you fix internal directories that point to paths inside the Oracle home:
@?/rdbms/admin/utlfixdirs.sql
Datapatch
When you roll back, you must execute Datapatch. It will automatically detect that you are rolling back and perform the necessary actions.
For each patch there is an apply script that brings changes into the database. Datapatch executes the apply script during patching.
For each apply script, there is always a rollback script. It will reverse the actions of the apply script. Datapatch executes the rollback script when you roll back.
You can learn much more about Datapatch in this video.
Normally, you would roll back to the Oracle home from where you came, but that’s not a requirement. This scenario is fully supported:
Patch from 19.23 to 19.25
Roll back to 19.24
How To Practice?
We have a hands-on lab in which you can try rollbacks – using AutoUpgrade and manually.
I’m a strong advocate for out-of-place patching, and I can see that many of my blog readers are interested in that topic as well. Thank you for that!
But a reader notified me about a specific issue that occurs during out-of-place patching of Oracle Grid Infrastructure 19c. The issue occurs when using OPatchAuto as well as SwitchGridHome.
Normally, I recommend creating a new Oracle home using the base release (so 19.3.0) and then applying the latest Release Update on top:
# Unzipping base release, 19.3.0
unzip -oq /u01/software/LINUX.X64_193000_grid_home.zip
# Install and patch Oracle home
./gridSetup.sh -ignorePrereq -waitforcompletion -silent \
-applyRU ...
However, that fails on AIX:
Preparing the home to patch...
Applying the patch /u01/software/36916690/36916690/36917416...
OPatch command failed while applying the patch. For details look at the logs
from /u01/app/19.25.0/grid/cfgtoollogs/opatchauto/.
The log file has a little more detail:
DeleteAction : Destination File ''/u01/app/19.25.0/grid/perl/bin/perl'' is not writeable.
Copy Action: Destination File ''/u01/app/19.25.0/grid/perl/bin/perl'' is not writeable.
The Solution
There is already a MOS note that describes a potential workaround:
First, you should continue to use out-of-place patching despite the above issue.
Second, instead of using the base release (19.3.0) as the basis for any new Oracle home, you must create a new base release. One that doesn’t contain the error that leads to the above issue.
On a non-prod system, create a brand-new Grid Infrastructure installation using the base release (19.3.0).
Use in-place patching to patch it to the latest Release Update (currently 19.25.0). You need to add a few parameters to the opatchauto command:
You now have a new base release. It is almost as pristine as the 19.3.0 base release. It just contains the additional Release Update (19.3.0 + 19.25.0).
When you need to patch another system, use out-of-place patching using SwitchGridHome. But instead of using the base release 19.3.0, you use your new gold image that is already patched to 19.25.0.
#Don't do this
#unzip -oq /u01/software/LINUX.X64_193000_grid_home.zip
#Do this
unzip -oq /u01/software/gi_gold_image.zip
When you install the using gridSetup.sh you don’t have to apply the Release Update because the gold image contains it already. You can still apply any one-offs you need.
Is it a viable workaround? Yes, I believe so. There’s a little more work to, on the other hard, you’ve now started to use gold images, which is a huge advantage.
If you continue patching in-place or out-of-place using OPatchAuto, be sure to clean up the Oracle home from time to time.
The issue occurs starting with Release Update 19.18 because that’s where Oracle started to add patches to Perl in the Oracle home.
Thanks to Axel Dellin for helping me with some details.
You should not let this little bump on the road prevent you from using out-of-place patching.
With the latest version, 24.8, AutoUpgrade can upgrade the RMAN catalog schema after patching and upgrading. This is useful to those who take RMAN backups and duplicate their RMAN metadata to a catalog database.
If you don’t upgrade the catalog schema after patching and upgrading, you’ll see this message in the RMAN output:
PL/SQL package RCO.DBMS_RCVCAT version 19.24.00.00. in RCVCAT database is not current
PL/SQL package RCO.DBMS_RCVMAN version 19.24.00.00 in RCVCAT database is not current
Details
After patching or upgrading, AutoUpgrade upgrades the RMAN catalog schema in the postupgrade stage.
AutoUpgrade connects with RMAN to the recovery catalog and issues the upgrade catalog command.
AutoUpgrade does not execute dbmsrmansys.sql. Normally, this is only needed for the upgrade of the first catalog schema of a given release (like for the first database on Oracle Database 23ai), and even then, it might not be needed.
How To
Specify the connect string to the catalog database in the AutoUpgrade config file:
<prefix>.rman_catalog_connect_string=catalogdb
catalogdb is a TNS alias to the catalog database.
Start AutoUpgrade to load the username and password for the recovery catalog:
You can enhance the solution using an after_action script that starts a level 1 backup after the job. The after_action script takes place after the postupgrade stage, where AutoUpgrade upgrades the catalog schema.
Version 24.8 of AutoUpgrade does not support this feature when you use the -patch command line option. This is coming in a later version.
When you enter the catalog credentials into the AutoUpgrade keystore, AutoUpgrade validates the credentials. Any errors result in AutoUpgrade returning the following message:
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.
Databases Are Fun 